PostgreSQL Interview Questions (Top 100 Questions with Answers)

Master PostgreSQL Interview Questions with production-oriented questions covering PostgreSQL architecture, MVCC, JSONB, indexing, partitioning, replication, VACUUM, transactions, performance tuning, and real-world production scenarios.

Introduction

PostgreSQL is one of the world's most powerful open-source relational databases.

It is widely used in

Financial Services
Banking
Insurance
SaaS Platforms
Cloud Native Applications
Government Systems
Healthcare

Major companies using PostgreSQL include

Apple
Microsoft
AWS
Stripe
Reddit
Instagram
IBM

This guide contains the Top 100 PostgreSQL Interview Questions frequently asked in Backend, Java, DevOps, Cloud, and Database interviews.

PostgreSQL Interview Roadmap

PostgreSQL Basics
        │
        ▼
Architecture
        │
        ▼
Storage
        │
        ▼
Indexes
        │
        ▼
JSONB
        │
        ▼
Transactions
        │
        ▼
MVCC
        │
        ▼
VACUUM
        │
        ▼
Replication
        │
        ▼
Performance

PostgreSQL Basics

1. What is PostgreSQL?

An advanced open-source relational database supporting SQL and advanced features like JSONB, MVCC, Partitioning, and Replication.

2. Why is PostgreSQL popular?

Open Source
ACID Compliant
High Performance
MVCC
JSON Support
Extensions
Excellent Reliability

3. PostgreSQL vs MySQL?

PostgreSQL	MySQL
Advanced SQL	Simpler
Better JSONB	JSON
MVCC	InnoDB MVCC
More Extensible	Easier Setup

4. What are PostgreSQL features?

MVCC
JSONB
CTE
Window Functions
Partitioning
Replication
Extensions

5. Which companies use PostgreSQL?

Instagram
Reddit
Apple
Microsoft
AWS
IBM

Architecture

6. Explain PostgreSQL Architecture.

Major components

Client
Server Process
Shared Buffers
WAL
Background Processes
Data Files

7. What is Postmaster?

Main PostgreSQL server process responsible for accepting new client connections and starting backend processes.

8. What is a Backend Process?

Dedicated server process handling one client connection.

9. What is Shared Buffer?

Memory area caching database pages.

10. What is WAL?

Write Ahead Logging.

Changes are written to WAL before data files.

Storage

11. What is a Page?

Default storage unit.

Usually

8 KB.

12. What is a Tuple?

Represents one table row.

13. Heap Table?

Default table storage format.

14. TOAST?

Stores oversized column values outside the main table.

15. FSM?

Free Space Map.

Tracks free space inside pages.

16. Visibility Map?

Tracks pages containing only visible tuples to optimize VACUUM and index-only scans.

17. What is OID?

Object Identifier.

Unique identifier for database objects (not used by default for user tables).

18. What are System Catalogs?

Internal metadata tables describing database objects.

19. pg_class?

Stores metadata about relations such as tables and indexes.

20. pg_stat_activity?

Displays active sessions.

JSONB

21. What is JSONB?

Binary JSON format supporting indexing and efficient querying.

22. JSON vs JSONB?

JSON	JSONB
Text	Binary
Slower	Faster
No Index	Supports Index

23. JSONB Advantages?

Fast Queries
GIN Index
Compression
Efficient Storage

24. GIN Index?

Optimized index for JSONB, arrays, and full-text search.

25. Query JSONB Example?

SELECT *
FROM employee
WHERE details->>'city'='Dallas';

Indexes

26. Types of Indexes?

B-Tree
Hash
GIN
GiST
BRIN
SP-GiST

27. Default Index?

B-Tree.

28. What is BRIN?

Block Range Index.

Suitable for very large sequential tables.

29. What is GiST?

Generalized Search Tree used for geometric data, ranges, and full-text search.

30. Index Only Scan?

Uses only the index without reading the table when visibility requirements are satisfied.

Transactions

31. Does PostgreSQL support ACID?

Yes.

Fully ACID compliant.

32. Default Isolation Level?

Read Committed.

33. Serializable?

Supported using Serializable Snapshot Isolation (SSI).

34. Savepoints?

Supported.

35. Two-Phase Commit?

Supported using PREPARE TRANSACTION and COMMIT PREPARED.

MVCC

36. What is MVCC?

Multi-Version Concurrency Control.

37. Why MVCC?

Readers don't block writers.

38. xmin?

Transaction ID that inserted the row.

39. xmax?

Transaction ID that deleted or updated the row.

40. Snapshot?

Consistent view of committed data.

VACUUM

41. Why VACUUM?

Removes dead tuples.

42. Autovacuum?

Automatic cleanup process.

43. VACUUM FULL?

Reclaims storage by rewriting the table.

Requires an exclusive lock.

44. ANALYZE?

Updates optimizer statistics.

45. Table Bloat?

Unused space caused by dead tuples.

Replication

46. Streaming Replication?

Transfers WAL continuously.

47. Logical Replication?

Replicates SQL changes.

48. Physical Replication?

Replicates entire database blocks through WAL.

49. Hot Standby?

Allows read-only queries on standby servers.

50. Replication Slot?

Prevents WAL removal before replicas consume it.

Performance

51. EXPLAIN?

