DB9 implements a PostgreSQL-compatible SQL engine over TiKV distributed storage. Most PostgreSQL clients, ORMs, and drivers work without changes. This page documents where DB9 matches PostgreSQL, where it diverges, and what is not available.
Use this matrix when evaluating DB9 for a new project or migrating an existing PostgreSQL application.
Category Coverage Notes SQL DML (SELECT, INSERT, UPDATE, DELETE) Full JOINs, CTEs, window functions, subqueries, upsert, RETURNING SQL DDL (CREATE, ALTER, DROP) Near-full No partitioning, table inheritance, or foreign data wrappers Data types 20+ types All common types; no XML, CIDR/INET, range types Indexes B-tree + HNSW full GIN/GiST/Hash/SP-GiST/BRIN accepted but fall back to table scan Transactions Full SERIALIZABLE accepted but runs as REPEATABLE READ Built-in functions 200+ String, math, date/time, JSON/JSONB, array, aggregate, window, FTS Wire protocol pgwire v3 Simple Query, Extended Query, COPY, prepared statements ORM compatibility 99%+ Prisma, Drizzle, Sequelize, Knex, TypeORM, GORM, SQLAlchemy tested System catalogs 50+ views pg_catalog, information_schema, cron schema Extensions 9 built-in http, fs9, pg_cron, vector, embedding, uuid-ossp, hstore, parquet, zhparser PL/pgSQL Partial Basics supported; no EXECUTE, exception handling, WHILE loops Replication None No logical or streaming replication
Feature Status SELECT with FROM, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT/OFFSET Supported DISTINCT / DISTINCT ON Supported JOINs (INNER, LEFT, RIGHT, FULL OUTER, CROSS) Supported Subqueries (correlated, scalar, EXISTS, IN) Supported CTEs (WITH … AS) Supported Recursive CTEs (WITH RECURSIVE) Supported Set operations (UNION, INTERSECT, EXCEPT) Supported Window functions (ROW_NUMBER, RANK, LAG, LEAD, etc.) Supported INSERT with VALUES, SELECT, DEFAULT, RETURNING Supported INSERT ON CONFLICT (upsert) Supported UPDATE with WHERE, subqueries, RETURNING Supported DELETE with WHERE, subqueries, RETURNING Supported CASE expressions Supported COPY (CSV, TEXT) Supported (BINARY format not supported) EXPLAIN / EXPLAIN ANALYZE Supported (plan-only; ANALYZE shows plan without runtime stats) LATERAL joins Supported
Feature Status CREATE/ALTER/DROP TABLE Supported Column constraints (PRIMARY KEY, UNIQUE, NOT NULL, DEFAULT, CHECK) Supported Foreign keys (CASCADE, SET NULL, SET DEFAULT, RESTRICT, NO ACTION) Supported Generated columns Supported SERIAL / BIGSERIAL Supported CREATE/DROP INDEX (B-tree, HNSW) Supported CREATE INDEX CONCURRENTLY Supported Expression indexes, partial indexes Supported CREATE/DROP VIEW Supported CREATE/DROP MATERIALIZED VIEW, REFRESH MATERIALIZED VIEW Supported CREATE/DROP SEQUENCE Supported CREATE/ALTER TYPE (enum) Supported CREATE/DROP SCHEMA Supported CREATE/DROP FUNCTION (PL/pgSQL) Supported CREATE/DROP TRIGGER (BEFORE/AFTER, row/statement level) Supported TRUNCATE Supported Table partitioning (RANGE, LIST, HASH) Not supported Table inheritance Not supported Foreign data wrappers (FDW) Not supported Tablespaces Not supported (TiKV manages storage placement)
Feature Status BEGIN / COMMIT / ROLLBACK Supported SAVEPOINT / RELEASE / ROLLBACK TO Supported READ COMMITTED isolation Supported (default) REPEATABLE READ isolation Supported SERIALIZABLE isolation Accepted but silently runs as REPEATABLE READ READ UNCOMMITTED isolation Accepted but runs as READ COMMITTED SET LOCAL (transaction-scoped settings) Supported DEFERRABLE transactions Not supported (requires SERIALIZABLE)
Type Aliases Notes BOOLEAN BOOL SMALLINT INT2 Stored as INT4 internally INTEGER INT, INT4 BIGINT INT8 REAL FLOAT4 Stored as FLOAT8 internally DOUBLE PRECISION FLOAT8 NUMERIC DECIMAL With precision and scale, up to precision 1000 TEXT Variable-length, no limit VARCHAR(n) CHARACTER VARYING CHAR(n) CHARACTER Stored as VARCHAR internally BYTEA Binary data DATE TIME Without time zone TIMESTAMP Without time zone, millisecond precision TIMESTAMPTZ With time zone, millisecond precision INTERVAL JSON Stored as text JSONB Canonicalized (sorted keys, normalized whitespace) UUID BOOLEAN[] / INT[] / TEXT[] / etc. 1-dimensional arrays of any supported type TSVECTOR Full-text search document representation TSQUERY Full-text search query VECTOR(n) pgvector-compatible; for HNSW indexes and distance operators NAME PostgreSQL identifier type
Type Notes XML CIDR / INET / MACADDR Network address types Range types (INT4RANGE, TSRANGE, etc.) Composite types (user-defined row types) Enum types are supported via CREATE TYPE Large objects (OID-based streaming) BYTEA is available for binary data Multi-dimensional arrays Only 1-dimensional arrays Money Bit string (BIT, VARBIT)
Type Status Notes B-tree Full Default. Point, range, bounded-range, in-list, expression, partial indexes HNSW Full pgvector-compatible. L2, cosine, inner product distance metrics. k-NN via ORDER BY <operator> LIMIT k GIN Syntax only CREATE INDEX accepted; EXPLAIN shows GIN scan; runtime falls back to sequential scan GiST Syntax only CREATE INDEX accepted; runtime falls back to sequential scan Hash Syntax only CREATE INDEX accepted; runtime falls back to sequential scan SP-GiST Syntax only CREATE INDEX accepted; runtime falls back to sequential scan BRIN Syntax only CREATE INDEX accepted; runtime falls back to sequential scan
GIN indexes on JSONB and tsvector columns are accepted and stored. The optimizer can plan GIN scans (visible in EXPLAIN output), but the runtime execution path is not yet implemented — queries fall back to sequential scan with filter.
Feature Status pgwire v3 Supported Simple Query (text) Supported Extended Query (Parse, Bind, Describe, Execute) Supported Binary parameter encoding Supported COPY (CSV, TEXT) Supported (BINARY format not supported) Prepared statements Supported Portals Supported Multiple result sets Supported SCRAM-SHA-256 authentication Supported (at pgwire layer) LISTEN / NOTIFY Not supported Logical replication protocol Not supported Streaming replication Not supported
DB9 implements 200+ built-in functions across these categories:
Category Examples String upper, lower, concat, substring, replace, trim, split_part, regexp_match, formatMath abs, ceil, floor, round, sqrt, power, log, random, trunc, trig functionsDate/Time now, date_trunc, date_part, extract, age, to_char, to_timestamp, make_dateAggregate count, sum, avg, min, max, string_agg, array_agg, json_agg, bool_and/orWindow row_number, rank, dense_rank, lag, lead, first_value, last_value, ntileJSON/JSONB jsonb_build_object, jsonb_set, jsonb_extract_path, jsonb_array_elements, jsonb_each, jsonb_typeof, to_jsonb, row_to_jsonArray array_length, array_agg, unnest, array_append, array_cat, array_position, string_to_arrayFull-text search to_tsvector, to_tsquery, plainto_tsquery, ts_rank, ts_headline, setweightUUID uuid_generate_v4 and related functionsType conversion cast, to_char, to_number, to_date, to_timestampConditional coalesce, nullif, greatest, leastSystem current_user, current_database, current_schema, pg_typeof, version
Operator Description Status ->Get JSON object field by key (returns JSON) Supported ->>Get JSON object field by key (returns text) Supported @>Contains Supported <@Contained by Supported ?Key exists Supported `? ` Any key exists ?&All keys exist Supported ` ` #-Delete path Supported
Feature Status DECLARE / BEGIN / END blocks Supported Variable assignment (:=) Supported SELECT INTO Supported IF / THEN / ELSIF / ELSE / END IF Supported FOR loops (query iteration and integer range) Supported PERFORM (execute without result) Supported EXIT (loop termination) Supported RAISE (NOTICE, WARNING, ERROR) Supported RETURN / RETURN NEXT / RETURN QUERY Supported RETURNS TABLE syntax Supported WHILE loops Not supported CASE statements (in PL/pgSQL) Not supported Exception handling (BEGIN…EXCEPTION) Not supported Dynamic SQL (EXECUTE) Not supported Cursor operations (FOR…IN CURSOR) Not supported
Other procedural languages (PL/Python, PL/Perl, PL/v8) are not supported.
DB9 is tested against major ORMs with a combined pass rate above 99%:
ORM / Driver Tested Version Pass Rate Notes Prisma 5.7+ 100% (89/89) Binary wire protocol; $queryRaw for advanced SQL Drizzle 0.29+ 100% (75/75) Type-safe queries; full query builder support Sequelize 6.35+ 100% (87/87) Raw queries for window/CTE features Knex.js 3.1+ 100% (97/97) Full query builder, window functions, CTEs TypeORM 0.3.17+ 98% (147/150) 3 tests skipped (schema introspection edge cases) node-postgres (pg) 8.11+ Full Native pgwire client SQLAlchemy 2.0+ Tested JSONB operators, RETURNING, transaction patterns GORM (Go) 1.25+ Tested CRUD, transactions, foreign keys
Known ORM limitations:
Schema introspection queries may return incomplete results for some ORMs that rely heavily on information_schema
Some ORMs assume PostgreSQL-specific system functions that are not yet implemented
DB9 implements 50+ virtual tables across pg_catalog, information_schema, and extension schemas.
View Status Notes pg_class Supported Relations (tables, views, indexes, sequences) pg_attribute Supported Column definitions pg_index Supported Index metadata pg_constraint Supported Constraints (PK, FK, UNIQUE, CHECK) pg_type Supported Data types pg_proc Supported Functions and procedures pg_namespace Supported Schemas pg_roles / pg_user Supported Users and roles pg_database Supported Database metadata pg_sequence Supported Sequence state pg_attrdef Supported Column defaults pg_extension Supported Installed extensions pg_am Supported Access methods pg_trigger Supported Trigger definitions pg_depend / pg_description Supported Object dependencies and comments pg_stat_user_tables Stub Returns rows but statistics columns are zeros pg_stat_statements Not available pg_publication / pg_subscription Not available No logical replication
View Status columns Supported tables Supported views Supported schemata Supported table_constraints Supported key_column_usage Supported check_constraints Supported referential_constraints Supported sequences Supported routines Supported
DB9 includes 9 built-in extensions. Custom or third-party extensions cannot be installed.
Extension Version Default Description http 1.0.0 Yes HTTP client (GET, POST, PUT, DELETE, PATCH, HEAD) pg_cron 1.0.0 Yes Job scheduler with cron expressions fs9 1.0.0 No File system operations (read, write, list, glob) vector 0.8.1 No pgvector-compatible vector type and HNSW indexes embedding 1.0.0 No Built-in text embedding generation uuid-ossp 1.1 No UUID generation functions (functions are built-in; extension is a metadata shim) hstore 1.0 No Key-value store type (metadata shim with limited semantics) parquet 1.0.0 No Parquet file import zhparser 2.0.0 No Chinese full-text search tokenizer
Extensions not available: PostGIS, pg_partman, pg_stat_statements, pgcrypto, pg_trgm, ltree, citext, and all other PostgreSQL contrib extensions.
These PostgreSQL features are not available in DB9:
Feature Category Table partitioning (RANGE, LIST, HASH) DDL Table inheritance DDL Foreign data wrappers (FDW) DDL Tablespaces DDL Rules (CREATE RULE) DDL Logical replication (PUBLICATION, SUBSCRIPTION) Replication Streaming replication Replication LISTEN / NOTIFY Protocol Advisory locks Locking Row-level security (RLS) Security SERIALIZABLE isolation (true serializable) Transactions DEFERRABLE transactions Transactions Large objects (OID-based) Data XML type Data Network types (CIDR, INET, MACADDR) Data Range types Data PL/Python, PL/Perl, PL/v8 Languages Dynamic SQL in PL/pgSQL (EXECUTE) Languages Custom extensions Extensions pg_dump / pg_restore (native format) Tools pg_basebackup Tools
You can verify compatibility for your specific use case:
SELECT typname FROM pg_type WHERE typnamespace = 11 ORDER BY typname;
-- Check available extensions
SELECT * FROM pg_extension;
-- Check installed functions
SELECT proname, pronargs FROM pg_proc WHERE pronamespace = 11 ORDER BY proname;
-- Check catalog coverage
SELECT schemaname, tablename FROM pg_tables WHERE schemaname = 'pg_catalog' ;
-- Verify transaction isolation
SHOW default_transaction_isolation;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE ;
SHOW transaction_isolation; -- returns 'repeatable read'