PostgreSQL Interview Questions and Answers
A focused interview bank covering relational modeling, SQL, indexes, concurrency, JSONB, security, operations, and application integration.
Q01. What is PostgreSQL?
PostgreSQL is an open-source relational database system that supports SQL, transactions, constraints, extensibility, JSONB, and advanced query features.
Interview tip: explain the core behavior, then mention one operational trade-off or edge case.
Interview approach: define the concept, explain when you use it, and mention one limitation or production consideration.
-- Demonstrate the concept with a small query or schema example
SELECT current_database();- Give a concise definition.
- Explain a real-world use case.
- Mention one trade-off or safety consideration.
What does PostgreSQL primarily manage?
Q02. What is a primary key?
A primary key uniquely identifies each row and cannot contain NULL. A table has one primary key constraint, which may include multiple columns.
Interview tip: explain the core behavior, then mention one operational trade-off or edge case.
Interview approach: define the concept, explain when you use it, and mention one limitation or production consideration.
-- Demonstrate the concept with a small query or schema example
SELECT current_database();- Give a concise definition.
- Explain a real-world use case.
- Mention one trade-off or safety consideration.
Can a composite primary key contain multiple columns?
Q03. What is a foreign key?
A foreign key enforces that referencing values match a referenced key, protecting referential integrity. It does not automatically create an index on the referencing columns.
Interview tip: explain the core behavior, then mention one operational trade-off or edge case.
Interview approach: define the concept, explain when you use it, and mention one limitation or production consideration.
-- Demonstrate the concept with a small query or schema example
SELECT current_database();- Give a concise definition.
- Explain a real-world use case.
- Mention one trade-off or safety consideration.
Does a foreign key automatically index its referencing columns in PostgreSQL?
Q04. What is normalization?
Normalization organizes facts to reduce duplication and update anomalies. Common relationship patterns use foreign keys and junction tables; denormalization is sometimes justified by measured needs.
Interview tip: explain the core behavior, then mention one operational trade-off or edge case.
Interview approach: define the concept, explain when you use it, and mention one limitation or production consideration.
-- Demonstrate the concept with a small query or schema example
SELECT current_database();- Give a concise definition.
- Explain a real-world use case.
- Mention one trade-off or safety consideration.
What is a main goal of normalization?
Q05. What is the difference between DELETE, TRUNCATE, and DROP?
DELETE removes selected rows and supports WHERE. TRUNCATE removes all rows from a table efficiently, with different locking and trigger behavior. DROP removes the database object itself.
Interview tip: explain the core behavior, then mention one operational trade-off or edge case.
Interview approach: define the concept, explain when you use it, and mention one limitation or production consideration.
-- Demonstrate the concept with a small query or schema example
SELECT current_database();- Give a concise definition.
- Explain a real-world use case.
- Mention one trade-off or safety consideration.
Which statement removes a table definition as well as its data?
Q06. What is the difference between WHERE and HAVING?
WHERE filters rows before grouping; HAVING filters groups after aggregation. Conditions that do not depend on aggregates are often best applied in WHERE.
Interview tip: explain the core behavior, then mention one operational trade-off or edge case.
Interview approach: define the concept, explain when you use it, and mention one limitation or production consideration.
-- Demonstrate the concept with a small query or schema example
SELECT current_database();- Give a concise definition.
- Explain a real-world use case.
- Mention one trade-off or safety consideration.
Which clause filters grouped aggregate results?
Q07. What is an index?
An index is a data structure that can speed up row lookup or ordering but consumes storage and adds write maintenance. Index design should follow query patterns and be verified with plans.
Interview tip: explain the core behavior, then mention one operational trade-off or edge case.
Interview approach: define the concept, explain when you use it, and mention one limitation or production consideration.
-- Demonstrate the concept with a small query or schema example
SELECT current_database();- Give a concise definition.
- Explain a real-world use case.
- Mention one trade-off or safety consideration.
What is a common index trade-off?
Q08. What is a B-tree index useful for?
B-tree indexes support common equality and range comparisons and can provide ordered access. They are PostgreSQL's default index type and suit many ordinary lookup patterns.
Interview tip: explain the core behavior, then mention one operational trade-off or edge case.
Interview approach: define the concept, explain when you use it, and mention one limitation or production consideration.
-- Demonstrate the concept with a small query or schema example
SELECT current_database();- Give a concise definition.
- Explain a real-world use case.
- Mention one trade-off or safety consideration.
Which index type is a versatile default for equality and range predicates?
Q09. What is a GIN index useful for?
GIN indexes are useful for composite values such as JSONB, arrays, and full-text search when the operators and operator class are supported.
Interview tip: explain the core behavior, then mention one operational trade-off or edge case.
Interview approach: define the concept, explain when you use it, and mention one limitation or production consideration.
-- Demonstrate the concept with a small query or schema example
SELECT current_database();- Give a concise definition.
- Explain a real-world use case.
- Mention one trade-off or safety consideration.
Which index type is commonly used for JSONB containment?
Q10. What is EXPLAIN ANALYZE?
It executes a statement and reports actual runtime information alongside the plan. Because it executes the query, data-changing statements can have real effects unless controlled.
Interview tip: explain the core behavior, then mention one operational trade-off or edge case.
Interview approach: define the concept, explain when you use it, and mention one limitation or production consideration.
-- Demonstrate the concept with a small query or schema example
SELECT current_database();- Give a concise definition.
- Explain a real-world use case.
- Mention one trade-off or safety consideration.
Does EXPLAIN ANALYZE execute the statement?