Top 50 PostgreSQL Interview Questions and Answers

PostgreSQL Interview Questions and Answers are designed to help candidates understand the key concepts, architecture, and advanced features of one of the most powerful open-source relational databases. Whether you’re preparing for a developer, DBA, or data engineer role, these questions cover essential topics like indexing, transactions, replication, performance tuning, and query optimization.

In this tutorial, PostgreSQL Interview Questions and Answers are given to help you prepare for technical interviews and strengthen your understanding of PostgreSQL concepts.

PostgreSQL Interview Questions and Answers for Freshers

PostgreSQL Interview Questions and Answers for Freshers help beginners to understand core database concepts and prepare effectively for entry-level interviews.

Q1. What is PostgreSQL? How does it differ from other SQL databases like MySQL?

PostgreSQL is an open-source, object-relational database management system (ORDBMS) known for its robustness, scalability, and support for advanced data types. It was developed at the University of California, Berkeley, and is maintained by a large global community.

Q2. What are the key features of PostgreSQL?

PostgreSQL offers several powerful features that make it a preferred database for developers and enterprises:

• Open Source & Cross-Platform: Freely available and works on all major operating systems.
• ACID Compliance: Ensures data reliability through atomicity, consistency, isolation, and durability.
• Advanced Data Types: Supports JSON, XML, arrays, hstore, UUID, and geometric data.
• Extensibility: Allows users to define custom data types, operators, and functions.
• MVCC (Multi-Version Concurrency Control): Enables high performance with concurrent transactions.

Q3. How do you create a new database in PostgreSQL?

You can create a new database in PostgreSQL using either the SQL command or the command-line tool (createdb).

Run this command after connecting to PostgreSQL:

CREATE DATABASE database_name;

Q4. How do you create a table in PostgreSQL?

To create a table in PostgreSQL, you use the CREATE TABLE statement followed by the table name and column definitions.

Syntax:

CREATE TABLE table_name (
    column1 data_type [constraints],
    column2 data_type [constraints],
    ...
);

Q5. What is a primary key in PostgreSQL?

A primary key in PostgreSQL is a unique identifier for each record in a table. It ensures that no two rows have the same value in the key column(s) and that the value is never NULL.

• Each table can have only one primary key.
• It automatically creates a unique index for faster lookups.
• Primary keys can consist of one or multiple columns (composite key).

Q6. What is a foreign key? How do you implement it?

A foreign key in PostgreSQL is a constraint used to establish a relationship between two tables. It ensures that the value in one table matches a value in another table’s primary key (or unique key), maintaining referential integrity in the database.

• A foreign key links a child table to a parent table.
• It prevents actions that would break the link between related tables (e.g., deleting a parent record that still has related child records).
• You can define actions like ON DELETE CASCADE or ON UPDATE CASCADE to handle changes automatically.

Q7. What is the difference between DELETE and TRUNCATE?

Both DELETE and TRUNCATE are used to remove data from a table in PostgreSQL, but they differ in how they work and their performance.

Q8. What is MVCC in PostgreSQL?

MVCC (Multi-Version Concurrency Control) is a mechanism in PostgreSQL that allows multiple transactions to access the database concurrently without locking the entire table, ensuring high performance and data consistency.

Key Points:

• Concurrent Access: Multiple users can read and write data at the same time without waiting for locks.
• Versioning: PostgreSQL keeps multiple versions of a row. When a row is updated, the old version remains until no transaction needs it.
• Consistency: Each transaction sees a consistent snapshot of the database, preventing issues like dirty reads.
• No Read Locks: Readers never block writers, and writers never block readers, improving concurrency.

Q9. What is the SERIAL data type?

The SERIAL data type in PostgreSQL is a special integer type used to create auto-incrementing columns, typically for primary keys. It automatically generates a unique sequential value whenever a new row is inserted.

• Auto-Increment: You don’t need to manually provide a value. PostgreSQL assigns the next number automatically.
• Underlying Mechanism: SERIAL is essentially an integer column backed by a sequence that keeps track of the next value.

