30 PostgreSQL Interview Questions & Answers

Beginner Level (1-10)

1. What is PostgreSQL?

Answer: PostgreSQL is an open-source, object-relational database management system (ORDBMS) emphasizing extensibility and SQL compliance. It supports ACID transactions, foreign keys, triggers, views, and stored procedures in multiple languages.

2. How is PostgreSQL different from MySQL?

Answer:

• PostgreSQL is ORDBMS, while MySQL is relational only

• PostgreSQL fully supports ACID compliance by default

• PostgreSQL has more advanced features (window functions, common table expressions, materialized views)

• PostgreSQL uses MVCC (Multi-Version Concurrency Control) for better concurrency

• MySQL is generally faster for read-heavy workloads, PostgreSQL better for complex operations

3. What are the ACID properties in PostgreSQL?

Answer:

• Atomicity: All operations in a transaction succeed or fail together

• Consistency: Database moves from one valid state to another

• Isolation: Concurrent transactions don't interfere with each other

• Durability: Committed transactions persist even after system failure

4. What is MVCC?

Answer: Multi-Version Concurrency Control allows multiple transactions to access the same data simultaneously without locking. Each transaction sees a snapshot of data. This prevents read-write conflicts and improves performance.

5. What is a primary key?

Answer: A primary key uniquely identifies each row in a table. It must contain unique values and cannot contain NULLs. A table can have only one primary key.

sql

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL
);

6. What is a foreign key?

Answer: A foreign key creates a relationship between two tables by referencing the primary key of another table, enforcing referential integrity.

sql

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    user_id INT REFERENCES users(id)
);

7. What are indexes and why use them?

Answer: Indexes are database structures that improve data retrieval speed. Common types:

• B-tree: Default, good for equality and range queries

• Hash: Good for equality comparisons

• GiST: Generalized Search Tree for geometric data

• GIN: Generalized Inverted Index for array and full-text search

8. What is a View?

Answer: A virtual table based on a SELECT query. Views don't store data physically but provide a way to simplify complex queries.

sql

CREATE VIEW active_users AS
SELECT * FROM users WHERE is_active = true;

9. What are stored procedures?

Answer: Precompiled database operations stored in the database. PostgreSQL supports multiple languages (PL/pgSQL, Python, Perl, etc.).

sql

CREATE OR REPLACE FUNCTION get_user_count()
RETURNS INTEGER AS $$
BEGIN
    RETURN (SELECT COUNT(*) FROM users);
END;
$$ LANGUAGE plpgsql;

10. Explain SELECT, INSERT, UPDATE, DELETE

Answer:

sql

-- SELECT
SELECT * FROM users WHERE age > 25;

-- INSERT
INSERT INTO users (name, age) VALUES ('John', 30);

-- UPDATE
UPDATE users SET age = 31 WHERE name = 'John';

-- DELETE
DELETE FROM users WHERE age < 18;

Intermediate Level (11-20)

11. What are window functions?

Answer: Functions that perform calculations across a set of table rows related to the current row without collapsing rows.

sql

SELECT 
    name, 
    salary,
    AVG(salary) OVER (PARTITION BY department) as avg_dept_salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) as rank
FROM employees;

12. What are Common Table Expressions (CTEs)?

Answer: Temporary result sets that can be referenced within a query. Useful for complex queries and recursion.

sql

WITH regional_sales AS (
    SELECT region, SUM(amount) as total_sales
    FROM orders
    GROUP BY region
)
SELECT * FROM regional_sales WHERE total_sales > 10000;

13. What is recursion in PostgreSQL?

Answer: Using recursive CTEs to query hierarchical data.

sql

WITH RECURSIVE employee_hierarchy AS (
    SELECT id, name, manager_id
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT e.id, e.name, e.manager_id
    FROM employees e
    INNER JOIN employee_hierarchy eh ON e.manager_id = eh.id
)
SELECT * FROM employee_hierarchy;

14. What are transactions?

Answer: A sequence of operations performed as a single logical unit of work.

sql

BEGIN;
UPDATE account SET balance = balance - 100 WHERE id = 1;
UPDATE account SET balance = balance + 100 WHERE id = 2;
COMMIT;
-- ROLLBACK; to undo

15. What is EXPLAIN and ANALYZE?

Answer: Tools for query optimization:

• EXPLAIN: Shows execution plan

• ANALYZE: Executes query and shows actual performance statistics

sql

EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com';

16. What is normalization?

Answer: Database design technique to minimize redundancy and dependency by organizing fields and tables.

17. What are triggers?

Answer: Automatic functions executed when specific events occur.

sql

CREATE OR REPLACE FUNCTION update_timestamp()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_user_timestamp
BEFORE UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION update_timestamp();

18. What are materialized views?

Answer: Views that store query results physically. Must be refreshed manually.

sql

CREATE MATERIALIZED VIEW monthly_sales AS
SELECT DATE_TRUNC('month', order_date) as month, SUM(amount)
FROM orders
GROUP BY DATE_TRUNC('month', order_date);

