PostgreSQL is a powerful open-source relational database known for reliability, standards compliance and extensibility. It runs everything from small web apps to large analytics and geospatial systems. These PostgreSQL interview questions and answers cover architecture, SQL features, performance, replication, backup and security.
Basics
1. What is PostgreSQL?
PostgreSQL is an open-source object-relational database. It grew from the POSTGRES project at the University of California, Berkeley, and adopted the PostgreSQL name in 1996. The PostgreSQL Global Development Group releases a new major version every year.
2. Why choose PostgreSQL?
- Strong standards compliance and full ACID transactions.
- Rich data types, including JSONB, arrays, ranges and UUID.
- Extensions such as PostGIS for geospatial data and pgvector for AI embeddings.
- Advanced indexing and a mature optimiser.
- A permissive open-source licence.
3. Describe the process architecture.
PostgreSQL uses a process per connection. A main postmaster process accepts connections and starts a backend process for each client. Background processes handle writing, checkpoints, WAL, autovacuum and replication. Because connections are relatively heavy, busy systems use a pooler such as PgBouncer.
MVCC and maintenance
4. What is MVCC?
Multi-Version Concurrency Control keeps several versions of a row, so readers do not block writers and writers do not block readers. Each transaction sees a consistent snapshot of the data.
5. Why does PostgreSQL need VACUUM?
Updates and deletes leave old row versions (“dead tuples”). VACUUM cleans them up and reclaims space for reuse. Autovacuum runs this automatically. If vacuuming falls behind, tables bloat and, in extreme cases, transaction ID wraparound can stop writes.
6. DELETE vs TRUNCATE vs DROP TABLE?
DELETE removes chosen rows and can be rolled back. TRUNCATE removes all rows quickly. DROP TABLE removes the table and its structure. Unlike many databases, PostgreSQL can roll back TRUNCATE and DROP inside a transaction.
SQL features
7. How do you get the first 10 rows?
SELECT * FROM orders ORDER BY created_at DESC LIMIT 10;The SQL-standard FETCH FIRST 10 ROWS ONLY also works.
8. JSON vs JSONB?
JSON stores text exactly as entered. JSONB stores a binary, parsed form that is faster to query and can be indexed with GIN indexes. In most cases, use JSONB.
9. Which index types exist?
B-tree (default), Hash, GIN (for JSONB, arrays and full-text search), GiST and SP-GiST (for geometric and range data) and BRIN (for very large, naturally ordered tables).
10. What is a CTE?
A common table expression, written with WITH, makes complex queries more readable. Recursive CTEs can walk hierarchies, such as organisation charts.
Performance
11. How do you analyse a slow query?
Run EXPLAIN (ANALYZE, BUFFERS) to see the actual plan and timing. Then check indexes, statistics (run ANALYZE) and the pg_stat_statements extension for the most expensive queries.
12. Which settings matter most?
shared_buffers, work_mem, effective_cache_size, maintenance_work_mem and max_connections. Tune them based on workload and memory.
Replication and backup
13. How does streaming replication work?
The primary writes changes to the write-ahead log (WAL). Standby servers receive and replay WAL records continuously. Standbys can serve read-only queries. In addition, logical replication copies selected tables between databases or versions.
14. How do you back up PostgreSQL?
pg_dumpandpg_dumpallfor logical backups.pg_basebackupplus WAL archiving for physical backups and point-in-time recovery.- Tools such as pgBackRest or Barman for managed, verified backups.
Security
15. How does PostgreSQL handle access control?
It uses roles, which can be users or groups, with privileges on databases, schemas and tables. The pg_hba.conf file controls which hosts can connect and how they authenticate. Use SCRAM-SHA-256 passwords and TLS, and consider row-level security for multi-tenant data.
For other databases, see our MySQL and MongoDB interview questions.
Keep preparing
Practise each answer out loud, and run the commands in a lab rather than memorising them. Interviewers often ask follow-up questions, so make sure you understand why each answer is true.
Sources
Preparing for an interview? Browse more questions in our interview preparation hub. For businesses, we also offer security services such as VAPT, server hardening and ISO 27001 readiness.
