30 Common PostgreSQL Interview Questions

1. Beginner-Level PostgreSQL Questions:

1. What is PostgreSQL?

   • PostgreSQL is an open-source, object-relational database management system (ORDBMS) known for its robustness, feature set, and support for complex queries, transactions, and data integrity.

2. What are the key features of PostgreSQL?

   • Key features include support for ACID transactions, rich data types, MVCC (Multi-Version Concurrency Control), indexing, full-text search, JSON support, and extensibility with custom functions and data types.

3. What is a table in PostgreSQL?

   • A table in PostgreSQL is a collection of rows and columns used to store structured data. Each row represents a record, and each column represents an attribute of the record.

4. What is a database in PostgreSQL?

   • A PostgreSQL database is a container that holds tables, views, functions, and other objects. Each PostgreSQL server can host multiple databases.

5. What is a schema in PostgreSQL?

   • A schema in PostgreSQL is a namespace that contains database objects like tables, views, and functions. It helps organize and manage database objects within a database.

6. What is psql?

   • psql is the command-line interface for interacting with PostgreSQL. It allows users to run queries, execute SQL commands, and manage the database.

7. What is a primary key in PostgreSQL?

   • A primary key is a unique identifier for rows in a table. It ensures that no duplicate values exist in the column(s) it is applied to and that the value is not NULL.

8. What is a foreign key in PostgreSQL?

   • A foreign key is a column or a group of columns that establishes a link between data in two tables, enforcing referential integrity between them.

9. What is an index in PostgreSQL?

   • An index is a data structure that improves the speed of data retrieval operations on a table. Common types include B-tree, hash, and GIN indexes.

10. What is a view in PostgreSQL?

    • A view is a virtual table that is based on a query. It allows you to present data in a specific format or simplify complex queries without storing data separately.

11. What is a sequence in PostgreSQL?

    • A sequence is a special database object that generates a series of unique numbers, often used for auto-incrementing fields like primary keys.

12. What is pgAdmin?

    • pgAdmin is a graphical user interface (GUI) for managing PostgreSQL databases. It allows users to interact with databases through a visual interface.

13. What is VACUUM in PostgreSQL?

    • VACUUM is used to reclaim storage occupied by dead tuples. It also helps to prevent transaction ID wraparound issues and maintain database performance.

14. What is the difference between CHAR, VARCHAR, and TEXT in PostgreSQL?

    • CHAR: Fixed-length character type.
    • VARCHAR: Variable-length character type with a limit.
    • TEXT: Variable-length character type with no limit.

15. What is a transaction in PostgreSQL?

    • A transaction is a sequence of one or more SQL statements that are executed as a single unit of work. It ensures that either all operations are performed or none, maintaining database integrity.

2. Intermediate-Level PostgreSQL Questions:

16. What is MVCC in PostgreSQL?

    • MVCC (Multi-Version Concurrency Control) is a method used by PostgreSQL to handle concurrent transactions, allowing readers to access consistent data without being blocked by writers.

17. What is the purpose of EXPLAIN in PostgreSQL?

    • EXPLAIN is used to analyze the execution plan of a query, showing how the PostgreSQL query planner will execute it. This helps in understanding and optimizing query performance.

18. What are the different types of joins in PostgreSQL?

    • INNER JOIN: Returns rows that have matching values in both tables.
    • LEFT JOIN: Returns all rows from the left table and matched rows from the right table.
    • RIGHT JOIN: Returns all rows from the right table and matched rows from the left table.
    • FULL JOIN: Returns rows when there is a match in either table.

19. What is a CTE (Common Table Expression) in PostgreSQL?

    • A CTE is a temporary result set that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement. It is defined using the WITH clause.

20. What is the difference between UNION and UNION ALL in PostgreSQL?

    • UNION combines the results of two queries and removes duplicates.
    • UNION ALL combines the results of two queries without removing duplicates, making it faster.

21. What is json and jsonb in PostgreSQL?

    • json is a data type that stores JSON data as text, while jsonb stores JSON data in a binary format for better indexing and performance.

22. What is a stored procedure in PostgreSQL?

    • A stored procedure is a set of SQL statements that can be executed on the database server. It allows for reusing code and performing complex operations.

23. What is a trigger in PostgreSQL?

    • A trigger is a function that is automatically invoked before or after an event (like INSERT, UPDATE, DELETE) occurs on a table.

24. What is a window function in PostgreSQL?

    • A window function performs calculations across a set of table rows that are related to the current row, allowing for tasks like calculating running totals and moving averages.

25. How does PostgreSQL handle concurrency?

    • PostgreSQL uses MVCC and a locking mechanism (row-level locking) to manage concurrent access to data, allowing multiple transactions to occur simultaneously without conflicts.

3. Advanced-Level PostgreSQL Questions:

26. What is a partitioned table in PostgreSQL?

    • A partitioned table in PostgreSQL is a logical structure that allows dividing a large table into smaller, more manageable pieces. This improves performance and manageability for large datasets.

27. How does replication work in PostgreSQL?

    • PostgreSQL supports streaming replication where changes from the primary server are continuously sent to a standby server. It also supports logical replication for replicating specific tables.

28. What is pg_stat_activity?

    • pg_stat_activity is a system view that provides information about the active queries and connections on the PostgreSQL server, useful for monitoring performance.

29. What is a tablespace in PostgreSQL?

    • A tablespace is a location on the disk where PostgreSQL stores data files. It allows administrators to store database objects in separate filesystems for better performance.

30. How do you optimize queries in PostgreSQL?

    • Query optimization techniques include using proper indexing, analyzing query plans with EXPLAIN, partitioning large tables, using VACUUM regularly, and leveraging materialized views for frequently accessed data.