PostgreSQL Interview Questions and Answers

What interviewers are testing: whether you know developer screens stress query design and ORM behavior while DBA rounds go deeper on vacuum, replication, and PITR.

Many postgresql developer interview questions still touch vacuum and indexes because app code causes bloat and bad plans. A strong answer is:

"Developer interviews stress query design, indexes, and ORM behavior; DBA rounds go deeper on vacuum, replication, backups, and failover."

What interviewers are testing: whether you articulate PostgreSQL strengths—MVCC, JSONB, extensions, and standards compliance—without dismissing MySQL use cases.

Interviewers want trade-off awareness, not fanboy answers.

MySQL/InnoDB also uses MVCC—Postgres interviews go deeper on vacuum, index types, and planner behavior. A strong answer is:

"I choose PostgreSQL for MVCC, rich SQL, JSONB, extensions, and standards compliance; MySQL still fits simpler LAMP stacks and specific hosting constraints."

What interviewers are testing: Whether you understand the process-per-connection architecture, background processes, WAL, and why connection pooling matters.

PostgreSQL uses a multi-process model (not multi-threaded like some databases):

Data files live under PGDATA (often /var/lib/postgresql/<version>/main on Ubuntu). Each database is a collection of schemas and tables stored in heap files.

Connection cost motivates PgBouncer pooling in production.

A strong answer is:

One backend process per connection—so pooling matters at scale—and WAL makes commits durable and feeds replication.

What interviewers are testing: Whether you understand database isolation, schema-qualified names, and search_path risks in applications and migrations.

search_path controls unqualified name resolution—migrations should set explicit schemas to avoid public surprises.

CREATE SCHEMA billing;
CREATE TABLE billing.invoices (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  amount numeric(12,2) NOT NULL
);

A strong answer is:

I use schemas to separate domains in one database and qualify names in migrations so search_path never hides the wrong table.

What interviewers are testing: Whether you choose types based on semantics—numeric for exact arithmetic, timestamptz for instants, UUID/identity for identifiers, and JSONB only for genuinely flexible structure.

timestamp without time zone still has legitimate uses—for example "every day at 09:00 local time" or intentionally timezone-free business timestamps. Using it for global instants is a common interview trap.

A strong answer is:

timestamptz for events, numeric for money, bigint or uuid for keys—I avoid float for currency and document why jsonb is not a default for every column.

What interviewers are testing: Whether you rely on database constraints for integrity and understand FK actions and the need to index referencing columns for common joins/delete cascades.

CREATE TABLE order_items (
  order_id bigint REFERENCES orders(id) ON DELETE CASCADE,
  sku text NOT NULL,
  qty int CHECK (qty > 0),
  PRIMARY KEY (order_id, sku)
);

ON DELETE actions: CASCADE, SET NULL, RESTRICT, NO ACTION—know ORM defaults.

A strong answer is:

I enforce business rules in CHECK and FK constraints close to data—migrations add indexes on FK columns to avoid slow cascades and joins.

What interviewers are testing: Whether you understand that SERIAL is legacy sequence shorthand, IDENTITY is the SQL-standard sequence-backed choice for new schemas, and UUIDs avoid centralized sequence allocation when IDs must be generated independently.

CREATE TABLE users (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email citext UNIQUE NOT NULL
);

Sequences can have gaps (rollback, crash)—interviewers check you do not assume gapless IDs.

A strong answer is:

IDENTITY for monotonic numeric PKs in single-database apps; UUID when services generate IDs independently—I never assume sequences are gap-free.

What interviewers are testing: whether you explain MVCC visibility, xmin/xmax, and why readers do not block writers in PostgreSQL.

Multi-Version Concurrency Control keeps multiple row versions so readers do not block writers and writers do not block readers.

Each row version has system columns:

MVCC determines visibility through transaction snapshots. Under PostgreSQL's default READ COMMITTED isolation, each statement receives a fresh snapshot; Repeatable Read retains a stable transaction snapshot. UPDATE creates a new row version; old version becomes a dead tuple until vacuum reclaims space.

This differs from in-place overwrite databases and explains why heavy UPDATE causes bloat.

A strong answer is:

MVCC uses transaction snapshots for visibility—READ COMMITTED gets a new snapshot per statement, Repeatable Read keeps one snapshot for the transaction. Dead tuple versions need vacuum, which is core Postgres DBA knowledge.

