PostgreSQL

Postgres is what you reach for when the data is tabular, the writes are concurrent, and you need actual transactional guarantees. It is the boring choice that has outlasted three generations of fashionable alternatives — and it earned that position. Official docs at postgresql.org/docs own the SQL reference; this page is the orient-and-anchor surface.

What it is

A relational, ACID-compliant SQL database. Single-process by default, replicas for read scaling, hot standby for failover. Stores rows in tables with strong typing, foreign keys, check constraints, and a query planner that has been refined for thirty-plus years. Free, BSD-style license, no enterprise tier to upsell — the project is what the project is.

Distribution: official Linux/Mac packages, Docker image, managed offerings everywhere (RDS, Cloud SQL, Neon, Crunchy, Supabase, etc.). The wire protocol is stable; libpq drivers exist for every language. In the GL stack, paiddaily.io runs on a self-hosted Postgres container — vanilla pg17 with pgvector, Keycloak handles auth, and the dashboard and admin surfaces are first-party.

The pitch: when the questions you ask are tabular ("what positions does this user hold"), transactional ("decrement balance and insert trade atomically"), or constrained ("foreign key, please prevent orphan rows"), Postgres is the right primitive. The wrong primitive in a document store is "give me every order for this customer, with line items, atomically." The wrong primitive in a graph database is "sum revenue by month." Postgres natively does both.

When to use it

Reach for it when:

• The data is inherently tabular — users, accounts, positions, trades, subscriptions. Rows, columns, foreign keys.
• You need real transactions — multi-row updates that must commit or roll back atomically. ACID is not a checkbox; it’s the whole product.
• The team needs SQL — every engineer knows it; every BI tool speaks it; every LLM writes passable queries against it.
• The workload is OLTP — many small reads and writes, low latency, high concurrency. Postgres is built for this.
• You want extensions — pgvector for embeddings, PostGIS for geo, TimescaleDB for time series, pg_partman for partitioning. The ecosystem is unmatched.
• You need a managed path — Supabase, RDS, Cloud SQL, Neon all run real Postgres. Lock-in is zero; the wire protocol is the standard.

Skip it when:

• The workload is analytics-first — large aggregations over billions of rows. DuckDB or ClickHouse will eat Postgres alive on those.
• The data is inherently graph-shaped — five-hop traversals get painful with recursive CTEs. Reach for Neo4j.
• You need single-file, zero-ops — embedded use cases want SQLite, not a server process.
• The state is purely ephemeral — caches, session stores, rate-limit counters. Redis is the right shape there.

At a glance

Core concepts

• Table — rows of typed columns with constraints (NOT NULL, UNIQUE, CHECK, FOREIGN KEY).
• Schema — namespace for tables, functions, types. In paiddaily.io each domain (scanner, manager, planner, journal, allocator, platform) gets its own schema.
• Transaction — BEGIN / COMMIT / ROLLBACK. Default isolation is READ COMMITTED; SERIALIZABLE available when you need it.
• Index — B-tree by default; GIN for JSONB and full-text; GiST for geometry; BRIN for huge append-only time-series tables.
• Stored procedure / function — server-side logic in PL/pgSQL. Domain logic that must be transactional belongs here, not in app code (Smith non-negotiable).
• Extension — first-class add-ons. CREATE EXTENSION vector; and you have a vector store inside your relational DB.

Extensions worth knowing

• pgvector — vector similarity in Postgres. Removes the need for a separate vector store for most RAG workloads up to ~10M rows.
• PostGIS — geospatial queries (distance, containment, routing). The reference standard.
• TimescaleDB — automatic partitioning and compression for time-series. Postgres-native, drop-in.
• pg_partman — declarative range partitioning for big tables when TimescaleDB is overkill.
• pg_stat_statements — query performance dashboard built in. Turn it on day one.

How to integrate

Default integration for a new GL surface:

