Question 1

What is MVCC in PostgreSQL?

Accepted Answer

MVCC (Multi-Version Concurrency Control) is PostgreSQL's concurrency mechanism that allows multiple transactions to access the same data simultaneously by maintaining multiple versions of rows. Readers don't block writers and writers don't block readers, enabling high concurrency without traditional locking overhead.

Question 2

What is the difference between JSON and JSONB in PostgreSQL?

Accepted Answer

JSON stores data as plain text exactly as input, while JSONB stores data in a decomposed binary format. JSONB is faster for processing, supports indexing with GIN indexes, and is recommended for most use cases, though JSON preserves exact formatting and key order.

Question 3

What is WAL (Write-Ahead Logging) in PostgreSQL?

Accepted Answer

WAL is a technique where changes are first written to a sequential log file before being applied to actual data files. This log enables crash recovery, point-in-time recovery (PITR), and replication by allowing standbys to replay the same WAL records to reconstruct the database state.

Question 4

What types of indexes does PostgreSQL support?

Accepted Answer

PostgreSQL supports five main index types: B-tree (default, for most comparisons), Hash (equality only), GiST (generalized search trees for spatial data), GIN (inverted indexes for arrays/JSONB), and BRIN (block range indexes for very large ordered tables). Each is optimized for different access patterns and data types.

Question 5

What is the difference between EXPLAIN and EXPLAIN ANALYZE?

Accepted Answer

EXPLAIN shows the query planner's estimated execution plan without running the query, while EXPLAIN ANALYZE actually executes the query and shows both planned estimates and actual runtime statistics. EXPLAIN ANALYZE is more accurate but has side effects since it executes the query, including data modifications.

Question 6

What is VACUUM in PostgreSQL and why is it necessary?

Accepted Answer

VACUUM reclaims storage space from dead tuples created by PostgreSQL's MVCC architecture and updates query planner statistics. It's necessary because UPDATE and DELETE operations don't immediately remove old row versions, leading to table bloat and degraded performance if not cleaned up regularly.

Question 7

What is the difference between VACUUM and VACUUM FULL?

Accepted Answer

VACUUM reclaims space by marking dead tuples as reusable but doesn't shrink the table file and runs concurrently with normal operations. VACUUM FULL completely rewrites the table to remove all dead space and returns disk space to the OS, but requires an exclusive lock and is much slower.

Question 8

What is table partitioning in PostgreSQL?

Accepted Answer

Table partitioning splits a large table into smaller physical pieces called partitions while maintaining a single logical table. It improves query performance through partition pruning, speeds up maintenance operations, and makes it easier to manage large datasets by enabling partition-specific operations.

Question 9

What are the different partitioning strategies in PostgreSQL?

Accepted Answer

PostgreSQL supports three strategies: Range partitioning (divides data by value ranges, ideal for dates), List partitioning (assigns data by discrete values, perfect for categories), and Hash partitioning (distributes data evenly using a hash function, useful for load balancing).

Question 10

How does the PostgreSQL query planner work?

Accepted Answer

The query planner analyzes SQL queries and generates an optimal execution plan by estimating costs for different strategies. It uses table statistics from ANALYZE, considers indexes and cost parameters, and chooses the most efficient combination of join methods, scan types, and execution order to minimize query execution time.

Question 11

What is the difference between sequential scan, index scan, and bitmap scan?

Accepted Answer

Sequential scan reads all table rows in order (efficient for small tables or large result sets). Index scan uses an index to find rows individually (efficient for small result sets). Bitmap scan builds an in-memory bitmap from indexes, then fetches rows in physical order (efficient for moderate result sets, 1-10% of table).

Question 12

What is a BRIN index and when should you use it?

Accepted Answer

BRIN (Block Range Index) stores min/max values for groups of table blocks instead of indexing individual rows, resulting in tiny indexes often 1000x smaller than B-tree. It's most effective for very large tables with natural physical ordering, like time-series data or append-only logs.

Question 13

What is transaction ID wraparound and how do you prevent it?

Accepted Answer

