📝 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.

4. Other Additional Questions

1. Should we use joins or Subqueries?

   In PostgreSQL, whether a JOIN or a SUBQUERY is faster depends on the specific query, data size, indexing, and database structure.

   • Joins are generally preferred when you need to combine data from multiple tables, as they can be more efficient when properly indexed. Database engines can optimize JOIN operations, especially if indexes exist on the keys being joined.

   • Subqueries can sometimes be simpler to write and read but may perform worse if they result in more data being processed or if they force the database to create temporary tables internally. However, modern databases like PostgreSQL can optimize certain types of subqueries, like EXISTS subqueries, to run more efficiently.

   In practice, it’s often recommended to use JOIN when possible, but performance should be evaluated using EXPLAIN statements to see how the database executes each query and identify which one performs better for your specific case.

2. Differences between SERIAL and INTEGER ?

   In PostgreSQL, SERIAL and INTEGER are both typically 32-bit types:

   • SERIAL: This is an auto-incrementing integer type. It’s shorthand for creating a column that uses a sequence to generate unique IDs. Behind the scenes, SERIAL creates an INTEGER column and an associated sequence that automatically increments. It has a range of -2,147,483,648 to 2,147,483,647 (32-bit).

   • INTEGER: This is a regular 32-bit integer type, which stores whole numbers within the same range as SERIAL. It does not automatically increment unless explicitly tied to a sequence.

   For larger values, PostgreSQL provides BIGSERIAL (64-bit) and BIGINT (64-bit).

3. Differences between DATE and TIMESTAMP ?

   DATE:

   • Stores only the date part (year, month, and day).
   • Format: YYYY-MM-DD.
   • Suitable for cases where time is not relevant, such as birthdays or event dates.

   TIMESTAMP:

   • Stores both date and time parts (year, month, day, hour, minute, second).
   • Format: YYYY-MM-DD HH:MM:SS.
   • Used when precise timing is important, such as logging events or transaction timestamps.

   Both types can also be accompanied by time zones, withTIMESTAMP WITH TIME ZONE providing additional context for time zone-aware applications.

4. Differences between Common Table Expressions (CTEs) and VIEWs ?

   In PostgreSQL, both Common Table Expressions (CTEs) and VIEWs are used to simplify complex queries, but they serve different purposes and have distinct characteristics.

   Common Table Expressions (CTEs)

   • Definition: CTEs are temporary result sets defined within the execution of a single SQL statement. They are created using the WITH clause.
   • Scope: CTEs are only available for the duration of the query that defines them. They do not persist in the database.
   • Usage: Ideal for breaking down complex queries into more manageable parts, enhancing readability and organization. They can be recursive, which allows for operations like hierarchical queries.
   • Example:

     WITH sales_summary AS (
     SELECT salesperson_id, SUM(amount) AS total_sales
     FROM sales
     GROUP BY salesperson_id
     )
     SELECT * FROM sales_summary WHERE total_sales > 1000;
     

   Views

   • Definition: A VIEW is a virtual table that is based on a SQL query. It is stored in the database and can be treated like a regular table.
   • Scope: Views persist in the database and can be reused across different queries. They do not store data themselves; rather, they store the SQL query used to retrieve data.
   • Usage: Useful for encapsulating complex queries that can be reused multiple times. They provide a level of abstraction and can enhance security by restricting access to certain data.
   • Example:

     CREATE VIEW sales_summary AS
     SELECT salesperson_id, SUM(amount) AS total_sales
     FROM sales
     GROUP BY salesperson_id;
     

Read more about PostgreSQL Commands and RESTful API with PostgreSQL

If you found this helpful, let me know by leaving a 👍 or a comment!, or if you think this post could help someone, feel free to share it! Thank you very much! 😃