Performance & PostgreSQL pools

This page says what tablewalk costs a browser and a database, and how to size and measure it.

{ "connections": [{ "name": "app", "url": "${DATABASE_URL}", "pool": { "read": 16 } }] }

The client

The client ships built in the package; npx tablewalk serves it and nobody builds anything.

First page	Script, brotli
Sign-in screen or public form	163 KB
An App page	314 KB
The Workbench	318 KB

• Only what the page draws. The SQL editor, dashboards and charts, boards and calendars, the New form and the account dialogs load the first time they are used, and the server preloads each page’s part. npm run build fails when the first paint passes its budget (scripts/bundle-budget.mjs).
• A return visit downloads nothing. Assets are content-named and served immutable; index.html answers 304.
• Precompressed. Each asset has a .br and .gz beside it.
• Readable in devtools. Minified, with source maps shipped beside it.

An App served from a build also starts faster, since nothing is evaluated for its definition.

The server

What Compass asks of the database is counted per request, on SQLite and PostgreSQL, so an unintended statement fails a test.

• Few statements. The catalog is read once per connection: Compass’s first view after start is 1 statement, a list page 2.
• Bounded counts. A filtered list counts to 10,000 and says 10,000+, with a click to count exactly; turning a page does not recount. A list’s tab badges are one /api/counts request, and a dashboard’s breakdowns one /api/breakdowns request — a record page’s counted figures too. A record page’s fields sections read their row once, together.
• Pages by key. Next, Previous and Last read the rows after or before the ones on screen, so a deep page costs what the first does. A page number typed in the box is read by offset, and refused past 10,000 rows.
• Long SQLite reads off the thread. A count, filter or sort over a table of 250,000 rows or more (or a SQL view) runs in a child process, so it never holds other requests; it is stopped at 30 seconds.
• Jobs wake on commit. An idle --jobs host polls from every second to every 30. On PostgreSQL a command’s new facts also wake it at once, from any process, by LISTEN/NOTIFY (not behind a transaction-mode pooler).
• Index advice. --check-app (and tablewalk build) warns where a related list’s reference, a tenant ownership column or a large list’s sort leads no index, and, on PostgreSQL, where a large list’s search box or text filter (contains) has no pg_trgm index.
• Compressed replies. JSON of 1 KB or more is sent brotli or gzip.
• Revalidated metadata. The schema, App, pages and runtime answer 304 when unchanged, keyed to the signed-in person. Row-policy and tenant replies are never stored.
• Private pictures. A signed-in reader’s images are private, no-cache with Vary: Cookie, so a sign-out or withdrawn grant takes effect at once.
• Streamed exports. “All matching rows” streams CSV, JSON or NDJSON from POST /api/export in batches, with flat server memory.
• Inlined row scopes on PostgreSQL 12+ (NOT MATERIALIZED).

PostgreSQL pools

Each configured PostgreSQL database opens two pools lazily:

Setting	Readers	Writer
Maximum connections	4 by default; pool.read or --read-pool, 1 to 64	1, fixed
Acquisition timeout	10 seconds	10 seconds
Idle expiry	30 seconds, keeping 2 warm	30 seconds
Statement timeout	30 seconds	30 seconds
Read-only by default	Yes	Database default

Reads go through the read-only pool. Writes exist only on a connection marked "writable": true, through the one writer, which keeps edits, Undo and read-then-write transactions in commit order. Write throughput scales by adding pods.

Sizing the reader pool

A connection’s "pool": { "read": n } sizes its readers. --read-pool <n> (or TABLEWALK_READ_POOL) is the default for every PostgreSQL pool that names none, including tenant sources, the job host and startup checks. Anything but a whole number from 1 to 64 refuses the start, as do "pool.write" and "pool" on a non-PostgreSQL connection.

Capacity planning

A process opens at most read + 1 connections per PostgreSQL database — five by default. Across a deployment:

pods × databases × (read + 1)

plus, where they apply:

Also opened	Per process
A postgres:// authentication store	up to 4
--jobs on a PostgreSQL source	2: its stores, and one listening for new facts
A tenant App’s private sources	at most 2 × read + 2
Startup checks and query cancels	1 each, short-lived

Ten pods at "read": 16 is 170 connections, past a stock max_connections of 100; at the default it is 50. Stay under max_connections, or put a pooler in front.

A reader serves about 1000 / hold_ms reads a second: four readers at 5 ms is about 800 a second. Past a few thousand, the event loop is the limit and more pods is the answer.

Reading the pool meters

Every checkout is metered per pool. The support bundle’s capacity section carries the meters beside the event loop’s busy time, and readiness reports pool_saturated while a checkout queues or has recently timed out.

What the meters show	What to do
waiting above zero, holds short, event loop idle	The pool is the limit: raise pool.read.
Checkouts queued behind long holds	The database is slow: look at the statements.
Pools calm, event loop busy	The process is the limit: add pods.
timedOut rising	Checkouts gave up after 10 seconds.

Behind a transaction-mode pooler

PgBouncer in pool_mode = transaction and AWS RDS Proxy hand a server connection to a different client each transaction, so session settings would leak. Start with

tablewalk --pooler transaction      # or TABLEWALK_POOLER=transaction

and every guarantee is made per transaction instead:

• Nothing is set on a session. Each statement runs in its own BEGIN READ ONLY (BEGIN on the writer) with SET LOCAL statement_timeout.
• User-authored SQL is fenced by a savepoint per statement and reset before its transaction ends, so no setting, lock or prepared statement reaches the pooler’s next client.
• A cancelled read discards its client instead of sending pg_cancel_backend.

The switch is process-wide and also safe on a direct connection; it costs round trips (a single read becomes three). VACUUM, CREATE INDEX CONCURRENTLY and CREATE DATABASE are refused as user SQL in this mode.

• The authentication store is not covered: point auth.store at the database directly or through a session-mode pool.
• RDS Proxy pins every session-default connection; with --pooler transaction it can multiplex.
• MySQL keeps its own small pool: up to four read-only connections, opened only while reads overlap, and one write connection per database. "pool" and --pooler do not apply.

docs/postgresql.md covers the command journal, row policies and stores on PostgreSQL.

Load testing

A local harness, from a source checkout, drives a fresh Thread App with concurrent HTTP users and real Chromium journeys:

npm run test:load:smoke
npm run test:load -- --users 8 --browser-users 2 --duration 30 --rows 1000

Every run seeds its own database and loopback server; it takes no URL, host or port, so it cannot be pointed at a live App. Run npx playwright install chromium first if Chromium is missing.

Flag	Default	Range
--users	4	1–32 HTTP users (closed loop)
--arrival-rps	off	1–100 fixed arrivals a second; --users becomes the in-flight cap
--browser-users	1	1–4
--duration	15 s	1–300 measured seconds
--rows	500	24–10,000 issues
--http-p95-ms	1000	successful-request p95 gate
--ui-p95-ms	5000	successful-journey p95 gate
--max-error-rate	0	0–1
--production	off	use the built dist/ server and client instead of source mode

A run exits nonzero when a gate fails and writes report.json and report.md (p50/p95/p99, throughput, failures) to a temporary directory it prints. Journey time is synthetic, not INP, and the generator shares the machine: treat it as a regression guard, not a capacity certificate. npm run test:load -- --help lists every option.