PostgreSQL Interview Questions (2026): Most Commonly Asked, With Answers

Barely. Postgres stores TEXT and VARCHAR(n) identically on disk and reads them identically at query time. The only real difference is that VARCHAR(n) adds a length check enforced on every insert or update, rejecting anything longer than n characters, while TEXT has no limit at all. There's no storage penalty and no speed penalty for picking TEXT over a length-constrained VARCHAR, unlike some other databases where the length actually changes how a row gets stored.

Most teams I'd trust default to TEXT and add a CHECK constraint if a length limit genuinely matters for the business, since a CHECK is easier to alter later than a column's declared type.

B-tree, the type you get from CREATE INDEX without specifying USING. It handles equality and ordering comparisons directly, less than, less-or-equal, equals, greater-or-equal, greater than, plus BETWEEN, IN, and IS NULL, and it can satisfy an ORDER BY without a separate sort step. For a plain scalar column someone's filtering or sorting on, B-tree is the right first guess almost every time.

EXPLAIN shows the planner's chosen plan and its cost estimates without running the query at all, which makes it safe to run against anything, including a DELETE or UPDATE you're not ready to execute. EXPLAIN ANALYZE actually runs the query, side effects included, and adds real row counts and real timing beside each step's estimate, which is the only way to see where the estimate and reality actually diverge.

Running EXPLAIN ANALYZE on a write you don't intend to keep needs a transaction you roll back afterward. Forgetting that once is a mistake most people only make once.

Multiversion concurrency control. Instead of locking rows for reads, Postgres keeps multiple versions of a row around and lets each transaction see a consistent snapshot of the data as of roughly when it started, rather than making readers and writers fight over the same lock.

The practical result: a reader never blocks a writer, and a writer never blocks a reader, in Postgres. Only writer-writer conflicts on the exact same row actually block each other.

No, not immediately. Because of MVCC, a DELETE or UPDATE can't erase the old row version the instant it runs, some other transaction's snapshot might still need to see it. The old version gets marked dead instead, and VACUUM is the process that comes back later and actually reclaims that space once nothing could possibly need the old version anymore (PostgreSQL docs, routine vacuuming).

JSON stores the exact text you inserted, byte for byte, whitespace, key order, and duplicate keys included, and Postgres reparses it every time a query touches it. JSONB stores a decomposed binary format instead: no whitespace, duplicate keys collapsed (the last one wins), no reparsing at query time, at the cost of a slightly slower write and losing the original formatting and key order.

Almost every application should default to JSONB. JSON is only worth it if you genuinely need the original text preserved exactly, or care about raw insert speed more than every query that ever touches that column afterward.

GROUP BY collapses matching rows down into one output row per group. You lose the individual rows entirely. A window function computes an aggregate or a ranking across a defined set of related rows, the "window," but keeps every original row in the output and attaches the computed value alongside it.

SELECT
  employee,
  department,
  salary,
  RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;

That query gives every employee their own row plus their rank inside their own department. GROUP BY can't produce that in one pass. It can only tell you the top salary per department, not where every individual employee lands relative to it.

RANGE splits data by a value range, a table of events partitioned by month, say. LIST splits by an explicit set of discrete values, a table partitioned by country_code. HASH splits by a hash of the key, mostly to spread write load evenly when there's no natural range or list boundary to lean on.

CREATE TABLE events (
  id BIGINT,
  created_at TIMESTAMPTZ NOT NULL,
  payload JSONB
) PARTITION BY RANGE (created_at);

CREATE TABLE events_2026_07 PARTITION OF events
  FOR VALUES FROM ('2026-07-01') TO ('2026-08-01');

RANGE is the most common in practice, mostly for time-series data where old partitions age out and get dropped in one fast operation instead of run through a slow, row-by-row DELETE.

Streaming replication ships the physical write-ahead log (WAL) byte for byte to a standby, which reconstructs an exact, block-level copy of the entire cluster, every database, every table, nothing selective about it. Logical replication, added in Postgres 10, decodes those same WAL records back into logical row changes, inserts, updates, deletes, and replicates them per table through a publication and subscription.

-- on the publisher
CREATE PUBLICATION orders_pub FOR TABLE orders;

-- on the subscriber
CREATE SUBSCRIPTION orders_sub
  CONNECTION 'host=primary.internal dbname=app'
  PUBLICATION orders_pub;

That per-table selectivity is the real appeal: you can replicate one table instead of the whole cluster, and the subscriber can even run a newer major Postgres version than the publisher, something byte-for-byte streaming replication can't do at all.