Q10. Explain ACID properties.

ACID properties are a set of key principles that ensure reliable and consistent database transactions in PostgreSQL and other relational databases. ACID stands for Atomicity, Consistency, Isolation, and Durability.

• Atomicity: All operations in a transaction succeed or none do.
• Consistency: Transactions maintain database rules and valid states.
• Isolation: Concurrent transactions don’t interfere with each other.
• Durability: Committed changes are permanent, even after a crash.

Q11. How do you backup a PostgreSQL database?

You can back up a PostgreSQL database using pg_dump or pg_dumpall, or through SQL commands.

pg_dump -U username -W -F c -b -v -f backup_file_name.backup database_name

pg_restore -U postgres -d mydatabase -v mydb_backup.backup

psql -U postgres -d mydatabase -f backup.sql

pg_basebackup -U replication_user -D /path/to/backup -Fp -Xs -P -v

Q12. What is a schema in PostgreSQL?

In PostgreSQL, a schema is a logical container or namespace within a database that organizes database objects such as tables, views, indexes, functions, and sequences. Schemas help avoid naming conflicts and allow multiple users or applications to use the same database without interfering with each other.

• Each database can have multiple schemas.
• By default, PostgreSQL creates a schema called public.
• Schemas help in access control; permissions can be granted at the schema level.
• Objects in different schemas can have the same name without conflict.

Q13. How do you handle exceptions in PL/pgSQL?

Syntax:

BEGIN
    -- Code that might raise an exception
EXCEPTION
    WHEN exception_name THEN
        -- Handle the exception
END;

Q14. What are CTEs (Common Table Expressions)?

A CTE (Common Table Expression) in PostgreSQL is a temporary named result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. CTEs make complex queries easier to read, write, and maintain.

• Defined using the WITH keyword.
• Can be recursive (helpful for hierarchical data).
• Exists only for the duration of a single query.

Q15. What is JSONB in PostgreSQL?

In PostgreSQL, JSONB (Binary JSON) is a binary storage format for JSON data that allows efficient storage, querying, and indexing of JSON documents. It is different from the plain JSON type, which stores data as text.

Key points JSONB

• Binary Format: Stores JSON in a binary format, enabling faster processing.
• Indexing Support: Can create GIN or B-tree indexes on JSONB fields for quick queries.
• Query Efficiency: Supports advanced operators and functions like @>, ->, ->>, #>> for filtering and accessing nested data.
• Validation: Ensures the stored data is valid JSON.
• Flexibility: Allows storing semi-structured or hierarchical data within relational tables.

Q16. How do you use window functions?

Window functions in PostgreSQL perform calculations across a set of table rows related to the current row, without grouping the result into a single output row. They are often used for ranking, running totals, averages, and cumulative calculations.

Example: Company Employee Salaries

SELECT employee_name, department, salary,
    RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM employees;

Output:

• Rahul Verma has the highest salary, so he’s ranked 1.
• Priya and Neha both earn ₹90,000, so they share Rank 2.
• Since rank 3 is skipped (due to a tie), Ankit gets Rank 4.

Q17. How do you grant permissions?

In PostgreSQL, you use the GRANT command to give specific permissions (privileges) to users or roles on database objects like tables, schemas, or databases.

Q18. What is the difference between CHAR and VARCHAR?

In PostgreSQL, both CHAR and VARCHAR are used to store character strings, but they differ in storage behavior and performance.

Example:

CREATE TABLE example (
  code CHAR(5),
  name VARCHAR(50)
);

Use CHAR for fixed-length strings and VARCHAR for variable-length text, VARCHAR is more flexible and commonly used in PostgreSQL.

Q19. How do you delete a database?

In PostgreSQL, you can delete (drop) an entire database using the DROP DATABASE command. This permanently removes the database and all its data, so use it carefully.

Syntax: DROP DATABASE database_name;

Q20. What is a trigger?

Trigger in PostgreSQL is a database callback function that automatically executes in response to a specific event on a table or view, such as an INSERT, UPDATE, or DELETE operation. Triggers help enforce rules, maintain audit logs, or automate data changes.