What interviewers are testing: Whether you understand PostgreSQL isolation levels, snapshot behavior, and when Serializable requires retry on 40001.

Postgres implements:

BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- consistent reads within this transaction
COMMIT;

Anomalies:

• Read Uncommitted behaves like Read Committed in PostgreSQL
• Read Committed can see different committed results between statements
• Repeatable Read uses a stable transaction snapshot, so PostgreSQL does not expose non-repeatable or phantom reads, although serialization anomalies are still possible
• Serializable prevents serialization anomalies through SSI and may abort transactions with 40001; applications must retry

A strong answer is:

Default read committed is fine for most OLTP; I use repeatable read or serializable when invariants need stronger guarantees and handle retry on 40001.

What interviewers are testing: Whether you distinguish row/table locks, identify blockers and waiters, and recognize long transactions as both concurrency and vacuum problems.

Postgres uses row-level locks for UPDATE/DELETE and table-level locks for DDL.

Useful views:

SELECT * FROM pg_locks WHERE NOT granted;
SELECT * FROM pg_stat_activity WHERE wait_event_type = 'Lock';

ORM sessions left open across HTTP requests are a common production blocker.

A strong answer is:

I keep transactions short, inspect blocking chains and old transactions in pg_stat_activity, and avoid leaving sessions idle in transaction.

What interviewers are testing: Whether you can explain cycle detection, SQLSTATE 40P01, deterministic lock ordering, and application retry.

Deadlock detector picks a victim transaction and aborts it with SQLSTATE 40P01. Application must retry.

Prevention:

• Lock rows in consistent order
• Keep transactions small
• Use advisory locks for application-level ordering

BEGIN;
SELECT * FROM accounts WHERE id IN (1, 2) ORDER BY id FOR UPDATE;
-- updates
COMMIT;

Interviewers want you to mention retry with backoff in app code.

A strong answer is:

Postgres detects deadlocks and kills one transaction—I retry idempotent operations and design lock order to prevent cycles.

What interviewers are testing: Whether you can trace commit durability from WAL generation through local flush and, when configured, synchronous standby acknowledgement.

Write-Ahead Logging: changes go to WAL on disk before heap pages. COMMIT waits for WAL flush (depending on synchronous_commit).

WAL enables PITR, streaming replication, and crash recovery.

A strong answer is:

COMMIT durability starts with WAL. With the default synchronous_commit=on, PostgreSQL waits for local WAL flush and may also wait on configured synchronous standbys. off trades recent-transaction durability for latency; local still preserves local crash durability.

What interviewers are testing: Whether you choose an access method from the operators and data distribution instead of defaulting every workload to B-tree.

CREATE INDEX idx_orders_created ON orders USING brin (created_at);
CREATE INDEX idx_docs_body ON docs USING gin (body jsonb_path_ops);

Wrong index type wastes space and slows writes—interviewers ask you to match operator class to query.

A strong answer is:

B-tree by default; GIN for jsonb @> and full-text; BRIN for append-only timestamps on huge tables—I justify type from the WHERE clause operators.

What interviewers are testing: Whether you understand column order, selectivity, partial predicates, index-only scans, and PostgreSQL 18 skip scan.

Composite — multicolumn B-tree indexes are usually most efficient when predicates constrain the leading columns. PostgreSQL 18 can also use skip scan in selected cases where useful predicates exist on later columns, so the old "leftmost prefix or unusable" rule is too absolute.

CREATE INDEX idx_orders_user_created ON orders (user_id, created_at DESC);

Partial — indexes subset of rows:

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

Smaller index, faster writes, perfect for hot filtered queries.

Covering index (INCLUDE):

CREATE INDEX idx_orders_user_inc ON orders (user_id) INCLUDE (total_cents);

Enables index-only scans when visibility map allows.

A strong answer is:

I order composite indexes around real predicates—usually equality columns first, then range/sort. In PostgreSQL 18 I also know skip scan can make later-column predicates usable in some distributions, but I verify with EXPLAIN rather than assuming it.

What interviewers are testing: whether you choose jsonb_path_ops for containment queries versus jsonb_ops for key-existence patterns.

JSONB stores binary JSON with efficient operators.

-- Containment query
SELECT * FROM products
WHERE attrs @> '{"color": "red"}';

CREATE INDEX idx_products_attrs ON products
USING gin (attrs jsonb_path_ops);