A primary key is a unique constraint plus NOT NULL plus "this is the one true identifier for the row" designation, and a table can only have one. A unique constraint can be added to as many columns or column combinations as you want, and it allows NULLs, in fact it allows multiple NULLs because NULL isn't considered equal to another NULL under the unique check.

Internally, both are enforced by an implicitly created unique B-tree index, so from a "how does the database stop duplicates" perspective they work identically. The real difference is intent and referential integrity: foreign keys can only point at a primary key or a column with a unique constraint, and tools, ORMs, and replication systems assume the primary key is the row's canonical identity.

DELETE is a row-by-row DML operation, it's transactional, it fires triggers, it respects WHERE clauses, and it leaves dead tuples behind for autovacuum to clean up later, so the table file doesn't shrink right away. TRUNCATE deallocates the pages directly, it's nearly instant even on a 100 million row table, but it takes an ACCESS EXCLUSIVE lock, can't be filtered with WHERE, and by default doesn't fire row-level triggers.

DROP TABLE removes the table definition entirely, including its indexes, constraints, and any foreign keys pointing at it, which errors out unless you use CASCADE. In practice, if you need to empty a big table fast for a reload, TRUNCATE is the tool, but you need to understand that ACCESS EXCLUSIVE lock will block reads until it commits.

A sequence is its own database object, independent of any table, that hands out an incrementing integer every time nextval() is called. When you use SERIAL or GENERATED ALWAYS AS IDENTITY, Postgres is just creating a sequence and wiring a DEFAULT expression to it for you.

Gaps happen because a sequence isn't transactional the way table rows are. Calling nextval() to reserve a value happens immediately and doesn't roll back if the surrounding transaction later fails, so an inserted row that gets rolled back still burns the sequence value, and a crashed connection mid-insert does the same thing. That's expected behavior, not a bug, and it's why you should never assume IDs are contiguous or use them to count rows.

By default, ON DELETE NO ACTION blocks the delete with an error if any child row still references that parent. ON DELETE CASCADE deletes the child rows automatically, which is convenient but genuinely dangerous, since a single delete on a parent table can quietly wipe out rows in several other tables.

ON DELETE SET NULL sets the foreign key column to NULL, which requires that column to be nullable. ON DELETE SET DEFAULT sets it to whatever DEFAULT is defined, which is rarely used. The same four choices exist for ON UPDATE. CASCADE makes sense for something like order_items depending on orders, but it's a mistake on anything where an accidental parent delete shouldn't silently take out unrelated business data.

A Postgres server can host many databases, and each one is fully isolated, you cannot join across databases in a single query without something like postgres_fdw or dblink. Inside a single database you can have many schemas, which are just namespaces, and you can join tables across schemas within the same database freely.

Every new database ships with a schema called public and a default search_path that includes it, which is why you can create a table without ever typing a schema name. Multi-tenant systems often use one schema per tenant inside a single database rather than one database per tenant, because it's cheaper to manage connections and migrations that way, though a bug in one tenant's queries can, in theory, touch another tenant's schema if permissions aren't locked down.

COMMIT tells Postgres to make every change since the last BEGIN permanent and visible to other transactions, and it involves a WAL flush to disk so the changes survive a crash. ROLLBACK discards everything since BEGIN as if it never happened. By default, psql and most client libraries run in autocommit mode, meaning every statement gets its own implicit transaction unless you explicitly issue BEGIN.

If the connection drops before COMMIT is sent, whether from a network blip, an app crash, or someone killing the terminal, Postgres treats that the same as an explicit ROLLBACK, the backend process notices the client is gone and aborts the open transaction. Nothing partial gets committed, but it does mean any application logic that assumed "the insert already ran" needs to actually check for that.

SERIAL is a shorthand Postgres expands into a separate sequence object plus a DEFAULT nextval() on the column, and it's not part of the SQL standard. That separate sequence has its own permissions, its own ownership rules, and nothing stops an INSERT from supplying an explicit id that later collides with wherever the sequence thinks it's counting from.

CREATE TABLE users (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email TEXT NOT NULL
);

GENERATED ALWAYS AS IDENTITY, added in Postgres 10, is the SQL-standard syntax, ties the sequence more tightly to the column's lifecycle, and blocks a plain INSERT from supplying its own value unless you explicitly add OVERRIDING SYSTEM VALUE. Most schemas designed since 2018 or so reach for IDENTITY over SERIAL by default.

