PostgreSQL Interview Questions and Answers
Qaisar Abbas
Sr. Software Engineer | Fintech | Java | Spring Boot | Microservices | Oracle DB | Containerization | Enterprise Software Architecture | Docker | Monolithic | System Design | EDD | SOA | DDD | Spring AI | CI/CD Pipline
PostgreSQL is one of the most popular relational databases in the world, known for its powerful features and reliability. Whether you're an aspiring DBA, developer, or engineer, acing a PostgreSQL interview requires the right preparation. Check out 25 professional PostgreSQL interview questions and answers to boost your confidence and showcase your expertise. ??
1. What is PostgreSQL?
Answer: PostgreSQL is an advanced open-source relational database management system (RDBMS) that supports SQL compliance, ACID properties, and various advanced features like JSON support, full-text search, and extensibility. It is known for its robustness, scalability, and support for complex queries.
2. What are the key features of PostgreSQL?
Answer:
3. Explain the concept of MVCC in PostgreSQL.
Answer: MVCC (Multi-Version Concurrency Control) is a method PostgreSQL uses to manage concurrent access to the database. It ensures that transactions do not block each other by maintaining multiple versions of data. Each transaction sees a snapshot of the database at a specific point, allowing for consistent reads and writes.
4. How does PostgreSQL handle indexing? What types of indexes are available?
Answer: PostgreSQL uses indexes to improve query performance by reducing the amount of data scanned. The types of indexes include:
5. What is the difference between JSON and JSONB in PostgreSQL?
Answer:
6. How can you optimize query performance in PostgreSQL?
Answer:
7. What is the purpose of the VACUUM command?
Answer: The VACUUM command removes dead tuples from tables and indexes to reclaim storage and maintain performance. There are two types:
8. How do you back up and restore a PostgreSQL database?
Answer:
Commands:
pg_dump -U [username] -d [dbname] > backup.sql
psql -U [username] -d [dbname] < backup.sql
9. What are tablespaces in PostgreSQL?
Answer: Tablespaces in PostgreSQL allow administrators to define locations on the filesystem where database objects can be stored. This provides better control over disk I/O and helps distribute data across different storage devices.
10. How does PostgreSQL handle replication?
Answer: PostgreSQL supports several replication methods:
11. What are CTEs, and why are they used?
Answer: CTEs (Common Table Expressions) are temporary result sets defined within the execution of a query. They improve query readability and can be referenced multiple times in a single query.
Example:
WITH recent_sales AS (
SELECT * FROM sales WHERE sale_date > NOW() - INTERVAL '30 days'
)
SELECT * FROM recent_sales WHERE amount > 1000;
12. Explain the difference between DELETE and TRUNCATE.
Answer:
13. How can you enforce unique constraints in PostgreSQL?
Answer: Use the UNIQUE constraint or create a unique index:
CREATE TABLE users (
id SERIAL PRIMARY KEY,
email VARCHAR(255) UNIQUE
);
14. How does PostgreSQL handle concurrency?
Answer: PostgreSQL uses MVCC to handle concurrency. It allows multiple transactions to occur simultaneously without locking the database. It uses snapshots to provide consistent reads and avoids conflicts through row-versioning.
15. What is the role of pg_hba.conf?
Answer: The pg_hba.conf file defines client authentication settings for PostgreSQL. It specifies how clients can connect, the authentication methods, and which IP addresses or hosts are allowed access.
16. What are the different data types available in PostgreSQL?
Answer: PostgreSQL supports a variety of data types:
17. How do you create a stored procedure in PostgreSQL?
Answer: Use the CREATE PROCEDURE statement. Example:
CREATE PROCEDURE update_salary(emp_id INT, increment NUMERIC)
LANGUAGE plpgsql
AS $$
BEGIN
UPDATE employees SET salary = salary + increment WHERE id = emp_id;
END;
$$;
Call it using CALL:
CALL update_salary(1, 500);
18. What is the use of pg_stat_activity?
Answer: The pg_stat_activity view provides information about the active processes in the PostgreSQL database. It is useful for monitoring queries, client connections, and troubleshooting performance issues.
19. How can you perform a case-insensitive search in PostgreSQL?
Answer: Use the ILIKE operator instead of LIKE:
SELECT * FROM users WHERE name ILIKE '%john%';
20. What are extensions in PostgreSQL?
Answer: Extensions are packages that add additional functionality to PostgreSQL. Common extensions include:
Install an extension:
CREATE EXTENSION IF NOT EXISTS hstore;
21. What is the difference between NOW() and CURRENT_TIMESTAMP?
Answer: Both return the current date and time. However:
22. How can you list all databases in PostgreSQL?
Answer: Use the \l command in psql or query the pg_database table:
SELECT datname FROM pg_database;
23. Explain the difference between INNER JOIN and OUTER JOIN.
Answer:
Example:
SELECT * FROM orders INNER JOIN customers ON orders.customer_id = customers.id;
24. How can you update multiple rows in PostgreSQL?
Answer: Use the UPDATE statement with a WHERE clause:
UPDATE employees SET salary = salary + 1000 WHERE department = 'IT';
25. What is a sequence in PostgreSQL?
Answer: A sequence is a database object used to generate unique numeric identifiers, often for primary keys.
CREATE SEQUENCE emp_id_seq START WITH 1 INCREMENT BY 1;
SELECT nextval('emp_id_seq');
Interesting