1. Pick the operational shape that matches the cost-and-control tradeoff. Self-hosted Postgres on a shared instance is the GL default (one server, one DB per product, separate roles per service — boring, cheap, no vendor surprises). RDS / Cloud SQL / Neon when a managed path saves more in ops than it costs in bill. Avoid the BaaS bundles (Supabase, Firebase) unless you specifically want the auth + storage + realtime sidecars; each is replaceable, but together they add operational surface you may not need.
2. Migrations are the source of truth. A versioned, ordered, append-only directory of .sql files. paiddaily.io ships ~88 of them today — every schema change shows up there first. Never edit a past migration.
3. No inline SQL in app code. Domain logic lives in stored procedures; the app calls a function and gets a result. This makes the DB the API contract; it makes diffs reviewable; it makes test surfaces obvious. (See paiddaily.io’s ADR 0007.)
4. Emit domain events from state-mutating procs. A consistent shape — fn_emit_domain_event in paiddaily.io — gives downstream consumers a single subscription surface and the system an audit log for free.
5. pgTAP tests against the live schema. SQL-native unit tests run by pg_prove (or psql -f against a test runner). Schemas drift; tests don’t lie.
6. Backups are not optional. Managed offerings handle this; self-hosted does not, by default. pg_dump + a cron is the minimum acceptable; PITR via base backup + WAL archive is the actual answer.

In the GL stack

paiddaily.io

• Primary store — self-hosted Postgres (pg17 with pgvector). Every product surface (scanner, manager, planner, journal, allocator) is its own schema with its own procs.
• ~88 migrations and counting — the canonical ledger of how the schema got to today. Smith reviews them; Neil writes them.
• pgTAP tests — every stored proc has tests in database/tests/. Catches signature drift before it ships.
• pgvector for embeddings — research notes, opportunity briefs, catalyst summaries. Same database; no separate vector infra; one less moving part.
• Domain events table — every mutating proc writes a row. The audit log and the message bus for free.
• See paiddaily.io Postgres deployment for the full deep dive.

sagedaily.io

• Per-user state — readings, intentions, prior cards. Postgres holds the durable, query-able surface; the graph holds the relational meaning.
• Subscription billing — Stripe webhooks land in Postgres; the source of truth for entitlements.

builddaily.io

• Daily log stats and admin — small Postgres footprint for analytics on the journal corpus and admin auth.

Gotchas

• Connection pooling is mandatory at scale. Postgres processes connections, not threads — a hundred idle clients with no pooler will hurt. PgBouncer (or pgcat / Supavisor for the managed crowd) sits between the app and the DB.
• JSONB is convenient and addictive. Use it for genuinely schemaless payloads (webhook bodies, raw events). Don’t use it as a way to avoid designing a schema; you’ll regret it when you need to query the third level deep.
• Long-running transactions wedge VACUUM. A transaction held open for hours prevents reclaiming dead tuples; the table grows; performance degrades. Keep transactions short or use idle_in_transaction_session_timeout.
• Default isolation is READ COMMITTED, not SERIALIZABLE. Concurrent updates can produce surprises. Know when to upgrade — and when SELECT FOR UPDATE is enough.
• Index bloat is real. A heavily-updated table needs REINDEX occasionally. Watch pg_stat_user_indexes.

Risks

• Vertical scaling first, sharding hurts. Postgres scales beautifully on one big box. When you need to shard, the answer is non-trivial — Citus, Aurora’s flavor, or a manual sharding layer. Most products never need this; if you do, plan early.
• Managed-vendor pricing. Hosted Postgres (RDS, Neon, Supabase, etc.) is friendly until you cross the free tier; usage-based pricing on connections, storage, and egress can surprise. Track it.
• Major-version upgrades. Postgres major versions (15 → 16 → 17) require a real migration, not a apt upgrade. Managed providers handle this with downtime windows; plan around them.

Alternatives

• MySQL / MariaDB — Wins when you’re inheriting an existing MySQL shop. Loses on planner sophistication, type system, and extension ecosystem. Postgres is the modern default.
• SQLite — Wins when single-process, single-file, zero-ops is the brief. Loses the moment you need concurrent writers or a server boundary. See SQLite.
• CockroachDB / Yugabyte — Wins when global distribution with strong consistency is non-negotiable. Loses on cost, ecosystem maturity, and operational complexity for 99% of projects.
• DuckDB — Wins for embedded analytics over Parquet / CSV. Loses on OLTP — it’s not built for concurrent writers. Complement, not replacement. See DuckDB.
• MongoDB — Wins when the team has religious objections to schemas. Loses on transactional guarantees, query planner, and the long-term maintainability of "schemaless" data. Postgres + JSONB does the same job better.

Related

• Neo4j — when the data is graph-shaped, not tabular. Both can be true at once; both can coexist.
• SQLite — the embedded cousin. Same SQL dialect family; very different operational model.
• DuckDB — the analytics cousin. Lives next to Postgres, doesn’t replace it.
• paiddaily.io Postgres deployment — the specific shape, with migrations, schemas, and procs.