GIN maps every component value inside a composite item, each word in a tsvector, each element in an array, each key in a jsonb document, to the rows that contain it. Lookups are fast, but a single row insert can touch many index entries at once, which makes GIN slower to build and slower to update than it is to read (PostgreSQL docs, index types).

GiST is a more general tree structure built for "nearest" and "overlaps" style questions rather than exact containment, which is why it shows up for geometric types, range types, and exclusion constraints. It can also be lossy, returning candidate rows the executor still has to double-check, in ways GIN usually isn't. For text search or JSONB containment on data read far more than it's written, GIN tends to win. For range overlap or exclusion constraints, GiST is often the only real option on the table.

It costs all three, where applicable, using table statistics, and picks whichever estimate comes out cheapest. It isn't following a fixed rule.

Nested loop scans the outer input row by row and probes the inner input for each one, which works well when the outer side is small or the inner side has a good index to probe with. Hash join builds an in-memory hash table from the smaller input, then streams the other side through it, the pick when there's no useful index but one side fits comfortably in work_mem. Merge join needs both inputs sorted on the join key, either already sorted through an index or sorted explicitly as a plan step, and pays off when both sides are large and a sort was happening anyway.

No, and this is where a lot of memorized answers fall apart. Read Uncommitted is accepted as a setting, but Postgres silently treats it exactly like Read Committed, because MVCC makes a real dirty read, seeing another transaction's uncommitted write, structurally impossible no matter what you ask for.

Read Committed, the default, gives every individual statement its own fresh snapshot. Repeatable Read gives the whole transaction one snapshot taken at its first statement, and Postgres's version of that level also blocks phantom reads, which is stricter than the SQL standard technically requires there. Serializable adds full detection of any interleaving that would produce a result impossible under some serial ordering of the transactions, using Serializable Snapshot Isolation rather than heavier locking (PostgreSQL docs, MVCC).

autovacuum kicks in on a table once its estimated dead-tuple count crosses autovacuum_vacuum_threshold, 50 rows by default, plus autovacuum_vacuum_scale_factor, 20 percent by default, times the table's estimated row count.

Run the arithmetic and the problem shows up fast. A 10,000-row table needs roughly 2,050 dead tuples before autovacuum bothers, easy to hit. A 500-million-row table needs roughly 100 million dead tuples, which on a table that size can mean autovacuum runs far less often than the table actually needs it to. That's the real reason DBAs tune scale_factor down per table instead of trusting the cluster-wide default on their biggest, hottest ones.

ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.01,
  autovacuum_vacuum_threshold = 1000
);

A GIN index on the whole column.

CREATE INDEX idx_products_attrs ON products USING GIN (attributes);

SELECT * FROM products WHERE attributes @> '{"color": "red"}';

The default GIN operator class for jsonb indexes every key and value inside the document, so it supports containment (@>), key-existence checks (?), and the ?| and ?& variants. It doesn't specifically speed up a plain equality check against one extracted field, though, which is where the next question picks up.

ROW_NUMBER assigns a strictly increasing number regardless of ties, so two rows with identical values still land on different numbers based on whatever order the database resolves the tie. RANK gives tied rows the same number, then skips ahead by the count of ties (1, 2, 2, 4). DENSE_RANK also gives tied rows the same number but never skips (1, 2, 2, 3).

Picking the wrong one is a genuinely common bug. "Top 3 distinct scores" written with DENSE_RANK behaves very differently from the same query written with RANK the moment the actual top scores tie with each other.

Partition pruning is the planner recognizing, from a query's WHERE clause, that only some partitions could possibly contain matching rows, and skipping the rest entirely instead of scanning every one of them.

-- prunes cleanly to one partition
SELECT * FROM events WHERE created_at >= '2026-07-01';

-- defeats pruning: the planner can't reason about the transformed value
SELECT * FROM events WHERE date_trunc('month', created_at) = '2026-07-01';

Wrap the partition key in a function and the planner generally loses the ability to match the transformed value against partition boundaries, so it falls back to scanning every partition, which defeats most of the point of partitioning the table at all. This is one of the easier mistakes to make and one of the easiest to miss in code review, since both queries return the correct rows. Only one of them is fast.

Postgres stores old row versions directly inline in the table's own heap. Every UPDATE writes a brand-new row version next to the old one, which is exactly why VACUUM has to exist to clean up afterward. InnoDB, MySQL's default storage engine, does the opposite: it updates the row in place and pushes the old version off into a separate undo log, reconstructed only if some other transaction still needs to see it.

