Postgres on the VPS: a database per preview, or a schema per preview on a shared server? #347

New issue

Closed

opened 2026-10-11 12:59:26 +00:00 by root · 7 comments

root commented

2026-10-11 12:59:26 +00:00

(Migrated from gitlab.core-project.space)

Imported from GitHub amaurycoudr/chess-project#347, opened by @amaurycoudr on 2026-10-11 12:59 UTC.
Closed on GitHub on 2026-10-11 13:15 UTC as completed.

Parent

#346

Question

Production and up to five PR previews run as Docker Compose stacks on one VPS (ADR 0029). Each stack needs Postgres. What are the options — a Postgres container per stack, one shared Postgres with a database or schema per stack, a managed service — and their cost in VPS memory, backup story for production, migration flow in CI (drizzle-kit), and teardown when a preview closes? Surface the facts the decision needs; the decision follows in grilling.

> _Imported from GitHub [amaurycoudr/chess-project#347](https://github.com/amaurycoudr/chess-project/issues/347), opened by [@amaurycoudr](https://github.com/amaurycoudr) on 2026-10-11 12:59 UTC._ > _Closed on GitHub on 2026-10-11 13:15 UTC as completed._  --- ## Parent #346 ## Question Production and up to five PR previews run as Docker Compose stacks on one VPS (ADR 0029). Each stack needs Postgres. What are the options — a Postgres container per stack, one shared Postgres with a database or schema per stack, a managed service — and their cost in VPS memory, backup story for production, migration flow in CI (drizzle-kit), and teardown when a preview closes? Surface the facts the decision needs; the decision follows in grilling.

root commented

2026-10-11 12:59:26 +00:00

(Migrated from gitlab.core-project.space)

GitHub issue relations, imported as "related" links (GitLab CE has no sub-issues or blocking links).

Parent (sub-issue of): #346
Blocking: #348

> _GitHub issue relations, imported as "related" links (GitLab CE has no sub-issues or blocking links)._  - Parent (sub-issue of): #346 - Blocking: #348

root commented

2026-10-11 13:04:03 +00:00

(Migrated from gitlab.core-project.space)

Imported from a GitHub comment by @amaurycoudr on 2026-10-11 13:04 UTC.

Research notes: https://github.com/amaurycoudr/chess-project/blob/research/postgres-on-vps/docs/research/postgres-on-vps.md

Four options compared: a Postgres container per stack, one shared server with a database per stack, one shared server with a schema per stack, or a managed service (Neon).
Memory: an idle postgres:18 container uses about 25 MiB (measured locally), so six cost about 150 MiB. Each can grow to its own 128 MB shared_buffers, which a -c flag can cap on previews.
Teardown: with a container per stack, the existing down.sh --volumes and verify-gone.sh already remove a preview's data. A shared server needs DROP DATABASE … WITH (FORCE) and an admin credential instead. Point-in-time recovery restores only a whole server, so production backups stay simpler on a server of its own.
Migrations: the image installs --prod and copies only dist, so migrating on boot with drizzle-orm's migrate() means also shipping the migrations folder. drizzle-kit migrate would need the database reachable from the runner.
Recommendation, flagged as input for #348 and not a decision: a container per stack, with a capped preview config, the PG 18 volume path /var/lib/postgresql, and a scheduled pg_dump of production copied off the VPS.

> _Imported from a [GitHub comment](https://github.com/amaurycoudr/chess-project/issues/347#issuecomment-6109315672) by [@amaurycoudr](https://github.com/amaurycoudr) on 2026-10-11 13:04 UTC._  Research notes: https://github.com/amaurycoudr/chess-project/blob/research/postgres-on-vps/docs/research/postgres-on-vps.md - Four options compared: a Postgres container per stack, one shared server with a database per stack, one shared server with a schema per stack, or a managed service (Neon). - Memory: an idle `postgres:18` container uses about 25 MiB (measured locally), so six cost about 150 MiB. Each can grow to its own 128 MB `shared_buffers`, which a `-c` flag can cap on previews. - Teardown: with a container per stack, the existing `down.sh --volumes` and `verify-gone.sh` already remove a preview's data. A shared server needs `DROP DATABASE … WITH (FORCE)` and an admin credential instead. Point-in-time recovery restores only a whole server, so production backups stay simpler on a server of its own. - Migrations: the image installs `--prod` and copies only `dist`, so migrating on boot with drizzle-orm's `migrate()` means also shipping the migrations folder. `drizzle-kit migrate` would need the database reachable from the runner. - Recommendation, flagged as input for #348 and not a decision: a container per stack, with a capped preview config, the PG 18 volume path `/var/lib/postgresql`, and a scheduled `pg_dump` of production copied off the VPS.

root commented

2026-10-11 13:15:45 +00:00

(Migrated from gitlab.core-project.space)

Imported from a GitHub comment by @amaurycoudr on 2026-10-11 13:15 UTC.

Resolution

One Postgres container per stack (production and each preview). The VPS is a Hostinger KVM 2 with 8 GB of RAM; six idle containers cost about 150 MiB. A closed preview's data goes with deploy/down.sh --volumes, and production has a server of its own, which keeps backups simple.
No Postgres port published, not even on the VPS loopback.
Drizzle Gateway (self-hosted Drizzle Studio) runs once on the VPS as its own Compose project, version pinned, joined to the production stack's network, its port published on 127.0.0.1 only and reached through an SSH tunnel. No public route.
Production is editable through the Gateway: it connects with a read-write role, not a read-only one.
ORM: Drizzle v1 release candidate, pinned exactly (the same stance as ADR 0020 for oRPC's beta), with its drizzle-kit for migrations. drizzle-kit studio stays available for local dev.
Left to the Postgres spec: memory and connection caps on previews; whether previews join the Gateway automatically (deploy connects, reaper disconnects) or on demand; a scheduled pg_dump of production copied off the VPS; migrations on API boot vs from the runner; secrets delivery when Compose runs on the runner against the VPS daemon; the Gateway's current licence terms.
Drizzle v1 RC and the editable-production stance each warrant an ADR when the spec lands.

Research: docs/research/postgres-on-vps.md on research/postgres-on-vps

> _Imported from a [GitHub comment](https://github.com/amaurycoudr/chess-project/issues/347#issuecomment-6109416031) by [@amaurycoudr](https://github.com/amaurycoudr) on 2026-10-11 13:15 UTC._  ## Resolution - **One Postgres container per stack** (production and each preview). The VPS is a Hostinger KVM 2 with 8 GB of RAM; six idle containers cost about 150 MiB. A closed preview's data goes with `deploy/down.sh --volumes`, and production has a server of its own, which keeps backups simple. - **No Postgres port published**, not even on the VPS loopback. - **Drizzle Gateway** (self-hosted Drizzle Studio) runs once on the VPS as its own Compose project, version pinned, joined to the production stack's network, its port published on `127.0.0.1` only and reached through an SSH tunnel. No public route. - **Production is editable** through the Gateway: it connects with a read-write role, not a read-only one. - **ORM: Drizzle v1 release candidate**, pinned exactly (the same stance as ADR 0020 for oRPC's beta), with its drizzle-kit for migrations. `drizzle-kit studio` stays available for local dev. - Left to the Postgres spec: memory and connection caps on previews; whether previews join the Gateway automatically (deploy connects, reaper disconnects) or on demand; a scheduled `pg_dump` of production copied off the VPS; migrations on API boot vs from the runner; secrets delivery when Compose runs on the runner against the VPS daemon; the Gateway's current licence terms. - Drizzle v1 RC and the editable-production stance each warrant an ADR when the spec lands. Research: [docs/research/postgres-on-vps.md on research/postgres-on-vps](https://github.com/amaurycoudr/chess-project/blob/research/postgres-on-vps/docs/research/postgres-on-vps.md)

root (Migrated from gitlab.core-project.space) closed this issue

2026-10-11 13:15:46 +00:00

root commented

2026-10-13 08:39:37 +00:00

(Migrated from gitlab.core-project.space)

mentioned in issue #348