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.

-- 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

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:

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.

-- 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.

-- 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).

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:

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:

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.

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

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:

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:

# 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:

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.

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.

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:

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:

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:

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:

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:

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:

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.