db9 PostgreSQL Engine Reference

Architecture Overview

db9 is a PostgreSQL-compatible distributed SQL database. Your data is stored in a distributed TiKV cluster, and each database (tenant) gets its own isolated keyspace.

Query Execution Pipeline

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

1. Parse — SQL text is parsed into an abstract syntax tree
2. Route — The statement is classified as DDL, DML, SELECT, transaction control, or settings
3. Expand Views — View references are recursively inlined (up to 64 levels deep)
4. Analyze — Semantic analysis resolves column names, infers types, and checks scope rules
5. Optimize — Cost-based optimizer chooses join order, access methods (table scan vs. index scan), and physical operators
6. Execute — A pull-based (Volcano) operator tree streams results one row at a time
7. Return — Results are sent back over the PostgreSQL wire protocol

Wire Protocol Compatibility

db9 implements the PostgreSQL wire protocol (v3). You can connect with any PostgreSQL client:

• psql
• Any PostgreSQL ORM or driver (psycopg2, node-postgres, JDBC, etc.)
• Standard connection tools and GUIs

Both Simple Query and Extended Query (Parse/Bind/Describe/Execute) protocols are supported.

Query Optimization

The query optimizer uses table statistics from ANALYZE for cardinality estimation, cost-based join reordering (DPccp algorithm), predicate pushdown, subquery decorrelation, and index scan selection when beneficial.

Data Types

db9 supports 23 data types. All data is stored in UTF-8 encoding.

Interval Representation

Intervals store months and sub-month components separately, enabling calendar-aware arithmetic (e.g., adding 1 month to January 31 gives February 28/29). The sub-month component is stored in milliseconds.

Type Coercion

The type system uses two coercion strategies matching PostgreSQL behavior:

UNION / CASE / COALESCE / VALUES ("Text wins")

When mixing Text with a typed value, the result is Text. Example: SELECT 1 UNION SELECT 'a' resolves to TEXT.

Comparisons ("Non-Text wins")

When comparing Text with a typed value, the text literal is coerced to the typed side. Example: WHERE col = '42' coerces '42' to the column's type.

Numeric Promotion Hierarchy

Int32 → Int64 → Float64 → Numeric. When two numeric types are mixed, the higher-precedence type wins.

Temporal Promotion

• Date + Timestamp = Timestamp
• Timestamp + TimestampTz = TimestampTz
• Date + TimestampTz = TimestampTz

CAST Contexts

SQL Reference: DDL

Data Definition Language statements for creating and managing database objects.

CREATE TABLE

CREATE TABLE [IF NOT EXISTS] table_name (
    column_name type [constraints],
    ...
    [table_constraints]
);

-- Create from query result
CREATE TABLE new_table AS SELECT ... FROM source;
SELECT ... INTO new_table FROM source;

Column constraints: NOT NULL, DEFAULT, PRIMARY KEY, UNIQUE, REFERENCES (foreign key), CHECK

Table constraints: PRIMARY KEY (col, ...), UNIQUE (col, ...), FOREIGN KEY (col) REFERENCES table(col) with ON DELETE/ON UPDATE actions, CHECK (expr)

SERIAL and BIGSERIAL columns are supported for auto-incrementing IDs. Self-referential foreign keys are supported.

CREATE INDEX

-- B-tree index (default)
CREATE INDEX idx_name ON table (column);

-- Unique index
CREATE UNIQUE INDEX idx_name ON table (column);

-- GIN index (for JSONB, full-text search, arrays)
CREATE INDEX idx_name ON table USING GIN (column);

-- GiST index
CREATE INDEX idx_name ON table USING GIST (column);

-- Expression index
CREATE INDEX idx_name ON table (lower(column));

-- Partial index
CREATE INDEX idx_name ON table (column) WHERE active = true;

-- Non-blocking index creation
CREATE INDEX CONCURRENTLY idx_name ON table (column);

Index methods: btree (default), GIN, GiST. IF NOT EXISTS is supported.

