PostgreSQL Interview Questions and Answers

1. What is PostgreSQL?
  • PostgreSQL is an open-source, object-relational database system that uses and extends the SQL language. It is known for its robustness, scalability, and support for complex queries, ACID compliance, and extensibility.
2. What are the key features of PostgreSQL?
  • ACID compliant
  • Support for complex queries
  • Extensibility through custom types, operators, and functions
  • Advanced indexing techniques
  • Full-text search
  • Support for JSON and XML
  • Multi-version concurrency control (MVCC)
3. What is the difference between PostgreSQL and MySQL?
  • PostgreSQL is an object-relational database with support for complex queries and advanced features like full-text search and JSON data types. MySQL is a relational database management system known for being simpler and faster for read-heavy workloads, but with fewer advanced features than PostgreSQL.
4. What is ACID compliance in PostgreSQL?
  • ACID stands for Atomicity, Consistency, Isolation, and Durability. PostgreSQL is ACID-compliant, which means that it ensures transactions are processed reliably and supports the rollback of incomplete transactions in case of failure.
5. What is a schema in PostgreSQL?
  • A schema in PostgreSQL is a namespace that contains named database objects such as tables, views, and functions. Schemas allow you to organize database objects and avoid naming conflicts between objects in different schemas.
6. What is the difference between DELETE, TRUNCATE, and DROP in PostgreSQL?
  • DELETE: Removes rows from a table based on a condition and can be rolled back.
  • TRUNCATE: Removes all rows from a table without logging individual row deletions, cannot be rolled back in some cases, and is faster than DELETE.
  • DROP: Removes a table or database entirely, and it cannot be undone.
7. What is the purpose of the `VACUUM` command in PostgreSQL?
  • The `VACUUM` command in PostgreSQL reclaims storage and optimizes the database by removing dead tuples. It also updates statistics and helps in maintaining the health and performance of the database.
8. What is the use of `EXPLAIN` in PostgreSQL?
  • The `EXPLAIN` command in PostgreSQL is used to display the query execution plan for a SELECT, INSERT, UPDATE, or DELETE query. It shows how PostgreSQL plans to execute the query and the cost of each step.
9. What are indexes in PostgreSQL?
  • Indexes are database objects that improve the speed of data retrieval operations on a table. They can be created on one or more columns of a table and provide quick access to data without scanning the entire table.
10. What is a foreign key in PostgreSQL?
  • A foreign key is a column or a set of columns in one table that links to the primary key of another table. It ensures referential integrity by restricting the values that can be inserted into the foreign key column.
11. What are the different types of indexes available in PostgreSQL?
  • B-tree: Default index type, used for equality and range queries.
  • Hash: Used for equality queries only, but less commonly used than B-tree.
  • GIN (Generalized Inverted Index): Used for indexing composite types such as arrays and JSONB.
  • GiST (Generalized Search Tree): Used for geometric data types, full-text search, etc.
  • SP-GiST: Used for partitioned data types.
12. What is the difference between `INNER JOIN` and `OUTER JOIN` in PostgreSQL?
  • INNER JOIN: Returns rows that have matching values in both tables.
  • OUTER JOIN: Returns all rows from one table and matching rows from the other table. If there’s no match, the result will contain NULLs for columns from the table without a match.
13. What is a view in PostgreSQL?
  • A view is a virtual table based on the result of a SELECT query. It allows you to encapsulate complex queries and present them as a simple table. Views can also be used for security purposes to restrict access to certain columns or rows of data.
14. What is `WITH` in PostgreSQL?
  • The `WITH` clause (Common Table Expression or CTE) in PostgreSQL allows you to define temporary result sets that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement.
15. What is the difference between `UNION` and `UNION ALL` in PostgreSQL?
  • UNION: Combines the results of two or more SELECT queries and removes duplicate rows.
  • UNION ALL: Combines the results of two or more SELECT queries, including duplicate rows.
16. What is `EXCEPT` in PostgreSQL?
  • The `EXCEPT` operator returns the result of the first query minus the result of the second query. It removes the rows from the first query that are also returned by the second query.
17. How can you improve the performance of a PostgreSQL query?
  • Optimizing PostgreSQL queries can involve:
    • Indexing columns frequently used in WHERE clauses or JOIN conditions.
    • Using `EXPLAIN` to analyze query plans and identify bottlenecks.
    • Writing efficient SQL queries with appropriate filters and avoiding subqueries when possible.
    • Optimizing data types for storage and retrieval efficiency.
    • Using partitioning for large tables to split them into smaller, more manageable pieces.
18. What is the difference between `TRUNCATE` and `DELETE`?
  • TRUNCATE is a faster way to remove all rows from a table without generating individual row deletions. It is not transaction-safe and cannot be rolled back if not wrapped in a transaction.
  • DELETE removes rows one by one and is transaction-safe, meaning it can be rolled back if an error occurs.
19. How do you perform a backup in PostgreSQL?
  • You can use the `pg_dump` utility to perform a logical backup of a PostgreSQL database, which includes schema and data. To perform a full backup, use `pg_dump database_name > backup.sql`.
20. How do you restore a PostgreSQL database?
  • Use the `psql` command to restore a PostgreSQL database from a backup file. For example, `psql database_name < backup.sql` will restore the database from the SQL dump.
21. What is a transaction in PostgreSQL?
  • A transaction in PostgreSQL is a sequence of SQL statements that are executed as a single unit of work. Transactions ensure that all operations are completed successfully (commit) or none are applied (rollback).
22. What are triggers in PostgreSQL?
  • A trigger is a function that automatically executes in response to certain events on a particular table or view, such as INSERT, UPDATE, or DELETE operations.
23. What is the difference between `DROP DATABASE` and `DROP SCHEMA`?
  • DROP DATABASE: Deletes the entire database, including all tables, schemas, and data.
  • DROP SCHEMA: Deletes a specific schema within a database, but leaves the database intact.
24. What are subqueries in PostgreSQL?
  • A subquery is a query embedded inside another query. It can be used in SELECT, INSERT, UPDATE, or DELETE statements to retrieve a value or set of values for use in the main query.
25. What is the `DISTINCT` keyword in PostgreSQL?
  • The `DISTINCT` keyword is used to return unique rows from a query. It eliminates duplicate values in the result set.
26. What is the `SERIAL` data type in PostgreSQL?
  • The `SERIAL` data type is used to create auto-incrementing integer columns. It automatically generates a unique value for each inserted row.
27. What is the purpose of `COALESCE()` function in PostgreSQL?
  • The `COALESCE()` function returns the first non-NULL value in a list of arguments. It is useful for handling NULL values in queries.
28. What is `GROUP BY` in PostgreSQL?
  • The `GROUP BY` clause is used to group rows that have the same values in specified columns. It is often used in conjunction with aggregate functions such as `COUNT`, `SUM`, `AVG`, etc.
29. What is the difference between `INNER JOIN` and `LEFT JOIN`?
  • INNER JOIN returns only the rows that have matching values in both tables.
  • LEFT JOIN returns all rows from the left table and matching rows from the right table. If there is no match, the result will contain NULLs for columns from the right table.