1. What is PostgreSQL?

PostgreSQL (Postgres) is an advanced, enterprise-class open-source object-relational database management system (ORDBMS). It has a strong reputation for reliability, feature robustness, and performance.

2. What are the key features of PostgreSQL?

Object-relational
Strongly ACID compliant
Support for structured/unstructured data (JSONB)
Extensible (PostGIS, custom types)
Advanced indexing (GIN, BRIN, GiST)
MVCC (Multi-Version Concurrency Control)

3. What is the difference between PostgreSQL and MySQL?

Postgres: Better for complex queries, data integrity, and extensibility; object-relational.
MySQL: Better for read-heavy, simple web applications; standard relational.

4. What is "MVCC" (Multi-Version Concurrency Control)?

A technique used by Postgres to provide data consistency and performance in a multi-user environment. It allows readers to read data without locking it, and writers to write data without blocking readers.

5. What is the difference between `JSON` and `JSONB`?

JSON: Stores an exact copy of the input text; fast to insert, slow to query.
JSONB: Stores data in a decomposed binary format; slower to insert, much faster to query and index.

6. What is the "VACUUM" command?

Reclaims storage occupied by dead tuples (rows that were deleted or outdated by updates). It is crucial for maintaining performance and preventing database bloat.

7. What is "Autovacuum"?

A background process that automatically performs VACUUM and ANALYZE operations on tables as they change, ensuring the database stays healthy.

8. What is a "Schema" in PostgreSQL?

A namespace that contains named database objects such as tables, views, and indexes. A single database can have multiple schemas.

9. What is the difference between a "Primary Key" and a "Unique Key"?

Primary: Only one per table; cannot be null.
Unique: Multiple per table; can allow one null value.

10. What are "Window Functions" in Postgres?

Functions that perform a calculation across a set of table rows that are related to the current row (e.g., ROW_NUMBER(), RANK(), SUM() OVER()).

11. What is "CTE" (Common Table Expression)?

A temporary result set that you can reference within another SELECT, INSERT, UPDATE, or DELETE statement (WITH clause).

12. What is "Recursive CTE"?

A CTE that references itself, used for querying hierarchical or tree-structured data (e.g., organizational charts).

13. What is "PostGIS"?

A powerful spatial database extender for Postgres that adds support for geographic objects, allowing location queries to be run in SQL.

14. What are the different types of Indexes in Postgres?

B-tree (Default)
Hash
GiST (Generalized Search Tree)
SP-GiST (Space-partitioned GiST)
GIN (Generalized Inverted Index)
BRIN (Block Range Index)

15. What is a "GIN Index"?

"Generalized Inverted Index" is ideal for indexing composite types like arrays, JSONB, and full-text search.

16. What is "BRIN Index"?

"Block Range Index" is designed for very large tables where data is physically sorted on disk based on a value (like a timestamp). It is very small and fast to build.

17. What is "Partitioning" in Postgres?

The process of splitting one large table into smaller physical pieces (partitions). It can be done by Range, List, or Hash.

18. What is the difference between "Declarative Partitioning" and "Inheritance Partitioning"?

Declarative: (Current standard) Easier to manage; syntax-level support in the CREATE TABLE statement.
Inheritance: (Legacy) Uses table inheritance and triggers to manage partitions.

19. What is "Logical Replication"?

A method of replicating data changes (inserts, updates, deletes) from a publisher to a subscriber based on a specific logic or subset of data.

20. What is "Streaming Replication"?

A physical replication method that sends the entire WAL (Write-Ahead Log) from a primary server to one or more standby servers.

21. What is "Write-Ahead Log" (WAL)?

A standard method for ensuring data integrity: all changes are written to a log file before they are applied to the data files, allowing recovery after a crash.

22. What is a "Tablespace"?

A location on disk where Postgres stores the actual data files. You can use different tablespaces to store different tables on different physical disks.

23. What are "Stored Procedures" in Postgres?

Subroutines stored in the database that can perform complex logic, including transaction control (COMMIT, ROLLBACK), which functions cannot do.

24. What is the difference between `PROCEDURE` and `FUNCTION`?

FUNCTION: Must return a value; cannot manage transactions.
PROCEDURE: Doesn't have to return a value; can manage transactions.

25. What is "Trigger"?

A function that is automatically called by the database in response to a specified event (e.g., BEFORE INSERT, AFTER UPDATE) on a table or view.

26. What is "TOAST" (The Oversized-Attribute Storage Technique)?

A mechanism used to store large attribute values (like long text) in a separate storage area if they exceed the 8KB page size.

27. What is "EXPLAIN ANALYZE"?

A command that not only shows the execution plan for a query but actually runs the query and displays the real-time execution statistics (timing, rows).

28. What is the "Search Path"?

A list of schemas that Postgres searches for an object when its name is not schema-qualified.

29. What is the "Information Schema"?

A standard-compliant set of views that provide metadata about all objects in the database.

30. What is "Full-Text Search" in Postgres?

