Top 30 PostgreSQL Intermediate Interview Questions – Part 1 (1-10)

Welcome to Part 1 of our PostgreSQL Intermediate Interview Questions series! 🎯

If you’re preparing for a PostgreSQL DBA interview, aiming to sharpen your SQL knowledge, or looking to upgrade your PostgreSQL skills, you’re in the right place.

In this post, we’ll cover the first 10 must-know intermediate PostgreSQL questions, including: Sequences, Tablespaces, Schema and more!
Note: All questions and answers are based on my experience, research, and self-study.

1. What are the key differences between PostgreSQL and other relational databases like Oracle and MySQL?

Ans: PostgreSQL vs Oracle: Key Differences

• Licensing and Cost: PostgreSQL is open-source and free, while Oracle is commercial and expensive.
• Scalability and Performance: Both are scalable, but PostgreSQL is more agile and handles high-traffic workloads well.
• Security: Both offer robust security features, but PostgreSQL strongly focuses on open-source security.

When to Choose Each:

• Oracle: Ideal for large-scale enterprise projects requiring advanced features, high-performance capabilities, and commercial support.
• PostgreSQL: Suitable for projects with limited budgets, requiring agility and scalability, and needing robust security.

2. Explain MVCC (Multi-Version Concurrency Control) in databases.

Ans: MVCC is a concurrency control method used in PostgreSQL to allow multiple transactions to access the same data simultaneously without blocking each other, ensuring data consistency and isolation.

How MVCC Works:

• Instead of locking data rows when a transaction is reading or writing, PostgreSQL maintains multiple versions of a row (hence the name “Multi-Version”).
• Visibility Rules:
  Each row has two hidden system columns:
  1. xmin – Transaction ID when the row was created.
  2. xmax – Transaction ID when the row was deleted or updated.
• When a query runs, PostgreSQL checks:
  • If xmin <= current transaction ID → Row is visible (committed before or by current transaction).
  • If xmax is NULL or > current transaction ID → Row is still valid.
• Updates & Deletes:
  Instead of modifying the row directly:
  • An update creates a new row version with a new xmin and marks the old row with xmax.
  • A delete sets the xmax of the row (marking it as deleted).

Garbage Collection:

Understanding PostgreSQL for Effective Database Management

• Over time, many old row versions pile up.
• PostgreSQL uses a process called VACUUM to clean up:
  • Removes outdated/dead row versions (those not visible to any transaction).
• AutoVacuum runs automatically to handle this.

3. What is a schema in PostgreSQL, and how is it different from a database?

Ans: In PostgreSQL, a schema is a logical container inside a database which holds tables, views, indexes, functions, and other objects. It helps to organize and group the database objects, similar to folders in a file system.

4. Explain the different types of indexes in databases.

Ans: There are multiple types of indexes and their use cases:

5. What is a tablespace?

Tablespace in PostgreSQL is a storage location on the disk where the actual data files (like tables, indexes) are stored.

🔹 Purpose:

• Helps in controlling where data is stored physically.
• Useful for optimizing performance, balancing load, or using different storage systems (e.g., SSDs vs HDDs).

🔹 Default Tablespace: PostgreSQL comes with a default tablespace called pg_default.

🔹 Creating a Tablespace:
CREATE TABLESPACE myspace LOCATION '/path/to/directory';
🔹 Assigning Tablespace to a Table:
CREATE TABLE my_table (id INT) TABLESPACE myspace;

6. Explain the difference between CHAR, VARCHAR, and TEXT in PostgreSQL.

✅ Performance-wise, there’s no major difference between VARCHAR and TEXT.

7. What are the different types of joins available in PostgreSQL?

INNER JOIN: Returns rows with matching condition in both tables.
LEFT JOIN: Returns all left table rows + matched rows from right (NULL if no match).
RIGHT JOIN: Returns all right table rows + matched rows from left.
FULL OUTER JOIN: Returns all rows from both tables (NULL if no match).
CROSS JOIN: Cartesian product (all possible row combinations).
SELF JOIN: Joins table to itself (used for hierarchical data).

8. How does PostgreSQL handle JSON and JSONB data types?

✅ JSONB is preferred for: Supports GIN indexes for key/element search, efficient storage and faster querying.

9. What are sequences, and how do they work in PostgreSQL?

10. What is the role of the EXPLAIN and EXPLAIN ANALYZE commands?

EXPLAIN: Displays the execution plan without running the query.
EXPLAIN ANALYZE: Displays plan and executes query, showing actual timings.
Usage:

• Analyze query performance.
• Understand index usage, join strategy.
• Optimize query by checking cost estimates and runtime stats.

📚 Continue Learning:

• 🔗 Part 1: PL/SQL Interview Questions
• 🔗 Part 2: PL/SQL Interview Questions