Tested containment on PostgreSQL 18:

SELECT '{"tags": ["a","b"]}'::jsonb @> '{"tags": ["a"]}'::jsonb;
-- returns true

Prefer real columns for stable typed fields; JSONB for dynamic attributes.

A strong answer is:

I use jsonb_path_ops when containment/jsonpath operations match the workload; default jsonb_ops when I also need key-existence operators.

What interviewers are testing: Whether you account for write amplification, storage, HOT-update impact, selectivity, and actual usage before adding/removing indexes.

Indexes speed reads and slow writes (every INSERT/UPDATE maintains index pages).

Skip or delay indexes when:

• Table is tiny (seq scan cheaper)
• Write-heavy with rare reads
• Low selectivity column alone (e.g. boolean flag)
• Wrong type for query pattern

Monitor unused indexes:

SELECT * FROM pg_stat_user_indexes
WHERE idx_scan = 0 AND schemaname = 'public';

Treat idx_scan = 0 as a candidate signal, not deletion proof. Check when statistics were reset, workload cycles, and whether the index backs a constraint before removing it.

A strong answer is:

I index for proven query patterns from pg_stat_statements, investigate idx_scan=0 before dropping anything, and accept seq scans on small tables.

What interviewers are testing: Whether you know why normal index creation blocks writers and the operational trade-offs/failure modes of CONCURRENTLY.

Normal CREATE INDEX allows reads but blocks writes. CREATE INDEX CONCURRENTLY allows writes and performs additional work/scans.

CREATE INDEX CONCURRENTLY cannot run inside a transaction block. A failed concurrent build can leave an INVALID index, which should be inspected and then dropped/retried or rebuilt appropriately.

CREATE INDEX CONCURRENTLY idx_users_email ON users (email);

Check pg_index.indisvalid; a failed concurrent build can leave an invalid index that must be cleaned up and rebuilt before relying on it.

Production migrations on large tables almost always use CONCURRENTLY.

A strong answer is:

I use CONCURRENTLY for online index creation on live tables, knowing it cannot run inside a transaction block. If the build fails, I check indisvalid and clean up or rebuild the invalid index before relying on it.

What interviewers are testing: Whether you can read plan nodes, estimated vs actual rows, loops, timing, buffer activity, and distinguish a genuinely bad sequential scan from one the planner chose correctly.

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE user_id = 42 AND created_at > now() - interval '30 days';

Important: EXPLAIN ANALYZE actually executes the query. Be careful with expensive statements and especially INSERT, UPDATE, DELETE, or functions with side effects in production. Use plain EXPLAIN first when execution is unsafe.

Compare estimated vs actual rows—large gaps mean stale statistics → ANALYZE.

A strong answer is:

EXPLAIN ANALYZE shows real timings; I look for seq scans on big tables, bad row estimates, and buffer hits versus reads.

What interviewers are testing: whether you justify why might postgres choose a sequential scan with trade-offs and production consequences.

Reasons:

Fix patterns: expression index, partial index, increase stats target, rewrite predicate.

A strong answer is:

Seq scan is not always wrong—I check row counts and selectivity; fix stats or add a targeted index when the scan is hot and large.

What interviewers are testing: Whether you rank workload cost by aggregate evidence before optimizing individual anecdotes.

Extension pg_stat_statements aggregates query stats (calls, total time, mean time, rows). pg_stat_statements must be loaded through shared_preload_libraries and enabled with CREATE EXTENSION in the database before you can query it.

SELECT query, calls, mean_exec_time, rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Triage flow:

1. Find top total time queries
2. EXPLAIN (ANALYZE) the normalized query
3. Add index or rewrite SQL
4. Verify with load test

Pair with log_min_duration_statement for raw slow logs.

A strong answer is:

I rank by total_exec_time in pg_stat_statements, EXPLAIN the winner, fix plan or index, and re-measure—not guess from ORM logs alone.

What interviewers are testing: Whether you connect bad join plans to cardinality estimates/statistics rather than forcing a favorite join algorithm.

Planner picks join order using table statistics—histograms, most-common values, n_distinct, and related statistics.

Tips:

• Keep planner statistics accurate.
• Index join/filter keys where the workload benefits—not every join column automatically.
• Compare estimated vs actual cardinalities.
• For correlated/skewed columns, consider higher statistics targets or extended statistics.
• Rewrite only after the execution plan shows why estimates or join strategy are poor.

