common PostgreSQL interview questions

 

Beginner Level

1. What is PostgreSQL and what are its main features?

2. How do you create a new database and a new table in PostgreSQL?

3. What are the different data types supported in PostgreSQL?

4. How do you insert, update, and delete records in PostgreSQL?

5. What is a SERIAL data type?

6. Explain the difference between CHAR, VARCHAR, and TEXT.

7. What is a primary key? Can a table have multiple primary keys?

8. How do you perform a JOIN in PostgreSQL? Give examples of INNER, LEFT, RIGHT, and FULL joins.

---

🟡 Intermediate Level

1. What are indexes in PostgreSQL? How do they work?

2. What is the difference between a unique constraint and a primary key?

3. How do you use EXPLAIN and EXPLAIN ANALYZE to optimize queries?

4. What is a CTE (Common Table Expression)? How is it different from a subquery?

5. How does PostgreSQL handle transactions? Explain ACID properties.

6. What is the purpose of the RETURNING clause in INSERT/UPDATE/DELETE?

7. What are some ways to improve performance in PostgreSQL?

8. How do you handle NULLs in PostgreSQL? How do they affect sorting and filtering?

---

🔴 Advanced Level

1. What is the difference between VACUUM, ANALYZE, AUTOVACUUM, and REINDEX?

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

3. What are TOAST tables and when are they used?

4. What is a materialized view? How is it different from a regular view?

5. How does PostgreSQL manage concurrency and locking?

6. What is the difference between logical and physical replication?

7. How would you handle database partitioning in PostgreSQL?

8. Explain how WAL (Write-Ahead Logging) works.

9. How would you monitor PostgreSQL performance in a production environment?