PostgreSQL Interview Questions and Answers

 

1. What is PostgreSQL?
   PostgreSQL is an open-source relational database management system (RDBMS) emphasizing extensibility and SQL compliance. It supports ACID transactions, offers advanced data types (e.g., JSON, arrays), and features like replication, full-text search, and geospatial extensions (PostGIS).

2. Explain ACID properties.

   • Atomicity: Ensures all operations in a transaction complete or none do.

   • Consistency: Guarantees valid data transitions between states.

   • Isolation: Transactions operate independently of others.

   • Durability: Committed transactions persist even after system failures.

3. JSON vs. JSONB in PostgreSQL?

   • JSON: Stores exact input (preserves whitespace, order).

   • JSONB: Stores in binary format (faster queries, supports indexing). Prefer JSONB for most use cases due to efficiency.

4. Common Index Types?

   • B-tree: Default for range queries.

   • GIN: For composite data (arrays, JSONB).

   • GiST: Geospatial or full-text search.

   • BRIN: Large, ordered datasets (e.g., time-series).

   • Hash: Equality checks (rarely used).

5. Purpose of EXPLAIN Command?
   Displays the query execution plan. Use EXPLAIN ANALYZE to execute and get timing details, aiding query optimization.

6. Table Partitioning?
   Splits large tables into smaller partitions (by range, list, or hash). Improves query performance and simplifies maintenance (e.g., archiving old data).

7. MVCC in PostgreSQL?
   Multi-Version Concurrency Control allows concurrent transactions by maintaining multiple row versions. Readers see a snapshot, avoiding locks and enhancing concurrency.

8. Replication Types?

   • Physical (Streaming): Replicates WAL files for high availability.

   • Logical: Replicates specific tables/databases for selective data sync.

9. CTE Example?

   sql

   WITH regional_sales AS (
       SELECT region, SUM(amount) AS total
       FROM orders
       GROUP BY region
   )
   SELECT region FROM regional_sales WHERE total > 1000;

   Recursive CTEs handle hierarchical data (e.g., employee-manager trees).

10. Window Functions?
    Perform calculations across related rows (e.g., RANK(), SUM() OVER()).
    Example:

    sql

    SELECT name, salary, RANK() OVER (ORDER BY salary DESC) FROM employees;

11. Foreign Key Actions?

    • ON DELETE CASCADE: Delete child rows when parent is deleted.

    • ON UPDATE SET NULL: Set child column to NULL if parent key updates.

12. Purpose of VACUUM?
    Reclaims space from dead tuples (unused row versions). AUTOVACUUM automates this. VACUUM FULL rewrites tables but locks them.

13. Triggers and Use Cases?
    Automatically execute functions on data changes (e.g., audit logs, enforcing business rules).
    Example: Log changes to an audit_table after UPDATE.

14. Connection Pooling Tools?
    PgBouncer (lightweight) and pgPool-II (advanced features) manage connections to reduce overhead.

15. Performance Tuning Tips?

    • Use indexes wisely.

    • Optimize queries with EXPLAIN.

    • Tune shared_buffers and work_mem.

    • Regular maintenance (e.g., VACUUM, ANALYZE).

16. PostGIS Extension?
    Adds geospatial support (e.g., GIS data types, spatial indexing, and queries).

17. CHAR vs. VARCHAR vs. TEXT?

    • CHAR(n): Fixed-length, padded.

    • VARCHAR(n): Variable-length, length limit.

    • TEXT: Unlimited length (preferred for flexibility).

18. Full-Text Search Setup?
    Use to_tsvector to convert text and to_tsquery for queries. Create a GIN index on the tsvector column.
    Example:

    sql

    CREATE INDEX idx_fts ON docs USING GIN (to_tsvector('english', content));

19. Materialized Views?
    Store query results physically. Refresh with REFRESH MATERIALIZED VIEW. Useful for caching complex queries.

20. Handling Deadlocks?

    • Ensure consistent transaction order.

    • Shorten transaction duration.

    • Adjust deadlock_timeout or use explicit locking (FOR UPDATE).

Scenario-Based Questions

• Slow Query Optimization: Use EXPLAIN ANALYZE, check for missing indexes, optimize joins.

• Backup/Restore: Use pg_dump for logical backups, pg_basebackup for physical.

• Security Practices: Enable SSL, use role-based access, encrypt sensitive data.

Example Query (Recursive CTE for Hierarchy):

sql

WITH RECURSIVE org_chart 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
    JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT * FROM org_chart;