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, 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, and dynamic EXECUTE (WHILE, FOREACH and cursors are not supported; EXCEPTION and nested blocks require a DO block)
• Triggers — BEFORE/AFTER on INSERT/UPDATE/DELETE, with gaps: a BEFORE trigger function cannot use loops, CASE statements, EXECUTE, exception handlers or nested blocks (CREATE TRIGGER refuses it); FOR EACH STATEMENT runs once per row; and in an AFTER trigger RETURN NEW; must be alone on its line — see Advanced SQL
• Indexes — btree (default), GIN, partial indexes, expression indexes, CREATE INDEX CONCURRENTLY. HNSW (vector) indexes are built and used by the planner (for an inlined literal probe, with a LIMIT and no WHERE clause), with structural requirements of their own — see pgvector. GiST, Hash, SP-GiST, and BRIN are rejected at CREATE INDEX time — see DDL — CREATE INDEX

Partial or different from PostgreSQL

DB9 Difference: SERIALIZABLE isolation

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE does not error on the wire protocol — it emits WARNING: TiKV provides snapshot isolation; SERIALIZABLE has been downgraded to REPEATABLE READ and runs the transaction at REPEATABLE READ. Applications that require Serializable Snapshot Isolation (SSI) to prevent write-skew anomalies are not supported, and nothing will fail to tell you so — enforce the invariant with SELECT ... FOR UPDATE or a unique constraint.

Feature	DB9 behavior
SERIALIZABLE isolation	Not implemented — downgraded to REPEATABLE READ with a warning.
SMALLINT / INT2	Aliased to INTEGER (32-bit).
REAL / FLOAT4	Aliased to DOUBLE PRECISION (64-bit).
CHAR(n) fixed-length	Accepted but silently aliased to VARCHAR(n) with the same length. Padding semantics differ from standard PostgreSQL.
Timestamp precision	Microsecond precision, matching PostgreSQL. TIMESTAMP(n) / TIMESTAMPTZ(n) columns round to the declared precision on insert and on ALTER COLUMN ... TYPE. The parameter is ignored in cast expressions and on TIME(n) / TIMETZ(n) columns.
GIN index execution	Functional. The planner produces GIN scan plans and the executor uses the index — EXPLAIN ANALYZE reports KV Table Scan Pairs: 0.
Advisory locks	pg_advisory_lock() family is supported, but lock coordination is node-local (not cross-process/global).
Encoding	UTF-8 only. No other server or client encoding.

GIN indexes are used at runtime

GIN index execution works: the planner produces a GIN scan plan and the executor reads the index rather than the table. EXPLAIN ANALYZE makes this checkable — with the index present the plan is an Index Scan reporting KV Table Scan Pairs: 0, and dropping the index turns the same query into a Seq Scan that reads every row.

-- with a GIN index on tags (1000 rows, 20 matches)
Index Scan using arr_idx on arr_t
  Index Cond: (tags @> ARRAY[t7])
KV Table Scan Pairs: 0
KV Index Scan Pairs: 40

-- same query after DROP INDEX
Seq Scan on arr_t
KV Table Scan Pairs: 1000

Selectivity depends on the operator class. Array and full-text predicates probe only the matching entries; jsonb_ops containment on a column of highly distinct values still reads a large share of the index, so the speedup there is smaller than in PostgreSQL.

Not supported

These PostgreSQL features are not available in DB9:

• Logical replication
• Foreign data wrappers (postgres_fdw, etc.)
• Tablespaces (all storage is TiKV-managed)

LISTEN / NOTIFY is supported, but only over a direct pgwire connection — a session has to stay open to receive notifications. The stateless HTTP SQL API can NOTIFY but cannot LISTEN.

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	600s / 10 min (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

Next Steps

• 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