databases/sql/general_sql_patterns.md

General SQL Design Patterns: Documented Failure Modes and Decision Thresholds

Cross-engine SQL guidance beyond textbook fundamentals: the GitLab 2017 backup-failure postmortem, why REPEATABLE READ silently permits write skew, sourced connection-pool sizing formulas, and SQL:2023's JSON type and property-graph queries (SQL/PGQ).

Assume the reader already knows 1NF/2NF/3NF, what a B-tree index is, and what EXPLAIN does. This document covers what isn't in the textbook chapter: a named production postmortem caused by a normalization/backup discipline failure, the specific isolation-level gap that lets "safe-looking" code corrupt data, sourced numeric thresholds for pooling and indexing decisions, and what actually changed in the SQL standard recently.

Isolation levels: REPEATABLE READ is not SERIALIZABLE, and the gap is write skew

The trap: REPEATABLE READ (Postgres's snapshot isolation) prevents dirty reads, non-repeatable reads, and - as a Postgres-specific implementation detail, not a standard guarantee - phantom reads. It does not prevent write skew: two concurrent transactions each read an overlapping snapshot, each independently conclude their write is safe, and both commit, jointly violating an invariant neither one violated alone (the canonical example: two on-call doctors, both check "at least one other is on call," both go off-call simultaneously).

Decision rule: any invariant that spans more than one row read-then-written by concurrent transactions requires SERIALIZABLE, not REPEATABLE READ - "snapshot isolation" in casual conversation is not a synonym for "serializable," and the difference is exactly the class of bug that passes code review because each transaction looks correct in isolation.

Non-negotiable pairing: SERIALIZABLE (and, less commonly, contended REPEATABLE READ) transactions abort with SQLSTATE 40001 under conflict. Shipping either isolation level without retry-with-backoff middleware converts a concurrency bug into a user-facing 500. This is not optional hardening - it is a required second commit in the same change.

Named postmortem: GitLab, 2017-01-31 - the backup wasn't the failure, the untested restore path was

GitLab's production Postgres primary had its data directory accidentally removed by an engineer running a cleanup command against the wrong host during an incident response. The proximate cause is almost irrelevant compared to what happened next: of five independent backup/replication mechanisms GitLab had deployed (pgdump cron, Postgres streaming replication, Azure disk snapshots, LVM snapshots, and a third-party backup service), effectively none produced a usable, current recovery point. pgdump had been silently failing for weeks because the version mismatch between the backup script and the installed Postgres client produced errors too small to trip alerting; the failure-alert emails were being rejected by the recipient mail server's DMARC policy and no one noticed the silence. The team recovered from an Azure disk snapshot roughly six hours old, permanently losing writes between 17:20 and 00:00 UTC - around 5,000 projects, 5,000 comments, and 700 user accounts.

The transferable lesson is not "have backups" - GitLab had five mechanisms. It is: a backup you have not restored from, on a schedule, in an automated test, is not a backup - it is an unverified hypothesis. Alerting on backup job failure is necessary but insufficient; alert on backup staleness (age of last verified-restorable artifact) as an independent signal, because a job can report success while producing an unusable artifact, and a failure alert can silently die in a mail pipeline exactly when you need it most.

Sourced connection-pool sizing: stop guessing round numbers

The HikariCP pool-sizing guidance (github.com/brettwooldridge/HikariCP/wiki/About-Pool-Sizing), the most commonly cited concrete formula in production incident writeups, derives pool size from actual hardware rather than a guessed constant:

connections = ((core_count * 2) + effective_spindle_count)

For an 8-core node on SSD-backed storage (effectivespindlecount = 1), that's ~17 connections - not 50, not 200. The formula's point isn't the arithmetic; it's that a larger pool does not mean more throughput past the point where Postgres's own CPU/lock contention becomes the bottleneck - oversized pools cause context-switch thrashing on the server, which is slower than queuing on the client.

For pooler-in-front-of-Postgres topologies (PgBouncer, PgCat, RDS Proxy), Heroku's operational rule of thumb is a concrete, citable split: the pooler should consume at most 75% of maxconnections, reserving the remaining 25% for direct psql/migration/admin connections that bypass the pooler entirely. A maxconnections = 100 instance should cap the pooler at ~75, not 95.

Decision rule: default to a small pool, add a pooler when client count exceeds what a single-digit-per-core pool can serve, and size the pool from (cores × 2) + spindles, not from "however many the previous team picked."

SQL:2023: what actually shipped

The standard finalized in mid-2023 (ISO/IEC 9075:2023) added two features worth knowing exist before reaching for a vendor extension or a separate database:

A native JSON type (Part 2) with comparison, sort, and GROUP BY support, plus a simplified dot-notation path syntax replacing verbose JSONQUERY/JSONVALUE calls in engines that adopt it.
SQL/PGQ - Property Graph Queries (new Part 16, ISO/IEC 9075-16:2023): query relational tables as a labeled property graph (MATCH (a)-[:FOLLOWS]->(b)) instead of hand-writing recursive/self-join SQL for graph traversal. Oracle Database 23ai and PostgreSQL (via ongoing patches tracked on pgsql-hackers) are the two engines actively implementing it as of 2026 - check your specific engine/version before assuming availability; it is not yet universal.

Decision rule: before reaching for Neo4j or a hand-rolled recursive CTE for a graph-shaped query against data that already lives in a relational schema, check whether your engine's SQL/PGQ support is far enough along to avoid the second system entirely.

N+1 in production is not hypothetical

Clerk's September 2025 database incident postmortem (clerk.com/blog/2025-09-18-database-incident-postmortem) - triggered upstream by a cloud provider's automatic Postgres version upgrade - included, as part of remediation, "optimiz[ing] several expensive queries" and "refin[ing] several critical indexes" to permanently reduce baseline load. The pattern recurring across postmortems like this one: unindexed or per-row query patterns that are invisible at low traffic become the blast-radius multiplier the moment an unrelated infrastructure event (a failover, a version upgrade, a traffic spike) adds latency anywhere in the path - the N+1 pattern that was "fine" at 10 req/s turns a 3x latency regression into a 30x one.

Detection threshold: if query count per request scales with result-set size rather than staying constant, that's the signature - instrument this as a CI assertion (max query count per endpoint), not just a staging-environment log review after the fact.

Decision Table: Numeric Thresholds Engineers Actually Use

Decision	Threshold	Source/rationale
Connection pool size	`(cores × 2) + spindles`; SSD counts as 1 spindle	HikariCP pool-sizing wiki
Pooler share of `max_connections`	≤ 75%, reserve 25% for direct/admin	Heroku PgBouncer operational guidance
Partial index candidate	predicate matches < ~5-10% of rows	Standard selectivity heuristic; verify with `EXPLAIN`, not assumption
Isolation level for cross-row invariants	`SERIALIZABLE` + mandatory `40001` retry	Write-skew is not prevented below this level
Backup validity	age since last successful automated restore test, not last backup job success	GitLab 2017 postmortem
N+1 regression gate	query count per request must not scale with result-set size	Assert in CI, not discovered in production

When Not to Use / Alternatives

Dimension	Relational (SQL)	Document (MongoDB)	Key-Value/Cache (Redis)
Ad-hoc relational queries & joins	Excellent	Fair (aggregation pipeline)	Poor
Flexible/evolving schema	Fair (migrations required)	Excellent	N/A (schemaless by nature)
Multi-row ACID transactions	Excellent, native	Good since multi-document transactions, at a cost	Limited (per-command atomicity, `MULTI`/`WATCH`)
Sub-millisecond read latency at massive scale	Good with caching	Good	Excellent
Horizontal write scaling	Requires sharding, added complexity	Native sharding support	Native clustering (Redis Cluster)

See PostgreSQL Best Practices, MongoDB Best Practices, and Redis Best Practices for engine-specific depth.

Sources

GitLab, "Postmortem of database outage of January 31" - about.gitlab.com/blog/postmortem-of-database-outage-of-january-31/ - five-backup-mechanisms-failed incident, restore-path-untested lesson.
HikariCP, "About Pool Sizing" - github.com/brettwooldridge/HikariCP/wiki/About-Pool-Sizing - (cores × 2) + spindles formula.
Clerk, "Postmortem: Database Incident (September 14-18, 2025)" - clerk.com/blog/2025-09-18-database-incident-postmortem - real-world query/index remediation under a cascading incident.
Peter Eisentraut, "SQL:2023 is finished: Here is what's new" - peter.eisentraut.org/blog/2023/04/04/sql-2023-is-finished-here-is-whats-new - JSON type and SQL/PGQ summary from a Postgres core contributor.
ISO/IEC 9075-16:2023 - iso.org/standard/79473.html - SQL/PGQ Property Graph Queries standard reference.
PostgreSQL documentation, transaction isolation - postgresql.org/docs/current/transaction-iso.html - write-skew definition and 40001 serialization-failure behavior.