Interview Q&A

Pick a topic — languages & databases — 100+ questions and answers in the center column.

Calculators Salary Emoji Website tools ← Listings

PostgreSQL

Interview questions & answers

Advanced SQL, MVCC, extensions, WAL, and tuning.

102 questions

Question 1
What is PostgreSQL?
Advanced open-source RDBMS (object-relational). Strong SQL standard support, extensible types, MVCC concurrency, rich indexing—including GiST/GIN—and popular for analytics with extensions.
Example code
```
SELECT version()
```
Question 2
MVCC in PostgreSQL?
Readers don’t block writers; old row versions kept until vacuumed—long transactions bloat tables and delay visibility.
Example code
```
VACUUM ANALYZE t
```
Question 3
VACUUM and autovacuum?
Reclaims dead tuple space and updates visibility map—tune autovacuum for high-churn tables; VACUUM FULL locks heavily.
Example code
```
SELECT relname, n_dead_tup FROM pg_stat_user_tables
```
Question 4
What is bloat?
Accumulated dead tuples increasing table/index size—monitor with pg_stat_user_tables; consider fillfactor and vacuum tuning.
Example code
```
SELECT pg_current_wal_lsn()
```
Question 5
WAL (write-ahead log)?
Durability log applied after crash—archive for PITR; synchronous_commit trades safety vs latency.
Example code
```
CHECKPOINT
```
Question 6
Checkpoints?
Periodic WAL flush points—checkpoint_timeout and max_wal_size tune IO spikes vs recovery time.
Example code
```
SHOW transaction_isolation
```
Question 7
Isolation levels?
Read committed default; repeatable read and serializable use snapshot + SSI for anomalies—serializable may retry transactions.
Example code
```
SELECT * FROM pg_locks WHERE NOT granted
```
Question 8
Lock types?
Row-level FOR UPDATE/SHARE; also relation and advisory locks—deadlocks detected and one transaction aborted.
Example code
```
CREATE SEQUENCE s
```
Question 9
Sequences and identity?
SERIAL/BIGSERIAL are sequences; GENERATED ALWAYS AS IDENTITY is SQL standard—watch ownership on table drops.
Example code
```
CREATE TABLE t (id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY)
```
Question 10
Primary key vs unique?
PK implies NOT NULL and clustered default via heap TID—unique allows NULL multiples unless constrained.
Example code
```
CREATE UNIQUE INDEX u ON t(email)
```
Question 11
EXPLAIN ANALYZE?
Runs query with timing and actual rows—BUFFERS option shows cache hits—gold standard for tuning.
Example code
```
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM t WHERE id=1
```
Question 12
Index types?
B-tree default; GIN for full text/jsonb containment; GiST for ranges/geo; BRIN for very large naturally ordered data.
Example code
```
CREATE INDEX ON t USING gin (j)
```
Question 13
Partial indexes?
CREATE INDEX … WHERE predicate—smaller and faster for filtered hot queries.
Example code
```
CREATE INDEX ON t WHERE active
```
Question 14
Expression indexes?
Index on function—must match query expression exactly.
Example code
```
CREATE INDEX ON t (lower(email))
```
Question 15
Covering indexes?
INCLUDE columns add payload without entering sort key—helps index-only scans when visibility map allows.
Example code
```
CREATE INDEX ON t (a) INCLUDE (b)
```
Question 16
JSON and JSONB?
JSONB binary with operators @>, ?, path queries—GIN indexes common; JSON preserves whitespace/text.
Example code
```
SELECT data->>'x' FROM t WHERE data @> '{"k":1}'
```
Question 17
Arrays and unnest?
First-class array types—unnest/lateral joins for normalization in queries.
Example code
```
SELECT * FROM t, unnest(arr) AS u(x)
```
Question 18
CTEs optimization?
Previously always optimization fence; now inliner can merge—test behavior per version.
Example code
```
WITH x AS (SELECT 1) SELECT * FROM x
```
Question 19
LATERAL joins?
Subquery can reference preceding FROM items—powerful top-N per group patterns.
Example code
```
SELECT * FROM t, LATERAL (SELECT * FROM u WHERE u.tid=t.id) s
```
Question 20
Window functions?
Full support—frame clauses, RANGE/ROWS—watch sort cost on large partitions.
Example code
```
SELECT sum(x) OVER (PARTITION BY g) FROM t
```
Question 21
Full-text search?
to_tsvector/to_tsquery with GIN—dictionaries and ranking ts_rank; not Elasticsearch-scale but solid.
Example code
```
SELECT to_tsvector('simple', body) @@ to_tsquery('simple', 'foo')
```
Question 22
PostGIS?
Industry-standard spatial extension—geometry types, indexes, and functions.
Example code
```
SELECT ST_DWithin(geom, ST_MakePoint(0,0), 1000)
```
Question 23
Foreign Data Wrappers?
Query remote SQL/NoSQL/HTTP sources from Postgres—pushdown varies by wrapper.
Example code
```
CREATE EXTENSION postgres_fdw
```
Question 24
Partitioning?
Declarative RANGE/LIST/HASH—partition pruning in planner; unique constraints need partition key.
Example code
```
CREATE TABLE t (...) PARTITION BY RANGE (d)
```
Question 25
Logical replication?
Publish/subscribe row changes—upgrade and selective replication; not a full physical HA story alone.
Example code
```
CREATE PUBLICATION p FOR ALL TABLES
```
Question 26
Streaming replication?
Physical standby applies WAL—sync/async replicas; hot standby for reads.
Example code
```
SELECT pg_is_in_recovery()
```
Question 27
Replication slots?
Retain WAL for consumers—prevent deletion until consumed; monitor disk if consumer stalls.
Example code
```
SELECT * FROM pg_replication_slots
```
Question 28
Roles and grants?
ROLE LOGIN inheritance; RLS further filters rows—defense in depth with app user separation.
Example code
```
GRANT SELECT ON t TO app
```
Question 29
Row-Level Security?
CREATE POLICY per table—must enable RLS; superuser bypass—test as app role.
Example code
```
ALTER TABLE t ENABLE ROW LEVEL SECURITY
```
Question 30
Extensions?
CREATE EXTENSION loads contrib modules—versioned in share/extension; some need superuser.
Example code
```
CREATE EXTENSION IF NOT EXISTS citext
```
Question 31
Listen/notify?
Pub/sub channel notifications—not durable; good for cache invalidation signals.
Example code
```
LISTEN ch; NOTIFY ch, 'payload'
```
Question 32
Advisory locks?
Application-level mutexes pg_advisory_lock—avoid deadlocks with consistent lock order.
Example code
```
SELECT pg_advisory_lock(12345)
```
Question 33
COPY vs INSERT?
COPY fastest bulk load—FORMAT csv FREEZE requires care; triggers still fire unless disabled.
Example code
```
COPY t FROM STDIN WITH (FORMAT csv)
```
Question 34
Parallel query?
Gather nodes split scans/aggregates—controlled by max_parallel_workers and cost thresholds.
Example code
```
SET max_parallel_workers_per_gather = 4
```
Question 35
Connection pooling?
PgBouncer transaction vs session pooling—prepared statements and temp tables have mode constraints.
Example code
```
SET jit = off
```
Question 36
pg_stat_statements?
Normalized query stats extension—find regressions and hotspots; requires shared_preload_libraries.
Example code
```
-- PgBouncer: session vs transaction pooling
```
Question 37
Base backup and PITR?
pg_basebackup + WAL archive—restore to timestamp with recovery_target.
Example code
```
SELECT * FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 5
```
Question 38
Upgrade strategies?
pg_upgrade in-place, logical dump/restore, or replication cutover—choose by size and extensions.
Example code
```
pg_basebackup -D /backup -Fp -Xs -P
```
Question 39
What is toast storage (PostgreSQL)?
Out-of-line large values—transparent; extreme wide rows have performance implications.
Example code
```
-- PostgreSQL: toast storage
SELECT 1
```
Question 40
What is fillfactor (PostgreSQL)?
Leave page free space for HOT updates—tune on append-mostly vs update-heavy tables.
Example code
```
-- PostgreSQL: fillfactor
SELECT 1
```
Question 41
What is HOT updates (PostgreSQL)?
Heap-only tuple updates when indexed columns unchanged—reduces index churn.
Example code
```
-- PostgreSQL: HOT updates
SELECT 1
```
Question 42
What is Visibility map (PostgreSQL)?
Helps index-only scans and vacuum—corruption rare but serious.
Example code
```
-- PostgreSQL: Visibility map
SELECT 1
```
Question 43
What is Free space map (PostgreSQL)?
Tracks page space for inserts—vacuum maintains.
Example code
```
-- PostgreSQL: Free space map
SELECT 1
```
Question 44
What is Multi-version snapshot limits (PostgreSQL)?
Very old xmin/xmax horizons block vacuum—watch long transactions and replication slots.
Example code
```
-- PostgreSQL: Multi-version snapshot limits
SELECT 1
```
Question 45
What is Statement timeout (PostgreSQL)?
Cancel runaway queries at server or role level—pair with pool timeouts.
Example code
```
-- PostgreSQL: Statement timeout
SELECT 1
```
Question 46
What is Lock timeout (PostgreSQL)?
Fail fast on lock wait—better UX than indefinite hangs.
Example code
```
-- PostgreSQL: Lock timeout
SELECT 1
```
Question 47
What is deadlock_timeout (PostgreSQL)?
How long before deadlock check runs—usually small.
Example code
```
-- PostgreSQL: deadlock_timeout
SELECT 1
```
Question 48
What is temp_file_limit (PostgreSQL)?
Cap disk sorts/hash spills—prevent runaway disk from one query.
Example code
```
-- PostgreSQL: temp_file_limit
SELECT 1
```
Question 49
What is work_mem (PostgreSQL)?
Per-sort/hash operator memory—high global * concurrency can OOM.
Example code
```
-- PostgreSQL: work_mem
SELECT 1
```
Question 50
What is maintenance_work_mem (PostgreSQL)?
VACUUM CREATE INDEX memory—raise for index builds carefully.
Example code
```
-- PostgreSQL: maintenance_work_mem
SELECT 1
```
Question 51
What is effective_cache_size (PostgreSQL)?
Planner hint about OS cache—not reserved memory.
Example code
```
-- PostgreSQL: effective_cache_size
SELECT 1
```
Question 52
What is random_page_cost vs seq_page_cost (PostgreSQL)?
Tune for SSD vs HDD—SSDs often lower random cost.
Example code
```
-- PostgreSQL: random_page_cost vs seq_page_cost
SELECT 1
```
Question 53
What is default_statistics_target (PostgreSQL)?
More samples for skewed columns—increase on important predicates.
Example code
```
-- PostgreSQL: default_statistics_target
SELECT 1
```
Question 54
What is Extended statistics (PostgreSQL)?
CREATE STATISTICS for correlated columns—helps planner on multi-column filters.
Example code
```
-- PostgreSQL: Extended statistics
SELECT 1
```
Question 55
What is pg_trgm extension (PostgreSQL)?
Trigram similarity and GIST/GIN ops for LIKE patterns—better than naive btree for contains.
Example code
```
-- PostgreSQL: pg_trgm extension
SELECT 1
```
Question 56
What is btree_gin / btree_gist (PostgreSQL)?
Hybrid opclasses—combined conditions on mixed types.
Example code
```
-- PostgreSQL: btree_gin / btree_gist
SELECT 1
```
Question 57
What is citext type (PostgreSQL)?
Case-insensitive text—simpler than lower() indexes for emails.
Example code
```
-- PostgreSQL: citext type
SELECT 1
```
Question 58
What is uuid-ossp / pgcrypto (PostgreSQL)?
UUID generation—prefer gen_random_uuid() in core when available.
Example code
```
-- PostgreSQL: uuid-ossp / pgcrypto
SELECT 1
```
Question 59
What is hstore vs jsonb (PostgreSQL)?
hstore flat string map—jsonb richer; choose by access patterns.
Example code
```
-- PostgreSQL: hstore vs jsonb
SELECT 1
```
Question 60
What is Range types (PostgreSQL)?
tstzrange, int4range with GiST indexes—scheduling and exclusion constraints.
Example code
```
-- PostgreSQL: Range types
SELECT 1
```
Question 61
What is Exclusion constraints (PostgreSQL)?
Prevent overlapping bookings—EXCLUDE USING gist.
Example code
```
-- PostgreSQL: Exclusion constraints
SELECT 1
```
Question 62
What is Rules vs triggers (PostgreSQL)?
Rules rewrite queries pre-plan—triggers per row; prefer triggers for clarity.
Example code
```
-- PostgreSQL: Rules vs triggers
SELECT 1
```
Question 63
What is Event triggers (PostgreSQL)?
DDL hooks—migration guardrails and auditing.
Example code
```
-- PostgreSQL: Event triggers
SELECT 1
```
Question 64
What is Logical decoding (PostgreSQL)?
Change capture for CDC tools—wal2json, pgoutput plugin.
Example code
```
-- PostgreSQL: Logical decoding
SELECT 1
```
Question 65
What is pg_repack (PostgreSQL)?
Online bloat cleanup without heavy locks—alternative to CLUSTER.
Example code
```
-- PostgreSQL: pg_repack
SELECT 1
```
Question 66
What is pg_cron (PostgreSQL)?
Scheduled jobs inside DB—convenient but operational discipline needed.
Example code
```
-- PostgreSQL: pg_cron
SELECT 1
```
Question 67
What is TimescaleDB (PostgreSQL)?
Time-series hypertable extension—chunking and retention policies.
Example code
```
-- PostgreSQL: TimescaleDB
SELECT 1
```
Question 68
What is Citus (PostgreSQL)?
Distributed Postgres—sharding and parallel queries across nodes.
Example code
```
-- PostgreSQL: Citus
SELECT 1
```
Question 69
What is pgBouncer auth (PostgreSQL)?
auth_query pattern for dynamic users—complex but scalable.
Example code
```
-- PostgreSQL: pgBouncer auth
SELECT 1
```
Question 70
What is SCRAM auth (PostgreSQL)?
Default password auth—ensure clients support; md5 legacy.
Example code
```
-- PostgreSQL: SCRAM auth
SELECT 1
```
Question 71
What is SSL modes (PostgreSQL)?
verify-full for prod—prevent MITM to DB.
Example code
```
-- PostgreSQL: SSL modes
SELECT 1
```
Question 72
What is pg_hba.conf (PostgreSQL)?
Host-based auth rules—order matters; reload not restart for many changes.
Example code
```
-- PostgreSQL: pg_hba.conf
SELECT 1
```
Question 73
What is pg_ident.conf (PostgreSQL)?
External name mapping—Kerberos/SSPI integrations.
Example code
```
-- PostgreSQL: pg_ident.conf
SELECT 1
```
Question 74
What is Tablespaces (PostgreSQL)?
Place objects on different disks—backup/restore paths must include all.
Example code
```
-- PostgreSQL: Tablespaces
SELECT 1
```
Question 75
What is Unlogged tables (PostgreSQL)?
Fast, not crash-safe—staging only.
Example code
```
-- PostgreSQL: Unlogged tables
SELECT 1
```
Question 76
What is Temporary tables (PostgreSQL)?
Session-scoped; ON COMMIT DROP—watch bloat in long sessions.
Example code
```
-- PostgreSQL: Temporary tables
SELECT 1
```
Question 77
What is Materialized views (PostgreSQL)?
REFRESH CONCURRENTLY needs unique index—snapshot reporting pattern.
Example code
```
-- PostgreSQL: Materialized views
SELECT 1
```
Question 78
What is Partition-wise join (PostgreSQL)?
Planner joins matching partitioned tables per partition—big wins when stats good.
Example code
```
-- PostgreSQL: Partition-wise join
SELECT 1
```
Question 79
What is Declarative partitioning pitfalls (PostgreSQL)?
FK referencing partitioned table limitations improved over versions—verify docs.
Example code
```
-- PostgreSQL: Declarative partitioning pitfalls
SELECT 1
```
Question 80
What is BRIN index use case (PostgreSQL)?
Huge time-series append-only—tiny index size with correlation.
Example code
```
-- PostgreSQL: BRIN index use case
SELECT 1
```
Question 81
What is Hash indexes pre-10 (PostgreSQL)?
Not WAL-logged historically—now safer; still niche vs btree.
Example code
```
-- PostgreSQL: Hash indexes pre-10
SELECT 1
```
Question 82
What is GIN pending list (PostgreSQL)?
Fast insert deferred merge—gin_pending_list_limit tuning.
Example code
```
-- PostgreSQL: GIN pending list
SELECT 1
```
Question 83
What is Autovacuum per table (PostgreSQL)?
storage_parameters—aggressive settings for append-heavy fact tables.
Example code
```
-- PostgreSQL: Autovacuum per table
SELECT 1
```
Question 84
What is pg_dump formats (PostgreSQL)?
Custom -Fc parallel restore friendly—directory format for parallel pg_restore.
Example code
```
-- PostgreSQL: pg_dump formats
SELECT 1
```
Question 85
What is pg_upgrade link mode (PostgreSQL)?
Hardlinks fast upgrade—cannot revert easily; have backups.
Example code
```
-- PostgreSQL: pg_upgrade link mode
SELECT 1
```
Question 86
What is repmgr / Patroni (PostgreSQL)?
HA failover managers—etcd/consul coordination common.
Example code
```
-- PostgreSQL: repmgr / Patroni
SELECT 1
```
Question 87
What is Stolon (PostgreSQL)?
Alternative HA controller—Kubernetes friendly patterns.
Example code
```
-- PostgreSQL: Stolon
SELECT 1
```
Question 88
What is pgBackRest (PostgreSQL)?
Backup tool with parallel, encryption, S3—popular in enterprise Postgres ops.
Example code
```
-- PostgreSQL: pgBackRest
SELECT 1
```
Question 89
What is WAL archiving to cloud (PostgreSQL)?
archive_command to object storage—lifecycle policies for cost.
Example code
```
-- PostgreSQL: WAL archiving to cloud
SELECT 1
```
Question 90
What is Logical replication limitations (PostgreSQL)?
DDL not replicated—sequences need manual handling; conflict strategies on subscribers.
Example code
```
-- PostgreSQL: Logical replication limitations
SELECT 1
```
Question 91
What is JIT compilation (PostgreSQL)?
Expression JIT for analytics—jit=off in postgresql.conf for predictable OLTP latency.
Example code
```
-- PostgreSQL: JIT compilation
SELECT 1
```
Question 92
What is pg_waldump (PostgreSQL)?
Inspect WAL records for deep debugging—niche DBA tool.
Example code
```
-- PostgreSQL: pg_waldump
SELECT 1
```
Question 93
What is checksums on data pages (PostgreSQL)?
initdb --data-checksums detects corruption—slight overhead; enable on new clusters.
Example code
```
-- PostgreSQL: checksums on data pages
SELECT 1
```
Question 94
What is pg_verify_checksums (PostgreSQL)?
Offline verification tool—part of data integrity drills.
Example code
```
-- PostgreSQL: pg_verify_checksums
SELECT 1
```
Question 95
What is amcheck extension (PostgreSQL)?
Verify B-tree structural integrity—find silent corruption early.
Example code
```
-- PostgreSQL: amcheck extension
SELECT 1
```
Question 96
What is pg_surgery extension (PostgreSQL)?
Emergency repair—last resort with backups.
Example code
```
-- PostgreSQL: pg_surgery extension
SELECT 1
```
Question 97
What is pg_stat_io (Postgres 16+) (PostgreSQL)?
IO timing visibility—identifies backend vs vacuum IO.
Example code
```
-- PostgreSQL: pg_stat_io (Postgres 16+)
SELECT 1
```
Question 98
What is subscription (logical) (PostgreSQL)?
CREATE SUBSCRIPTION on replica—publication name and copy_data options matter.
Example code
```
-- PostgreSQL: subscription (logical)
SELECT 1
```
Question 99
What is publication row filter (PostgreSQL)?
Selective logical replication—WHERE on published tables.
Example code
```
-- PostgreSQL: publication row filter
SELECT 1
```
Question 100
What is slot replication origins (PostgreSQL)?
Track upstream for bidirectional setups—conflict avoidance is hard.
Example code
```
-- PostgreSQL: slot replication origins
SELECT 1
```
Question 101
What is pg_stat_progress_* (PostgreSQL)?
Progress bars for VACUUM, CREATE INDEX, CLUSTER—estimate ETA.
Example code
```
-- PostgreSQL: pg_stat_progress_*
SELECT 1
```
Question 102
What is wal_buffers (PostgreSQL)?
Shared-memory buffers for WAL before fsync—usually auto-tuned; adjust only with measurement.
Example code
```
-- PostgreSQL: wal_buffers
SELECT 1
```

