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, hash, 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, control flow, dynamic EXECUTE, EXCEPTION handlers and nested blocks, in a CREATE FUNCTION body, a CREATE PROCEDURE body run with CALL, and a DO block (WHILE, FOREACH and cursors are not supported — see Advanced SQL)
• Triggers — BEFORE/AFTER on INSERT/UPDATE/DELETE, with gaps: a BEFORE trigger function cannot use counted FOR loops, bare LOOPs, CASE statements, EXECUTE, exception handlers or nested blocks (CREATE TRIGGER refuses it; a FOR r IN SELECT … LOOP query loop is allowed, over a declared record variable). FOR EACH STATEMENT, row-level WHEN (...) conditions, UPDATE OF column lists and event triggers are supported — see Advanced SQL
• Indexes — btree (default), GIN, partial indexes, expression indexes, CREATE INDEX CONCURRENTLY. Hash indexes are built and used by the planner, on a single integer or OID column and not as a UNIQUE index. HNSW (vector) indexes are built and used by the planner (for a literal or bound-parameter probe, with a LIMIT and no WHERE clause), with structural requirements of their own — see pgvector. GiST, 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 on rows the conflicting transactions also update, or with a unique constraint. At the downgraded level the transaction that loses a FOR UPDATE race fails at COMMIT with 40001 rather than at the statement, so retry on 40001 at COMMIT as well; a unique-constraint conflict fails the INSERT with 23505, as in PostgreSQL. See Transactions.

Feature	DB9 behavior
SERIALIZABLE isolation	Not implemented — downgraded to REPEATABLE READ with a warning on the wire protocol, rejected with an error over the HTTP SQL API.
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) / TIME(n) columns round to the declared precision on insert, update and ALTER COLUMN ... TYPE, and a cast such as ::timestamp(3) rounds the same way. TIMETZ / TIME WITH TIME ZONE has basic support only — see the compatibility matrix.
GIN index execution	Functional for a literal or bound-parameter probe — for @@, a tsquery value or a *_tsquery() call that names its configuration. The planner produces GIN scan plans and the executor uses the index — EXPLAIN ANALYZE reports KV Table Scan Pairs: 0. A probe computed by an expression falls back to a sequential scan.
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.
Logical replication	Logical decoding only: a wal2json slot returns the changes made to tables in a publication, plus the rows a CREATE TABLE ... AS or SELECT ... INTO loads, read with pg_logical_slot_get_changes(...) and format-version 2. DB9 cannot be a subscriber — CREATE SUBSCRIPTION is not available. Creating a slot turns on Realtime capture for the database permanently, after which ADD PRIMARY KEY on a non-empty table, dropping a non-empty table’s primary key, and ALTER TYPE ... RENAME VALUE on a label that stored rows use fail with 55000 — see the compatibility matrix.

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.

The probe above is a literal; the same predicate with the operand supplied as a driver-bound parameter is indexed too. This holds for @> on arrays and JSONB and for @@ against a tsquery or a *_tsquery() call. What the planner does not recognise is a probe computed by an expression — ARRAY[lower('T7')], jsonb_build_object(...), a subquery, or a *_tsquery() call that omits or binds its configuration. Those plan as a Seq Scan with KV Table Scan Pairs equal to the row count, exactly as if the index had been dropped. Results are correct either way, so nothing signals the fallback — read EXPLAIN ANALYZE rather than assuming.

Not supported

These PostgreSQL features are not available in DB9:

• Logical replication as a subscriber (CREATE SUBSCRIPTION); logical decoding is available, see above
• Foreign data wrappers (postgres_fdw, etc.)
• Tablespaces (all storage is TiKV-managed)
• MERGE — rejected with 0A000 (MERGE is not supported); write upserts with INSERT ... ON CONFLICT

LISTEN / NOTIFY is supported, but only a pgwire session can receive — a session has to stay open. The stateless HTTP SQL API can NOTIFY, but a LISTEN there is accepted and never receives anything.

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	12 h (default)
Idle transaction timeout	300s / 5 min (default)
View nesting depth	64 levels
Pending portals per connection	32
Timestamp precision	Microseconds
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