# Database pools ## Introduction A pool can front a **PostgreSQL database** instead of an HTTPS origin. Callers send SQL over HTTP to the pool's own hostname and get rows back. Every query competes for the pool's concurrency limit, runs as a Postgres role you defined, and lands in the access log attributed to the key that ran it. Callers hold a short-lived key to a pool, not a database credential, and every query is subject to the pool's concurrency limit. Offboarding someone is revoking one grant. Choose the backend when you create the pool. It cannot be changed afterwards. Every plan includes database pools; concurrency, queue, and request limits are the same as for an HTTP pool on that plan. ## The endpoints A database pool serves query at the **pool hostname itself**. Structure and job results are the other two paths. There is no origin behind it, so any other path returns `404`. ``` POST https://{pool}.nthpool.cloud GET https://{pool}.nthpool.cloud/structure GET https://{pool}.nthpool.cloud/__nth/jobs/{jobId} ``` On a [verified custom hostname](/docs/domains) these become `https://warehouse.acme.com` and so on, which is the address a data team would actually circulate. Every request needs `X-Nthpool-Key`, exactly like the proxy. Pool keys still carry a read/write access level, the same field as on an HTTP pool. On a database pool that field does not cap HTTP methods: `POST /` is how every query arrives. Read-only is the role you created, plus the session `default_transaction_read_only` guard. A writes-allowed key does not get a write role in v1. There is none. See [Access](/docs/pools/access). ## Running a query ```bash curl https://warehouse.nthpool.cloud \ -H "X-Nthpool-Key: $NTHPOOL_KEY" \ -H "content-type: application/json" \ -d '{ "queries": [ { "sql": "select country, count(*) from orders where created_at > $1 group by 1", "params": ["2026-01-01"] } ], "schema": "analytics", "maxRows": 500, "waitMs": 10000 }' ``` ```json { "results": [ { "columns": [{ "name": "country", "type": "text" }, { "name": "count", "type": "int8" }], "rows": [{ "country": "US", "count": 1420 }, { "country": "CA", "count": 233 }], "rowCount": 2, "capped": false, "durationMs": 412, "rowFormat": "object" } ], "durationMs": 412 } ``` | Field | Meaning | |---|---| | `queries` | 1–5 statements, pipelined, with per-statement error isolation. `results[i]` is `queries[i]`. One statement is still this array. | | `schema` | Optional. Sets `search_path` to this schema once, before the first statement. Simple identifier only (`analytics`, not `analytics, public`). | | `maxRows` | Row cap per statement, clamped to the pool's setting. | | `waitMs` | How long you will hold the request open. Default 30s, max 120s. | | `rowFormat` | `object` (default) or `array`. | Rows come back as objects by default so each value is keyed by column name. `rowFormat: "array"` is the compact positional form when you already have `columns` and want a smaller payload. ### Parameters are bound, not interpolated `params` accepts strings, numbers, booleans, and `null`. They are sent as untyped text and Postgres infers the type from context, so cast when the inference would be wrong: ```json { "queries": [{ "sql": "select * from events where day = $1::date", "params": ["2026-01-01"] }] } ``` For JSON or arrays, serialize the value yourself and cast it: `$1::jsonb`. ### One statement, structurally Each `sql` string runs through the extended query protocol, which executes exactly one statement. Semicolon soup in one string is rejected by Parse, not filtered by us. Every call is `queries`. One statement or five, same body and the same `{ results, durationMs }` response. That is one connection: each statement is its own `BEGIN READ ONLY` … `SET LOCAL` … statement … `COMMIT`, flushed as a pipeline. A failing statement does not break the others — `results[i]` is either the success shape or a per-item `db_error` (`index`, and `name` if that item had one). The HTTP status is 200 when the connection ran; the client interprets `results`. Connect failures are still 502/503. Transaction-mode poolers allow this; statement-mode PgBouncer does not, and we do not target that. ```json { "queries": [ { "sql": "SELECT 1 AS n", "params": [] }, { "sql": "SELECT id FROM users WHERE id = $1", "params": [1], "name": "user" } ] } ``` ```json { "results": [ { "columns": [{ "name": "n", "type": "int4" }], "rows": [{ "n": 1 }], "rowCount": 1, "capped": false, "durationMs": 4, "rowFormat": "object" }, { "name": "user", "columns": [{ "name": "id", "type": "int8" }], "rows": [{ "id": 1 }], "rowCount": 1, "capped": false, "durationMs": 3, "rowFormat": "object" } ], "durationMs": 8 } ``` ```json { "results": [ { "columns": [{ "name": "n", "type": "int4" }], "rows": [{ "n": 1 }], "rowCount": 1, "capped": false, "durationMs": 12, "rowFormat": "object" }, { "error": "db_error", "sqlstate": "42601", "message": "syntax error", "index": 1 }, { "columns": [{ "name": "n", "type": "int4" }], "rows": [{ "n": 3 }], "rowCount": 1, "capped": false, "durationMs": 9, "rowFormat": "object" } ], "durationMs": 40 } ``` Max 5. An empty array, leftover `sql`, or leftover top-level `params` is a 400. Bind parameters on each `queries` item. `name` labels the matching result and, when repeated with the same SQL, is a prepared statement. Duplicate names are fine; order is the contract. `maxRows` and `maxResultBytes` apply per statement. Transactions you start yourself, cursors, `COPY`, and `LISTEN` are not supported. A database pool is not a session; see [what this is not](#what-this-is-not). ## Long queries: one endpoint, two timings You state how long you are willing to wait. Finish inside `waitMs` and you get rows. Outlast it and you get a handle to the **same, still-running query** — it is never restarted. ```json { "jobId": "job_9f2c…", "status": "running", "pollUrl": "https://warehouse.nthpool.cloud/__nth/jobs/job_9f2c…" } ``` Send `waitMs: 0` (or `X-Nthpool-Async: 1`) to never wait at all. A batch becomes **one** job for the whole request, not one job per statement. Attach a [webhook destination](/docs/webhooks) and the result is delivered to you when it lands, signed, with no polling. The job inlines `detail.results` when it fits the body cap; otherwise `resultUrl` is the same job GET. Handles require **Async** to be enabled on the pool, under Concurrency. With it off, a query that outlasts `waitMs` keeps running until the pool's request timeout rather than turning into a handle, and `waitMs: 0` is refused with `async_disabled`. `waitMs` measures the **query**, not the queue. On a busy pool a request waits for a concurrency slot first, so the longest a call can take is the pool's queue timeout plus `waitMs`. Size your client's own timeout for the sum, or send `waitMs: 0` and never hold a connection at all. MCP clients apply their own tool-call timeout, so a synchronous query is bounded by whatever the client allows. Send `waitMs: 0`, end the turn, and read the result from the handle later. ### Reading a stored result ``` GET /__nth/jobs/{jobId}?rowFormat=array ``` A completed job is the same body as the sync 200 (`results` plus `durationMs`). The read hits storage, never the database, and takes no concurrency slot. Stored results expire on the pool's **result TTL** — one hour by default, deliberately shorter than the async job TTL. A query result is a table extract, not a side effect of one. ### Caps `capped: true` means the pool stopped the result short. `cappedReason` says which ceiling fired: - `max_rows`: pool row cap (`maxRows`) - `max_result_bytes`: pool byte ceiling (`maxResultBytes`) A SQL `LIMIT` alone does not raise those ceilings. New pools default to 10,000 rows and 8 MiB; raise the pool limits (or narrow the select list) when you need more. `affectedRows` is the size of the result Postgres produced before those ceilings trimmed the payload. It can be larger than `rowCount` when the byte ceiling wins first. ## Reading the structure ``` GET /structure GET /structure?schema=analytics GET /structure?schema=analytics&table=events GET /structure?schema=analytics&table=events&include_column_details=1 ``` The payload follows the filters. With no query string you get the schemas the role can see and table names. `schema=` lists tables in that schema. `schema=` and `table=` together list column names and types for that table. `include_column_details=1` adds nullability, defaults, and comments, and only applies when `table` is set. If the catalog scan hits a row or byte ceiling, the response sets `capped` and `cappedReason` (`max_rows` or `max_result_bytes`), the same reasons a query result uses. Narrow with `schema=` or `table=` rather than raising a limit. Structure answers are cached briefly and shared across everyone on the team. Add `refresh=1` after a migration to bypass the cache. ## Setting up the role **The Postgres role is the security boundary.** nthbouncer decides which role you connect as, for how long, and how many at once — the database decides what that role may read. nthbouncer does not inspect your SQL, so the role's grants are the only enforcement. Create a role with exactly the access you intend to expose: ```sql CREATE ROLE nthbouncer_ro LOGIN PASSWORD '…'; GRANT CONNECT ON DATABASE analytics TO nthbouncer_ro; GRANT USAGE ON SCHEMA public TO nthbouncer_ro; GRANT SELECT ON ALL TABLES IN SCHEMA public TO nthbouncer_ro; ALTER ROLE nthbouncer_ro SET default_transaction_read_only = on; ALTER ROLE nthbouncer_ro SET statement_timeout = '30s'; ``` The last two lines are defence in depth. The pool sets both on every session anyway — but a role that is read-only *in the database* stays read-only even if we have a bug. `GRANT USAGE ON SCHEMA` is the line people forget. Without it the role can log in and see nothing, and every query reports that the relation does not exist. **Test connection** counts the tables your role can see so you find that in seconds rather than in a support thread. **v1 is read-only.** There is no write credential slot and no flag for one. ### Password encryption The role must use `scram-sha-256`. `md5` and cleartext passwords are refused, deliberately — md5 is a broken password hash, and cleartext hands your password to anything that can terminate the connection. If your server still defaults to md5: ```sql SET password_encryption = 'scram-sha-256'; ALTER ROLE nthbouncer_ro PASSWORD '…'; ``` ## Connecting The database must be **reachable from the public internet** over TLS. That covers Neon, Supabase, PlanetScale, RDS and Aurora with a public endpoint, Redshift with public accessibility, and anything else you can dial from outside your network. Connections are always TLS. The default mode is **Verify certificate**: the server certificate must chain to a trusted CA and match the hostname. Direct Amazon RDS and Aurora instance endpoints are recognized automatically and trust the AWS RDS certificate authority. **RDS Proxy** uses public ACM certificates and verifies against the normal public trust store (no RDS CA override). **Require SSL** encrypts the connection without verifying the certificate. Use it when a private or custom CA is in the way. Uploading your own CA is not supported yet. A database with no public address cannot be reached. Use a public read replica or a bastion host. ### Why the host must be a hostname The host field takes a bare public hostname; a bare IP address is rejected. The connection is verified against the certificate presented for the host you entered, and managed Postgres certificates name hostnames, not addresses. If you only have an address, point a hostname you control at it and use that. ### Test connection Every database pool starts in **needs connectivity** and offers **Test connection**, which opens a real connection and reports which stage failed: DNS or firewall, TLS, authentication, a missing database, or a role that connected but cannot see anything. It also reports the role it authenticated as, whether the read-only guard took effect, and how many tables that role can see. ## Which engines work Anything that speaks the PostgreSQL wire protocol: PostgreSQL itself, Aurora PostgreSQL, Redshift, Timescale, Materialize, Greenplum, CockroachDB. You never tell us which one it is. The query path is identical on all of them, and the engine is detected from the server at connect time. Only schema introspection differs, and an engine we do not recognize degrades that one feature with a clear message rather than breaking the pool. MySQL is not supported. ## Limits | Setting | Default | Ceiling | |---|---|---| | Statement timeout | 30s | 10m | | Max rows | 10,000 | 50,000 | | Max result bytes | 8 MiB | 16 MiB | | Result TTL | 1h | 24h | The byte ceiling can stop a result before the row cap does. Wide `jsonb` rows exhaust the byte budget long before the row count does. Both caps are enforced while rows accumulate, and `statement_timeout` is set on the server for every query — so a runaway query is stopped by the database itself, not only by us dropping the connection. ## Errors Failures carry the Postgres `SQLSTATE`, which is the field stable enough to act on: ```json { "error": "db_error", "sqlstate": "42501", "message": "permission denied for table customers", "hint": "grant SELECT on the table to the role" } ``` | Status | Meaning | |---|---| | `400` | The statement is wrong — syntax, unknown column, bad cast. | | `403` | The database refused it: read-only violation (`25006`) or missing privilege (`42501`). | | `429` / `504` | The pool is at capacity, or no slot came free in time. | | `502` | The database is unreachable, or the connection failed. | | `504` | The query outlived the pool's request timeout and was cancelled. | A `403` is recorded as its own outcome in your logs, with the full statement text. A rejection there is an attempt to escape read-only mode, and the statement is the only useful evidence of what was tried. Cancelled queries are cancelled **at the database**, not just dropped. Without that, a runaway query would keep burning your database after the caller was gone and the slot had already been released. ## From an agent Connect once as in [Agents](/docs/agents). The grant scopes the agent to specific pools, `start_pool_session` mints a short-lived key, and the agent gets `pool_schema`, `pool_query`, `get_query_result`, and `pool_count_rows`. See [MCP server](/docs/mcp) for the tool list. Results render as TSV in tool output rather than JSON — measurably fewer tokens for the same rows. `pool_count_rows` exists so an agent can size a table before querying it instead of fetching rows to find out how many there are. The two access levels differ in what they reach. **Read** gets the schema and whole-table counts. **Session** adds queries and *filtered* counts — filtered, because a count you can narrow at will can be repeated to reconstruct the rows it counts, which is the same access a query has by a longer route. The consent screen says which is which before anyone approves it. ## Logs Database queries appear in [Logs](/docs/pools/logs) with `requestKind: query` and their own columns: the statement(s) (truncated; numbered when the request sent more than one), how many statements ran, duration, rows returned, whether it was capped, and the key that ran it. That last column is the point. "Who ran what against production" is answerable here because the pool knows who is asking — which is precisely what a shared connection string cannot tell you. ## What this is not **Not a replacement for your application's connection string.** Nothing here is meant to sit between an app and its primary database. That needs connection reuse and sub-millisecond overhead, and it is what pgbouncer, RDS Proxy, and Hyperdrive are for. This is for agents, analysts, and scheduled analytical work: low query rate, long duration, high value per query. **Not a connection pooler.** One connection per query, opened and closed. The concurrency limit is the pool; the TCP connection is disposable. That is also why a session's state can never leak to the next caller — there is no next caller on that connection. **Not a BI tool.** No saved queries, no charts, no scheduling. Webhook delivery is the integration point; your cron calls it. Concurrency is the reason this exists. Twelve agents each deciding to run a heavy aggregation against the read replica at 9am is the failure the pool prevents — and a connection pooler would not help, because multiplexing twelve connections down to four still runs twelve expensive queries.