SQL Reference

DB9 implements a PostgreSQL-compatible SQL engine with pgwire protocol v3, a cost-based optimizer, and TiKV-backed distributed storage. Most PostgreSQL clients, ORMs, and drivers work without changes.

What You Can Do

Task	Where to look
Create tables, indexes, views, types	DDL
Insert, update, delete, upsert	DML and Queries
JOINs, CTEs, window functions, subqueries	DML and Queries
Transactions, savepoints, isolation levels	Transactions
Data types and coercion rules	Data Types
100+ built-in functions	Built-in Functions
PL/pgSQL, triggers, sequences, custom types	Advanced SQL
Roles, users, grants	Auth and Roles
Session parameters and GUC settings	Session Parameters
Engine and extension limits	Limits
System catalog tables	System Catalog

PostgreSQL Compatibility at a Glance

Fully supported

• Wire protocol — pgwire v3 with Simple Query and Extended Query (Parse/Bind/Describe/Execute)
• DDL — CREATE/ALTER/DROP TABLE, indexes (btree, GIN, GiST, HNSW), views, materialized views, schemas, sequences, functions, triggers, types, collations
• DML — INSERT, UPDATE, DELETE with RETURNING; INSERT ON CONFLICT (upsert)
• Queries — JOIN (inner, left, right, full outer, cross, lateral), CTEs (including recursive), window functions, subqueries (correlated, EXISTS, IN/ANY/ALL), set operations (UNION/INTERSECT/EXCEPT)
• Transactions — BEGIN/COMMIT/ROLLBACK, savepoints, autocommit
• Data types — boolean, integer, bigint, double precision, numeric, text, varchar, bytea, timestamp/timestamptz, date, time, interval, uuid, json/jsonb, arrays, serial/bigserial, vector, tsvector/tsquery
• PL/pgSQL — functions, procedures, control flow, exception handling
• Triggers — row-level and statement-level, BEFORE/AFTER on INSERT/UPDATE/DELETE
• Indexes — btree (default), GIN, GiST, HNSW (vector), partial indexes, expression indexes, CREATE INDEX CONCURRENTLY

Partial or different from PostgreSQL

Feature	DB9 behavior
SERIALIZABLE isolation	Accepted but downgraded to REPEATABLE READ (TiKV snapshot isolation). A warning is logged.
SMALLINT / INT2	Aliased to INTEGER (32-bit).
REAL / FLOAT4	Aliased to DOUBLE PRECISION (64-bit).
CHAR(n) fixed-length	Not supported. Use VARCHAR(n) or TEXT.
Timestamp precision	Millisecond precision. Fractional-second syntax (0) through (6) is accepted but sub-millisecond values are not representable.
GIN index execution	The planner produces GIN scan plans (visible in EXPLAIN), but the runtime currently falls back to table scan.
Encoding	UTF-8 only. No other server or client encoding.

Not supported

These PostgreSQL features are not available in DB9:

• LISTEN / NOTIFY (async notifications)
• Logical replication
• Foreign data wrappers (postgres_fdw, etc.)
• Tablespaces (all storage is TiKV-managed)
• Advisory locks

Query Execution Pipeline

When you send a SQL query to DB9, it follows this pipeline:

1. Parse — SQL text is parsed into an AST
2. Route — Statement classified as DDL, DML, transaction control, or settings
3. Expand views — View references inlined recursively (up to 64 levels)
4. Analyze — Semantic analysis resolves names, infers types, checks scope
5. Optimize — Cost-based optimizer (DPccp algorithm) chooses join order, access methods, and physical operators
6. Execute — Pull-based (Volcano) operator tree streams results row by row
7. Return — Results sent over pgwire

The optimizer uses table statistics from ANALYZE for cardinality estimation, with predicate pushdown and subquery decorrelation.

Key Limits

Limit	Value
Statement timeout	60s (default)
Idle transaction timeout	60s (default)
View nesting depth	64 levels
Pending portals per connection	32
Timestamp precision	Milliseconds
Encoding	UTF-8 only

See Limits for extension-specific limits (HTTP, fs9, pg_cron).

Connecting

DB9 uses standard PostgreSQL connection strings:

Terminal

# psql
psql "postgresql://dbname.admin:password@pg.db9.io:5433/postgres"

# Or via the CLI
db9 db sql <db> -q "SELECT now()"

Any PostgreSQL client library works: psycopg2, node-postgres, JDBC, Go pgx, etc. See Connect for full details.

Reference Pages

• Data Types — 23+ supported types with coercion and casting rules
• DDL — CREATE, ALTER, DROP for tables, indexes, views, schemas, types, triggers
• DML and Queries — INSERT, UPDATE, DELETE, JOINs, CTEs, window functions, subqueries
• Transactions — isolation levels, savepoints, COPY
• Built-in Functions — string, math, date/time, JSON, array, aggregate, window, vector, FTS
• System Catalog — pg_class, pg_attribute, information_schema, and more
• Auth and Roles — users, roles, grants, and permissions
• Advanced SQL — PL/pgSQL, triggers, sequences, custom types, collations
• Session Parameters — SET/SHOW/RESET and GUC compatibility
• Limits — engine, extension, and timing constraints

Related Docs

• Connect — connection strings, TLS, and driver configuration
• Extensions — fs9, HTTP, vector, pg_cron, embedding, and more
• Architecture — TiKV storage, pgwire, and query engine internals
• CLI Reference — db9 db sql for terminal SQL execution
• Compatibility Matrix — full supported/unsupported feature matrix across SQL, types, indexes, ORMs, and extensions