Limits & Constraints

Limits & Constraints

Resource limits and constraints across the db9 engine and extensions.

Engine Limits

Resource	Limit
Statement timeout	12 h (default)
Idle transaction timeout	300s / 5 min (default)
View nesting depth	64 levels
Pending portals per connection	32
Timestamp precision	Microseconds
Encoding	UTF-8 only
Index / primary key entry size	~6,963 bytes per encoded key

Statement timeout can be changed per session

The default statement timeout is 12 hours (SHOW statement_timeout returns 43200000ms), so index builds and data migrations over the PostgreSQL wire protocol normally fit without raising it; PostgreSQL’s default is 0 (no limit). Set a shorter limit per session, for example SET statement_timeout = '30s'. For a role that is not a superuser, SET statement_timeout to 0 or to a value above 12 hours fails with 42501. Over the HTTP SQL API (db9 db sql) a request returns Error 504: upstream request timeout after about 15 seconds whatever the setting, so run long statements over the wire protocol.

Fractional-second precision

DB9 timestamps have microsecond precision, matching standard PostgreSQL. A TIMESTAMP(n), TIMESTAMPTZ(n) or TIME(n) column rounds stored values to the declared precision, on insert, on update and on ALTER COLUMN ... TYPE, and a cast expression such as '...'::timestamp(3) or '...'::time(3) rounds the same way. TIMETZ(n) / TIME(n) WITH TIME ZONE rounds too; TIMETZ has basic support only — see the compatibility matrix.

Long values can be stored but not indexed

A value only has to fit the key budget when it is part of an index entry or a primary key. An unindexed TEXT column holds a 10 KB string without complaint; add a btree index on that same column and the index build fails with 54000 — the rows stay. Once such an index exists, an INSERT or UPDATE carrying an oversized value is the thing rejected. The cap is on the encoded key — the column bytes plus per-index overhead, and for a non-unique index the primary key as well — so it applies to the combined width of a multi-column index, not to each column separately. Because a UNIQUE index does not carry the primary key, a wider key costs a plain index value bytes and costs a UNIQUE one nothing. See CREATE INDEX for the two error messages and the workarounds.

HTTP Extension Limits

Resource	Limit
Connect timeout	1s
Total timeout	5s
Max request body	256 KiB
Max response body	1 MiB
Max calls per statement	100
Max concurrent per tenant, per node	20

fs9 Extension Limits

Resource	Limit
Max file size	100 MB
Max glob total size	100 MB
Max file traversal	10,000 files
Max directory recursion	10 levels

pg_cron Limits

Resource	Limit
Job execution timeout	Default: 5 minutes; max: 30 minutes

Unsupported PostgreSQL Features

The following PostgreSQL features are not supported:

• SMALLINT / INT2 type — aliased to INTEGER (32-bit)
• REAL / FLOAT4 type — aliased to DOUBLE PRECISION (64-bit)
• CHAR(n) fixed-length type — aliased to VARCHAR(n) (no padding semantics)

One clarification on a feature often assumed missing: LISTEN / NOTIFY is supported, but only a pgwire session can receive — a session has to stay open, and notifications are delivered on commit. The stateless HTTP SQL API can send NOTIFY but cannot receive notifications; a LISTEN issued over it returns a LISTEN command tag and then never delivers anything.