CREATE VIEW

CREATE VIEW view_name AS SELECT ...;
CREATE OR REPLACE VIEW view_name AS SELECT ...;

CREATE OR REPLACE VIEW cannot drop or rename existing columns; only appending new columns is allowed.

CREATE MATERIALIZED VIEW

CREATE MATERIALIZED VIEW mv_name AS SELECT ...;
REFRESH MATERIALIZED VIEW mv_name;

ALTER TABLE

DROP Statements

DROP TABLE [IF EXISTS] table_name [CASCADE];
DROP INDEX [IF EXISTS] index_name;
DROP VIEW [IF EXISTS] view_name [CASCADE];
DROP MATERIALIZED VIEW [IF EXISTS] mv_name [CASCADE];
TRUNCATE TABLE table_name;

CASCADE transitively resolves and drops all dependent views and materialized views.

Other DDL

SQL Reference: DML

Data Manipulation Language statements for inserting, updating, and deleting data.

INSERT

-- Single row
INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');

-- Multi-row
INSERT INTO users (name, email) VALUES
    ('Alice', 'alice@example.com'),
    ('Bob', 'bob@example.com');

-- INSERT ... SELECT
INSERT INTO archive SELECT * FROM users WHERE active = false;

-- With RETURNING
INSERT INTO users (name) VALUES ('Alice') RETURNING id, name;

INSERT ON CONFLICT (Upsert)

-- Skip conflicting rows
INSERT INTO users (email, name) VALUES ('alice@example.com', 'Alice')
ON CONFLICT (email) DO NOTHING;

-- Upsert by column
INSERT INTO users (email, name) VALUES ('alice@example.com', 'Alice Updated')
ON CONFLICT (email) DO UPDATE SET name = excluded.name;

-- Upsert by constraint name
INSERT INTO users (email, name) VALUES ('alice@example.com', 'Alice')
ON CONFLICT ON CONSTRAINT users_email_key DO UPDATE SET name = excluded.name;

The excluded pseudo-table is available in DO UPDATE SET clauses, referencing the row that was proposed for insertion.

UPDATE

UPDATE users SET name = 'Bob' WHERE id = 1;
UPDATE users SET name = 'Bob' WHERE id = 1 RETURNING *;

DELETE

DELETE FROM users WHERE id = 1;
DELETE FROM users WHERE id = 1 RETURNING *;

Foreign Key Referential Actions

Supported actions for ON DELETE and ON UPDATE: CASCADE, SET NULL, SET DEFAULT, RESTRICT, NO ACTION. Recursive cascade operations include cycle detection. MATCH SIMPLE semantics (PostgreSQL default): if any referencing column is NULL, the FK check is skipped.

SQL Reference: Queries

SELECT query features, JOINs, window functions, CTEs, subqueries, and set operations.

JOINs

Both hash join and nested-loop join implementations are available. The optimizer uses cost-based join reordering (DPccp algorithm).

Aggregation

• GROUP BY with hash-based aggregation
• HAVING clause
• DISTINCT aggregates: COUNT(DISTINCT col)
• FILTER clause: COUNT(*) FILTER (WHERE x > 0)
• GROUPING() function

Window Functions

SELECT
    name,
    department,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees;

Supported ranking functions: ROW_NUMBER, RANK, DENSE_RANK, NTILE, PERCENT_RANK, CUME_DIST

Supported access functions: LAG, LEAD, FIRST_VALUE, LAST_VALUE, NTH_VALUE

All standard aggregate functions can also be used as window functions.

Common Table Expressions (CTEs)

-- Standard CTE
WITH active_users AS (
    SELECT * FROM users WHERE active = true
)
SELECT * FROM active_users;

-- Recursive CTE
WITH RECURSIVE tree AS (
    SELECT id, parent_id, name, 1 AS depth
    FROM categories WHERE parent_id IS NULL
    UNION ALL
    SELECT c.id, c.parent_id, c.name, t.depth + 1
    FROM categories c JOIN tree t ON c.parent_id = t.id
)
SELECT * FROM tree;