-- Filter early in CTE
WITH recent AS (
    SELECT user_id
    FROM orders
    WHERE created_at > now() - interval '7 days'
)
SELECT u.email, COUNT(*)
FROM recent r
JOIN users u ON u.id = r.user_id
GROUP BY u.email;

A strong answer is:

Accurate cardinality estimates drive join choices. I compare estimated and actual rows, refresh or improve statistics when necessary, and add indexes only where the query pattern benefits.

What interviewers are testing: whether you prefer keyset pagination over OFFSET for large tables to avoid full scans.

OFFSET pagination degrades on large offsets (scans skipped rows):

-- Slow at high page numbers
SELECT * FROM orders ORDER BY id LIMIT 20 OFFSET 100000;

Keyset (seek) pagination:

SELECT * FROM orders
WHERE id > 100000
ORDER BY id
LIMIT 20;

Requires stable sort key; handle tie-breaker column.

A strong answer is:

Keyset pagination on indexed columns for infinite scroll; OFFSET only for small admin pages.

What interviewers are testing: whether you choose JSONB for indexed containment queries and JSON when you only store and return opaque text.

Use JSONB for querying; JSON only if you need exact text preservation.

SELECT attrs->>'sku' AS sku,
       attrs->'dimensions'->>'width' AS width
FROM products;

A strong answer is:

JSONB for application payloads I filter on; extract hot keys to typed columns when queries stabilize.

What interviewers are testing: Whether you distinguish readability from materialization behavior and know when windows replace self-joins/subqueries.

Modern PostgreSQL can inline/fold eligible side-effect-free CTEs instead of treating every CTE as an optimization fence. MATERIALIZED and NOT MATERIALIZED let you influence that behavior when needed. PostgreSQL has supported CTE inlining since PostgreSQL 12.

WITH monthly AS (
  SELECT date_trunc('month', created_at) AS m, SUM(amount) AS revenue
  FROM orders
  GROUP BY 1
)
SELECT m, revenue,
       revenue - LAG(revenue) OVER (ORDER BY m) AS mom_delta
FROM monthly;

Window functions: ROW_NUMBER, RANK, LEAD/LAG, SUM() OVER (PARTITION BY …)—see SQL technical guide for more patterns.

A strong answer is:

CTEs clarify multi-step analytics; windows for ranking and period-over-period without self-join explosion.

What interviewers are testing: whether you answer lateral joins — practical use with specific, production-grounded detail—not generic recall.

LATERAL lets subquery reference columns from preceding FROM item—great for top-N per group:

SELECT c.name, r.*
FROM customers c
CROSS JOIN LATERAL (
  SELECT *
  FROM orders o
  WHERE o.customer_id = c.id
  ORDER BY o.created_at DESC
  LIMIT 3
) r;

Alternative to window + filter patterns; planner can use indexes per customer.

A strong answer is:

LATERAL for correlated top-N per group—I reach for it when a subquery needs outer row values.

What interviewers are testing: whether you answer common postgresql extensions in interviews with specific, production-grounded detail—not generic recall.

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_users_name_trgm ON users USING gin (name gin_trgm_ops);

Core PostgreSQL already provides UUID generation such as gen_random_uuid(), and PostgreSQL 18 adds uuidv7().

A strong answer is:

I enable extensions deliberately—pg_stat_statements in production where operational policy/managed service support allows it, pg_trgm or pgvector when product needs demand it.

What interviewers are testing: whether you answer upsert — on conflict with specific, production-grounded detail—not generic recall.

INSERT INTO inventory (sku, qty)
VALUES ('ABC', 10)
ON CONFLICT (sku) DO UPDATE
SET qty = inventory.qty + EXCLUDED.qty;

Requires UNIQUE or PRIMARY KEY on conflict target.

EXCLUDED refers to proposed insert row. Watch lock contention on hot keys.

A strong answer is:

ON CONFLICT for idempotent writes—I define unique constraints explicitly and handle conflict updates atomically.

What interviewers are testing: Whether you can connect MVCC dead tuples, visibility maps, autovacuum thresholds, freezing, and XID wraparound.

UPDATE/DELETE leave dead tuples. VACUUM reclaims space for reuse, updates visibility map, prevents transaction ID wraparound.

Autovacuum runs automatically—tune, do not disable.