That single design choice explains a lot of what people notice later. MySQL doesn't need anything like VACUUM, because old versions live in undo space that gets purged automatically. Postgres, having chosen to make every version a real row sitting in the table, ends up with a simpler storage model on paper and a genuinely harder operational story around bloat in practice.

ON CONFLICT attempts the insert, and if it collides with a unique index, unique constraint, or exclusion constraint, instead of raising a duplicate key error it runs the UPDATE (or does nothing, with DO NOTHING). You have to name the conflicting column or constraint because Postgres needs to know exactly which existing index guarantees the conflict is even possible, there's no generic "if this row already exists" without one.

Inside the UPDATE clause you get the special EXCLUDED pseudo-table, holding the values that were about to be inserted.

INSERT INTO users (id, email, login_count)
VALUES (1, 'a@b.com', 1)
ON CONFLICT (id) DO UPDATE
SET login_count = users.login_count + EXCLUDED.login_count,
  updated_at = now();

One gotcha: ON CONFLICT DO UPDATE still burns a sequence value on the attempted insert even when it ends up updating instead, and it acquires a row lock on the conflicting row for the duration, so heavy upsert traffic on the same key can serialize more than people expect.

A CHECK constraint is the right tool whenever the rule only needs to look at the row being written, things like price greater than or equal to zero, a status column limited to a fixed set of strings, or a date range where start_date is before end_date. It's declarative, and it's enforced no matter what inserts the row, including a raw psql session, a migration script, or a bulk COPY.

A trigger is what you need when the rule has to look outside the single row, checking a value against another table, enforcing a limit like no more than five active sessions per user, or auto-populating a column from a computation that isn't a simple expression. The tradeoff is that triggers are slower, harder to reason about when several fire on the same table, and easy to forget exist when someone's debugging unexpected behavior six months later. Default to CHECK, escalate to a trigger only when the logic genuinely needs to see beyond the row.

FOR UPDATE takes the strongest row lock, it blocks any other transaction from updating, deleting, or taking any conflicting lock on that row until commit or rollback, and it's what you want before you read a row specifically in order to modify it a moment later. FOR NO KEY UPDATE is a slightly weaker variant Postgres uses internally for plain UPDATE statements that don't touch a column referenced by a foreign key, it still blocks concurrent FOR UPDATE but not FOR KEY SHARE, which matters for reducing lock conflicts on tables heavily referenced by foreign keys.

FOR SHARE is a read lock, multiple transactions can hold it on the same row at once, but it blocks anyone trying to update or delete that row. Mixing these up is a common source of surprise deadlocks, especially FOR UPDATE used where FOR SHARE would have been enough, which unnecessarily serializes readers against each other.

Postgres runs a deadlock detector that kicks in whenever a process has been waiting on a lock longer than deadlock_timeout, which defaults to one second. At that point it builds a wait-for graph of every backend blocked on a lock and who's holding it, and if it finds a cycle, transaction A waiting on B while B waits on A, it kills one of the transactions with a "deadlock detected" error, rolling that one back so the other can proceed.

The classic trigger is two transactions updating the same two rows in opposite order. The fix is almost always application-level discipline: acquire locks or update rows in the same order across your codebase, and keep transactions short. The one-second deadlock_timeout also means a real deadlock costs a full second of stalled connections before Postgres even notices, which matters if you're seeing intermittent latency spikes that don't show up in slow query logs.

A regular view is a stored query, querying it re-runs the underlying SQL every time, so it's always current but exactly as expensive as running the query by hand. A materialized view executes the query once and stores the result on disk like a real table, so reading it is as cheap as a normal table scan, but the data goes stale the moment underlying tables change, it doesn't update itself.

You have to explicitly run REFRESH MATERIALIZED VIEW, and by default that takes an exclusive lock, blocking reads while it rebuilds, unless you've created a unique index on it and use REFRESH MATERIALIZED VIEW CONCURRENTLY, which lets reads continue against the old data while the new version builds. The mistake people make is treating a materialized view like a live cache that magically stays fresh, it doesn't, you need a cron job, a trigger-based refresh, or some other mechanism to call REFRESH on a schedule matching how stale your use case can tolerate.

Recursive CTEs are for hierarchical or graph-shaped data where you don't know the depth ahead of time, an org chart where you need every report under a manager regardless of level, a bill-of-materials where a part contains sub-parts, or building a path from a node to a root in a tree. The structure is a base case UNION ALL a recursive term that joins the CTE's own name back to the base table, and Postgres keeps executing that recursive term against only the rows produced in the previous iteration until it produces zero new rows.