Uses the tsvector (lexemes) and tsquery (search terms) types and functions to perform linguistic searches on text columns.

31. What is "Upsert" (`INSERT ... ON CONFLICT`)?

Allows you to perform an update or do nothing if an insert operation would cause a unique constraint violation.

32. What is "Foreign Data Wrapper" (FDW)?

A feature that allows you to access and query data stored in external databases (like MySQL, MongoDB, or another Postgres) as if it were a local table.

33. What is the purpose of `pg_dump` and `pg_restore`?

Utilities used for taking database backups and restoring them.

34. What is `pg_hba.conf`?

The Host-Based Authentication configuration file. it controls which clients can connect to which databases and using which authentication methods.

35. What is `postgresql.conf`?

The main configuration file where you set parameters like max_connections, shared_buffers, and work_mem.

36. What is the difference between `UNION` and `UNION ALL`?

UNION: Unique results (removes duplicates).
UNION ALL: All results (including duplicates).

37. What is the `ARRAY` data type?

Postgres allows columns to be defined as multidimensional arrays of variable-length base types.

38. What is the `UUID` data type?

A 128-bit identifier that is globally unique, useful for distributed systems.

39. What are "Partial Indexes"?

An index built over a subset of a table specified by a conditional expression (e.g., indexing only active users).

40. What are "Expression Indexes"?

An index built on the result of an expression rather than just the column values (e.g., LOWER(email)).

41. What is "Data Integrity" in Postgres?

Enforced through constraints like NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, and CHECK.

42. What is the difference between `DELETE` and `TRUNCATE`?

DELETE: Row-level; can store triggers; uses transaction log.
TRUNCATE: Table-level; faster; doesn't fire row-level triggers.

43. What is "Database Connection Pooling"?

A technique (using tools like PgBouncer) to reuse database connections, reducing the overhead of establishing new connections for every request.

44. What is the "shared_buffers" setting?

The amount of memory the database server uses for shared memory buffers (caching data blocks).

45. What is the "work_mem" setting?

The amount of memory used by internal sort operations and hash tables before writing to temporary disk files.

46. What is "Savepoint"?

A special mark inside a transaction that allows you to roll back a part of the transaction without rolling back the entire transaction.

47. What is "Materialized View"?

A view that physically stores the result of its query on disk. It must be manually refreshed (REFRESH MATERIALIZED VIEW).

48. What is "PL/pgSQL"?

A loadable procedural language for the Postgres database system, providing control structures and computation capabilities.

49. What is the purpose of the `RETURNING` clause?

Used in INSERT, UPDATE, and DELETE statements to return the values of the rows modified by the query.

50. What is "Citus"?

An extension that transforms Postgres into a distributed database, allowing it to scale out across multiple nodes.

PostgreSQL Interview Questions

Table of Contents

What is PostgreSQL?

What are the key features of PostgreSQL?

What is the difference between PostgreSQL and MySQL?

What is "MVCC" (Multi-Version Concurrency Control)?

What is the difference between <code>JSON</code> and <code>JSONB</code>?

What is the "VACUUM" command?

What is "Autovacuum"?

What is a "Schema" in PostgreSQL?

What is the difference between a "Primary Key" and a "Unique Key"?

What are "Window Functions" in Postgres?

What is "CTE" (Common Table Expression)?

What is "Recursive CTE"?

What is "PostGIS"?

What are the different types of Indexes in Postgres?

What is a "GIN Index"?

What is "BRIN Index"?

What is "Partitioning" in Postgres?

What is the difference between "Declarative Partitioning" and "Inheritance Partitioning"?

What is "Logical Replication"?

What is "Streaming Replication"?

What is "Write-Ahead Log" (WAL)?

What is a "Tablespace"?

What are "Stored Procedures" in Postgres?

What is the difference between <code>PROCEDURE</code> and <code>FUNCTION</code>?

What is "Trigger"?

What is "TOAST" (The Oversized-Attribute Storage Technique)?

What is "EXPLAIN ANALYZE"?

What is the "Search Path"?

What is the "Information Schema"?

What is "Full-Text Search" in Postgres?

What is "Upsert" (<code>INSERT ... ON CONFLICT</code>)?

What is "Foreign Data Wrapper" (FDW)?

What is the purpose of <code>pg_dump</code> and <code>pg_restore</code>?

What is <code>pg_hba.conf</code>?

What is <code>postgresql.conf</code>?

What is the difference between <code>UNION</code> and <code>UNION ALL</code>?

What is the <code>ARRAY</code> data type?

What is the <code>UUID</code> data type?

What are "Partial Indexes"?

What are "Expression Indexes"?

What is "Data Integrity" in Postgres?

What is the difference between <code>DELETE</code> and <code>TRUNCATE</code>?

What is "Database Connection Pooling"?

What is the "shared_buffers" setting?

What is the "work_mem" setting?

What is "Savepoint"?

What is "Materialized View"?

What is "PL/pgSQL"?

What is the purpose of the <code>RETURNING</code> clause?

What is "Citus"?