Long transactions block vacuum cleanup → bloat.

A strong answer is:

Vacuum reclaims dead tuple versions for reuse and protects against XID wraparound. I tune autovacuum on high-churn tables and investigate long-running or idle in transaction sessions that hold old snapshots before terminating anything.

What interviewers are testing: Whether you distinguish reusable dead space from physical relation shrinkage and choose VACUUM, tuning, pg_repack, or VACUUM FULL appropriately.

Bloat = heap pages holding dead tuples or sparse space.

Plain VACUUM makes dead space reusable inside the table but does not normally shrink the relation file significantly or return space to the OS. Use VACUUM FULL or pg_repack when you need to reclaim disk space from the relation itself.

pg_stat_user_tables.n_dead_tup is an approximate dead-tuple signal; use pgstattuple or dedicated bloat-estimation techniques when you need to measure actual table/index bloat.

A strong answer is:

Bloat from dead tuples and blocked vacuum—I lower autovacuum thresholds on hot tables. VACUUM reuses internal space; pg_repack or VACUUM FULL is for actually shrinking the on-disk relation.

What interviewers are testing: whether you answer analyze and statistics with specific, production-grounded detail—not generic recall.

ANALYZE samples table data to update pg_statistic for the planner.

When to run:

• After large COPY/INSERT
• When EXPLAIN estimates diverge from actual
• Autovacuum analyze handles routine cases

ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000;
ANALYZE orders;

Higher statistics target improves estimates on skewed columns (costs more analyze time).

A strong answer is:

Bad plans often mean stale stats—I ANALYZE after bulk changes and raise statistics target on skewed filter columns.

What interviewers are testing: whether you answer table partitioning — range, list, hash with specific, production-grounded detail—not generic recall.

Native declarative partitioning (Postgres 10+):

CREATE TABLE measurements (
  logdate date NOT NULL,
  value numeric
) PARTITION BY RANGE (logdate);

CREATE TABLE measurements_2026_06
  PARTITION OF measurements
  FOR VALUES FROM ('2026-06-01') TO ('2026-07-01');

Partition pruning skips irrelevant child tables—critical for performance.

A strong answer is:

Range partition time-series by month, attach new partitions ahead of time, and verify pruning in EXPLAIN.

What interviewers are testing: whether you answer connection pooling — why pgbouncer with specific, production-grounded detail—not generic recall.

Each PostgreSQL connection normally consumes a dedicated backend process and associated memory/resources, so thousands of mostly idle application connections are inefficient.

PgBouncer pools connections:

ORMs + serverless need pooling (PgBouncer, RDS Proxy, Supavisor).

Transaction pooling works well for stateless web transactions but changes session semantics. Review features that assume a stable backend session—session-level settings/state, temporary objects, session advisory locks, and similar behavior—before choosing it.

A strong answer is:

I pool at PgBouncer transaction mode for stateless web apps when session semantics allow it—raw Postgres connections do not scale to thousands of lambdas or pods.

What interviewers are testing: Whether you understand WAL send/write/flush/replay and choose synchronous vs asynchronous replication from RPO/latency requirements.

Primary streams WAL records to standby; standby replays WAL—physical streaming replication sends WAL changes that a standby replays against a physical copy of the cluster. It is physical/block-level replication of the cluster rather than row-level logical change replication.

Standbys are hot (read-only) with hot_standby = on.

Monitor replication lag via pg_stat_replication.

A strong answer is:

WAL streaming keeps replica identical to primary—I monitor lag bytes and choose sync vs async based on RPO requirements.

What interviewers are testing: whether you distinguish physical streaming replication for HA failover from logical replication for selective table sync.

CREATE PUBLICATION orders_pub FOR TABLE orders;
CREATE SUBSCRIPTION orders_sub
  CONNECTION 'host=primary dbname=app'
  PUBLICATION orders_pub;

A lagging logical slot can retain WAL through restart_lsn; slots can also expose xmin/catalog_xmin horizons that prevent VACUUM from removing tuples/catalog rows still required by the slot. Monitor pg_replication_slots, especially restart_lsn, confirmed_flush_lsn, xmin, and catalog_xmin. PostgreSQL 18 also provides safeguards including max_slot_wal_keep_size and idle_replication_slot_timeout.

A strong answer is:

Physical for HA failover; logical for selective sync and upgrades—I watch replication slot lag to prevent WAL disk fill on primary.