What is PostgreSQL?

MVCC in PostgreSQL?

VACUUM and autovacuum?

What is bloat?

WAL (write-ahead log)?

Checkpoints?

Isolation levels?

Lock types?

Sequences and identity?

Primary key vs unique?

EXPLAIN ANALYZE?

Index types?

Partial indexes?

Expression indexes?

Covering indexes?

JSON and JSONB?

Arrays and unnest?

CTEs optimization?

LATERAL joins?

Window functions?

Full-text search?

PostGIS?

Foreign Data Wrappers?

Partitioning?

Logical replication?

Streaming replication?

Replication slots?

Roles and grants?

Row-Level Security?

Extensions?

Listen/notify?

Advisory locks?

COPY vs INSERT?

Parallel query?

Connection pooling?

pg_stat_statements?

Base backup and PITR?

Upgrade strategies?

What is toast storage (PostgreSQL)?

What is fillfactor (PostgreSQL)?

What is HOT updates (PostgreSQL)?

What is Visibility map (PostgreSQL)?

What is Free space map (PostgreSQL)?

What is Multi-version snapshot limits (PostgreSQL)?

What is Statement timeout (PostgreSQL)?

What is Lock timeout (PostgreSQL)?

What is deadlock_timeout (PostgreSQL)?

What is temp_file_limit (PostgreSQL)?

