Prepare for Your PostgreSQL Interview with These Essential Questions
This comprehensive guide features 30 PostgreSQL interview questions covering basic, intermediate, and advanced topics. Whether you’re a fresher, have 1-3 years of experience, or are a seasoned professional with 3-6 years, these questions will help you demonstrate your PostgreSQL expertise to companies like Zoho, Atlassian, and Swiggy.
Basic PostgreSQL Interview Questions (1-10)
1. What is PostgreSQL?
PostgreSQL is an open-source, object-relational database management system (ORDBMS) known for its robustness, feature set, and support for complex queries, transactions, and data integrity[2].
2. What are the key features of PostgreSQL?
Key features include ACID transactions, rich data types, MVCC (Multi-Version Concurrency Control), indexing, full-text search, JSON support, and extensibility with custom functions and data types[2].
3. What is a table in PostgreSQL?
A table in PostgreSQL is a structured data storage object that organizes data into rows and columns, similar to a spreadsheet[2].
4. How do you create a new database in PostgreSQL?
Use the CREATE DATABASE database_name; command to create a new database[3].
5. How do you add new values to a table in PostgreSQL?
PostgreSQL uses the standard INSERT INTO table_name (column1, column2) VALUES (value1, value2); statement to add data[3].
6. How do you delete a database in PostgreSQL?
To delete a database, use the DROP DATABASE database_name; command[3].
7. What is a schema in PostgreSQL?
A schema in PostgreSQL contains the logical configuration of tables, data types, views, indexes, sequences, constraints, and functions within a database[3].
8. What are the main data types in PostgreSQL?
PostgreSQL supports data types like Boolean, CHAR, VARCHAR, INT, FLOAT(n), UUID, DATE, and many others[1].
9. What is the difference between a primary key and a foreign key?
A primary key ensures unique, non-null values in a column, while a foreign key creates relationships between tables by referencing a primary key in another table[3].
10. What are the main constraints in PostgreSQL?
Main constraints include CHECK, NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, and EXCLUSION constraints[3].
Intermediate PostgreSQL Interview Questions (11-20)
11. What is an index in PostgreSQL?
An index is a data structure that improves query performance by enabling faster data retrieval, avoiding full table scans[3].
12. What are the different types of indexes in PostgreSQL?
PostgreSQL supports B-tree (default), Hash, GIN (for arrays and full-text search), GiST (for geospatial data), BRIN (for large ordered tables), and SP-GiST indexes[1][4].
13. What is MVCC in PostgreSQL?
Multi-Version Concurrency Control (MVCC) allows multiple transactions to access data simultaneously without blocking, using row versioning instead of locks[4].
14. What is normalization in PostgreSQL?
Normalization organizes data to reduce redundancy and improve integrity through normal forms like 1NF, 2NF, and 3NF[3].
15. What are triggers in PostgreSQL?
Triggers are special functions automatically executed when certain events (INSERT, UPDATE, DELETE) occur on a table[3].
16. What are the main operators in PostgreSQL?
PostgreSQL provides arithmetic, logical, comparison, and bitwise operators for query operations[3].
17. How do you manage users and roles in PostgreSQL?
Create users with CREATE ROLE username WITH LOGIN PASSWORD 'password'; and assign privileges using GRANT commands[1].
18. What authentication methods does PostgreSQL support?
PostgreSQL supports password-based (MD5, SCRAM-SHA-256), trust, peer, ident, Kerberos, LDAP, PAM, SSPI, and certificate-based authentication[1].
19. What is full-text search in PostgreSQL?
PostgreSQL provides built-in full-text search capabilities using tsvector and tsquery data types for efficient text searching[6].
20. How does parallel query support work in PostgreSQL?
Parallel queries use multiple CPU cores to execute queries faster by dividing work across processes[6].
Advanced PostgreSQL Interview Questions (21-30)
21. What is Write-Ahead Logging (WAL) in PostgreSQL?
WAL writes changes to log files before updating data files, enabling crash recovery, replication, and point-in-time recovery[4].
22. What are the types of replication in PostgreSQL?
PostgreSQL supports asynchronous, synchronous, and logical replication for high availability[1][2].
23. How do you implement high availability in PostgreSQL?
Use tools like Patroni or repmgr with streaming replication for automatic failover and high availability[1].
24. What is a partitioned table in PostgreSQL?
A partitioned table divides large tables into smaller, manageable pieces based on a key, improving performance and maintenance[2].
25. How do you handle index corruption in PostgreSQL?
Rebuild corrupted indexes using REINDEX INDEX index_name; or REINDEX DATABASE database_name;[1].
26. What is GEQO in PostgreSQL?
Genetic Query Optimization (GEQO) uses genetic algorithms for efficient planning of large join queries with many tables[1].
27. What is random_page_cost in PostgreSQL?
random_page_cost estimates the cost of fetching a non-sequentially accessed page, affecting query planner decisions[1].
28. How do you set up streaming replication?
-- On primary server
CREATE ROLE replication_user WITH REPLICATION PASSWORD 'password' LOGIN;
-- Edit pg_hba.conf
host replication replication_user 192.168.1.10/32 md5;[5]
29. What is BRIN indexing and when to use it?
Block Range INdex (BRIN) is efficient for very large tables with naturally ordered data, storing summary info per page range[1].
30. Explain the PostgreSQL architecture.
PostgreSQL follows a client-server model with background processes, query processor, shared memory, and data storage accessed via clients like pgAdmin[6].
## Key Citations
– [1] PostgreSQL DBA Interview Questions – Learnomate Technologies
– [2] 30 Common PostgreSQL Interview Questions – DEV Community
– [3] Top 45 PostgreSQL Interview Questions – DataCamp
– [4] Top 25+ PostgreSQL Interview Questions – Hirist
– [5] PostgreSQL Interview Questions – GeeksforGeeks
– [6] 90+ SQL Interview Questions – InterviewBit