Syntax:

CREATE TRIGGER trigger_name
{ BEFORE | AFTER | INSTEAD OF } { INSERT | UPDATE | DELETE | TRUNCATE }
ON table_name
FOR EACH { ROW | STATEMENT }
EXECUTE FUNCTION function_name();

Q21. What is the default port for PostgreSQL?

The default port for PostgreSQL is 5432. It’s the default TCP/IP port used for PostgreSQL client–server communication.

PostgreSQL Interview Questions and Answers for Intermediate

PostgreSQL Intermediate Interview Questions focus on enhancing your understanding of database architecture, indexing, and performance tuning for practical, real-world applications.

Q22. Explain partitioning in PostgreSQL.

Types of Partitioning

• Data is divided based on a range of values.
• Example: Partition sales data by year or month.

CREATE TABLE sales (
    id SERIAL,
    sale_date DATE,
    amount NUMERIC
) PARTITION BY RANGE (sale_date);

CREATE TABLE sales_2024 PARTITION OF sales
    FOR VALUES FROM ('2024-01-01') TO ('2024-12-31');

• Data is divided based on specific values in a column.
• Example: Partition by region or category.

CREATE TABLE customers (
    id SERIAL,
    region TEXT
) PARTITION BY LIST (region);

CREATE TABLE customers_asia PARTITION OF customers
    FOR VALUES IN ('Asia');

• Data is divided using a hash function on the partition key.
• Useful for evenly distributing data.

CREATE TABLE logs (
    id SERIAL,
    user_id INT
) PARTITION BY HASH (user_id);

CREATE TABLE logs_part1 PARTITION OF logs FOR VALUES WITH (MODULUS 4, REMAINDER 0);  

Q23. How do you optimize slow queries?

Query optimization in PostgreSQL is the process of improving the performance of slow-running SQL queries by analyzing how the database executes them and applying techniques to reduce execution time, memory usage, and I/O operations.

PostgreSQL uses a query planner and optimizer that evaluates multiple execution strategies for a given query and chooses the most efficient one based on table statistics, indexes, and available system resources.

Optimization typically involves:

• Creating indexes to speed up data retrieval.
• Using the EXPLAIN and EXPLAIN ANALYZE commands to understand execution plans.
• Writing efficient queries (avoiding unnecessary joins or SELECT *).
• Vacuuming and analyzing tables to keep planner statistics accurate.
• Partitioning large tables for faster access.
• Tuning PostgreSQL parameters like work_mem, shared_buffers, and effective_cache_size for better resource usage.

Q24. What is Write-Ahead Logging (WALa)?

Key benefits of Write-Ahead Logging:

• Durability: No data loss after a crash — committed transactions can be recovered.
• Crash Recovery: The WAL allows PostgreSQL to rebuild the database state after failure.
• Performance: Sequential writes to WAL are faster than random writes to data files.
• Replication & Backup: WAL files are used in streaming replication and point-in-time recovery (PITR).

Q25. How do you handle full-text search?

Full-text search in PostgreSQL is a built-in feature that allows you to efficiently search natural-language text (like blog posts, comments, or articles) for relevant words or phrases — rather than doing simple pattern matches with LIKE. It’s designed for fast, intelligent text searching with ranking and stemming (word variations like “run”, “running”, “ran”).

Q26. What is replication in PostgreSQL?

Replication in PostgreSQL is the process of copying data from one database server (the primary) to one or more others (the replicas or standbys) to ensure data availability, fault tolerance, and load balancing. It allows you to have multiple copies of your database synchronized in real time or near real time.

Advantages of Replication in PostgreSQL:

Q27. What is the difference between synchronous and asynchronous replication?

Q28. What is a materialized view?

Materialized View in PostgreSQL is a database object that stores the result of a query physically on disk, unlike a normal view which is virtual and computes results on the fly.

• Materialized views store results physically, unlike normal views.
• Manual refresh is required after underlying table changes.
• Can be indexed for faster queries.
• Ideal for aggregations, reporting, and analytics.