What is work_mem (PostgreSQL)?

What is maintenance_work_mem (PostgreSQL)?

What is effective_cache_size (PostgreSQL)?

What is random_page_cost vs seq_page_cost (PostgreSQL)?

What is default_statistics_target (PostgreSQL)?

What is Extended statistics (PostgreSQL)?

What is pg_trgm extension (PostgreSQL)?

What is btree_gin / btree_gist (PostgreSQL)?

What is citext type (PostgreSQL)?

What is uuid-ossp / pgcrypto (PostgreSQL)?

What is hstore vs jsonb (PostgreSQL)?

What is Range types (PostgreSQL)?

What is Exclusion constraints (PostgreSQL)?

What is Rules vs triggers (PostgreSQL)?

What is Event triggers (PostgreSQL)?

What is Logical decoding (PostgreSQL)?

What is pg_repack (PostgreSQL)?

What is pg_cron (PostgreSQL)?

What is TimescaleDB (PostgreSQL)?

What is Citus (PostgreSQL)?

What is pgBouncer auth (PostgreSQL)?

What is SCRAM auth (PostgreSQL)?

What is SSL modes (PostgreSQL)?

What is pg_hba.conf (PostgreSQL)?

What is pg_ident.conf (PostgreSQL)?

What is Tablespaces (PostgreSQL)?

What is Unlogged tables (PostgreSQL)?

What is Temporary tables (PostgreSQL)?

What is Materialized views (PostgreSQL)?

What is Partition-wise join (PostgreSQL)?

What is Declarative partitioning pitfalls (PostgreSQL)?

What is BRIN index use case (PostgreSQL)?