WITH RECURSIVE reports AS (
 SELECT id, manager_id, name
 FROM employees
 WHERE manager_id IS NULL
 UNION ALL
 SELECT e.id, e.manager_id, e.name
 FROM employees e
 JOIN reports r ON e.manager_id = r.id
)
SELECT * FROM reports;

You need UNION ALL rather than UNION because UNION would try to deduplicate results by comparing every new row against everything already accumulated, which is slower and, in graphs with cycles, can change correctness. If your data can have cycles, you generally add an explicit array of visited IDs and check that the current row isn't already in it as a real cycle guard, because UNION alone won't reliably stop infinite recursion.

A BEFORE trigger fires before the row change happens and can modify or reject the row by changing or nulling out the NEW record, that's how you'd auto-set updated_at or normalize an email before it's stored. An AFTER trigger fires once the change has already happened, it can't change what got written, it's for side effects like writing an audit log entry or cascading a change to a denormalized column elsewhere.

ROW level means the trigger function runs once per affected row, which is what you want for anything reading NEW or OLD values. STATEMENT level runs exactly once regardless of how many rows the statement touched, useful for logging that a bulk delete happened without caring about individual rows, or refreshing a summary table once after a batch operation rather than once per row on a 50,000-row UPDATE.

A SQL function is just a query with a name and parameters, Postgres can often inline it directly into the calling query's plan, getting full benefit of the planner's optimizations, index selection, join reordering, as if you'd written the SQL by hand. That makes plain SQL functions the right default for anything that's genuinely a parameterized query.

PL/pgSQL exists for actual procedural control: IF/ELSE branching, loops, exception handling, local variables you compute and reuse, or RAISE to emit a notice or a custom error. It's also required for triggers, since trigger functions have to read NEW/OLD and make decisions based on them. The cost is that PL/pgSQL functions are typically a black box to the planner, so a PL/pgSQL function called per row in a large query can be dramatically slower than an equivalent plain SQL expression or a join.

Each Postgres connection is a full OS process, not a lightweight thread, and every process carries real memory overhead plus the cost of tracking its own snapshot and lock state for MVCC. That means Postgres struggles past a few hundred concurrent connections even if most are idle, and it degrades in throughput well before hitting any hard connection limit.

PgBouncer multiplexes many client connections onto a smaller pool of real Postgres backend connections. In session pooling, a client holds a connection for its whole session, preserving session state but not saving much. In transaction pooling, the client only holds the real connection for a single transaction, then it's returned to the pool, which is where the real capacity gain comes from, but it breaks anything depending on session state persisting between transactions, like prepared statements created outside a transaction, advisory locks held across transactions, or SET commands you expect to stick. Picking transaction pooling without auditing the app for those assumptions is a common way to get intermittent, hard-to-reproduce bugs in production.

Postgres pages are 8KB by default, and the rule of thumb is a single row is supposed to fit on one page, so anything blowing past roughly 2KB per value gets handled by TOAST, The Oversized Attribute Storage Technique. Postgres transparently moves that value out to a separate TOAST table, chops it into chunks, and optionally compresses it before deciding whether to move it out-of-line at all. The main table row just keeps a small pointer to the TOASTed value.

This is invisible from SQL, you SELECT the column and get the full value back, but it has real performance implications. A query that only needs columns that aren't TOASTed can skip fetching them entirely, which is part of why selecting specific columns instead of SELECT * actually matters on tables with large JSONB or text columns, and why VACUUM and backups take noticeably longer on tables with a lot of TOASTed data.

An array column like tags text[] is fine when the values are small in number, rarely queried individually, never need their own metadata, and you're not going to need referential integrity against them. Tagging a blog post with a handful of freeform labels is a reasonable case, and you can index it with a GIN index and use the containment operator to search efficiently.

It falls apart once you need a foreign key relationship, per-element metadata like when a tag was applied and by whom, the ability to update a single element without rewriting the whole row, or real relational queries like finding all posts sharing at least two tags with this one, which is awkward in array form and trivial with a join table. The failure mode people hit is starting with an array because it's convenient, then needing exactly one of those things six months later and having to do a painful migration to a proper many-to-many table under load.

LIKE with a leading wildcard can't use a normal B-tree index at all, Postgres has no way to know where in the string to start looking, so it forces a sequential scan of every row. Full text search instead converts your text into a tsvector, a sorted list of normalized lexemes with words reduced to their stem, so running and runs both become run, with position information kept for ranking.

