PostgreSQL Interview Questions

PostgreSQL is an open-source, advanced relational database management system (RDBMS) known for its extensibility, SQL compliance, and support for both relational and non-relational data.

• ACID compliance
• Support for JSON and XML data types
• MVCC (Multi-Version Concurrency Control) for concurrent transactions
• Full-text search
• Extensible with custom functions, types, and operators
• Advanced indexing options (B-tree, GiST, GIN)

MVCC stands for Multi-Version Concurrency Control, a technique PostgreSQL uses to handle concurrent transactions without locking, providing snapshot isolation for readers.

Using SQL command:

CREATE DATABASE dbname;

Or with createdb CLI command.

A schema is a namespace within a database that contains tables, views, functions, etc. It helps organize database objects logically.

• Use indexes
• Analyze and vacuum tables
• Use EXPLAIN and EXPLAIN ANALYZE to understand query plans
• Optimize joins and subqueries
• Use partitioning for large tables

• DELETE removes rows one by one and can be rolled back, fires triggers.
• TRUNCATE quickly removes all rows in a table without logging individual row deletions, cannot be rolled back if inside a transaction block.

• B-tree (default)
• Hash
• GiST (Generalized Search Tree)
• BRIN (Block Range Index)

• UNION removes duplicate rows between result sets.
• UNION ALL includes all rows including duplicates.

A CTE is a temporary named result set used within a query, defined using WITH keyword, improving readability and reusability.

Transactions in PostgreSQL follow ACID principles, managed with BEGIN, COMMIT, and ROLLBACK commands, ensuring atomicity and consistency.

VACUUM reclaims storage by removing dead tuples created by updates and deletes, helping maintain database performance.

PostgreSQL supports streaming replication (asynchronous/synchronous) and logical replication for data copying between primary and standby servers.

• Physical replication copies the entire database cluster byte-for-byte.
• Logical replication replicates changes based on database operations (insert/update/delete) and can be selective.

Using GRANT and REVOKE commands to assign or remove privileges on database objects like tables, schemas, and databases.

pg_hba.conf (host-based authentication) controls client authentication methods and access rules based on IP, user, and database.

Aggregate functions perform a calculation on a set of values and return a single value, e.g., SUM(), AVG(), COUNT(), MAX(), MIN().

PostgreSQL supports full-text search using tsvector and tsquery types, with functions like to_tsvector() and to_tsquery().

• JSON stores JSON data as text.
• JSONB stores JSON in a binary format allowing faster processing and indexing.

Use pg_dump to export and pg_restore or psql to import backups.