What interviewers are testing: Whether you distinguish logical backups from physical base backup + WAL recovery and understand that a backup is only trustworthy after restore testing.

PITR flow: restore base backup to time T0, replay WAL to target timestamp.

Tools: WAL-G, pgBackRest, cloud automated backups.

Test restores regularly—untested backups are folklore.

A strong answer is:

pg_dump for logical portability; base backup plus WAL archiving for PITR—I schedule restore drills, not just backups.

What interviewers are testing: Whether you include fencing/split-brain prevention, promotion, client rerouting, replication state, and recovery after failover—not merely "promote the standby."

Options:

Application needs connection targeting to new primary and handling brief errors during failover.

Read replicas offload read scaling; writes still go to primary (unless sharded).

A strong answer is:

HA needs automated failover plus app retry—I use managed HA or Patroni and test promote without losing replication slots blindly.

What interviewers are testing: Whether you understand role grants, RLS bypass rules, and tenant isolation patterns for multi-tenant applications.

Postgres roles can be users or groups:

CREATE ROLE app_read NOLOGIN;
GRANT CONNECT ON DATABASE shop TO app_read;
GRANT USAGE ON SCHEMA public TO app_read;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_read;

Row Level Security (RLS) filters rows per session:

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON orders
  USING (tenant_id = current_setting('app.tenant_id')::bigint);

Important: superusers and BYPASSRLS roles bypass row security, and table owners normally bypass their own policies unless ALTER TABLE ... FORCE ROW LEVEL SECURITY is used. If tenant identity is stored in a session setting, set/reset it transaction-safely so pooled connections cannot leak tenant context.

Least privilege: app role without SUPERUSER, CREATEDB, or broad DDL.

A strong answer is:

Separate roles per app with minimal grants; RLS for multi-tenant row isolation when defense in depth matters.

What interviewers are testing: whether you walk through a structured, ordered investigation for query suddenly slow after deploy — debug steps—stating impact and first checks before deep tools.

Checklist:

1. pg_stat_statements — new query shape or regressed mean time?
2. Obtain the current plan with plain EXPLAIN if executing it is risky; run EXPLAIN (ANALYZE, BUFFERS) on a safe reproduction or production only when execution impact is understood
3. Statistics — run ANALYZE after migration bulk load
4. Indexes — migration drop index accidentally?
5. Data volume — crossed threshold where plan flips
6. Locks — blocking DDL or long transaction?
7. Connection storm — pool saturation?

Narrate before diving into \d and catalogs.

A strong answer is:

I compare EXPLAIN before and after, check stats and indexes first, then locks—deploys that bulk-load without ANALYZE are a frequent culprit.

What interviewers are testing: whether you walk through a structured, ordered investigation for replica lag growing — what do you check—stating impact and first checks before deep tools.

Diagnose logical/CDC slot lag separately through pg_replication_slots; a stalled logical consumer is primarily a slot/WAL-retention issue, not physical standby replay lag.

A strong answer is:

I read physical lag in bytes and replay state on the standby, then check WAL generation and replay speed. Logical slot lag is a separate diagnosis through pg_replication_slots.

What interviewers are testing: whether you walk through a structured, ordered investigation for zero-downtime schema migration—stating impact and first checks before deep tools.

Patterns:

ADD COLUMN with a non-volatile constant default is fast on modern PostgreSQL and does not rewrite every existing row. Volatile defaults and many other ALTER TABLE operations can still require expensive rewrites or strong locks, so inspect the exact operation and lock mode. Even metadata-fast DDL can still need an ACCESS EXCLUSIVE lock briefly, so lock acquisition on a busy table matters.

Use Flyway/Liquibase with review for lock modes.

A strong answer is:

Expand-contract with concurrent index builds—I avoid blocking DDL on hot tables and backfill in batches with throttling.

What interviewers are testing: whether you match relational ACID and join workloads to Postgres and schema-flexible document access patterns to MongoDB.

See MongoDB interview questions for document-store depth.

A strong answer is:

PostgreSQL is strongest when relational constraints, joins, transactions, and SQL analytics dominate; MongoDB fits document-centric access patterns and offers integrated sharding for workloads designed around that model. PostgreSQL can scale horizontally through external/distributed solutions, but core PostgreSQL does not provide MongoDB-style built-in sharding.