You store or generate that tsvector, typically in a generated column or maintained via trigger, index it with GIN, and query it with a tsquery, matching whether the tsvector satisfies the tsquery rather than doing string pattern matching. That gets you stemming, stop word removal, and ranking via ts_rank, and it's genuinely fast because it's a real index lookup, not a table scan. It's not a replacement for something like Elasticsearch if you need fuzzy matching or faceted search at real scale, but for letting users search a few tables of text without standing up a whole search cluster, it covers a surprising amount of ground.

The planner doesn't look at your actual data when choosing a plan, it looks at statistics: estimated row counts, the most common values in a column and their frequencies, a histogram of value distribution, and correlation between physical row order and value order. Those statistics come from ANALYZE, which autovacuum runs automatically based on how many rows have changed, and they go stale fast after a bulk load, a big delete, or a schema change autovacuum hasn't caught up on.

When statistics are stale or too coarse, the planner might estimate a filter returns 50 rows when it actually returns 2 million, and pick a nested loop join that's fine for 50 rows but catastrophic for 2 million. default_statistics_target controls how detailed the histogram is, and it can be raised per column with ALTER COLUMN SET STATISTICS, which matters most on columns with skewed distributions. If you suspect a bad plan is a stats problem rather than a missing index, running ANALYZE by hand and re-running EXPLAIN ANALYZE is the fastest way to confirm it before you build an index you didn't need.

BRIN, Block Range Index, doesn't index individual rows, it divides the table into physical block ranges and stores just the min and max value of the indexed column within each range. That makes it dramatically smaller than a B-tree, at the cost of being much less precise, a query has to scan every row in any block range whose min/max could possibly contain the value, rather than jumping straight to it.

The condition that makes it worth it is physical correlation, the indexed column's values need to roughly match the order rows are stored in on disk. The textbook case is a created_at timestamp on an append-only table like logs or events, since rows are naturally inserted in time order. Using BRIN on a column with no correlation to insert order, like a random UUID, gives you almost no pruning benefit and you'd be better off with a B-tree or nothing at all.

An index-only scan means Postgres can answer a query using just the index, without going back to the actual table to fetch the row. Two things have to be true: every column the query needs has to be present in the index, and the page has to be marked all-visible in the visibility map, meaning no other transaction could still need to see an older version of those rows under MVCC. That second condition is why index-only scans on a table with heavy write activity and infrequent vacuuming often silently degrade back into regular index scans.

The INCLUDE clause lets you add columns to an index that aren't part of the index's key, not used for searching or sorting, but stored in the index so a query needing them can be satisfied without a heap fetch. That's the difference between indexing customer_id alone and indexing customer_id with order_total included, the second lets a query selecting both skip the heap entirely, while keeping the index cheaper to maintain than if order_total were part of the actual sort key.

Old-style partitioning built the parent-child relationship using real table inheritance, then relied on CHECK constraints on each child plus constraint_exclusion to get the planner to skip partitions that couldn't match a query, and you had to hand-write triggers or rules to route inserts to the correct child table. It worked, and plenty of production systems ran on it for years, but everything about it was manual and error-prone, forgetting a CHECK constraint on a new partition meant pruning silently stopped working for it, and there was no real concept of a partition key at the schema level, just a convention everyone had to remember.

Declarative partitioning makes partitioning a first-class concept, the parent table declares its partition key, you attach or detach partitions with dedicated DDL, routing happens automatically, and pruning is guaranteed to work based on the declared bounds rather than hoping constraint exclusion figures it out. The old approach isn't gone, it's still occasionally useful for genuinely different table shapes sharing a base set of columns, but as a partitioning strategy it's been correctly obsoleted.

Because FLOAT and DOUBLE PRECISION are binary floating point, and plenty of ordinary decimal fractions, 0.1 being the classic example, can't be represented exactly in binary. Add enough of those small rounding errors across enough transactions and a ledger stops reconciling for reasons nobody can point to in any single row.

NUMERIC(precision, scale) stores exact decimal digits with no silent rounding, at a real cost: it's slower for heavy arithmetic than native floating point, since Postgres does the math in software instead of hardware. For money, that tradeoff almost always goes the right way. There's also a dedicated money type, but its formatting depends on the session's locale settings in ways that surprise people, and I'd skip it in favor of NUMERIC plus a separate currency column every time.

A partial index only indexes the rows matching a WHERE clause you add at creation time, not the whole table.

CREATE INDEX idx_orders_pending
  ON orders (created_at)
  WHERE status = 'pending';