Transaction ID wraparound occurs when PostgreSQL's 32-bit transaction ID counter reaches its maximum and wraps around, potentially making old data invisible. It's prevented by regular VACUUM operations that freeze old transaction IDs before reaching the wraparound threshold (typically 200 million transactions).

Question 14

What are the main background processes in PostgreSQL?

Accepted Answer

Main background processes include: checkpointer (writes dirty buffers to disk at checkpoints), background writer (continuously writes dirty buffers to reduce I/O spikes), WAL writer (flushes write-ahead log buffers for durability), and autovacuum launcher/workers (reclaim dead tuple space and update statistics).

Question 15

What is the PostgreSQL memory architecture?

Accepted Answer

PostgreSQL memory is divided into shared memory and per-process memory. Shared_buffers (typically 25% of RAM) caches table and index data for all processes. Work_mem (default 4MB) is per-operation memory for sorts and joins. Maintenance_work_mem (64MB+) is for VACUUM, CREATE INDEX, and maintenance operations.

PostgreSQL Architecture(3 questions)

Explain the PostgreSQL process architecture (postmaster, backend processes, background workers).

What are the main background processes in PostgreSQL (checkpointer, bgwriter, walwriter, autovacuum)?

Explain the PostgreSQL memory architecture (shared_buffers, work_mem, maintenance_work_mem).

JSON and Full-Text Search(4 questions)

What is the difference between JSON and JSONB in PostgreSQL?

How do you index JSONB columns?

How does full-text search work in PostgreSQL?

What is a tsvector and tsquery?

Security(3 questions)

How does PostgreSQL authentication work (pg_hba.conf)?

How do you implement row-level security (RLS)?

What are the security best practices for PostgreSQL in production?

MVCC and Concurrency Control(5 questions)

Explain MVCC (Multi-Version Concurrency Control) in PostgreSQL.

What is transaction ID wraparound and how do you prevent it?

Explain the different transaction isolation levels in PostgreSQL.

How do you detect and resolve deadlocks in PostgreSQL?

Explain the different lock types in PostgreSQL (row-level, table-level, advisory locks).

Query Planning and Optimization(6 questions)

How does the PostgreSQL query planner work?

What is the difference between sequential scan, index scan, and bitmap scan?

What is a query execution plan and how do you read EXPLAIN output?

What is the difference between EXPLAIN and EXPLAIN ANALYZE?

How do you use pg_stat_statements to identify slow queries?

What is the difference between a CTE and a subquery in terms of optimization?

Indexing(6 questions)

What types of indexes does PostgreSQL support (B-tree, Hash, GiST, GIN, BRIN)?

What is a BRIN index and when is it most effective?

How do you create a partial index and when would you use one?

How do you create a covering index with INCLUDE?

What is index bloat and how do you detect and fix it?

What is a multi-column index and how does column order affect queries?

Partitioning(4 questions)

What is table partitioning in PostgreSQL?

What are the different partitioning strategies (range, list, hash)?

What is partition pruning and how does it improve performance?

How do you convert an existing table to a partitioned table?

VACUUM and Maintenance(5 questions)

What is VACUUM in PostgreSQL and why is it necessary?

What is the difference between VACUUM and VACUUM FULL?

How does autovacuum work?

How do you tune autovacuum parameters for high-write tables?

What is table bloat and how do you identify it?

Replication(5 questions)

Explain streaming replication in PostgreSQL.

What is the difference between synchronous and asynchronous replication?

What is WAL (Write-Ahead Logging) and how does it enable replication?

What is logical replication and how does it differ from physical replication?

How do you monitor replication status and lag?

High Availability and Backup(5 questions)

What are the common high availability architectures for PostgreSQL?

What is Patroni and how does it provide HA?

What is the difference between logical and physical backups?

Explain Point-in-Time Recovery (PITR).

What is PgBouncer and why is connection pooling important?

Performance Tuning(4 questions)

What are the most important PostgreSQL configuration parameters for performance?

How do you size shared_buffers?

What is the difference between session, transaction, and statement pooling modes?

How do you optimize for OLTP vs OLAP workloads?