# PostgreSQL Beyond SELECT *: A Working Developer's Deep Dive Most of us meet PostgreSQL through an ORM. You write `prisma.user.findMany()`, it works, you move on. Then one day a query that took 40ms takes 4 seconds, your disk fills up overnight, or a security review asks "how do you guarantee tenant A can't read tenant B's rows?" — and suddenly the database stops being a black box. This post walks through the parts of PostgreSQL that actually bite you in production: how it uses resources, how it stays durable, how it cleans up after itself, how it replicates, how it decides to run your query, and how it locks things down. Each section is written to be useful on its own, so feel free to jump around. * * * ## 1\. Resource Usage: Where Your Memory Actually Goes PostgreSQL uses a **process-per-connection** model. Every client connection forks a backend OS process — not a lightweight thread. That single fact explains most of the surprising memory behavior people run into. ### The four knobs that matter most `shared_buffers` — PostgreSQL's own page cache, shared by all backends. The conventional starting point is ~25% of system RAM. Going much higher often doesn't help, because Postgres deliberately leans on the operating system's page cache too; you'd just be double-caching. `work_mem` — memory available for a single sort, hash, or materialize operation. This is the one people get wrong. It is **not** per query and **not** per connection — it's per *operation node*. A query with three hash joins and two sorts can allocate roughly five times `work_mem` simultaneously. Multiply that by 200 connections and a "safe-looking" 64MB becomes a very unsafe number. `maintenance_work_mem` — used by `VACUUM`, `CREATE INDEX`, and `ALTER TABLE ... ADD FOREIGN KEY`. Since only a few of these run at once, you can afford to be generous here (512MB–2GB on a decent server) and index builds get noticeably faster. `effective_cache_size` — a lie you tell the planner, and a useful one. It allocates nothing. It tells the query planner roughly how much memory the OS and Postgres together are likely to have available for caching, which influences whether an index scan looks attractive. Setting it to ~50–75% of RAM is typical. ```sql -- What is this instance actually running with? SELECT name, setting, unit, source FROM pg_settings WHERE name IN ('shared_buffers','work_mem','maintenance_work_mem', 'effective_cache_size','max_connections'); ``` ### The connection problem If you're deploying a Node or Next.js app to a serverless platform, this is the failure mode you will hit first. Each serverless instance opens its own pool; scale to 50 instances with a pool size of 10 and you've asked for 500 connections from a database configured for 100. Postgres starts refusing connections, and each one that *does* connect costs several megabytes of process overhead. The fix is not "raise `max_connections`." It's a pooler — **PgBouncer** in transaction mode, or a managed equivalent (Supabase's pooler, Neon's pooled endpoint, RDS Proxy). A pooler multiplexes thousands of client connections onto a small number of real backends. Note the caveat: transaction-mode pooling breaks session-level features like `LISTEN/NOTIFY`, session-scoped `SET`, and some prepared-statement patterns. Know which mode you're in. * * * ## 2\. Write-Ahead Log: How Postgres Refuses to Lose Your Data The core rule of the WAL is simple: **changes are written to the log before they're written to the data files.** Commit means "the WAL record is durably on disk," not "the table is updated on disk." The actual table pages get flushed later, lazily. This sounds like extra work but it's the opposite. WAL writes are sequential appends, which even spinning disks handle well, while data page writes are scattered all over the file system. Batching them up and writing them out later is a huge win. If the server loses power mid-transaction, recovery replays the WAL from the last checkpoint forward. Anything committed is reapplied; anything not committed is discarded. ### Settings worth knowing * `wal_level` — `replica` (the default, enough for streaming replication and PITR) or `logical` (adds enough information for logical decoding / logical replication). `minimal` disables both and is rarely worth it. * `synchronous_commit` — the interesting one. Set to `on`, a commit waits for the WAL to hit disk. Set to `off`, commits return immediately and you risk losing the last few hundred milliseconds of transactions in a crash — but *not* corrupting anything. For analytics ingestion or event logging where a fraction of a second of loss is acceptable, turning it off is a legitimate and dramatic speedup. For payments, don't. * `full_page_writes` — on the first modification of a page after a checkpoint, Postgres writes the whole page to WAL to protect against torn pages. This is why WAL volume spikes right after a checkpoint. * `max_wal_size` — a soft target that indirectly controls how often checkpoints happen (more on that below). WAL is also the substrate for two other features you probably care about: **point-in-time recovery** (keep a base backup plus the WAL stream, replay to any moment) and **replication** (ship the WAL to another server). * * * ## 3\. Vacuum: The Tax You Pay for MVCC PostgreSQL never updates a row in place. An `UPDATE` writes a new row version and marks the old one dead. A `DELETE` just marks it dead. This is **MVCC** — multi-version concurrency control — and it's why readers never block writers and writers never block readers in Postgres. The cost is garbage. Dead tuples accumulate in the table, and something has to clean them up. That something is `VACUUM`. ### What vacuum actually does 1. Reclaims space from dead tuples so new rows can reuse it. 2. Updates the **visibility map**, which lets Postgres do index-only scans and skip pages during future vacuums. 3. Freezes old transaction IDs to prevent **wraparound**. That third one is not optional. Transaction IDs are 32-bit and wrap around after ~4 billion transactions. If freezing falls too far behind, Postgres will start emitting increasingly panicked warnings and eventually refuse to accept new writes to protect your data. This has taken down real companies. Sentry famously wrote about it. It is entirely preventable by not disabling autovacuum. ### Bloat, and how to see it ```sql SELECT relname, n_live_tup, n_dead_tup, ROUND(n_dead_tup::numeric / NULLIF(n_live_tup, 0) * 100, 1) AS dead_pct, last_autovacuum, last_autoanalyze FROM pg_stat_user_tables WHERE n_dead_tup > 1000 ORDER BY n_dead_tup DESC; ``` If a table shows 40% dead tuples and `last_autovacuum` is days old, autovacuum isn't keeping up. ### Tuning autovacuum The default trigger is `autovacuum_vacuum_threshold` (50) + `autovacuum_vacuum_scale_factor` (0.2) × table size. That 20% default is fine for a 10,000-row table and terrible for a 200-million-row table, where it means waiting for 40 million dead tuples before doing anything. For big, hot tables, override per-table: ```sql ALTER TABLE events SET ( autovacuum_vacuum_scale_factor = 0.01, autovacuum_analyze_scale_factor = 0.005 ); ``` Two more things worth internalizing: * `VACUUM FULL` **is not "vacuum but better."** It rewrites the entire table and takes an `ACCESS EXCLUSIVE` lock, blocking everything including reads. On a large production table this is an outage. Use `pg_repack` if you genuinely need to reclaim disk space back to the OS. * **HOT updates** avoid touching indexes when the updated columns aren't indexed and the new version fits on the same page. Leaving some room via `fillfactor` (e.g. 85) on frequently-updated tables can measurably reduce index bloat. * * * ## 4\. Replication: Copies That Actually Matter Replication in PostgreSQL comes in two flavors, and they solve different problems. ### Physical (streaming) replication The primary ships its WAL stream to one or more standbys, which replay it byte-for-byte. The result is an exact block-level copy of the entire cluster. Standbys can serve read-only queries (`hot_standby = on`), which makes this the standard approach for read scaling and high availability. ```sql -- On the primary: how far behind is each replica? SELECT client_addr, state, sync_state, pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS lag_bytes FROM pg_stat_replication; ``` **Replication slots** make the primary retain WAL until a given replica has consumed it. Essential for reliability, dangerous if you forget about them: an inactive slot will retain WAL forever and fill your disk. Always monitor `pg_replication_slots`. **Synchronous vs asynchronous** is the classic tradeoff. Async (default) means the primary commits without waiting — fast, but a failover can lose the last few transactions. Sync (`synchronous_standby_names`) guarantees zero data loss but ties your commit latency to network round-trips, and if the sync standby dies, writes stall. ### Logical replication Instead of shipping blocks, this decodes WAL into row-level changes and replays them as inserts/updates/deletes on the subscriber. Requires `wal_level = logical`. ```sql -- Publisher CREATE PUBLICATION app_pub FOR TABLE users, orders; -- Subscriber CREATE SUBSCRIPTION app_sub CONNECTION 'host=primary dbname=app user=repl' PUBLICATION app_pub; ``` This gives you things physical replication can't: replicating a subset of tables, replicating *between different major versions* (which makes near-zero-downtime upgrades possible), and feeding changes into other systems. The catch is that it doesn't replicate DDL, sequences need care, and every replicated table needs a replica identity (usually the primary key). ### The application-level gotcha If you route reads to a replica, you inherit **replication lag**. A user submits a form, you write to the primary, then immediately read from a replica that's 200ms behind — and the user sees stale data. Handle it explicitly: route reads to the primary for a short window after a write, or read your own writes from the primary always. Don't discover this in production. * * * ## 5\. The Query Planner: Reading Postgres's Mind The planner is a **cost-based optimizer**. For any query it enumerates plausible execution strategies, estimates the cost of each using table statistics, and picks the cheapest. Costs are in arbitrary units where 1.0 is roughly "read one sequential page." The single most important skill here is reading `EXPLAIN (ANALYZE, BUFFERS)`. ```sql EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT u.email, COUNT(o.id) FROM users u JOIN orders o ON o.user_id = u.id WHERE u.created_at > now() - interval '30 days' GROUP BY u.email; ``` `EXPLAIN` alone shows the plan and estimates. `ANALYZE` actually runs the query and shows real timings and row counts. `BUFFERS` shows how many pages came from cache versus disk. ### What to look for **Estimated rows vs actual rows.** This is the diagnostic that matters most. If a node says `rows=12` but actual is `rows=480000`, the planner made every downstream decision based on bad information — that's your bug. Usually the fix is `ANALYZE` (stale statistics), a higher `default_statistics_target`, or extended statistics for correlated columns: ```sql CREATE STATISTICS city_state_stats (dependencies) ON city, state FROM addresses; ANALYZE addresses; ``` **Scan types.** A `Seq Scan` is not automatically bad — reading 60% of a table sequentially genuinely beats 60% of it via random index lookups. A `Bitmap Heap Scan` sits in between: gather matching pages from the index, sort them, read them in physical order. What *is* usually bad is a Seq Scan on a large table where you expected an index to be used, which often means a type mismatch, a function wrapping the column (`WHERE lower(email) = ...` needs an expression index), or a `LIKE '%foo'` leading wildcard. **Join types.** Nested Loop is great for small outer inputs, catastrophic for large ones. Hash Join is the workhorse for large unsorted inputs. Merge Join is chosen when both sides are already sorted. ### One setting worth changing `random_page_cost` defaults to `4.0`, a value calibrated for spinning rust in the 1990s. On SSDs and NVMe, random reads are nearly as cheap as sequential ones. Lowering it to `1.1` makes the planner appropriately more willing to use indexes, and is one of the highest-value one-line changes on most modern deployments. Finally: install `pg_stat_statements`. It aggregates execution stats per normalized query and is how you find *which* query to optimize instead of guessing. * * * ## 6\. Checkpoints and the Background Writer: Smoothing the I/O Since dirty pages live in `shared_buffers` and get written lazily, something must eventually push them to disk. Two mechanisms do this. A **checkpoint** flushes all dirty buffers and records a point in the WAL from which recovery can safely start. Everything before that point in the WAL can be recycled. Checkpoints fire when `checkpoint_timeout` elapses (default 5 minutes) or when WAL volume approaches `max_wal_size` (default 1GB) — whichever comes first. The **background writer** trickles dirty buffers out continuously in the background, so that backends looking for a free buffer don't have to write one out themselves, and so checkpoints have less to do. ### The tradeoff Frequent checkpoints → fast crash recovery, but more I/O and more full-page writes bloating your WAL. Infrequent checkpoints → less steady-state I/O, but longer recovery after a crash and bigger write spikes. The symptom of misconfiguration is periodic latency spikes — the app is fine, then every five minutes everything gets slow for a few seconds. Check for it: ```sql SELECT * FROM pg_stat_bgwriter; ``` If `checkpoints_req` (triggered by WAL volume) far exceeds `checkpoints_timed`, checkpoints are being forced too often — raise `max_wal_size`. And `checkpoint_completion_target` (0.9 by default since PG 14) spreads the checkpoint's writes across most of the interval rather than dumping them all at once. If you see `WARNING: checkpoints are occurring too frequently` in your logs, Postgres is telling you exactly what to fix. * * * ## 7\. Security: Roles, Privileges, and Who Can See What PostgreSQL's permission system is layered. Getting it right means understanding each layer. ### Roles There is no separate "user" concept — there are only **roles**. A role with the `LOGIN` attribute can connect; a role without it functions as a group. Roles can be members of other roles and inherit their privileges. ```sql CREATE ROLE app_readonly; -- a group CREATE ROLE reporting_svc LOGIN PASSWORD 'xxx' IN ROLE app_readonly; ``` The practical pattern: define permissions on group roles, grant login roles membership in them. Never let your application connect as a superuser — a SQL injection against a superuser connection is total compromise, while the same injection against a role limited to `SELECT, INSERT, UPDATE` on a handful of tables is survivable. ### Grant and Revoke ```sql GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_readonly; GRANT INSERT, UPDATE ON orders TO app_writer; REVOKE DELETE ON orders FROM app_writer; ``` Two traps. First, `PUBLIC` is an implicit role that every role belongs to — privileges granted to `PUBLIC` apply to everyone. Second, in PostgreSQL 15 and later the `public` schema no longer grants `CREATE` to all users by default. If you're on an older version, revoking it is a good hardening step. ### Object privileges Privileges are per object type, and they don't overlap the way people assume: | Object | Relevant privileges | | --- | --- | | Table / view | `SELECT`, `INSERT`, `UPDATE`, `DELETE`, `TRUNCATE`, `REFERENCES`, `TRIGGER` | | Column | `SELECT`, `INSERT`, `UPDATE` on specific columns | | Sequence | `USAGE`, `SELECT`, `UPDATE` | | Function | `EXECUTE` | | Schema | `USAGE`, `CREATE` | | Database | `CONNECT`, `CREATE`, `TEMPORARY` | Column-level grants are underused and genuinely handy — an analytics role can read `users` without ever seeing `password_hash` or `national_id`. The classic head-scratcher: you grant `INSERT` on a table with a `SERIAL` primary key and inserts still fail. The sequence is a separate object and needs its own `USAGE` grant. ### Default privileges `GRANT ... ON ALL TABLES` applies only to tables that exist *right now*. The next migration creates a table your read-only role can't see. `ALTER DEFAULT PRIVILEGES` fixes this permanently: ```sql ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO app_readonly; ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE, SELECT ON SEQUENCES TO app_writer; ``` Important detail: default privileges are tied to the role that *creates* the object. If migrations run as `deploy_user`, set the defaults for `deploy_user` (`ALTER DEFAULT PRIVILEGES FOR ROLE deploy_user ...`). ### Authentication models and `pg_hba.conf` Everything above concerns what you can do *after* connecting. `pg_hba.conf` (host-based authentication) governs whether you can connect at all. Each line is: ```plaintext # TYPE DATABASE USER ADDRESS METHOD local all postgres peer hostssl app app_user 10.0.0.0/8 scram-sha-256 host all all 0.0.0.0/0 reject ``` **Rules are evaluated top to bottom and the first match wins** — including a first match that rejects. A too-permissive line near the top silently defeats every stricter rule below it. Methods worth knowing: `scram-sha-256` (the modern default; use it), `md5` (legacy, deprecated), `peer` (matches OS username, local connections only), `trust` (no authentication at all — fine in a throwaway Docker container, catastrophic anywhere else), `cert` (client TLS certificates), plus `ldap` and `gss` for enterprise environments. Changes take effect on reload, not restart: ```sql SELECT pg_reload_conf(); ``` ### SSL/TLS Server side: `ssl = on` plus a certificate and key. Client side, the part people get wrong is `sslmode`. `require` encrypts the connection but **does not verify the server's certificate**, so it stops passive eavesdropping but not an active man-in-the-middle. Only `verify-ca` and `verify-full` actually validate identity, and only `verify-full` checks the hostname. ```plaintext postgresql://user:pass@db.example.com:5432/app?sslmode=verify-full&sslrootcert=/etc/ssl/rds-ca.pem ``` If you're connecting to a managed database over the public internet, `verify-full` is the setting you want. ### Row-Level Security RLS moves authorization from your application into the database. Instead of remembering to add `WHERE tenant_id = $1` to every single query — and eventually forgetting on one — the database enforces it. ```sql ALTER TABLE documents ENABLE ROW LEVEL SECURITY; ALTER TABLE documents FORCE ROW LEVEL SECURITY; -- applies to the owner too CREATE POLICY tenant_isolation ON documents USING (tenant_id = current_setting('app.current_tenant')::uuid); ``` Then, per request/transaction: ```sql SET LOCAL app.current_tenant = '3f2a...'; ``` `USING` filters what rows are visible to reads; a separate `WITH CHECK` clause constrains what new rows may be written. Note that superusers and roles with `BYPASSRLS` skip policies entirely — another reason your app role shouldn't be a superuser. And be aware that policies are predicates the planner must evaluate, so index the columns they reference. This is the mechanism behind Supabase's authorization model, and it's worth understanding even if you never use Supabase. ### SELinux For the highest-assurance environments, the `sepgsql` extension integrates PostgreSQL with SELinux's mandatory access control, applying OS-enforced security labels to database objects. It's meaningful in government and regulated deployments; for most teams it's good to know exists and unlikely to be your next task. * * * ## 8\. Grouping: Aggregation Past `GROUP BY` Everyone knows the basics: ```sql SELECT customer_id, COUNT(*) AS orders, SUM(total) AS revenue FROM orders WHERE created_at >= date_trunc('month', now()) GROUP BY customer_id HAVING SUM(total) > 1000; ``` `WHERE` filters rows before grouping; `HAVING` filters groups after. Mixing them up is one of the most common SQL mistakes. But there's more here than most people use. `FILTER` — conditional aggregation without ugly `CASE` expressions: ```sql SELECT date_trunc('day', created_at) AS day, COUNT(*) AS total, COUNT(*) FILTER (WHERE status = 'paid') AS paid, COUNT(*) FILTER (WHERE status = 'refunded') AS refunded FROM orders GROUP BY 1; ``` `ROLLUP` **/** `CUBE` **/** `GROUPING SETS` — multiple levels of aggregation in a single pass. `ROLLUP (region, product)` gives you per-product totals, per-region subtotals, *and* a grand total, all in one result set. This replaces the common pattern of firing three separate queries and stitching them together in JavaScript. `DISTINCT ON` — a Postgres extension that solves "the most recent row per group" cleanly: ```sql SELECT DISTINCT ON (user_id) user_id, id, created_at FROM orders ORDER BY user_id, created_at DESC; ``` **Window functions** — when you need aggregates *alongside* detail rows rather than collapsing them: ```sql SELECT id, user_id, total, SUM(total) OVER (PARTITION BY user_id) AS user_lifetime_total, RANK() OVER (PARTITION BY user_id ORDER BY total DESC) AS rnk FROM orders; ``` The distinction is worth stating plainly: `GROUP BY` reduces rows; window functions preserve them. * * * ## 9\. Set Operations: Combining Result Sets Four operators, and the rule that both queries must produce the same number of columns with compatible types. * `UNION` — combines and removes duplicates. * `UNION ALL` — combines and keeps everything. * `INTERSECT` — rows present in both. * `EXCEPT` — rows in the first query but not the second. The one performance note that matters: `UNION` **deduplicates, and deduplication requires a sort or hash of the entire result.** If you know the sets are disjoint, `UNION ALL` is meaningfully faster. Using plain `UNION` out of habit is a common and invisible cost. `EXCEPT` is excellent for reconciliation — finding what's in one system and missing from another: ```sql SELECT id FROM orders_staging EXCEPT SELECT id FROM orders; ``` An `ORDER BY` at the end applies to the combined result, not the individual branches. To sort within a branch, wrap it in a subquery. * * * ## 10\. Advanced Topics: What to Learn Next A map rather than a treatment — each of these deserves its own post. **Indexing strategies.** B-tree is the default and right most of the time. **GIN** for `jsonb` containment and full-text search. **GiST** for geometric and range types. **BRIN** for enormous append-only tables with natural physical ordering (time-series logs), where it's tiny compared to a B-tree. Then the modifiers: **partial** indexes (`WHERE deleted_at IS NULL` — smaller and faster when you always filter that way), **expression** indexes (`ON users (lower(email))`), and **covering** indexes (`INCLUDE (...)`) that enable index-only scans. **Partitioning.** Declarative partitioning by `RANGE`, `LIST`, or `HASH`. The real win is usually operational: dropping last year's partition is instant, while `DELETE FROM events WHERE created_at < ...` on 500 million rows generates enormous WAL and leaves behind bloat that vacuum has to chew through. **Concurrency and isolation.** `READ COMMITTED` is the default. `REPEATABLE READ` and `SERIALIZABLE` exist and behave differently than in MySQL — in particular, `SERIALIZABLE` in Postgres uses predicate locking and can abort transactions with a serialization failure, which means your application must be prepared to retry. **Backup and recovery.** `pg_dump` is a logical, portable, restore-anywhere snapshot. `pg_basebackup` plus archived WAL gives you physical backups and point-in-time recovery. They're different tools for different failures. And the only backup that counts is one you've actually restored — test it. **Extensions.** `pg_stat_statements` (query performance — install it today), `pgcrypto`, `pg_trgm` (fuzzy text matching and speeding up `LIKE '%foo%'`), `PostGIS`, `pgvector` for embeddings. `LISTEN` **/** `NOTIFY`**.** Lightweight pub/sub built into the database. Genuinely useful for cache invalidation and real-time updates in small-to-medium systems — just remember it doesn't survive through a transaction-mode connection pooler. * * * ## Closing Thought You don't need to memorize all of this. What's worth internalizing is the shape of the system: Postgres keeps versions of rows rather than overwriting them, which means it needs vacuum; it writes to a log before writing to tables, which gives it durability, replication, and PITR for free; it guesses at query costs using statistics, which means bad statistics produce bad plans; and it layers security from connection rules down to individual rows. Once those four ideas click, the specific settings stop being trivia and start being obvious consequences. Pick one thing to do this week. If you do nothing else: install `pg_stat_statements`, run `EXPLAIN (ANALYZE, BUFFERS)` on your slowest query, and check `pg_stat_user_tables` for a bloated table. That's usually where the wins are hiding.