If 2 percent of a 40-million-row orders table is sitting in 'pending' at any given moment, and nearly every dashboard query filters on exactly that status, a partial index built only on those rows is a fraction of the size of a full index across the whole table. It costs nothing to maintain on the other 98 percent of writes, since rows that never match 'pending' never touch it, and the planner reaches for it automatically whenever a query's WHERE clause implies the same condition.

Stale or thin statistics, almost always. The planner's row estimates come from pg_statistic, refreshed by ANALYZE (run automatically as part of autovacuum, or by hand), and a five-order-of-magnitude miss usually means those statistics haven't caught up with how the data's shape actually changed, or the column's distribution is too skewed for the default statistics target to capture well.

ALTER TABLE orders ALTER COLUMN customer_id SET STATISTICS 500;
ANALYZE orders;

A miss that size rarely just misreports something cosmetic. It usually pushes the planner into the wrong join strategy entirely, a nested loop that looked cheap against an estimated 40 rows turns into a nested loop actually probing an index 2 million times. Running ANALYZE by hand or raising the target statistics for that specific column, as above, is the fix, not rewriting the query.

Whichever transaction commits its UPDATE first wins normally. The second transaction's UPDATE hits a serialization conflict, since its snapshot no longer reflects what's actually in the row anymore, and Postgres raises an error instead of silently applying a write based on data that's gone stale underneath it.

-- Session A
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE id = 1;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

-- Session B (started before Session A committed)
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE id = 1;
UPDATE accounts SET balance = balance - 50 WHERE id = 1;
COMMIT;
-- ERROR: could not serialize access due to concurrent update

The only correct fix is catching that error and retrying the entire transaction from its first statement, not just the UPDATE. Most ORMs and connection poolers don't do this for you automatically, which is the part candidates usually miss: choosing Repeatable Read or Serializable buys correctness your application code has to earn with a retry loop, not correctness you get for free by flipping one setting.

Regular VACUUM reclaims dead-tuple space for reuse by future inserts on that same table, but it generally doesn't shrink the file on disk or hand space back to the operating system, and it runs fine alongside normal reads and writes. VACUUM FULL rewrites the entire table into a new, compact file and does return space to the OS, but it takes an ACCESS EXCLUSIVE lock for the whole operation, which means every other query against that table queues up and waits.

VACUUM (VERBOSE, ANALYZE) orders;
-- vs, rarely, and during a maintenance window:
VACUUM FULL orders;

On anything bigger than a small table in production, that lock is the outage, not the fix. Regular, frequent VACUUM, mostly handled by autovacuum already, is the actual day-to-day tool. VACUUM FULL is closer to a last resort scheduled deliberately, not a habit.

Not much. A default GIN index on the whole column optimizes containment and existence checks across the entire document, not equality on one specific extracted text field. For a hot, specific path like that, a B-tree expression index built directly on the extracted value pulls more weight.

CREATE INDEX idx_products_brand ON products ((attributes->>'brand'));

SELECT * FROM products WHERE attributes->>'brand' = 'nike';

That index only helps queries written to match its exact expression, attributes->>'brand' compared with equality, not the JSONB column in general. The tradeoff is real: broad GIN coverage for flexible, unpredictable queries against the whole document, or a narrow, cheap expression index for one query shape you already know is hot.

Not since Postgres 12. Before that version, every CTE materialized as its own separate step no matter what, an optimization fence that could stop the planner from pushing a filter down into it. Since 12, a non-recursive CTE referenced exactly once gets inlined into the main query automatically, the same way a subquery would, unless you add MATERIALIZED explicitly to force the old fencing behavior (PostgreSQL docs, WITH queries).

WITH recent_orders AS NOT MATERIALIZED (
  SELECT * FROM orders WHERE created_at > now() - interval '7 days'
)
SELECT customer_id, count(*)
FROM recent_orders
WHERE status = 'shipped'
GROUP BY customer_id;

Forcing MATERIALIZED still matters when a CTE is genuinely expensive and referenced more than once, computing it once and reusing the result beats recomputing it per reference. Recursive CTEs are still always materialized, since inlining something that iterates on itself wouldn't make sense in the first place.

Asynchronous, the default, lets the primary commit and report success to the client the instant it's durable locally, then ships WAL to standbys afterward at whatever pace the network allows. A crash right after that commit can lose the most recent transactions that hadn't shipped yet.