REFRESH MATERIALIZED VIEW monthly_sales;

19. What is table partitioning?

Answer: Splitting large tables into smaller physical pieces for better performance.

sql

-- Range partitioning
CREATE TABLE sales (id INT, sale_date DATE, amount DECIMAL)
PARTITION BY RANGE (sale_date);

CREATE TABLE sales_2023 PARTITION OF sales
FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

20. How to handle NULL values?

Answer:

sql

-- Check for NULL
SELECT * FROM users WHERE email IS NULL;

-- Coalesce (returns first non-NULL value)
SELECT COALESCE(email, 'N/A') FROM users;

-- NULLIF (returns NULL if equal)
SELECT NULLIF(column, 'N/A') FROM table;

Advanced Level (21-30)

21. What is connection pooling?

Answer: Maintaining a pool of database connections for reuse, improving performance. Tools: PgBouncer, PgPool-II.

22. What is WAL (Write-Ahead Logging)?

Answer: All changes are written to a log before being applied to the database, ensuring durability and supporting point-in-time recovery.

23. What is vacuuming?

Answer: PostgreSQL's garbage collection process that reclaims storage occupied by dead tuples.

sql

VACUUM ANALYZE table_name;  -- Manual vacuum
-- Autovacuum handles this automatically

24. What are prepared statements?

Answer: Precompiled SQL statements for repeated execution, improving performance and security against SQL injection.

sql

PREPARE user_plan (TEXT) AS
SELECT * FROM users WHERE username = $1;

EXECUTE user_plan('john_doe');

25. What is full-text search?

Answer: Advanced text search capabilities using tsvector and tsquery.

sql

CREATE INDEX idx_fts ON documents USING gin(to_tsvector('english', content));

SELECT * FROM documents 
WHERE to_tsvector('english', content) @@ to_tsquery('english', 'postgresql & database');

26. What are JSON/JSONB data types?

Answer:

• JSON: Stores exact copy, faster insertion

• JSONB: Stores in binary format, faster queries, supports indexing

sql

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    data JSONB
);

INSERT INTO products (data) VALUES 
('{"name": "Laptop", "price": 999.99, "tags": ["electronics", "sale"]}');

SELECT data->>'name' FROM products WHERE data @> '{"tags": ["sale"]}';

27. What is replication?

Answer: Copying data from one database server to another. Types:

• Streaming replication (asynchronous/synchronous)

• Logical replication (selective table replication)

28. How to monitor PostgreSQL performance?

Answer:

• Use pg_stat_activity for current activity

• pg_stat_statements for query statistics

• EXPLAIN ANALYZE for query plans

• pg_locks for lock monitoring

• Built-in views in pg_catalog schema

29. What is deadlock and how to handle it?

Answer: When two or more transactions wait for each other to release locks. PostgreSQL automatically detects and rolls back one transaction.

sql

-- Prevention strategies:
-- 1. Access tables in same order
-- 2. Keep transactions short
-- 3. Use appropriate lock timeouts
SET lock_timeout = '5s';

30. How to backup and restore?

Answer:

bash

# Logical backup
pg_dump dbname > backup.sql
pg_dumpall > all_databases.sql

# Physical backup (requires archiving)
# Using pg_basebackup
pg_basebackup -D /backup/location -Ft -z

# Restore
psql dbname < backup.sql

# Point-in-time recovery
# Requires WAL archiving configured

Bonus: Practical Example Questions

Scenario: E-commerce Database Design

sql

-- Design normalized tables for users, products, orders, order_items
CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    price DECIMAL(10,2) CHECK (price > 0),
    stock INTEGER DEFAULT 0
);

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(user_id),
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status VARCHAR(20) DEFAULT 'pending'
);

CREATE TABLE order_items (
    order_item_id SERIAL PRIMARY KEY,
    order_id INTEGER REFERENCES orders(order_id),
    product_id INTEGER REFERENCES products(product_id),
    quantity INTEGER CHECK (quantity > 0),
    price_at_order DECIMAL(10,2)
);

-- Complex query: Monthly sales report
WITH monthly_stats AS (
    SELECT 
        DATE_TRUNC('month', o.order_date) as month,
        COUNT(DISTINCT o.order_id) as total_orders,
        COUNT(oi.order_item_id) as total_items,
        SUM(oi.quantity * oi.price_at_order) as total_revenue,
        COUNT(DISTINCT o.user_id) as unique_customers
    FROM orders o
    JOIN order_items oi ON o.order_id = oi.order_id
    WHERE o.status = 'completed'
    GROUP BY DATE_TRUNC('month', o.order_date)
)
SELECT 
    TO_CHAR(month, 'YYYY-MM') as month,
    total_orders,
    total_items,
    total_revenue,
    ROUND(total_revenue / NULLIF(total_orders, 0), 2) as avg_order_value,
    ROUND(total_revenue / NULLIF(unique_customers, 0), 2) as revenue_per_customer,
    LAG(total_revenue) OVER (ORDER BY month) as previous_month_revenue
FROM monthly_stats
ORDER BY month;