# PostgreSQL: The Four Layers Every Backend Engineer Eventually Hits Most PostgreSQL learning material fails for the same reason. It hands you a flat list — `pg_hba.conf`, Patroni, `SKIP LOCKED`, PL/pgSQL, PgBouncer, RLS — with no sense of which layer each item lives in or when you'd ever touch it. The material you've collected is actually one stack, not four topics. It maps cleanly onto the path a query takes from your Next.js API route down to a row on disk: ```plaintext ┌─────────────────────────────────────────────────┐ │ 4. AUTOMATION how you stop doing 1–3 by │ │ hand │ ├─────────────────────────────────────────────────┤ │ 3. INFRASTRUCTURE how the database survives │ │ failure and scales │ ├─────────────────────────────────────────────────┤ │ 2. SECURITY who gets in, and what they │ │ can touch once inside │ ├─────────────────────────────────────────────────┤ │ 1. APPLICATION schema, migrations, queries,│ │ data movement │ └─────────────────────────────────────────────────┘ ↑ you live here as an app engineer ``` Read it bottom-up. Layer 1 is 80% of your day. Layer 4 is where you'll be in a year if you keep going. * * * ## Layer 1 — Application: the part you own This is schema design, migrations, and moving data around. Nobody else on your team is going to do it for you, and every mistake here shows up as a production incident later. ### Normalization comes first Normalization means storing each fact once and making relationships explicit. The payoff is that you stop getting update anomalies — the state where the same customer name lives in four tables and three of them are stale. The working rule: **normalize by default, denormalize only when you have a specific measured reason.** A reporting query that's too slow is a reason. "It felt cleaner as JSON" is not. ### Migrations are a lock-time problem, not a SQL problem The SQL in a migration is rarely the hard part. The risk is how long it holds a lock while your app is serving traffic. Good habits: * **Additive first.** Add the new column, backfill it, switch reads, drop the old one in a later deploy. Four small migrations beat one clever one. * `CREATE INDEX CONCURRENTLY` on any table with real traffic. * **Backfill in batches**, not one `UPDATE` over ten million rows. * **Split large changes across deploys** so a rollback is always available. The anti-pattern is heavy DDL on a busy table in a single shot at 2pm on a Tuesday. That's the migration that blocks writes for four minutes and turns into a status page update. Tooling depends on scale, not preference: | Task | Tool | | --- | --- | | Ordinary schema change | Framework migrations (Prisma, Drizzle, Knex) or plain SQL files | | Large data movement | Staged scripts with batched backfills | | Major version upgrade | `pg_upgrade`, or logical replication for lower downtime | | Same change across many servers | Versioned SQL plus automation (see Layer 4) | ### Postgres as a job queue You can absolutely run a queue in Postgres, and the pattern is: ```sql SELECT * FROM jobs WHERE status = 'pending' ORDER BY created_at FOR UPDATE SKIP LOCKED LIMIT 10; ``` `SKIP LOCKED` lets multiple workers grab different rows without blocking each other. It's transactional, it lives in the same database as your app, and it's one less piece of infrastructure to operate. For moderate workloads this is a genuinely good default. Where it breaks: very high throughput, long-running jobs that hold locks, and workloads where fairness matters. At that point you get contention, starvation, and table bloat, and a dedicated queue starts earning its keep. Know the exit condition before you build on it. ### Bulk loading The core idea is to **separate ingest from validation from final shaping.** 1. `COPY` into a staging table — raw, minimal constraints, fast. 2. Validate and transform inside the database. 3. Merge into the real tables. Trying to enforce every business rule during ingest is what makes imports crawl. ### Partitioning and sharding Partitioning splits one large table into pieces, usually by time or by tenant. It helps with pruning, retention (dropping a partition beats a giant `DELETE`), and maintenance windows. It also adds real planning complexity, so it should solve a problem you can name. Sharding — spreading data across separate databases or nodes — is an architecture decision, not a schema trick. If you're reaching for it early, the answer is usually indexes or partitioning instead. ### The anti-pattern list, condensed * Long-lock migrations during business hours * Using the DB as a queue past the point it fits * One giant table absorbing unrelated concerns * Skipping constraints because "the app handles it" — the app will not handle it **Learn in this order:** normalization → migrations → bulk loading → queues → partitioning → then study the anti-patterns so you can spot them in review. * * * ## Layer 2 — Security: four questions, in order Security in Postgres answers four questions, and they nest: 1. **Can you connect?** → `pg_hba.conf` 2. **Who are you?** → authentication method 3. **What may you do?** → roles and privileges 4. **Which rows may you see?** → row-level security Plus one wrapper: **is the connection encrypted?** → SSL/TLS. ### Roles and privileges Roles are the core identity object. A role can be a user, a group, or both. Roles own objects, inherit from other roles, and receive grants at the database, schema, table, sequence, and function level. The pattern worth internalizing is **three roles, three jobs**: * an **owner** role that owns the schema and runs migrations * an **app** role with only the table privileges your API actually needs * a **login** role for humans, scoped tight Your Next.js app should not be connecting as the schema owner. That single change removes a whole category of accident. ### `pg_hba.conf` Host-based authentication. Each line specifies: connection type, database, user, address, method, options. Postgres reads it **top to bottom and uses the first match**, which is the detail that bites people — a broad rule at the top silently shadows the careful rules below it. Practically: keep local admin access narrow (peer auth over the Unix socket), require SCRAM password auth for remote connections, and avoid `trust` outside a throwaway local container. Available methods include trust, password/SCRAM, peer, ident, LDAP, RADIUS, GSSAPI, SSPI, certificate, PAM, BSD auth, and OAuth. You need two of those in practice. ### SSL/TLS Enable SSL, point the server at a cert and key, and decide how strictly clients must encrypt. If your database is reachable over a network at all — and with any managed Postgres it is — this is the single most important protection on the list. Client certificates go a step further and use the cert itself as identity. ### Row-level security RLS restricts which rows a role can see or modify, through policies attached to the table: ```sql ALTER TABLE documents ENABLE ROW LEVEL SECURITY; CREATE POLICY tenant_isolation ON documents USING (tenant_id = current_setting('app.tenant_id')::uuid) WITH CHECK (tenant_id = current_setting('app.tenant_id')::uuid); ``` `USING` filters what's visible on reads. `WITH CHECK` constrains what can be written. Policies can be written per command — `SELECT`, `INSERT`, `UPDATE`, `DELETE`. The important framing from the docs: **RLS is an addition to the privilege system, not a replacement for it.** You still need correct grants. RLS is what makes multi-tenant isolation enforced by the database instead of by every `WHERE` clause a developer remembers to write. ### SELinux Not a Postgres feature. It's an OS-level access control layer that can block Postgres from reading files, writing its data directory, binding ports, or reaching backups — even when your Postgres config is flawless. If a server behaves inexplicably on RHEL/Fedora, check the OS policy before you rewrite `postgresql.conf`. Keep Postgres on its expected paths, label files correctly, adjust policy only when you understand the access you're granting. **Learn in this order:** roles and privileges → `pg_hba.conf` → SSL → RLS → SELinux. That mirrors enforcement from the network edge inward to the row. * * * ## Layer 3 — Infrastructure: how it survives This is DBA territory. You don't need to run it, but you need to understand it well enough to ask the right questions in an architecture review — which is exactly the credibility gap that separates a senior engineer from a staff one. ### Backups Two different things wearing the same word: * **Logical** — `pg_dump`, `pg_dumpall`, `pg_restore`. Portable, selective, good across versions, slow for large databases. * **Physical** — `pg_basebackup` plus WAL archiving. This is what gives you point-in-time recovery. Third-party tools that do physical backup properly: pgBackRest, barman, WAL-G, pg\_probackup. The thing most teams skip is **backup validation**. A backup you have never restored is a hypothesis, not a backup. ### Replication * **Streaming replication** — a standby stays close behind the primary. Use it for high availability and read scaling. * **Logical replication** — replicates selected tables. Use it for selective data movement, cross-version migration, and integration between systems. The rule: **streaming for availability, logical for flexibility.** This is also why upgrades sit next to logical replication — `pg_upgrade` is faster for in-place major upgrades, logical replication gives you a controlled migration with less downtime. ### Cluster management and failover **Patroni** automates high availability and failover. It leans on a distributed coordination store — **etcd** or **Consul** — to agree on who the primary is, and usually sits behind **HAProxy** or **Keepalived** so applications always reach the current primary without knowing its address. Patroni is a category, not a mandate; alternatives exist and the right one depends on how much automation you want to own. ### Connection pooling **PgBouncer** reuses server connections so your app can open far more connections than Postgres should handle directly. For a serverless Next.js deployment this is not optional — every cold lambda wants its own connection, and Postgres will run out long before your traffic does. PgBouncer is a pooler, not a failover manager. It sits alongside Patroni, it doesn't replace it. ### Monitoring Prometheus, Zabbix, temBoard, `check_pgactivity`, `check_pgbackrest`. The tools matter less than the four questions they answer: * Is the replica falling behind? * Are backups completing and restorable? * Is disk growing faster than expected? * Are sessions piling up? ### Kubernetes and the rest Three patterns for Postgres on Kubernetes: plain StatefulSet, Helm chart, or an operator. Operators give the most day-2 automation — failover, provisioning, upgrades — and are what most serious deployments land on. **PostgreSQL Anonymizer** masks sensitive data for non-production environments, so your staging database can be realistic without being a compliance incident. **Learn in this order:** backups → replication → failover/cluster management → pooling → monitoring → Kubernetes and capacity planning. * * * ## Layer 4 — Automation: stop doing this by hand Three tools, three different jobs. The confusion here is thinking they compete. | You need to… | Use | | --- | --- | | Perform an action | Shell script (+ cron for scheduling) | | Enforce configuration state | Ansible / Puppet / Chef | | Run logic close to the data | PL/pgSQL and friends | ### Shell scripts The fastest payoff. Scheduled `pg_dump` backups with retention cleanup, batch execution of migration SQL through `psql`, role and grant creation, table-size monitoring, log collection. Credentials go in `.pgpass` or environment variables so the script runs non-interactively. This is the piece people get wrong first. ### Procedural languages Postgres runs code inside the engine. Core-supported: **PL/pgSQL**, **PL/Tcl**, **PL/Perl**, **PL/Python**. There's also PL/sh as an add-on for shell-style stored procedures. Use them when the logic belongs to the data — validation, triggers, transformations, reusable procedures. Use external scripts when the task touches the filesystem, the network, backups, or orchestration across services. ### Ansible, Puppet, Chef These manage infrastructure, not SQL. In Postgres work they install the server, template `postgresql.conf` and `pg_hba.conf`, deploy SSL certs, create roles, and keep dev/staging/prod identical. * **Ansible** — agentless, simplest to start, best payoff for most teams * **Puppet** — policy-driven, keeps servers in a declared desired state * **Chef** — code-driven, fits when database provisioning is one part of a larger DevOps pipeline **Starter path:** Bash and `psql` → `pg_dump` / `pg_restore` / `.pgpass` / cron → Ansible. * * *