Subqueries

• Scalar subqueries
• EXISTS / NOT EXISTS
• IN / NOT IN with subquery
• ANY / ALL with subquery
• Array subqueries
• Correlated subqueries (with subquery decorrelation optimization)

Set Operations

Other Query Features

• SELECT DISTINCT and SELECT DISTINCT ON (expr)
• ORDER BY with ASC/DESC and NULLS FIRST/NULLS LAST
• LIMIT and OFFSET
• EXPLAIN and EXPLAIN ANALYZE for query plans
• PREPARE name AS ... / EXECUTE name (params) / DEALLOCATE name
• generate_series() table function
• SHOW TABLES

SQL Reference: Transactions

Transaction control, savepoints, isolation semantics, and autocommit behavior.

Transaction Control

BEGIN;
-- ... your SQL statements ...
COMMIT;     -- persist all changes atomically
ROLLBACK;   -- discard all changes

Autocommit

Single statements outside an explicit BEGIN block run in implicit autocommit mode. The system begins a transaction, executes the statement, and commits (or rolls back on error). Retryable TiKV errors trigger automatic retry with backoff.

Savepoints

BEGIN;
INSERT INTO users (name) VALUES ('Alice');

SAVEPOINT sp1;
INSERT INTO users (name) VALUES ('Bob');
ROLLBACK TO SAVEPOINT sp1;  -- Bob's insert is undone

RELEASE SAVEPOINT sp1;     -- destroys savepoint, merges state
COMMIT;  -- only Alice is committed

Savepoint behavior matches PostgreSQL: duplicate names are allowed (most recent is targeted), RELEASE destroys the named savepoint and all later ones, ROLLBACK TO re-establishes the savepoint for reuse.

Isolation Level

Default transaction mode is pessimistic with Snapshot Isolation provided by TiKV. Each transaction sees a consistent snapshot of the database at its start time.

Sequence Behavior

Sequence advances (nextval) are non-transactional — they survive transaction rollbacks, matching PostgreSQL behavior.

Failed Transaction State

When an error occurs in a transaction, it enters a failed state. Only ROLLBACK, COMMIT, and END are accepted. All other statements return an error. This matches PostgreSQL behavior.

SQL Reference: COPY

Bulk data import and export.

-- Import from file (via client)
COPY table_name FROM STDIN;

-- Import from Parquet (with parquet extension)
COPY table_name FROM '/data/file.parquet' FORMAT parquet;

Text, CSV, and Parquet formats are supported for COPY FROM.

Built-in Functions

Comprehensive reference of all built-in functions, organized by category.

Extensions

Available extensions that add capabilities to your database. Install with CREATE EXTENSION name;

See the SDK Docs for detailed usage examples of the HTTP, fs9, and vector search extensions.

System Catalog

System views for introspecting database objects.

Authentication & Roles

Connection format, TLS, and role-based access control.

Connecting

Connect using the standard PostgreSQL connection string format:

psql "postgresql://<db_id>.<user>:<password>@pg.staging.db9.ai:5433/postgres"

All connections use TLS (sslmode=require).

RBAC

• CREATE ROLE / ALTER ROLE
• GRANT / REVOKE privileges on tables and schemas
• SET ROLE for session role switching
• Privilege checks enforced on every table access

Advanced SQL

PL/pgSQL, sequences, triggers, and other advanced features.

Session Parameters

Session-level configuration with SET/SHOW.

SET variable = value;           -- session-scoped
SET LOCAL variable = value;     -- transaction-scoped
SHOW variable;
RESET variable;

-- Functions
SELECT current_setting('variable_name');
SELECT set_config('variable_name', 'value', false);

Statement timeout enforcement is supported.

Limits & Constraints

Resource limits and constraints across the db9 engine and extensions.

Engine Limits

HTTP Extension Limits

fs9 Extension Limits

pg_cron Limits

Unsupported PostgreSQL Features