Databend September Monthly Report: AUTOINCREMENT and Row-Level Security

Hey Databend community! 🚀

September focused on production readiness across security, data integrity, and performance. We shipped row-level access controls with statistics privacy, declarative check constraints, AUTOINCREMENT columns, zero-downtime table swap, and intelligent error correction. These features bring Databend closer to enterprise-grade database capabilities.

By the Numbers

This month: 15+ new features, 30+ bug fixes, 15+ performance optimizations, and 10+ other improvements. The highlights: security enforcement, schema-level data validation, and performance improvements for large result sets.

Monthly Highlights Summary

🔥 Major New Features

• Row Access Policies & Stats Privacy - Fine-grained row-level security with automatic statistics hiding
• AUTOINCREMENT keyword - Automatic ID generation with managed sequence lifecycle
• Check Constraints - Schema-level data validation with constraint enforcement
• Result set spilling - Unlimited query result sizes via automatic disk spilling
• SQL error correction - Intelligent suggestions for common syntax mistakes

🛠 Usability Improvements

• Table SWAP - Single-transaction atomic table name swap for zero-downtime schema evolution
• Pivot enhancements - Arbitrary ORDER BY expressions in pivot queries
• Procedure RBAC support - Permission control for stored procedures
• Enhanced sequence options - Configurable START and INCREMENT parameters
• Worksheet session reset - Session state management for multi-query workflows

⚡ Performance & Infrastructure

• Virtual column write optimization - Faster JSON data ingestion
• Meta-service snapshot streaming - More efficient cluster synchronization
• Join partition refactoring - Reduced memory amplification
• Distributed recluster - Better table maintenance at scale

🐛 Stability Improvements

• 30+ bug fixes across query execution, transactions, and meta-service
• Better spill handling - Fixed nullable data loss in result spilling
• Improved vacuum operations - Better cleanup of dropped tables
• Transaction reliability - Fixed DDL auto-commit in procedures

What's New and Game-Changing

🔐 Row Access Policies

Fine-grained row-level security with automatic statistics hiding.

-- Define policy once
CREATE ROW ACCESS POLICY tenant_isolation AS (tenant_id INT)
RETURNS BOOLEAN -> tenant_id = CURRENT_TENANT();

-- Apply to table
ALTER TABLE customer_data
    ADD ROW ACCESS POLICY tenant_isolation(tenant_id);

-- All queries automatically filtered
SELECT * FROM customer_data;
-- Users only see their own tenant's data

What you get:

• Automatic row filtering applied at query execution
• Statistics hidden to prevent data leakage
• Perfect for multi-tenant SaaS and compliance (GDPR, HIPAA)

🎯 AUTOINCREMENT

Automatic ID generation without manual sequence management.

Before:

CREATE SEQUENCE user_id_seq START 1 INCREMENT 1;
CREATE TABLE users (id INT DEFAULT NEXTVAL('user_id_seq'), name VARCHAR);
-- Manual cleanup needed

After:

CREATE TABLE users (
    id INT AUTOINCREMENT,
    name VARCHAR,
    email VARCHAR
);

INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');
-- ID automatically generated: 0, 1, 2...

Flexible syntax:

-- Basic usage
id INT AUTOINCREMENT

-- Custom start and step
order_id INT AUTOINCREMENT (1000, 10)  -- 1000, 1010, 1020...

-- MySQL-compatible
invoice_id INT IDENTITY START 1000 INCREMENT 10

-- Multiple columns supported
CREATE TABLE events (
    event_id INT AUTOINCREMENT,
    sequence_num INT AUTOINCREMENT,
    data VARCHAR
);

What you get:

• Sequences automatically created and cleaned up with table
• MySQL/PostgreSQL/Snowflake compatible syntax
• No orphaned sequences, no manual maintenance

✅ Check Constraints

Schema-level data validation.

CREATE TABLE products (
    id INT AUTOINCREMENT,
    name VARCHAR NOT NULL,
    price DECIMAL(10,2),
    discount_percent INT,
    stock INT,

    CONSTRAINT price_valid CHECK (price > 0),
    CONSTRAINT discount_range CHECK (discount_percent BETWEEN 0 AND 100),
    CONSTRAINT stock_non_negative CHECK (stock >= 0)
);

-- Valid data accepted
INSERT INTO products (name, price, discount_percent, stock)
VALUES ('Widget', 59.99, 10, 100);

-- Invalid data rejected
INSERT INTO products (name, price, discount_percent, stock)
VALUES ('Gadget', -10.00, 150, -5);
-- Error: CHECK constraint failed

What you get:

• Data validation enforced at the database level
• Constraints managed via

  ALTER TABLE ADD/DROP CONSTRAINT
  

• Query

  system.constraints
  

  to view all constraints

🔄 Table SWAP

Single-transaction atomic table name swap for zero-downtime schema changes.

The Problem:

-- Traditional approach: Two-step RENAME with gap
ALTER TABLE orders RENAME TO orders_backup;  -- Step 1
ALTER TABLE orders_v2 RENAME TO orders;      -- Step 2
-- ⚠️ orders doesn't exist between these steps, queries fail

The Solution:

-- 1. Create new table with updated schema
CREATE TABLE orders_v2 (
    order_id INT,
    user_id INT,
    amount DECIMAL(10,2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,  -- New column
    status VARCHAR DEFAULT 'pending'                  -- New column
);

-- 2. Migrate data offline
INSERT INTO orders_v2 SELECT *, CURRENT_TIMESTAMP, 'completed' FROM orders;

-- 3. Atomic swap in single transaction
ALTER TABLE orders SWAP WITH orders_v2;
-- ✓ orders and orders_v2 swap names atomically
-- ✓ Application accesses orders continuously, zero downtime

Use Cases:

• Adding/removing columns on large tables
• Table schema refactoring
• Data type changes
• Partition strategy adjustments

📝 SQL Error Correction

Intelligent suggestions for common SQL mistakes.

SHOW TABLE;
-- Before: "unexpected TABLE, expecting STATUS, TABLES..."
-- After: "Did you mean SHOW TABLES or SHOW TABLE FUNCTIONS?"

VACUUM TEMPARE FILES;
-- Suggestion: "Did you mean VACUUM TEMPORARY FILES?"

What you get:

• Clear suggestions based on common SQL patterns
• Faster debugging with helpful error messages
• Works for typos and incomplete commands

Other Notable Improvements

📊 Query Optimization

• Pivot with ORDER BY - Now supports arbitrary expressions in pivot ordering
• CTE improvements - Better optimization for materialized common table expressions
• Runtime filter enhancements - Faster joins with dynamic predicate pushdown

🔧 Operational Improvements

• Distributed recluster - Better parallelization for table maintenance
• Improved vacuum - More efficient cleanup of dropped tables and old snapshots

🛠 Meta-Service Enhancements

• Streaming snapshot protocol - V004 for more efficient state synchronization
• Better observability - Enhanced logging and metrics
• Separate runtime for Raft - Improved performance isolation

September was about production readiness. Row-level security enforcement, schema-level data validation, automatic sequence management, and performance improvements for large result sets. These features bring Databend closer to feature parity with enterprise databases while maintaining its cloud-native architecture.

Multi-tenant applications get fine-grained security controls. Schema designers get declarative constraints. Applications get automatic ID generation. Large queries get disk spilling. All production-ready features for real-world workloads.

Try it today: https://databend.com

The Databend Team