Compatibility Matrix

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.

Summary

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 incl. INET; no XML, CIDR/MACADDR, range types
Indexes	B-tree + GIN + HNSW	GiST/Hash/SP-GiST/BRIN are rejected
Transactions	Full	READ COMMITTED and REPEATABLE READ enforced; SERIALIZABLE is downgraded to 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

SQL

DML and Queries

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 (ANALYZE adds summary runtime stats — actual rows, execution time, KV counters — but no per-operator timing; FORMAT option accepted but ignored)
LATERAL joins	Supported

DDL

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, GIN)	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
Row-Level Security (ENABLE/DISABLE/FORCE RLS)	Supported
CREATE/ALTER/DROP POLICY	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)

Transactions

Feature	Status
BEGIN / COMMIT / ROLLBACK	Supported
SAVEPOINT / RELEASE / ROLLBACK TO	Supported
READ COMMITTED isolation	Supported — statement-level snapshots, as in PostgreSQL. Default level.
REPEATABLE READ isolation	Supported — transaction-level snapshot
SERIALIZABLE isolation	Downgraded to REPEATABLE READ, see note below
READ UNCOMMITTED isolation	Supported — behaves as READ COMMITTED, as in PostgreSQL
SET LOCAL (transaction-scoped settings)	Supported
READ ONLY transactions	Supported — DML and DDL writes are rejected with SQLSTATE 25006
DEFERRABLE transactions	Not supported (requires SERIALIZABLE)
Advisory locks (pg_advisory_lock family)	Supported (node-local; not coordinated across multiple db9-server processes)

SERIALIZABLE is silently downgraded

READ COMMITTED and REPEATABLE READ behave as they do in PostgreSQL — a READ COMMITTED transaction takes a fresh snapshot per statement and sees concurrent commits, and a REPEATABLE READ transaction holds one snapshot for its lifetime.

SERIALIZABLE is not implemented. TiKV provides snapshot isolation, not PostgreSQL’s serializable snapshot isolation (SSI), so anomalies that only SSI prevents — write skew in particular — are not detected. What happens when you ask for it depends on how you connect:

SQL

-- PostgreSQL wire protocol (psql, ORMs, any pg driver): accepted with a warning
BEGIN ISOLATION LEVEL SERIALIZABLE;
-- WARNING: TiKV provides snapshot isolation; SERIALIZABLE has been downgraded to REPEATABLE READ
SHOW transaction_isolation;  -- reports 'serializable' — the transaction still runs at REPEATABLE READ

Note the trap: after the downgrade, SHOW transaction_isolation echoes back serializable, so the session cannot be used to confirm the level actually in effect. The warning is the only signal.

Over the HTTP SQL API (as used by db9 db sql), the same request is rejected outright instead:

ERROR: SERIALIZABLE isolation level is not supported. Use REPEATABLE READ or READ COMMITTED instead.

If your application depends on true serializability, enforce it yourself with explicit row locks (SELECT ... FOR UPDATE) or a unique constraint that makes the conflicting write fail.

Data Types

Supported

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
INET		IPv4/IPv6 host or network address; equality and ordering only — no network operators/functions, no CIDR
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

Not Supported

Type	Notes
XML
CIDR / MACADDR	Network address types; INET is supported (equality/ordering only)
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)

Indexes

Type	Status	Notes
B-tree	Full	Default. Point, range, bounded-range, in-list, expression, partial indexes
GIN	Full	JSONB containment (@>), full-text search (@@), array operators
HNSW	Full	Approximate k-NN over VECTOR columns. Requires a single-column primary key — see the note below
GiST	Not supported	CREATE INDEX is rejected
Hash	Not supported	CREATE INDEX is rejected
SP-GiST	Not supported	CREATE INDEX is rejected
BRIN	Not supported	CREATE INDEX is rejected

Only btree, gin, and hnsw are recognized access methods. Any other method is rejected at CREATE INDEX time — it is not accepted-then-ignored:

ERROR: access method "gist" is not supported (XX000)
HINT:  Only btree, gin, and hnsw indexes are currently supported.

IVFFlat is not among them — CREATE INDEX ... USING ivfflat fails with access method "ivfflat" does not exist. HNSW is the only vector index type.

HNSW indexes carry extra structural requirements: the table needs a single-column primary key (no-PK and composite-PK tables are rejected), and an INTEGER/BIGINT primary key must hold only non-negative values. UUID and TEXT primary keys are fine. Partial (WHERE) and multi-column HNSW indexes are rejected.

Queries fall back to an exact sequential scan when no usable index exists, so all distance operators (<->, <=>, <#>) and functions return correct results either way. See pgvector for details.

GIN indexes on JSONB columns are fully functional for containment queries (@>). Queries using @> on GIN-indexed columns use index scans instead of sequential scans.

Wire Protocol

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

Functions

DB9 implements 200+ built-in functions across these categories:

Category	Examples
String	upper, lower, concat, substring, replace, trim, split_part, regexp_match, format
Math	abs, ceil, floor, round, sqrt, power, log, random, trunc, trig functions
Date/Time	now, date_trunc, date_part, extract, age, to_char, to_timestamp, make_date
Aggregate	count, sum, avg, min, max, string_agg, array_agg, json_agg, bool_and/or
Window	row_number, rank, dense_rank, lag, lead, first_value, last_value, ntile
JSON/JSONB	jsonb_build_object, jsonb_set, jsonb_extract_path, jsonb_array_elements, jsonb_each, jsonb_typeof, to_jsonb, row_to_json
Array	array_length, array_agg, unnest, array_append, array_cat, array_position, string_to_array
Full-text search	to_tsvector, to_tsquery, plainto_tsquery, ts_rank, ts_headline, setweight
UUID	uuid_generate_v4 and related functions
Type conversion	cast, to_char, to_number, to_date, to_timestamp
Conditional	coalesce, nullif, greatest, least
HTTP (scalar)	http_get, http_post, http_put, http_delete returning JSONB
Document chunking	CHUNK_TEXT — table-valued function for RAG pipelines
Storage	db9_refresh_storage_stats — trigger storage scan
System	current_user, current_database, current_schema, pg_typeof, version

JSON/JSONB Operators

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

PL/pgSQL

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.

ORM and Driver Compatibility

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

System Catalogs

DB9 implements 50+ virtual tables across pg_catalog, information_schema, and extension schemas.

pg_catalog

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

information_schema

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

Extensions

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.

Not Supported

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
SERIALIZABLE isolation (true serializable)	Transactions
DEFERRABLE transactions	Transactions
Large objects (OID-based)	Data
XML type	Data
Network types (CIDR, 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

Validation

You can verify compatibility for your specific use case:

SQL

-- Check supported types
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 transaction_isolation;  -- returns 'read committed' (the default)

-- SERIALIZABLE is not implemented. On the PostgreSQL wire protocol it is accepted
-- with a warning and downgraded; on the HTTP SQL API it returns an error:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- WARNING: TiKV provides snapshot isolation; SERIALIZABLE has been downgraded to REPEATABLE READ

Next Pages

• SQL Reference — detailed SQL syntax and function reference
• Architecture — how DB9’s SQL engine connects to TiKV storage
• Extensions — guide to all 9 built-in extensions
• Limits and Quotas — operational limits and safety boundaries
• Production Checklist — evaluate compatibility gaps before going live
• Migrate from Neon — step-by-step Neon to DB9 migration
• Migrate from PostgreSQL — general PostgreSQL migration path