Armen Ghazaryan

PostgreSQL through plain SQL, optional, migrated before a deploy

The API reaches PostgreSQL with plain SQL through JdbcClient, starts and serves everything else without a database, and migrates it in a deploy step, never during a cold start.

Status
Accepted 3 October 2026
The record
0012-postgresql-plain-sql-optional.md

Context

ADR 0005 chose PostgreSQL on Neon. The experiments that come next are about what a database does under load: connection pools, isolation and locks, indexes, an outbox. The site's own data (visits, introductions) comes after them. Two constraints shape how the API holds the database: Cloud Run scales the API to zero, so every cold start is a visitor waiting (ADR 0009); and Neon suspends its compute when idle, so the first query after a pause wakes it.

Decision

  • Plain SQL. JdbcClient with the SQL written out, not Spring Data repositories as ADR 0005 had it: the experiments show what statements, transactions and locks do, which an ORM exists to hide.
  • Optional. Spring Boot's DataSource auto-configuration stops the application when it has no URL, so it is excluded and imported again by DatabaseAutoConfiguration only when there is a database to connect to: a spring.datasource.url, or connection details from Docker Compose (bootRun) or Testcontainers (tests). Features reach it through the Database port; without one it is a Null Object whose every use answers 503 DATABASE_NOT_CONFIGURED. A database that cannot be reached answers 503 DATABASE_UNAVAILABLE. /api/health answers for the API alone.
  • A lazy, small pool. No connection at startup and none kept while idle, so a cold start never waits for Neon and Neon can suspend between visits; five connections at most for the API's own queries. The experiments build pools of their own.
  • Migrations before a deploy. Flyway migrates at startup where the database is local (development, tests). In production it is off at startup; a Cloud Run job runs the new image with the migrate profile, which applies the migrations and exits, before the new revision takes traffic, and a failed migration stops the deploy, not the site.
  • A schema for the experiments. Their tables live in lab, apart from the site's own data, so an experiment can be reset or loaded until it breaks without touching what visitors left.
  • Every endpoint that reaches the database is rate-limited. Each request can wake Neon, and its free plan budgets compute hours.

Consequences

  • Tests need Docker: ./gradlew check starts PostgreSQL 17 with Testcontainers, the same major version as production and the Compose file.
  • Measured on one vCPU, six starts each: 3.6 s before and 3.8 s after the database arrived, within the noise of the machine; the production service opens no connection until the first query.
  • The first query after Neon has suspended pays for waking it. Neon runs on AWS and Azure, not Google Cloud, so the Cloud Run region should be chosen next to Neon's: Frankfurt (europe-west3 beside aws-eu-central-1) keeps a round trip short; the current default, europe-west1, is a few milliseconds further away.
  • A connection string in production comes from Secret Manager, never from the repository; without one the API runs with its database features answering 503.