Q29. Explain transaction isolation levels.

Transaction isolation levels define how concurrent transactions interact with each other and determine what data a transaction can see while other transactions are running. They help manage concurrency issues like dirty reads, non-repeatable reads, and phantom reads, balancing data consistency with system performance.

PostgreSQL supports four standard isolation levels:

• Transactions can see uncommitted changes (dirty reads).
• In PostgreSQL, it behaves the same as Read Committed.

• A transaction sees only committed changes from other transactions.
• Prevents dirty reads, but non-repeatable reads can occur (the same row read twice may have different values).

• A transaction sees a consistent snapshot of the database taken at the start.
• Prevents dirty reads and non-repeatable reads.

• Highest level of isolation.
• Transactions behave as if they are executed one after another, fully preventing dirty reads, non-repeatable reads, and phantom reads.
• Ensures strict consistency at the cost of potentially higher transaction conflicts.

Q30. How do you monitor performance?

Q31. What is pgAdmin?

pgAdmin is a free, open-source, web-based GUI (Graphical User Interface) tool for managing and administering PostgreSQL databases. It provides an intuitive interface for database developers and administrators to perform tasks without writing complex SQL commands manually.

Key Features of pgAdmin

• Database Management: Create, modify, and delete databases, tables, indexes, and other objects.
• Query Tool: Execute SQL queries and view results. Supports query formatting, execution history, and graphical explain plans.
• Backup and Restore: Provides easy-to-use wizards to backup or restore databases.
• Monitoring: Monitor server activity, sessions, locks, and query performance.
• User Management: Manage roles, permissions, and access control for PostgreSQL users.

Q32. How do you handle JSON data?

You can Handle JSON data in PostgreSQL by:

• json: Stores JSON as text, validates format.
• jsonb: Binary format, optimized for indexing and querying (preferred).

2. Creating JSON Columns

CREATE TABLE users (id SERIAL, info JSONB);

3. Inserting JSON Data

INSERT INTO users (info) VALUES ('{"age":25, "city":"Mumbai"}');

4. Querying JSON Data

• Access key: info->'age'
• Access key as text: info->>'city'
• Filter: WHERE info->>'city' = 'Delhi'

Q33. What is the difference between UNION and UNION ALL?

Q34. What are locks in PostgreSQL?

Locks in PostgreSQL are mechanisms that control concurrent access to database objects (like tables, rows, or sequences) to maintain data consistency and integrity. They prevent conflicts when multiple transactions try to read or write the same data simultaneously.

Q35. How do you avoid deadlocks?

Deadlock occurs when two or more transactions wait indefinitely for each other’s locks, creating a cycle. PostgreSQL detects deadlocks and automatically rolls back one transaction to resolve it.

Strategies to avoid deadlocks in PostgreSQL are:

1. Access objects in a consistent order: Always lock tables or rows in the same order across transactions.
2. Keep transactions short: Commit or rollback quickly to reduce lock holding time.
3. Use lower isolation levels when possible: Read Committed or Read Uncommitted can reduce lock contention.
4. Avoid user interaction during transactions: Don’t wait for input while holding locks.

Q36. What is pg_dump vs. pg_basebackup?

Q37. How do you change a user's password?

In PostgreSQL, you can change a user's password using SQL commands or psql commands.

1. Using ALTER USER

ALTER USER username WITH PASSWORD 'new_password';

2. Using psql Meta Command

\password username

If you are using the psql command-line interface. You’ll be prompted to enter the new password twice.

Q38. What is the role of pg_hba.conf?

Role of pg_hba.conf

• Controls Client Authentication: Determines who can connect, from where, and how.
• Location: Found in PostgreSQL’s data directory (e.g., /etc/postgresql/<version>/main/pg_hba.conf).
• Defines Access Rules: Specifies which users can access which databases.
• Authentication Methods: Supports methods like trust, md5, scram-sha-256, peer, etc.

Q40. What are advisory locks?

