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/sqlwith 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. A parameter has the type it was declared with wherever it stands, as in PostgreSQL, and an undeclared one the type its context gives it: a client that declares text for a comparison with an int column gets 42883, one that stores a declared text into an int column or mixes it with an integer in a CASE, COALESCE or UNION gets 42804, and a call that two overloads fit equally is 42725; declare the type the column has, or cast the parameter ($1::int). Describe announces each parameter's and each column's type as the statement runs |
| Prepared statements | PREPARE / EXECUTE / DEALLOCATE, plus $1 style bind parameters over the wire; EXECUTE evaluates each argument once and converts it to its declared type as PostgreSQL does (Plan Cache) |
| 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 and md5 password authentication, PostgreSQL's stored password forms, 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 (each sequence under the schema it was made in, with PostgreSQL's precision and cycle columns), triggers, routines, parameters (one row per argument of each CREATE FUNCTION and CREATE PROCEDURE, joined to routines by specific_name), table_privileges, column_privileges, domains, character_sets, collations and PostgreSQL's four fixed tables sql_features, sql_implementation_info, sql_parts and sql_sizing (the rows PostgreSQL 18.6 holds; is_supported is that server's declared conformance, not a per-feature audit of this engine). A domain is listed in domains with its base type's data_type, length, numeric facts, udt_name and default (a domain over a domain as USER-DEFINED, naming the domain under it), a column over a domain shows the same facts in columns, pg_type.typtypmod of a domain is its base type's modifier and pg_attribute.atttypmod of a column over a domain is -1, as PostgreSQL shows them, and a declared timestamp, time or interval precision and an interval's fields show as datetime_precision and interval_type (pg_type.typtypmod of an array domain is its element's, and domain_default is pg_type.typdefault, the default as PostgreSQL's text of it, made when the domain was created or its default set). 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 whatever its schema, information_schema.views, table_privileges and column_privileges list only what the reading role may see (a view it owns or holds a privilege on, a grant it made or holds, or PUBLIC's) with a table-wide grant of a column privilege listed on every column and an owner's own privileges listed as PostgreSQL lists them (granted by the owner to itself, grantable), information_schema.views shows a view's text only to a role that owns it (itself or through a role it inherits from) and NULL to every other, information_schema.tables lists a relation only to a role that owns it or holds a privilege on it or on one of its columns, pg_attribute.attacl holds a column's own grants, 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. information_schema.columns lists a column only to a role that owns its table or view (itself or through a role it inherits from) or holds SELECT, INSERT, UPDATE or REFERENCES on the table or on that column, and information_schema.domains lists a domain only to a role that owns it or holds USAGE on it, and information_schema.routines and parameters list a routine only to a role that owns it (itself or through a role it inherits from) or holds EXECUTE on it, with routine_definition shown only to the owner's roles, as PostgreSQL does, so a role is told of no column, domain or routine it could not use; another session's temporary table is listed to no one, a superuser included. The 65 views and 4 tables of information_schema are relations of the catalogs as in PostgreSQL 18.6: pg_class lists them (relkind v and r, with a row type), pg_attribute their columns, and pg_type their five domains and a row type and array type for each, under PostgreSQL's own oids, so a join over them answers as PostgreSQL's does and 'information_schema.tables'::regtype and 'information_schema.yes_or_no[]'::regtype resolve. Every table, view, materialized view and partitioned table has a row type and an array type in pg_type from the day it is made (pg_class.reltype), drawn from the one oid counter, kept through a rename, a move and a restart and the same on every node (relations an older store held, bound by each node alone at its first start, are agreed through the cluster's metadata group once, whichever node starts first); the array type of a name cut at the 63-byte limit is _ and the first 62 bytes, and the next name that meets it takes _1, as in PostgreSQL. Every relation pg_class lists in information_schema answers a read: the fifteen relations of objects the engine never holds (foreign data wrappers, foreign servers, foreign tables, user mappings and their options, transforms) are empty and information_schema_catalog_name is one row, and the role views (applicable_roles, enabled_roles, administrable_role_authorizations), the grant views (role_table_grants, routine_privileges, usage_privileges, udt_privileges and their siblings), the typed views (user_defined_types, domain_constraints, element_types, column_udt_usage) and the usage views (constraint_column_usage, view_table_usage, routine_table_usage and their siblings) are read from the engine's own records. The built-in functions are in no routine view; see Not yet supported. routine_privileges lists the built-in routines of pg_catalog, each overload of one to PUBLIC and to the bootstrap superuser. pg_depend and pg_aggregate exist with PostgreSQL's columns and list no rows yet |
| Monitoring views | pg_stat_activity (one row per session: state, last statement, start times, wait event, with PostgreSQL's privacy rule), pg_stat_database (one row per database and the row of the shared objects, numbackends the sessions connected now; the transaction, block and tuple counters are not kept per database and are NULL), pg_stats (the column statistics ANALYZE collected), pg_class.reltuples (the analyzed row count) and pg_stat_user_indexes (one row per index with the scans that read it, which add up to the table's pg_stat_user_tables.idx_scan). The size functions answer as PostgreSQL's do: pg_relation_size with its forks, pg_table_size, pg_indexes_size, pg_total_relation_size, pg_database_size by name or oid (CONNECT on the database or pg_read_all_stats required), pg_size_pretty and pg_size_bytes. An index is kept in memory, so its size is the memory it holds, the figure scram_indexes and Index memory show; 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); the settings that act are the planner switches (Planner Switches), work_mem (Memory), synchronous_commit (Synchronous Commit), fsync (fsync), track_counts (Track Counts) and plan_cache_mode (Plan Cache) |
| 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, and one waiting for a lock, which stops waiting at once |
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) |
| Prepared transactions | PREPARE TRANSACTION, COMMIT PREPARED, and ROLLBACK PREPARED, with pg_prepared_xacts introspection, off by default ([resilience] max_prepared_transactions). They commit through ScramDB's own commit path, not a separate two-phase commit protocol. Prepared transactions survive client disconnect. On a cluster's distributed tables a prepared transaction is kept by the shard groups: once PREPARE TRANSACTION succeeds, COMMIT PREPARED succeeds from any session on any node after any restart, a later writer of what it touched fails with 40001, and its name is unique across the cluster (Prepared Transactions) |
| 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 two writers of a row are decided at COMMIT: the second fails with 40001, so retry it (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 |
| Database privileges | CONNECT and TEMPORARY are held by PUBLIC on every database and enforced at login and at every temporary table, view or sequence, SQLSTATE 42501 otherwise |
| Column-level privileges | GRANT and REVOKE on specific columns, unioned with table-level grants |
| Table privilege lists | pg_class.relacl lists the grants of a table, view, materialized view and sequence as PostgreSQL does (NULL until a table-level GRANT or REVOKE, items in the order PostgreSQL keeps, the owner's items rewritten in place by ALTER ... OWNER TO, MAINTAIN as m); ALTER DEFAULT PRIVILEGES reaches every relation a statement makes, and a dropped view takes its grants with it. LOCK TABLE asks the privilege of its mode |
| Schema privilege lists | pg_namespace.nspacl lists the grants of a schema as PostgreSQL does (NULL until a GRANT or REVOKE, the built-in schemas and public with their default lists, the owner's item real data, \dn+ prints it) |
| Role membership | GRANT role TO role [WITH ADMIN, INHERIT, SET {OPTION, TRUE, FALSE}] and REVOKE {ADMIN, INHERIT, SET} OPTION FOR: privileges flow along INHERIT edges, SET ROLE along SET edges, and ADMIN decides who may grant and administer. Grant cycles are rejected |
| Session role switching | SET ROLE, SET SESSION AUTHORIZATION, and RESET, gated by membership. SET LOCAL values, role and authorization included, end with the transaction and with a failed block, and DISCARD ALL restores the login identity |
| Predefined roles | All sixteen: pg_read_all_data, pg_write_all_data, pg_signal_backend, pg_maintain (holds MAINTAIN on every relation: gates VACUUM, ANALYZE, CLUSTER, REFRESH MATERIALIZED VIEW, REINDEX and LOCK TABLE), pg_checkpoint (gates CHECKPOINT), pg_read_server_files / pg_write_server_files (gate COPY to and from a server-side file; the first also widens the paths the file functions may name), pg_execute_server_program (gates COPY ... PROGRAM, which runs the command as /bin/sh -c), pg_read_all_stats and pg_monitor (widen what a role sees of another role's session in pg_stat_activity; see Monitoring Sessions), pg_read_all_settings (reads the settings only a superuser sees otherwise), pg_database_owner (its implicit member is the owner of the database a session is in, and public belongs to it; see Roles and Privileges), and the rest, which exist and can be granted but have nothing here to gate (pg_stat_scan_tables, pg_create_subscription, pg_signal_autovacuum_worker, pg_use_reserved_connections) |
| 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 | PostgreSQL 16's model: CREATEROLE creates roles and administers only the roles it holds the ADMIN option on (it holds it on every role it creates), confers only attributes it holds and never SUPERUSER; a role that has SUPERUSER is created, altered, renamed and dropped only by a superuser; any role changes its own password. Role names fold like identifiers, and public, none and pg_ names are reserved. CREATE USER and ALTER USER follow PostgreSQL's forms |
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, a DO block, and a CALL or DO that such a body runs |
| 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; a BEFORE row trigger reads the rows its own statement already wrote, so a row limit or a numbering counts them |
| 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; a foreign key is judged and an AFTER ROW trigger fires when the statement ends, so one arm may insert a parent row and another arm its child, and a trigger sees the rows of every arm |
| 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 (MATCH SIMPLE or MATCH FULL), NOT NULL, CHECK, and DEFAULT |
| Views | Standard views and materialized views |
| Schemas | CREATE SCHEMA and qualified names of one to three parts, as PostgreSQL reads them (see 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, BIGINT, NUMERIC and text, and to NUMERIC and text, as pg_cast lists them (a REAL, DOUBLE PRECISION or SMALLINT either way is 42846); +, -, * and / with the operands pg_operator lists, no unary sign; CASH_WORDS |
JSON | The document kept as its text, exactly as written (whitespace, key order, duplicate keys), with the json operators, functions and aggregates; no equality, ordering or index, as in PostgreSQL |
JSONB | Native storage with containment and path operators and the JSONB function set |
ARRAY | Arrays of any supported element type with PostgreSQL's dimensions (up to six) and lower bounds, with ARRAY[...] and '{...}' literals, subscripts and slices, ARRAY_AGG, UNNEST, and the array functions, in text and binary on the wire |
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, field access and field assignment (SET c.f = ..., INSERT INTO t (c.f)) |
| Domains | CREATE DOMAIN with CHECK, DEFAULT, and NOT NULL |
| Casts | CREATE CAST and DROP CAST with WITH FUNCTION, WITHOUT FUNCTION, WITH INOUT, AS ASSIGNMENT and AS IMPLICIT |
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 inside a transaction block. A table, view, function, sequence, index, type, domain or schema made in a block is invisible to other sessions until
COMMIT, andROLLBACKremoves it, as in PostgreSQL. For a statement not covered there, run it outside a transaction block. See DDL inside a transaction block and Not yet supported. - A cluster's sequences hand out values in runs. On a cluster,
nextvalnever hands out a value twice and its values increase within a session, but each node draws from a run of values it takes from the sequence, so sessions on different nodes may see values out of time order, and a node that stops leaves the rest of its run unused, as PostgreSQL'sCACHEsetting does across sessions. A sequence drawn rarely takes one value at a time. - Hash partitions place rows by ScramDB's own hash. Queries through a hash-partitioned table answer as in PostgreSQL, but which partition holds a given row differs, because ScramDB uses its own (faster) hash rather than PostgreSQL's
hash_*_extendedfunctions. Restore a PostgreSQL dump of a hash-partitioned table through the partitioned parent, not into individual partitions. ANALYZEreturns rows. PostgreSQL'sANALYZEreturns 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.JSON_SERIALIZEof ajsonbvalue answers its text. PostgreSQL 18.6 answers the binary container of the value (a one-byte\x01for'{"a":1}'::jsonb). ScramDB answers the documented result: the canonical JSON text of the value, asjsonb::textwrites it.
For features that are not yet available, see Not yet supported.