Skip to content

Technology Data Access and Caching

mavrovde edited this page Nov 1, 2026 · 2 revisions

Technology: Data Access and Caching

Part of the Technology Evaluation. How the API talks to PostgreSQL, how it pools connections, and how it avoids repeating work.

Decision

  • The chosen language's native PostgreSQL driver, used so that a request's deadline also stops the query inside the database.
  • Typed queries generated from SQL and checked against the read-contract stub at generation time (sqlc for Go, clorinde for Rust, Kysely with generated view types for TypeScript).
  • A small application pool, hard-capped by the database role; no external pooler while there is one instance.
  • ETag/304 from source freshness plus an in-process result cache with single-flight.

Summary

Option Verdict Reason
pgx v5 (Go) Recommended (Go) sends a real cancel request when the context ends
tokio-postgres + deadpool (Rust) Recommended (Rust) explicit cancel token, wrapped by a guard; statement timeout is primary
sqlc (Go) Recommended (Go) typed code from SQL, checked against the contract stub
clorinde (Rust) Recommended (Rust) the same idea for Rust; smaller community
node-postgres (TypeScript) Recommended (TypeScript) client-side timeout only; the TypeScript design adds a statement timeout and its own cancel request
Kysely (TypeScript) Recommended (TypeScript) typed views without sharing code
Drizzle (TypeScript) Viable, not needed assumes it owns migrations
sqlx (Rust) Rejected dropping a future does not cancel the server query; compile-time checks need a database
Prisma Rejected weak PostGIS support; hides SQL and deadline control
PgBouncer Viable later central pooling with several instances or a replica
pgcat Rejected slower development; more complex than needed
Supavisor Rejected built for thousands of tenants
Read replica Viable later isolates API reads; needs a larger host
In-process LRU result cache Recommended polling clients repeat identical questions
HTTP ETag/Cache-Control (and an optional edge micro-cache) Recommended repeated polls cost no database work
Varnish Rejected duplicates the existing edge
A CDN for the API Rejected authenticated, licence-gated responses; keys would pass a third party

Drivers

pgx v5 (Go)

The leading Go driver and pool. When the request context ends, its cancel-request context watcher sends a PostgreSQL cancel request automatically; binary protocol; statement cache; batch and pipeline modes. Licence MIT.

tokio-postgres with deadpool (Rust)

Cancellation is explicit through a cancel token and documented as inherently racy: the cancel can arrive after the query finished and hit the next statement. The Rust design therefore makes the server-side statement timeout the primary stop, wraps the token in a guard that also fires when the request future is dropped, and never reuses a connection after a client-side cancel unless the query's error arrived within a short grace period. Licence MIT or Apache-2.0.

sqlx (Rust)

Compile-time-checked queries, but dropping a future does not cancel the server query, and the checks need a live database or an offline cache. Verdict: Rejected.

node-postgres (TypeScript)

Proven and familiar, but its query timeout is documented as a client-side timeout; true server cancellation needs SET LOCAL statement_timeout and an explicit cancel. Verdict: Recommended (TypeScript), with the deadline executor described on Architecture: TypeScript.

Typed queries against the contract

Tool Language Notes
sqlc Go mature, widely used; generates typed code from .sql files against a schema file (the contract stub)
clorinde Rust the maintained fork of cornucopia; generates against a throwaway database loaded with the contract stub
Kysely + codegen TypeScript a typed query builder with types generated from the catalogue
Drizzle TypeScript an ORM that assumes it owns migrations; not needed
Prisma TypeScript weak geometry support; rejected

Pooling

Option Notes Verdict
Application pool small and capped below the role's connection limit; pool size equals admission permits Recommended
PgBouncer lightweight; recent versions handle protocol-level prepared statements in transaction mode Viable later
pgcat sharding and replica routing; development has slowed Rejected
Supavisor multi-tenant pooler for very large tenant counts Rejected
Read replica isolates API reads from other workloads; doubles storage; replication lag on fresh data Viable later

Caching

  • HTTP: ETag from the source families' freshness versions; Cache-Control: max-age per family; 304 before admission. See Performance and Caching.
  • In-process: a byte-bounded cache with per-entry expiry and single-flight loading (otter for Go, moka for Rust, lru-cache for TypeScript). The allowed-families hash is part of the key, so licence boundaries hold inside the cache.
  • Edge micro-cache: optional, keyed on URL and plan.
  • Varnish: rejected; it duplicates the existing edge.
  • CDN: rejected for the API; only the static documentation could use one.

Sources

Home

About

Design

Data Model

Technical specification

Testing

Engineering

Technology choice

Reference

Clone this wiki locally