Shows execution plan.

52. EXPLAIN ANALYZE?

Executes query and reports actual runtime statistics.

53. pg_stat_statements?

Tracks SQL performance statistics.

54. Slow Query Analysis?

EXPLAIN ANALYZE
pg_stat_statements
Logs

55. Sequential Scan?

Reads entire table.

56. Index Scan?

Uses an index.

57. Bitmap Index Scan?

Combines index lookups with efficient table access.

58. Parallel Query?

Multiple workers execute one query.

59. Partitioning?

Splits large tables.

60. Common Partition Types?

Range
List
Hash

SQL

61. Window Functions?

Supported.

62. Recursive CTE?

Supported.

63. Materialized Views?

Supported.

64. UPSERT?

INSERT ...

ON CONFLICT

65. RETURNING Clause?

Returns affected rows after INSERT, UPDATE, or DELETE.

Extensions

66. What are Extensions?

Add additional functionality.

67. Popular Extensions?

PostGIS
pg_stat_statements
pgcrypto
uuid-ossp

68. PostGIS?

Geospatial extension.

69. pgcrypto?

Encryption functions.

70. UUID Extension?

Generates UUID values.

Backup & Recovery

71. pg_dump?

Logical backup.

72. pg_restore?

Restore logical backup.

73. PITR?

Point-in-Time Recovery.

74. Base Backup?

Starting point for recovery.

75. WAL Archiving?

Stores WAL files for recovery.

Security

76. pg_hba.conf?

Client authentication configuration.

77. postgresql.conf?

Main server configuration file.

78. Roles?

Manage users and permissions.

79. GRANT?

Assign permissions.

80. REVOKE?

Remove permissions.

Production Scenarios

81. High CPU?

Check

Slow Queries
Missing Indexes
EXPLAIN ANALYZE

82. High Disk Usage?

Check

WAL
Table Bloat
VACUUM

83. Replication Lag?

Check

Network
WAL Generation
Replica Status

84. Autovacuum not keeping up?

Tune

autovacuum_workers
autovacuum_vacuum_cost_limit
autovacuum_naptime

85. Slow JSONB Query?

Add GIN Index.

86. Table Bloat?

Run

VACUUM

VACUUM FULL.

87. Long Running Queries?

Check

pg_stat_activity.

88. Lock Contention?

Check

pg_locks.

89. Database Connection Issues?

Review

max_connections
Connection Pool
pgBouncer

90. Failover Strategy?

Streaming Replication

Standby

Promotion.

Senior-Level Questions

91. PostgreSQL vs Oracle?

Oracle offers extensive enterprise tooling and commercial support, while PostgreSQL provides comparable core database capabilities with an open-source ecosystem.

92. PostgreSQL vs MongoDB?

Relational

Document Database.

93. PostgreSQL vs Cassandra?

ACID

Eventual Consistency.

94. PostgreSQL vs DynamoDB?

SQL

Managed NoSQL.

95. Best PostgreSQL Monitoring Tools?

pg_stat_statements
pgAdmin
Prometheus
Grafana

96. Explain WAL Internals.

WAL stores transaction changes before writing modified pages to data files, enabling crash recovery and replication.

97. Explain MVCC Internals.

Uses

xmin

xmax

Snapshots

Visibility Rules.

98. Explain VACUUM Internals.

Removes dead tuples and updates visibility information without rewriting the table (unless using VACUUM FULL).

99. Explain Streaming Replication Internals.

Primary sends WAL records.

Standby replays WAL continuously.

100. PostgreSQL Performance Optimization Checklist?

Proper Indexes
EXPLAIN ANALYZE
VACUUM
ANALYZE
Partitioning
Connection Pooling
Replication
Query Optimization

PostgreSQL Production Workflow

Application
      │
      ▼
Connection Pool
      │
      ▼
PostgreSQL
      │
      ▼
Shared Buffers
      │
      ▼
WAL
      │
      ▼
Data Files
      │
      ▼
Streaming Replica

Quick Revision

Area	Focus
Architecture	Postmaster, Backend
Storage	Pages, Tuples, TOAST
JSONB	GIN Index
Transactions	MVCC
Performance	EXPLAIN ANALYZE
Cleanup	VACUUM
Replication	Streaming
Recovery	WAL
Monitoring	pg_stat_activity
Extensions	PostGIS

Interview Tips

For PostgreSQL interviews

Explain MVCC using row versioning and transaction snapshots.
Mention WAL when discussing durability and replication.
Use EXPLAIN ANALYZE for query optimization discussions.
Explain the purpose of VACUUM and Autovacuum.
Understand GIN indexes for JSONB queries.
Be familiar with streaming replication and Point-in-Time Recovery.
Discuss monitoring using pg_stat_activity, pg_locks, and pg_stat_statements.

Summary

PostgreSQL is a feature-rich, enterprise-grade relational database widely used for modern cloud-native applications. A strong PostgreSQL interview requires understanding architecture, MVCC, WAL, JSONB, indexes, VACUUM, replication, performance tuning, and production troubleshooting.

Mastering these 100 PostgreSQL interview questions prepares you for Backend Developer, Database Engineer, DevOps Engineer, Senior Java Developer, Solution Architect, and PostgreSQL DBA interviews.