PostgreSQL Compatibility

Connect with psql, JDBC, psycopg, or any PostgreSQL driver, no changes. ScramDB speaks the PostgreSQL wire protocol, so the tools, drivers, and BI clients you already use work as-is. There is no ScramDB SDK to learn and no custom connector to install.

ScramDB is a UTAP database (Unified Transactional Analytical Processing). Transactions, analytics, and AI or vector queries all run on one live copy of your data. The PostgreSQL wire protocol is how you talk to it.

psql -h localhost -p 5432 scramdb

Any client that speaks PostgreSQL protocol v3 connects the same way:

• Python: psycopg2, psycopg3, asyncpg, SQLAlchemy
• Java / JVM: the PostgreSQL JDBC driver (pgjdbc)
• Go: pgx, database/sql with lib/pq
• Node.js: node-postgres (pg), knex
• Rust: tokio-postgres, sqlx
• BI and tooling: anything that connects to PostgreSQL

The tables below list capabilities that are implemented and covered by ScramDB's test suite. Where ScramDB behaves differently from stock PostgreSQL, the difference is called out in Behavior differences.

---

Protocol and drivers

Capability	Details
Wire protocol v3	Full simple query protocol and extended query protocol
Extended query protocol	Parse, Bind, Describe, and Execute, with text and binary parameter and result formats
Prepared statements	PREPARE / EXECUTE / DEALLOCATE, plus $1 style bind parameters over the wire
Cursors	DECLARE a cursor, SCROLL and NO SCROLL, WITH HOLD (outliving its transaction), FETCH in every direction, MOVE, and CLOSE (Cursors)
Authentication	SCRAM-SHA-256 password authentication, with pg_hba.conf style host rules
Catalog introspection	pg_catalog and information_schema queries, so drivers and BI tools that inspect the catalog work. The information_schema views are schemata, tables, columns, views, table_constraints, key_column_usage, referential_constraints, check_constraints, sequences, triggers, routines, parameters (one row per argument of each CREATE FUNCTION and CREATE PROCEDURE, joined to routines by specific_name), table_privileges, column_privileges, character_sets and collations. They list the current database's own relations, each under one oid that 'name'::regclass, to_regclass, pg_attribute, pg_index and pg_description all agree on; no two catalog objects (a table, a function, a type and its array type, a schema, an index, a constraint) share an oid, so obj_description(oid) names one object (an object an earlier version made keeps the oid it was given). The working relations a statement builds while it runs (a materialized WITH query, a join input, a trigger's transition table) are never listed, a materialized view is described as the view itself (its columns, its indexes, its pg_stat_user_tables row), never as a separate table, a plain view is listed with its columns in information_schema.tables and columns, and a session's temporary tables and sequences are listed in its temporary schema, pg_temp_N. A NOT NULL constraint is listed as PostgreSQL 18 lists it, <table>_<column>_not_null, in pg_constraint and in information_schema, and a constraint is always named without its table's schema or database
Monitoring views	pg_stat_activity (one row per session: state, last statement, start times, wait event, with PostgreSQL's privacy rule), pg_stats (the column statistics ANALYZE collected) and pg_class.reltuples (the analyzed row count); see ANALYZE and Monitoring sessions
Asynchronous notifications	LISTEN, UNLISTEN, NOTIFY, pg_notify, pg_listening_channels and pg_notification_queue_usage, transactional and delivered in commit order, to idle sessions too; on a cluster, to listeners on every node (LISTEN, UNLISTEN and NOTIFY)
Server parameters	SHOW and SET for session settings, including statement_timeout and idle_session_timeout; max_connections and superuser_reserved_connections read on every session and refused with 55P02 on SET or in startup options (Session timeouts)
Query cancellation	PostgreSQL's out-of-band CancelRequest protocol and pg_cancel_backend() both interrupt a running statement and return SQLSTATE 57014, even a CPU-bound one that never yields to check for a cancellation

---

Transactions and isolation

Capability	Details
Transaction control	BEGIN / COMMIT / ROLLBACK
MVCC isolation levels	READ COMMITTED, REPEATABLE READ, and SERIALIZABLE. The default is READ COMMITTED, the same as PostgreSQL
Serializable isolation	Serializable Snapshot Isolation (SSI) with read-set validation, which prevents write-skew anomalies
Setting the level	SET TRANSACTION ISOLATION LEVEL, SET SESSION CHARACTERISTICS, inline BEGIN ... ISOLATION LEVEL, and SHOW TRANSACTION ISOLATION LEVEL
Savepoints	SAVEPOINT, RELEASE SAVEPOINT, and ROLLBACK TO SAVEPOINT, including nested and shadowed names
Snapshot synchronization	pg_export_snapshot() and SET TRANSACTION SNAPSHOT, with PostgreSQL's checks and SQLSTATEs (Sharing a Snapshot)
Two-phase commit	PREPARE TRANSACTION, COMMIT PREPARED, and ROLLBACK PREPARED, with pg_prepared_xacts introspection. Prepared transactions survive client disconnect. On a cluster's distributed tables a prepared transaction also survives a restart of its node, and COMMIT PREPARED can fail with 40001 when a conflicting transaction committed first (Two-Phase Commit)
Row locking	SELECT ... FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE and FOR KEY SHARE, with OF, NOWAIT and SKIP LOCKED. On a cluster's distributed tables rows are not locked: the second of two writers fails at COMMIT with 40001 instead of waiting (Row locks on distributed tables)
Table locking	LOCK TABLE in all lock modes, with NOWAIT
Advisory locks	pg_advisory_lock, pg_try_advisory_lock, pg_advisory_unlock, pg_advisory_unlock_all, and the _xact_ and _shared variants, in one-key and two-key forms

---

Security

Capability	Details
Roles	CREATE ROLE / CREATE USER with attributes such as LOGIN and NOLOGIN
Privileges	GRANT and REVOKE on tables, enforced at plan time. A denied statement returns SQLSTATE 42501
Column-level privileges	GRANT and REVOKE on specific columns, unioned with table-level grants
Role membership	GRANT role TO role, with inheritance flowing transitively through the membership graph. Grant cycles are rejected
Session role switching	SET ROLE, SET SESSION AUTHORIZATION, and RESET, gated by membership
Predefined roles	Eight bootstrapped at first boot: pg_read_all_data, pg_write_all_data, pg_signal_backend, pg_maintain (gates VACUUM, ANALYZE and REFRESH MATERIALIZED VIEW), pg_checkpoint (gates CHECKPOINT), pg_read_server_files / pg_write_server_files (gate COPY to and from a server-side file), and pg_monitor (widens what a role sees of another role's session in pg_stat_activity; see Monitoring Sessions)
Row-level security	CREATE / ALTER / DROP POLICY, ALTER TABLE ... ENABLE / DISABLE / FORCE ROW LEVEL SECURITY, with USING and WITH CHECK enforcement and bypass semantics for the table owner
Object ownership	DROP TABLE, ALTER TABLE, CREATE INDEX, and DROP INDEX require the table's owner or a superuser, SQLSTATE 42501 otherwise. GRANT on an object requires its owner, a superuser, or holding every granted privilege WITH GRANT OPTION
Role administration	CREATE ROLE and DROP ROLE need CREATEROLE or superuser; granting SUPERUSER, REPLICATION, or BYPASSRLS needs superuser, and so does altering or dropping a role that already holds SUPERUSER; ALTER ROLE otherwise lets a role change its own password, connection limit, and expiry, but nothing privilege-bearing

---

Programmability

Capability	Details
Functions	CREATE FUNCTION in PL/pgSQL and in SQL, called with SELECT fn(...)
Procedures	CREATE PROCEDURE and CALL, including transaction control (COMMIT and ROLLBACK) inside a procedure body
Anonymous blocks	DO blocks
Arguments	Named and $n positional arguments, argument coercion, and STRICT null-in null-out handling
Exception handling	EXCEPTION blocks inside function and procedure bodies
Row triggers	BEFORE and AFTER triggers on INSERT, UPDATE, and DELETE, able to skip or rewrite the affected row
Statement triggers	BEFORE and AFTER statement-level triggers, including transition tables and TRUNCATE triggers
INSTEAD OF triggers	INSTEAD OF triggers on views
Event triggers	ddl_command_start, ddl_command_end, and sql_drop, with WHEN TAG IN (...) filtering

---

SQL features

Capability	Details
Joins	Inner, left, right, full, semi, and anti-semi joins, LATERAL joins, plus subqueries
Common table expressions	WITH clauses, including WITH RECURSIVE
Writable CTEs	WITH cte AS (INSERT / UPDATE / DELETE ... RETURNING ...) consumed by the main statement
Window functions	16 functions: ROW_NUMBER, RANK, DENSE_RANK, NTILE, PERCENT_RANK, CUME_DIST, LAG, LEAD, FIRST_VALUE, LAST_VALUE, NTH_VALUE, and SUM / COUNT / AVG / MIN / MAX over a window
Upsert	INSERT ... ON CONFLICT DO NOTHING and DO UPDATE, with excluded.*, a WHERE clause, and RETURNING on both arms
DML	INSERT, UPDATE, DELETE, MERGE, TRUNCATE, UPDATE ... FROM (a derived table or a CTE as its source, not only a table), DELETE ... USING (a derived table as its source), and RETURNING
Bulk load	COPY ... FROM and COPY ... TO in TEXT (the default when no FORMAT is given), CSV, TSV and PostgreSQL binary format, including COPY FROM STDIN and COPY TO STDOUT
Set operations	UNION, INTERSECT, and EXCEPT, with ALL variants
Grouping	GROUP BY, GROUPING SETS, ROLLUP, CUBE, and the GROUPING(...) function
Aggregates	Standard aggregates plus STRING_AGG and ARRAY_AGG
Expressions	CASE, BETWEEN (SYMMETRIC and ASYMMETRIC both), LIKE / ILIKE, COALESCE, NULLIF, IN, and EXISTS
Constraints	PRIMARY KEY, FOREIGN KEY, NOT NULL, CHECK, and DEFAULT
Views	Standard views and materialized views
Schemas	CREATE SCHEMA and schema-qualified names

---

Data types

Type	Notes
SMALLINT, INTEGER, BIGINT	Native 16, 32, and 64-bit integers
REAL, DOUBLE PRECISION	IEEE 754 single and double precision
NUMERIC / DECIMAL(p,s)	128-bit decimal with precision and scale
BOOLEAN
VARCHAR(n), TEXT, CHAR(n)	UTF-8 encoded
DATE, TIME
TIMESTAMP, TIMESTAMPTZ	Microsecond precision, with and without time zone
INTERVAL
UUID	Native type, with text and binary wire formats
BYTEA	Binary strings
INET, CIDR	Network addresses, parsed, printed, compared, sorted, and hashed on their stored representation. CIDR rejects a value with a host bit set to the right of its mask, as PostgreSQL does
MONEY	Casts from integer and NUMERIC values, and to NUMERIC
JSONB	Native storage with containment and path operators and the JSONB function set
ARRAY	Arrays of scalar element types, with ARRAY[...] and '{...}' literals, subscripting, ARRAY_AGG, and UNNEST
ENUM	CREATE TYPE ... AS ENUM, ordered by definition order, with pg_enum introspection (its oid and enumtypid are oid-typed, so a typed driver's own type discovery resolves an enum column)
Composite types	CREATE TYPE ... AS (...), with ROW(...) construction and field access
Domains	CREATE DOMAIN with CHECK, DEFAULT, and NOT NULL
VECTOR, HALFVEC, SPARSEVEC	pgvector's native embedding types: same typmods, text and binary formats, operators (<->, <#>, <=>, <+>), functions, casts, and avg/sum aggregates
BIT(n), BIT VARYING(n)	Native fixed and variable-length bit strings, plus pgvector's <~>/<%> (hamming_distance/jaccard_distance)

---

Vector similarity (pgvector)

ScramDB implements pgvector's SQL surface directly: the vector, halfvec, and sparsevec types (same typmods, same text and binary formats, same error messages and SQLSTATEs), the distance operators (<->, <#>, <=>, <+>, plus <~> and <%> over bit), every function (l2_distance, inner_product, cosine_distance, l1_distance, hamming_distance, jaccard_distance, vector_dims, vector_norm, l2_norm, l2_normalize, binary_quantize, subvector), every cast pgvector defines between the three vector types and PostgreSQL's numeric array types, and the element-wise avg/sum aggregates. Existing pgvector client code, ORMs, and library registration (psycopg's register_vector, JDBC's binary vector codec) work unchanged, since they discover the types and operators by name, not by a fixed OID: to_regtype('vector'), 'vector'::regtype::oid, and a pg_type lookup by name or oid all answer, and a driver's own type discovery query (the one tokio-postgres runs for an unknown type) resolves the family with oid-typed parameters and results.

The index methods are anode (a single node's table) and manode (a sharded, distributed table), ScramDB's own engines behind pgvector's opclasses (vector_l2_ops, vector_cosine_ops, and the rest keep their pgvector names, since they name the distance, not the engine). Existing DDL written against hnsw or ivfflat needs only the method name changed:

-- pgvector
CREATE INDEX ON items USING hnsw (embedding vector_l2_ops);

-- ScramDB: same opclass, same options, one word different
CREATE INDEX ON items USING anode (embedding vector_l2_ops);

---

Behavior differences

ScramDB aims to match PostgreSQL semantics. The following points are where current behavior differs, so there are no surprises.

• DDL is not transactional. CREATE, ALTER, and DROP take effect immediately. They are not undone by a later ROLLBACK, and a statement error after a DDL change does not roll that change back. In PostgreSQL most DDL runs inside the transaction.
• Advisory lock keys are literals. The pg_advisory_lock family accepts integer literals or bind parameters as the lock key. A key computed from a column expression (for example pg_advisory_lock(id) FROM t) is not supported.
• Arrays are single-dimensional. Any supported element type works, including a VARCHAR(n)/CHAR(n), an enum, a domain, or a composite type. A multi-dimensional array literal ('{{1,2},{3,4}}') or one with an explicit lower bound ('[0:1]={5,6}') is refused with SQLSTATE 0A000, where PostgreSQL accepts both. A nested ARRAY[[1,2],[3,4]] constructor is not refused today: it is stored as its four elements in order and reads back as {1,2,3,4}, where PostgreSQL returns {{1,2},{3,4}}.
• ANALYZE returns rows. PostgreSQL's ANALYZE returns no result set. ScramDB's returns one row per table analyzed, with the table name, the number of rows analyzed, and the number of columns. A client that expects an empty result will need to drain it.

For features that are not yet available, see Limitations.