Advisory locks are user-controlled locks that allow applications to manage concurrency manually without locking actual database rows or tables. They are application-level locks, not automatically enforced by PostgreSQL. They used to coordinate access to shared resources (like files, records, or custom business logic) outside normal table locking.

PostgreSQL Advanced Interview Questions

Q41. Explain GIN vs. GiST indexes.

Q42. How do you set up logical replication?

Logical replication in PostgreSQL allows data to be replicated at the table level rather than copying entire databases or clusters. It replicates data changes (INSERT, UPDATE, DELETE) from a publisher to one or more subscribers using a publish–subscribe model.

Steps to Set Up Logical Replication

1. Enable Logical Replication on the Publisher

wal_level = logical
max_wal_senders = 10
max_replication_slots = 10

sudo systemctl restart postgresql

host    replication    replicator    192.168.1.10/32    md5

SELECT pg_reload_conf();

CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'replica123';

CREATE PUBLICATION my_publication FOR TABLE employees, departments;

CREATE PUBLICATION my_publication FOR ALL TABLES;

CREATE SUBSCRIPTION my_subscription
CONNECTION 'host=192.168.1.5 port=5432 dbname=company user=replicator password=replica123'
PUBLICATION my_publication;

SELECT * FROM pg_stat_subscription;

SELECT * FROM pg_stat_replication;

CREATE INDEX index_name
ON table_name
USING BRIN (column_name);

CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    title TEXT,
    content TEXT
);

CREATE EXTENSION IF NOT EXISTS bloom;
CREATE INDEX index_name
ON table_name
USING bloom (column1, column2, ...);

• Identify source database, schema, data volume, and dependencies.
• Decide on full, incremental, or hybrid migration.
• Backup the source database before starting.

• Map data types (VARCHAR2 → VARCHAR, NUMBER → NUMERIC).
• Recreate tables, indexes, sequences, and constraints in PostgreSQL.
• Rewrite stored procedures and triggers in PL/pgSQL if needed.

• Dump & Restore – CSV or SQL export/import.
• ETL Tools – Talend, Pentaho, Apache NiFi.
• Foreign Data Wrappers (FDW) – Direct queries from PostgreSQL.
• Migration Services – AWS DMS, DBConvert for automated migration.

• Export and transform data from the source.
• Load into PostgreSQL using \copy or pgloader.
• Preserve referential integrity and handle constraints.

• Verify row counts, data consistency, and checksums.
• Test queries, indexes, and triggers for correctness.
• Ensure application functionality works with PostgreSQL.

Q50. What is logical decoding? Explain Step-by-Step Process. 

Logical Decoding is a PostgreSQL feature that allows you to stream changes (INSERT, UPDATE, DELETE) made to the database in a human-readable (logical) format, instead of the default binary format used in Write-Ahead Logs (WAL). It’s the foundation for logical replication, change data capture (CDC), and data integration with external systems.

Step-by-Step Process

1. WAL (Write-Ahead Log) Generation: Every change in PostgreSQL is written to the WAL as a binary record for durability and crash recovery.
2. Decoding WAL into Logical Changes: Logical Decoding converts these binary WAL records into logical change sets  i.e., what rows changed and how.
3. Replication Slot Creation: A replication slot ensures that the WAL files needed for decoding are not deleted until they are consumed.
4. Output Plugin: The output plugin defines the format in which decoded changes are sent to clients (e.g., JSON, text).
5. Client Consumption: The client reads these logical changes and processes them for replication or analytics.

Conclusion

PostgreSQL is a powerful, feature-rich database system trusted by developers worldwide. These interview questions help you strengthen core concepts, from basics to advanced features like replication and tuning. Keep learning and practicing to master PostgreSQL and excel in your next interview.

.NET full stack developers climb to senior roles 15% faster. Accelerate your growth with our .NET Full Stack Developer Course and unlock leadership positions!

Take our Dbms skill challenge to evaluate yourself!

In less than 5 minutes, with our skill challenge, you can identify your knowledge gaps and strengths in a given skill.

GET FREE CHALLENGE