Synchronous replication makes the primary wait for at least one standby to confirm it received, or applied, the transaction before reporting success to the client, which guarantees no committed transaction is ever lost to a primary crash. The cost is every single commit's latency now including a network round trip to that standby. Most teams run asynchronous by default and reach for synchronous only on the specific tables or transactions where losing the last few seconds of data is genuinely unacceptable, a financial ledger being the obvious case, not the whole cluster by default.

I'd default to Postgres for almost anything with real query complexity: native JSONB, arrays, window functions, CTEs that inline cleanly since version 12, and an extension system, PostGIS, pg_trgm, pgvector, that turns it into a specialized database without switching systems entirely.

That's not a universal answer, though. MySQL's replication defaults are simpler to reason about for a straightforward primary-replica setup, and there's still a wider bench of hosting platforms and DBAs with deep MySQL-specific tuning experience out there, mostly a legacy of WordPress and the early-2010s web stack defaulting to it. If the workload is genuinely simple, key-value-ish CRUD at huge scale, and the team already knows MySQL cold, rewriting the stack to Postgres for its own sake isn't a strong argument. I'd want a specific feature gap, not a general preference, before recommending that migration to anyone.

Regular VACUUM marks dead tuples as reusable space and records it in the table's free space map, so future inserts and updates can reuse those slots. What it does not do is give the space back to the operating system, the table file stays the same size, it just becomes internally fragmented with dead space scattered between live rows. That's bloat, and it accumulates fastest on tables with heavy update or delete churn if autovacuum can't keep up, whether because it's misconfigured or getting starved by a long-running transaction holding back the oldest visible row, which prevents VACUUM from reclaiming anything newer than that transaction's start.

VACUUM FULL genuinely reclaims the space, but it works by rewriting the entire table into a new file and swapping it in, requiring an ACCESS EXCLUSIVE lock for the whole operation, meaning the table is completely unavailable for however long the rewrite takes, a non-starter on an actively used production table of any real size. The practical fix is pg_repack, an extension that does the equivalent of VACUUM FULL but builds the new table alongside the old one and only takes a brief exclusive lock at the very end to do the swap, or, for partitioned tables, you can sometimes sidestep the whole problem by dropping old partitions instead of deleting rows out of a single giant table.

First I'd confirm the no-change assumption is actually true rather than assumed, running EXPLAIN ANALYZE on the exact query now and comparing the plan shape, not just timing, to a saved baseline. A plan shape change, say from an index scan to a sequential scan, tells you the planner's estimates flipped, while an identical plan just running slower points somewhere else entirely, disk I/O, lock contention, or resource starvation.

If the plan changed, the most common cause is stale statistics, autovacuum falling behind after unusually heavy write activity, or a bulk load or delete that changed the data distribution before ANALYZE caught up, so running ANALYZE manually and re-checking is the fastest diagnostic. If the plan is identical but slower, I'd check pg_stat_activity for other sessions holding long locks on the same tables, since a query waiting on a lock shows as slow in application logs but is actually just queued. I'd also check for autovacuum actively running on the table right then, competing for I/O, and whether the table has quietly grown past the point where it fits in shared_buffers or the OS page cache, turning what used to be cached reads into real disk I/O, a gradual regression that only becomes visible once you cross that threshold. Prepared statement plan caching is worth checking too, since Postgres switches from a generic plan to re-planning with actual parameter values after enough executions, and the generic plan it settled on can be bad for the specific values in production traffic even though the data hasn't changed.

A replication slot, logical or physical, is Postgres's way of guaranteeing it won't discard any WAL that a given subscriber might still need, it tracks the oldest WAL position that consumer hasn't confirmed receiving, and Postgres will not recycle or delete WAL segments past that point regardless of checkpoint activity or other retention settings. That guarantee is exactly what makes replication reliable, a temporarily disconnected subscriber can reconnect later and resume exactly where it left off.

The failure mode is when a subscriber goes offline or gets stuck, a downstream consumer crashes, a connector dies, a connection silently hangs, and nobody notices, because the slot doesn't go away just because nothing's reading from it, it keeps holding that retention point indefinitely. WAL keeps accumulating on the primary's disk, ordinary checkpoint activity has no ability to override a replication slot's hold on WAL segments, and eventually the disk hosting pg_wal fills up completely, at which point Postgres can't accept writes at all, taking down every application on that database, not just whatever was supposed to be consuming that slot. The fix is entirely operational, alert on replication slot lag and either fix or explicitly drop a slot for a subscriber that isn't coming back, since an abandoned slot is a silent, slow-motion outage waiting to happen.