PostgreSQL Interview Questions and Answers
Last Updated : 21 Jan, 2026
Are you preparing for a PostgreSQL interview? PostgreSQL is a powerful open-source RDBMS widely used for its reliability, scalability, and advanced features, making strong preparation essential for success.

Suitable for both beginners and experienced professionals
Covers a curated list of important PostgreSQL interview questions and answers
PostgreSQL Basic Interview Questions
PostgreSQL Basic Interview Questions covers fundamental concepts that are essential for anyone preparing for a PostgreSQL interview. Explore these essential questions to build a strong understanding and boost our confidence in PostgreSQL basics.

1. What Is PostgreSQL, And How Does It Differ From Other SQL Databases?

PostgreSQL is an open-source relational database management system (RDBMS) that supports both SQL for relational and JSON for non-relational queries. 
It differs from other SQL databases by offering advanced features like support for complex queries, foreign keys, triggers, and updatable views. 
It also supports user-defined types, functions, and operators, which makes it highly extensible.

2. What Are The Key Features Of PostgreSQL?
Key features of PostgreSQL include:

ACID compliance (Atomicity, Consistency, Isolation, Durability)
Support for foreign keys, joins, views, triggers, and stored procedures
Full-text search
Advanced data types such as arrays, hstore, and JSONB
Extensibility (user-defined functions, operators, types)
MVCC (Multi-Version Concurrency Control) for handling concurrent transactions

3. How to Create a New Database In PostgreSQL?
To create a new database in PostgreSQL, you can use the CREATE DATABASE command. For example:

CREATE DATABASE mydatabase;

4. How to Create a New Table In PostgreSQL?
To create a new table in PostgreSQL, you can use the CREATE TABLE command. For example:

CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    position VARCHAR(100),
    salary NUMERIC,
    hire_date DATE
);
5. What is a Primary Key in PostgreSQL?
A primary key is a column or a set of columns that uniquely identifies each row in a table. It ensures that the values in the primary key column(s) are unique and not null.
 For example:

CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    name VARCHAR(100)
);
6. How to Insert Data Into a Table in PostgreSQL?
To insert data into a table, you can use the INSERT INTO command. For example:

INSERT INTO employees (name, position, salary, hire_date)
VALUES ('John Doe', 'Software Engineer', 80000, '2021-01-15');

7. How to Query Data From a Table in PostgreSQL?
To query data from a table, you can use the SELECT statement. For example:

SELECT * FROM employees;

8. What is a Foreign Key in PostgreSQL?

A foreign key is a column or a set of columns that establishes a link between data in two tables. It ensures that the value in the foreign key column matches a value in the referenced column of another table, 
enforcing referential integrity. For example:

CREATE TABLE departments (
    department_id SERIAL PRIMARY KEY,
    department_name VARCHAR(100)
);

CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);
9. How to Update Data in a Table in PostgreSQL?
To update data in a table, you can use the UPDATE statement. For example:

UPDATE employees
SET salary = 85000
WHERE name = 'John Doe';
10. How to Delete Data From a Table in PostgreSQL?
To delete data from a table, you can use the DELETE statement. For example:

DELETE FROM employees
WHERE name = 'John Doe';
11. 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 reuse them as if they were tables. For example:

CREATE VIEW high_salary_employees AS
SELECT name, salary
FROM employees
WHERE salary > 80000;
12. How to Create an Index in PostgreSQL?
To create an index in PostgreSQL, you can use the CREATE INDEX statement. Indexes improve query performance by allowing faster retrieval of records. For example:

CREATE INDEX idx_employee_name ON employees(name);
13. What Is A Transaction In PostgreSQL?
A transaction is a sequence of one or more SQL statements that are executed as a single unit of work. Transactions ensure data integrity and consistency. You can start a transaction with the BEGIN command and end it with COMMIT or ROLLBACK. For example:

BEGIN;
UPDATE employees SET salary = 90000 WHERE name = 'John Doe';
COMMIT;
14. What is MVCC in PostgreSQL?
MVCC (Multi-Version Concurrency Control) is a concurrency control method used by PostgreSQL to handle simultaneous transactions. It allows multiple transactions to read and write data without blocking each other by maintaining multiple versions of data.

15. How to Handle Backup and Restore in PostgreSQL?
To backup a PostgreSQL database, you can use the pg_dump utility. To restore a database, you can use the psql utility. For example:

pg_dump mydatabase > mydatabase_backup.sql
psql mydatabase < mydatabase_backup.sql

PostgreSQL Intermediate Interview Questions
This section covers advanced PostgreSQL topics such as complex SQL queries, data modeling, performance tuning, and transaction management. These questions help enhance skills for both database developers and administrators, preparing you for more challenging roles in the field.

16. What is a Schema in PostgreSQL, and How to Use It?
A schema in PostgreSQL is a way to organize and group database objects such as tables, views, and functions. It helps manage namespaces, so objects with the same name can exist in different schemas. To create and use a schema, you can use the following commands:

CREATE SCHEMA myschema;
CREATE TABLE myschema.mytable (id SERIAL PRIMARY KEY, name VARCHAR(100));
SELECT * FROM myschema.mytable;
17. How to Perform a Backup and Restore in PostgreSQL?
To backup a PostgreSQL database, we use the pg_dump utility, and to restore it, we use the psql utility. For example:

pg_dump mydatabase > mydatabase_backup.sql
psql mydatabase < mydatabase_backup.sql
18. Explain the Concept of Transactions in PostgreSQL.
Transactions in PostgreSQL are used to execute a series of operations as a single unit of work. They ensure that either all operations are executed successfully (committed) or none (rolled back), maintaining data integrity. Use the BEGIN, COMMIT, and ROLLBACK commands to manage transactions:

BEGIN;
UPDATE employees SET salary = 90000 WHERE name = 'John Doe';
COMMIT;
19. What are Triggers in PostgreSQL, and How to Create Them?
Triggers are special procedures that automatically execute when certain events (INSERT, UPDATE, DELETE) occur on a table. To create a trigger:

CREATE FUNCTION update_timestamp() RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_timestamp
BEFORE UPDATE ON employees
FOR EACH ROW
EXECUTE FUNCTION update_timestamp();
20. How to Implement Foreign Keys in PostgreSQL?
Foreign keys are used to establish a relationship between two tables. They ensure referential integrity by requiring that the value in one table must match a value in another table. To implement a foreign key:

CREATE TABLE departments (
    department_id SERIAL PRIMARY KEY,
    department_name VARCHAR(100)
);

CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);
21. What is a View in PostgreSQL, and How to Create One?
A view is a virtual table based on the result of a SELECT query. It allows us to encapsulate complex queries and reuse them as if they were tables. To create a view:

CREATE VIEW high_salary_employees AS
SELECT name, salary
FROM employees
WHERE salary > 80000;
22. How to Handle Exceptions in PL/pgSQL?
In PL/pgSQL, we can handle exceptions using the EXCEPTION block. Here's an example:

DO $$
BEGIN
    -- Attempt to insert a duplicate key
    INSERT INTO employees (employee_id, name) VALUES (1, 'John Doe');
EXCEPTION
    WHEN unique_violation THEN
        RAISE NOTICE 'Duplicate key error!';
END;
$$;
23. What are CTEs (Common Table Expressions) in PostgreSQL?
Common Table Expressions (CTEs) are temporary result sets that we can reference within a SELECT, INSERT, UPDATE, or DELETE statement. CTEs improve query readability and organization. To use a CTE:

WITH employee_salaries AS (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
)
SELECT * FROM employee_salaries;
24. How to Use Window Functions in PostgreSQL?
Window functions perform calculations across a set of table rows related to the current row. They are used for ranking, running totals, and moving averages. For example:

SELECT name, salary, 
    RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM employees;
25. Explain the Concept of JSON Data Types in PostgreSQL.
PostgreSQL supports JSON data types, which allow us to store and query JSON (JavaScript Object Notation) data. This enables semi-structured data storage. You can use json or jsonb types, where jsonb is a binary format that is more efficient for indexing. Example:

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    details JSONB
);

INSERT INTO products (details) VALUES ('{"name": "Laptop", "price": 1200}');
26. How to Implement Partitioning in PostgreSQL?
Partitioning divides a large table into smaller, more manageable pieces, improving performance and maintenance. PostgreSQL supports range and list partitioning. Example:

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

CREATE TABLE sales_2021 PARTITION OF sales
FOR VALUES FROM ('2021-01-01') TO ('2022-01-01');
27. What Is The pg_hba.conf File, And What Is Its Purpose?
The pg_hba.conf file controls client authentication in PostgreSQL. It specifies which clients are allowed to connect, their authentication methods, and the databases they can access. It is essential for securing our PostgreSQL server.

28. How Do You Optimize Queries In PostgreSQL?
To optimize queries, we can:

Use indexes to speed up data retrieval
Analyze and vacuum tables regularly
Write efficient SQL queries (avoid SELECT *)
Use EXPLAIN to understand query execution plans
Optimize joins and subqueries
29. Explain The Concept Of Table Inheritance In PostgreSQL.
Table inheritance allows a table to inherit columns from a parent table. This feature helps organize data hierarchically. Example:

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE managers (
    department VARCHAR(100)
) INHERITS (employees);
30. How to Perform Full-Text Search in PostgreSQL?
Full-text search allows you to search for text within a large corpus of documents. PostgreSQL supports full-text search using tsvector and tsquery types. Example:

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

UPDATE documents SET tsvector_content = to_tsvector(content);

SELECT * FROM documents
WHERE tsvector_content @@ to_tsquery('search_term');
PostgreSQL Advanced Interview Questions
This section covers in-depth PostgreSQL topics like index optimization, replication, partitioning, and advanced data handling techniques. Tackling these questions will enhance expertise, making us well-prepared for senior roles and technical interviews.

31. What Is The WAL (Write-Ahead Logging) In PostgreSQL, And How Does It Work?
Write-Ahead Logging (WAL) in PostgreSQL is a method used to ensure data integrity. Before any changes are made to the database, the changes are first recorded in a log (WAL). 
This log helps in recovering the database to a consistent state in case of a crash. WAL operates by writing the changes to a log file before they are applied to the database, 
ensuring that the data is safe even if a failure occurs.

32. How to Configure Replication in PostgreSQL?
Replication in PostgreSQL involves copying data from one database server (master) to another (slave). To configure replication:

Edit postgresql.conf on the master server to enable WAL archiving and set up replication parameters.
wal_level = replica
max_wal_senders = 3
archive_mode = on
archive_command = 'cp %p /var/lib/postgresql/wal_archive/%f'
Create a replication user on the master.
CREATE ROLE replication_user WITH REPLICATION PASSWORD 'password' LOGIN;
Set up pg_hba.conf to allow replication connections from the slave.
host replication replication_user 192.168.1.10/32 md5
On the slave, set up recovery.conf with the connection information
standby_mode = 'on'
primary_conninfo = 'host=192.168.1.1 port=5432 user=replication_user password=password'
trigger_file = '/tmp/postgresql.trigger'
Start the slave server, and it will begin replicating data from the master.
33. What are the Different Types of Indexes Available in PostgreSQL?
PostgreSQL supports several types of indexes to optimize query performance:

B-Tree Indexes: The default type, suitable for most queries.
Hash Indexes: Used for equality comparisons.
GiST (Generalized Search Tree) Indexes: Supports complex data types and queries, like geometric data.
SP-GiST (Space-Partitioned Generalized Search Tree) Indexes: Supports partitioned data types, like points in a plane.
GIN (Generalized Inverted Index) Indexes: Efficient for full-text search and JSONB data.
BRIN (Block Range INdex) Indexes: Suitable for large tables with naturally ordered data.
34. Explain the Concept of MVCC (Multi-Version Concurrency Control) in PostgreSQL.
Multi-Version Concurrency Control (MVCC) in PostgreSQL is a method to handle concurrent transactions without locking. It allows multiple transactions to access the database simultaneously by maintaining multiple versions of data. Each transaction sees a consistent snapshot of the database, ensuring isolation. MVCC helps avoid conflicts and improves performance in a multi-user environment.

35. How to Use the pg_stat_activity View to Monitor PostgreSQL?
The pg_stat_activity view provides information about the current activity in the PostgreSQL database. It includes details like active queries, process IDs, user information, and query start times. To use it:

SELECT pid, usename, application_name, state, query
FROM pg_stat_activity;
This query lists all active connections and their current state.

36. What are the Different Isolation Levels in PostgreSQL?
PostgreSQL supports four isolation levels to control how transactions interact:

Read Uncommitted: Transactions can see uncommitted changes made by other transactions.
Read Committed: A transaction only sees changes committed before it began.
Repeatable Read: A transaction sees a consistent snapshot of the database and no new changes made by other transactions during its execution.
Serializable: Ensures complete isolation, transactions appear to run sequentially.
37. How to Handle Deadlocks in PostgreSQL?
Deadlocks occur when two or more transactions block each other. PostgreSQL automatically detects deadlocks and terminates one of the transactions to resolve it. To minimize deadlocks:

Access tables in a consistent order.
Keep transactions short and simple.
Use explicit locking carefully.
To investigate deadlocks, check the pg_locks view and PostgreSQL logs.
38. Explain the Concept of the Query Planner and Optimizer in PostgreSQL.
The query planner and optimizer in PostgreSQL analyze SQL queries to determine the most efficient execution plan. The planner uses statistics about the tables and indexes to estimate the cost of different execution strategies and chooses the one with the lowest cost. The optimizer considers factors like join methods, index usage, and query rewriting to improve performance.

39. How Do You Implement Sharding In PostgreSQL?
Sharding involves partitioning data across multiple servers to distribute load and improve performance. PostgreSQL doesn't have built-in sharding but can be implemented using logical replication, partitioning, and custom routing logic in the application. Tools like Citus can also be used to add sharding capabilities to PostgreSQL.

40. What are the Different Types of Backup Strategies in PostgreSQL?
PostgreSQL supports several backup strategies:

SQL Dump: Using pg_dump to create a logical backup of the database.
File System Level Backup: Using tools like rsync to copy the data directory while the server is offline.
Continuous Archiving: Using WAL archiving and pg_basebackup for continuous backups.
Logical Replication: Setting up logical replication for real-time data backup and recovery.
PostgreSQL Query-Based Interview Questions
This section focuses on practical SQL query challenges in PostgreSQL, including complex joins, subqueries, aggregate functions, and window functions. Mastering these questions will strengthen your query-building skills and prepare you to handle real-world database scenarios confidently.

We have created some table for the reference of the questions like: Departments Table, Projects Table, Employees Table, Tasks Table, and TimeLogs Table

CREATE TABLE Departments (
    DepartmentID SERIAL PRIMARY KEY,
    DepartmentName VARCHAR(100) NOT NULL
);

INSERT INTO Departments (DepartmentID, DepartmentName) VALUES
(1, 'Engineering'),
(2, 'Design'),
(3, 'Management');
Output

DepartmentID	DepartmentName
1	Engineering
2	Design
3	Management
CREATE TABLE Projects (
    ProjectID SERIAL PRIMARY KEY,
    ProjectName VARCHAR(100) NOT NULL,
    Budget DECIMAL(15, 2),
    StartDate DATE,
    EndDate DATE,
    DepartmentID INT REFERENCES Departments(DepartmentID)
);

INSERT INTO Projects (ProjectName, Budget, StartDate, EndDate, DepartmentID) VALUES
('Project Alpha', 100000, '2021-01-01', '2021-12-31', 1),
('Project Beta', 200000, '2021-02-01', '2021-11-30', 2),
('Project Gamma', 150000, '2021-03-01', '2022-03-01', 3);
Output

ProjectID	ProjectName	Budget	StartDate	EndDate	DepartmentID
1	Project Alpha	100000.00	2021-01-01	2021-12-31	1
2	Project Beta	200000.00	2021-02-01	2021-11-30	2
3	Project Gamma	150000.00	2021-03-01	2022-03-01	3
CREATE TABLE Employees (
    EmployeeID SERIAL PRIMARY KEY,
    Name VARCHAR(100) NOT NULL,
    Age INT,
    Position VARCHAR(100),
    Salary DECIMAL(10, 2),
    DepartmentID INT REFERENCES Departments(DepartmentID),
    HireDate DATE
);

INSERT INTO Employees (Name, Age, Position, Salary, DepartmentID, HireDate) VALUES
('John Doe', 28, 'Software Engineer', 80000, 1, '2021-01-15'),
('Jane Smith', 34, 'Project Manager', 95000, 1, '2019-06-23'),
('Emily Johnson', 41, 'CTO', 150000, 3, '2015-03-12'),
('Michael Brown', 29, 'Software Engineer', 85000, 1, '2020-07-30'),
('Sarah Davis', 26, 'UI/UX Designer', 70000, 2, '2022-10-12');
Output

EmployeeID	Name	Age	Position	Salary	DepartmentID	HireDate
1	John Doe	28	Software Engineer	80000.00	1	2021-01-15
2	Jane Smith	34	Project Manager	95000.00	1	2019-06-23
3	Emily Johnson	41	CTO	150000.00	3	2015-03-12
4	Michael Brown	29	Software Engineer	85000.00	1	2020-07-30
5	Sarah Davis	26	UI/UX Designer	70000.00	2	2022-10-12
CREATE TABLE Tasks (
    TaskID SERIAL PRIMARY KEY,
    TaskName VARCHAR(100) NOT NULL,
    ProjectID INT REFERENCES Projects(ProjectID),
    AssignedTo INT REFERENCES Employees(EmployeeID),
    Status VARCHAR(50),
    Deadline DATE
);

INSERT INTO Tasks (TaskName, ProjectID, AssignedTo, Status, Deadline) VALUES
('Design Database', 1, 1, 'Completed', '2021-03-01'),
('Develop API', 1, 1, 'In Progress', '2021-06-01'),
('Create UI', 2, 5, 'Not Started', '2021-09-01'),
('Project Planning', 3, 2, 'Completed', '2021-05-01'),
('Market Analysis', 3, 3, 'In Progress', '2021-12-01');
Output

TaskID	TaskName	ProjectID	AssignedTo	Status	Deadline
1	Design Database	1	1	Completed	2021-03-01
2	Develop API	1	1	In Progress	2021-06-01
3	Create UI	2	5	Not Started	2021-09-01
4	Project Planning	3	2	Completed	2021-05-01
5	Market Analysis	3	3	In Progress	2021-12-01
CREATE TABLE TimeLogs (
    LogID SERIAL PRIMARY KEY,
    EmployeeID INT REFERENCES Employees(EmployeeID),
    TaskID INT REFERENCES Tasks(TaskID),
    HoursWorked DECIMAL(5, 2),
    LogDate DATE
);

INSERT INTO TimeLogs (EmployeeID, TaskID, HoursWorked, LogDate) VALUES
(1, 1, 40, '2021-02-01'),
(1, 2, 35, '2021-04-01'),
(5, 3, 20, '2021-07-01'),
(2, 4, 25, '2021-03-01'),
(3, 5, 30, '2021-10-01');
Output

LogID	EmployeeID	TaskID	HoursWorked	LogDate
1	1	1	40.00	2021-02-01
2	1	2	35.00	2021-04-01
3	5	3	20.00	2021-07-01
4	2	4	25.00	2021-03-01
5	3	5	30.00	2021-10-01
41. Find all Employees Who have Logged More than 30 Hours on a Single Task
Query:

SELECT E.Name, T.TaskName, TL.HoursWorked
FROM Employees E
JOIN TimeLogs TL ON E.EmployeeID = TL.EmployeeID
JOIN Tasks T ON TL.TaskID = T.TaskID
WHERE TL.HoursWorked > 30;
Output

Name	TaskName	HoursWorked
John Doe	Design Database	40.00
John Doe	Develop API	35.00
Explanation:

This query joins the Employees, TimeLogs, and Tasks tables and filters the results to show employees who have logged more than 30 hours on a single task.

42. List the Total Hours Worked by Each Employee on All Projects
Query:

SELECT E.Name, SUM(TL.HoursWorked) AS TotalHoursWorked
FROM Employees E
JOIN TimeLogs TL ON E.EmployeeID = TL.EmployeeID
GROUP BY E.Name;
Output

Name	TotalHoursWorked
John Doe	75.00
Sarah Davis	20.00
Jane Smith	25.00
Emily Johnson	30.00
Explanation:

This query sums the total hours worked by each employee by grouping the results by the employee name.

43. Find the Average Salary of Employees in Each Department Where the Average Salary is Greater Than 75,000
Query:

SELECT D.DepartmentName, AVG(E.Salary) AS AvgSalary
FROM Departments D
JOIN Employees E ON D.DepartmentID = E.DepartmentID
GROUP BY D.DepartmentName
HAVING AVG(E.Salary) > 75000;
Output

DepartmentName	AvgSalary
Engineering	86666.67
Management	150000.00
Explanation:

This query calculates the average salary of employees in each department and filters the results to show only those departments where the average salary is greater than 75,000.

44. Retrieve the Details of Projects That Have More Than 2 Tasks Assigned
Query:

SELECT P.ProjectName, P.Budget, P.StartDate, P.EndDate, D.DepartmentName
FROM Projects P
JOIN Tasks T ON P.ProjectID = T.ProjectID
JOIN Departments D ON P.DepartmentID = D.DepartmentID
GROUP BY P.ProjectName, P.Budget, P.StartDate, P.EndDate, D.DepartmentName
HAVING COUNT(T.TaskID) > 2;
Output

ProjectName	Budget	StartDate	EndDate	DepartmentName
Project Alpha	100000.00	2021-01-01	2021-12-31	Engineering
Explanation:

This query groups the tasks by project and filters the results to show projects that have more than 2 tasks assigned.

45. List the Employees Who Have Not Been Assigned to Any Tasks
Query:

SELECT E.Name
FROM Employees E
LEFT JOIN Tasks T ON E.EmployeeID = T.AssignedTo
WHERE T.AssignedTo IS NULL;
Output

Name
Jane Smith
Michael Brown
Explanation:

This query performs a left join between the Employees and Tasks tables and filters the results to show employees who have not been assigned to any tasks.

46. Find the Project with the Highest Total Budget and Display Its Department Name
Query:

SELECT P.ProjectName, P.Budget, D.DepartmentName
FROM Projects P
JOIN Departments D ON P.DepartmentID = D.DepartmentID
ORDER BY P.Budget DESC
LIMIT 1;
Output

ProjectName	Budget	DepartmentName
Project Beta	200000.00	Design
Explanation:

This query orders the projects by budget in descending order and limits the result to show only the project with the highest budget, along with its department name.

47. Calculate the Total Budget Allocated to Each Department
Query:

SELECT D.DepartmentName, SUM(P.Budget) AS TotalBudget
FROM Departments D
JOIN Projects P ON D.DepartmentID = P.DepartmentID
GROUP BY D.DepartmentName;
Output

DepartmentName	TotalBudget
Engineering	100000.00
Design	200000.00
Management	150000.00
Explanation:

This query sums the total budget allocated to each department by grouping the results by the department name.

48. List the Names of Employees Who Have Worked on 'Project Alpha'
Query:

SELECT DISTINCT E.Name
FROM Employees E
JOIN Tasks T ON E.EmployeeID = T.AssignedTo
JOIN Projects P ON T.ProjectID = P.ProjectID
WHERE P.ProjectName = 'Project Alpha';
Output

Name
John Doe
Explanation:

This query joins the Employees, Tasks, and Projects tables and filters the results to show employees who have worked on 'Project Alpha'.

49. Find the Department with the Most Employees and Display the Number of Employees
Query:

SELECT D.DepartmentName, COUNT(E.EmployeeID) AS NumberOfEmployees
FROM Departments D
JOIN Employees E ON D.DepartmentID = E.DepartmentID
GROUP BY D.DepartmentName
ORDER BY NumberOfEmployees DESC
LIMIT 1;
Output

DepartmentName	NumberOfEmployees
Engineering	3
Explanation:

This query counts the number of employees in each department, orders the results by the number of employees in descending order, and limits the result to show only the department with the most employees.

50. Retrieve the Details of Employees Who Have Been Hired in the Last Two Years
Query:

SELECT *
FROM Employees
WHERE HireDate >= (CURRENT_DATE - INTERVAL '2 years');
Output

EmployeeID	Name	Age	Position	Salary	DepartmentID	HireDate
John Doe	1	28	Software Engineer	80000.00	1	2021-01-15
Sarah Davis	5	26	UI/UX Designer	70000.00	2	2022-10-12
Explanation:

This query retrieves the details of employees who have been hired in the last two years by comparing their hire date with the current date minus two years.




=======================================================================================================================================================================================

1. What is MVCC in PostgreSQL, and why is it important?

Answer:
MVCC stands for Multi-Version Concurrency Control. PostgreSQL uses MVCC to allow multiple transactions to read and write data at the same time without locking rows for every read.

Explanation:
Instead of overwriting a row directly, PostgreSQL creates a new version of the row. Older transactions can still see the old version, while newer transactions can see the updated one depending on their snapshot.

Why it matters:

Readers do not block writers
Writers do not block readers in most cases
Improves concurrency and performance

Example:
If Transaction A updates a row, Transaction B can still read the old committed version until A commits.

2. How does VACUUM work in PostgreSQL?

Answer:
VACUUM removes dead tuples created by updates and deletes, making space reusable.

Explanation:
Because PostgreSQL uses MVCC, old row versions remain in the table after updates/deletes. These old versions are called dead tuples. VACUUM marks their space as reusable.

Types:

VACUUM → reclaims space for reuse
VACUUM ANALYZE → also updates planner statistics
VACUUM FULL → rewrites the table and returns space to the OS, but takes an exclusive lock

Key point:
Without vacuuming, tables can bloat and query performance can degrade.

3. What is the difference between VACUUM and ANALYZE?

Answer:
VACUUM cleans up dead tuples, while ANALYZE updates statistics used by the query planner.

Explanation:

VACUUM is about storage cleanup
ANALYZE is about better execution plans

PostgreSQL’s planner decides whether to use a sequential scan, index scan, join type, and more based on statistics. If statistics are outdated, the planner may choose a poor plan.

4. What is table bloat, and how do you reduce it?

Answer:
Table bloat is wasted space in a table or index caused by dead tuples and inefficient storage reuse.

Explanation:
Frequent UPDATE and DELETE operations create dead rows. If autovacuum cannot keep up, the table grows larger than necessary.

Ways to reduce it:

Tune autovacuum
Use VACUUM
Use REINDEX for bloated indexes
Use VACUUM FULL or tools like pg_repack when needed
Reduce unnecessary updates

Interview insight:
A senior answer should mention that index bloat and table bloat are related but separate issues.

5. What is autovacuum, and why is it critical?

Answer:
Autovacuum is PostgreSQL’s background process that automatically runs vacuum and analyze operations.

Explanation:
It prevents:

Transaction ID wraparound
Table bloat
Outdated statistics

Why critical:
Without autovacuum, PostgreSQL can eventually face severe performance issues, and in extreme cases, transaction wraparound can make the database stop accepting writes.

Good senior point:
Autovacuum often needs tuning on large or write-heavy tables.

6. What is transaction isolation in PostgreSQL?

Answer:
Transaction isolation controls how changes made by one transaction become visible to others.

PostgreSQL isolation levels:

Read Committed
Repeatable Read
Serializable

Explanation:

Read Committed: each statement sees only committed data at the time it starts
Repeatable Read: all statements in the transaction see the same snapshot
Serializable: transactions behave as if executed one by one

Important note:
PostgreSQL does not implement isolation exactly like every other database. Its MVCC model gives strong behavior, especially for Repeatable Read.

7. What is the difference between Read Committed and Repeatable Read?

Answer:
In Read Committed, each query gets a fresh snapshot. In Repeatable Read, the whole transaction uses one snapshot.

Explanation:
In Read Committed, if you run the same SELECT twice in one transaction, you may get different results if another transaction commits changes in between.

In Repeatable Read, repeated reads return the same result for the same snapshot.

Example:
This matters in reporting, financial calculations, and consistency-sensitive operations.

8. What are deadlocks in PostgreSQL?

Answer:
A deadlock happens when two transactions wait on each other forever.

Example scenario:

Transaction A locks row 1, then wants row 2
Transaction B locks row 2, then wants row 1

PostgreSQL detects this and aborts one transaction.

How to prevent:

Access tables/rows in consistent order
Keep transactions short
Avoid unnecessary locking

Senior-level point:
Deadlocks are not just a database issue; they are often caused by application transaction design.

9. What is the difference between DELETE, TRUNCATE, and DROP?

Answer:

DELETE removes selected rows
TRUNCATE removes all rows quickly
DROP removes the entire table object

Explanation:

DELETE is row-by-row and can use WHERE
TRUNCATE is faster for clearing a table and uses fewer resources
DROP removes table structure, data, indexes, constraints, everything

Important detail:
TRUNCATE is transactional in PostgreSQL, unlike in some other databases.

10. How do indexes work in PostgreSQL?

Answer:
Indexes improve query speed by allowing PostgreSQL to find rows without scanning the whole table.

Common types:

B-tree
Hash
GIN
GiST
BRIN

Explanation:
The default and most common type is B-tree, used for equality and range comparisons.

Interview tip:
A good answer includes that indexes speed up reads but add overhead to writes because inserts/updates/deletes must also maintain the index.

11. When would you use GIN instead of B-tree?

Answer:
GIN is useful for indexing composite values like arrays, JSONB, and full-text search data.

Explanation:
A B-tree works well when one indexed value maps to one row key comparison. GIN is better when a single column contains multiple searchable elements.

Examples:

jsonb
arrays
tsvector

Use case:
Searching inside JSON documents or full-text search is a typical GIN use case.

12. What is the difference between clustered and non-clustered storage in PostgreSQL?

Answer:
PostgreSQL tables are heap-organized by default, not automatically physically ordered by an index.

Explanation:
The CLUSTER command can reorder a table based on an index, but PostgreSQL does not continuously maintain that order.

Key point:
This is different from some databases where clustered indexes define physical row order permanently.

13. What is the PostgreSQL query planner?

Answer:
The query planner decides the most efficient way to execute a SQL statement.

Explanation:
It chooses:

scan type
join order
join algorithm
whether to use indexes

It makes decisions based on:

table statistics
row count estimates
cost settings

Why it matters:
A bad plan can make a query take minutes instead of milliseconds.

14. What is EXPLAIN and EXPLAIN ANALYZE?

Answer:
EXPLAIN shows the planned execution strategy. EXPLAIN ANALYZE actually runs the query and shows real execution details.

Explanation:
EXPLAIN ANALYZE is more useful because it compares:

estimated rows vs actual rows
estimated cost vs actual timing

Why important:
It helps identify bad estimates, missing indexes, poor joins, and expensive operations.

15. What does it mean if estimated rows and actual rows are very different?

Answer:
It usually means PostgreSQL has inaccurate statistics or data distribution is more complex than the planner expects.

Explanation:
When row estimates are wrong, PostgreSQL may choose:

wrong join type
wrong join order
sequential scan instead of index scan

Possible fixes:

Run ANALYZE
Increase statistics target for important columns
Rewrite the query
Improve indexing
16. What are the main join algorithms in PostgreSQL?

Answer:
The main join algorithms are:

Nested Loop
Hash Join
Merge Join

Explanation:

Nested Loop: good when one side is small
Hash Join: good for equality joins, common for medium/large datasets
Merge Join: efficient when inputs are already sorted

Senior-level point:
The best join method depends on data size, indexes, memory, and sort cost.

17. What is WAL in PostgreSQL?

Answer:
WAL stands for Write-Ahead Logging. PostgreSQL writes changes to the WAL before writing them to the main data files.

Explanation:
This ensures durability and crash recovery.

Why it matters:

supports recovery after crash
supports replication
improves consistency

Core idea:
If the server crashes, PostgreSQL can replay WAL records to restore the database to a consistent state.

18. What is the difference between physical replication and logical replication?

Answer:

Physical replication copies the entire database cluster at the file/block level
Logical replication replicates changes at the table/row level

Explanation:

Physical replication is used for standby servers and disaster recovery
Logical replication is more flexible and can replicate selected tables

Use cases:

Physical: high availability
Logical: selective replication, migrations, version upgrades, data distribution
19. What is a checkpoint in PostgreSQL?

Answer:
A checkpoint is the process of flushing dirty pages from memory to disk and recording a checkpoint in WAL.

Explanation:
Checkpoints reduce recovery time after a crash, because PostgreSQL only needs to replay WAL from the last checkpoint forward.

Tradeoff:
Very frequent checkpoints can increase I/O load. Very infrequent checkpoints can increase recovery time and WAL volume.

20. What is partitioning in PostgreSQL?

Answer:
Partitioning splits a large table into smaller physical pieces called partitions.

Types:

Range
List
Hash

Explanation:
It improves manageability and can improve performance when queries only access relevant partitions.

Example:
A sales table partitioned by month allows monthly data management and can reduce scanned data.

Senior-level point:
Partitioning is not automatically faster for everything. Good partition design matters.

21. What is partition pruning?

Answer:
Partition pruning is when PostgreSQL skips partitions that cannot contain matching rows.

Explanation:
If a table is partitioned by date and the query asks for one month, PostgreSQL can avoid scanning unrelated partitions.

Benefit:
Less I/O and faster queries.

22. What is the difference between a CTE and a subquery in PostgreSQL?

Answer:
A CTE is a named temporary result set defined with WITH, while a subquery is nested directly inside a query.

Explanation:
CTEs improve readability. In older PostgreSQL versions, CTEs were often optimization fences. In newer versions, PostgreSQL can inline them in many cases.

Interview point:
A senior candidate should know that CTE behavior changed over time and is not always slower now.

23. What is json vs jsonb in PostgreSQL?

Answer:

json stores exact input text
jsonb stores binary parsed JSON

Explanation:
jsonb is usually preferred because it:

is faster for querying
supports indexing
normalizes formatting

json is useful when preserving the exact original input matters.

24. What are window functions in PostgreSQL?

Answer:
Window functions perform calculations across a set of rows related to the current row without collapsing rows like GROUP BY.

Examples:

row_number()
rank()
sum() over (...)
lag()
lead()

Explanation:
They are useful for:

ranking
running totals
comparing current row with previous/next row

Example use case:
Finding the second-highest salary in each department.

25. What is the difference between WHERE and HAVING?

Answer:
WHERE filters rows before grouping. HAVING filters groups after aggregation.

Explanation:

Use WHERE for row-level conditions
Use HAVING for aggregate conditions like COUNT(*) > 5
26. What is work_mem, and why is it important?

Answer:
work_mem is the memory PostgreSQL uses for operations like sorting and hashing before spilling to disk.

Explanation:
If work_mem is too low:

sorts may spill to disk
hash joins may spill to disk
queries become slower

If too high:

many concurrent queries may consume too much RAM

Senior-level point:
It applies per operation, not per query, so memory usage can grow quickly under concurrency.

27. What is the difference between shared_buffers and work_mem?

Answer:

shared_buffers is PostgreSQL’s shared cache for data pages
work_mem is per-operation memory for query execution tasks

Explanation:
They serve different purposes:

shared_buffers helps cache table/index pages
work_mem helps sort/join/aggregate efficiently
28. What is an execution plan red flag you look for first?

Answer:
A common first red flag is a large mismatch between estimated and actual rows.

Other red flags:

sequential scan on a huge table when an index should help
repeated nested loops over large datasets
sorts spilling to disk
high-cost nodes with very high actual time

Explanation:
These signs usually point to poor statistics, missing indexes, or inefficient SQL structure.

29. What is REINDEX, and when would you use it?

Answer:
REINDEX rebuilds an index.

Use cases:

index bloat
index corruption
performance degradation

Explanation:
Over time, indexes may become bloated or inefficient, especially in write-heavy workloads.

30. How would you troubleshoot a slow PostgreSQL query?

Answer:
A strong troubleshooting approach is:

Check the query plan with EXPLAIN ANALYZE
Compare estimated vs actual rows
Look for sequential scans, bad joins, or spills
Check indexes
Check table statistics
Review table bloat
Check configuration like work_mem
Check locking/waiting
Rewrite the query if needed

Explanation:
Senior-level troubleshooting is systematic. You do not guess first; you measure first.

31. What is the difference between optimistic and pessimistic locking in PostgreSQL?

Answer:
Pessimistic locking explicitly locks rows early, while optimistic locking assumes conflicts are rare and checks for conflicts before commit or update.

Explanation:
In PostgreSQL, pessimistic locking often uses:

SELECT ... FOR UPDATE
FOR SHARE

Optimistic locking is often implemented in the application using:

version columns
timestamps
conflict detection logic
32. What does SELECT ... FOR UPDATE do?

Answer:
It locks the selected rows so other transactions cannot update or delete them until the current transaction finishes.

Explanation:
This is useful when you want to read data and then update it safely without another transaction changing it first.

Example use case:
Account balance processing, job queue workers, inventory reservation.

33. What are PostgreSQL materialized views?

Answer:
A materialized view stores the result of a query physically, unlike a normal view which runs the query each time.

Explanation:
They are useful for expensive reporting queries.

Tradeoff:
They can become stale and must be refreshed.

Important detail:
Refreshing may be expensive depending on the size and complexity of the query.

34. What is the difference between a normal view and a materialized view?

Answer:

A normal view is just saved SQL
A materialized view stores data physically

Explanation:
Normal views always show current data. Materialized views are faster for reads but need refreshes.

35. What is transaction ID wraparound?

Answer:
PostgreSQL tracks transactions with transaction IDs. If old row versions are not vacuumed properly, transaction IDs can wrap around and create data visibility risk.

Explanation:
PostgreSQL prevents this with aggressive vacuuming. This is one reason autovacuum is essential.

Interview point:
This is a classic senior PostgreSQL topic. Mentioning wraparound shows strong operational understanding.

Advanced scenario-based interview questions
36. A query suddenly became slow after data volume increased. What likely happened?

Answer:
Common causes:

outdated statistics
different execution plan
index no longer selective enough
table or index bloat
memory settings not sufficient for larger workload

Explanation:
When data size changes, the planner may choose a different plan. A query that worked well at 1 million rows may behave very differently at 100 million rows.

37. Why would PostgreSQL ignore an index and do a sequential scan?

Answer:
Because the planner thinks a sequential scan is cheaper.

Possible reasons:

large percentage of table is needed
outdated statistics
low selectivity
function on indexed column prevents efficient use
type mismatch or bad query pattern

Example:
Using a function like lower(column) without a matching functional index can prevent effective index use.

38. What is a functional index?

Answer:
A functional index is an index on the result of an expression rather than a raw column.

Example:
Indexing lower(email) for case-insensitive searches.

Explanation:
It helps when queries repeatedly use the same expression in filters or joins.

39. What is a partial index?

Answer:
A partial index indexes only rows that satisfy a condition.

Example:
Index only active users instead of all users.

Explanation:
It reduces index size and maintenance cost while improving performance for targeted queries.

40. How do you design PostgreSQL for high write throughput?

Answer:
A senior answer would include:

minimize unnecessary indexes
tune autovacuum
batch writes where possible
use partitioning when appropriate
avoid excessive random updates
tune checkpoints and WAL settings carefully
monitor bloat and contention

Explanation:
High write performance is not only about hardware. Schema design, indexing strategy, vacuum behavior, and workload pattern all matter.

Short rapid-fire PostgreSQL interview questions
41. Is PostgreSQL ACID compliant?

Answer: Yes.

42. Which index type is default in PostgreSQL?

Answer: B-tree.

43. Can PostgreSQL index JSON data?

Answer: Yes, especially with jsonb and GIN indexes.

44. Does TRUNCATE fire DELETE triggers?

Answer: No, not normal DELETE triggers.

45. Can a materialized view be indexed?

Answer: Yes.

46. Is VACUUM FULL online-friendly?

Answer: No, because it takes stronger locks.

47. Does PostgreSQL support full-text search?

Answer: Yes.

48. Can PostgreSQL do logical replication?

Answer: Yes.

49. Are CTEs always slower than subqueries?

Answer: No.

50. Does PostgreSQL support partitioning natively?

Answer: Yes.

How to answer in an interview

For Level 4 interviews, do not stop at definitions. Structure your answer like this:

1. Define it
State what it is.

2. Explain why it matters
Show the practical impact.

3. Give a real-world example
This makes your answer sound experienced.

Example:
For MVCC, do not just say “it allows concurrency.”
Say:
“PostgreSQL uses MVCC to keep multiple versions of rows, so readers usually do not block writers. That is why OLTP workloads perform well even with many concurrent users.”

10 most important PostgreSQL topics for Level 4

Focus hardest on these:

MVCC
VACUUM / autovacuum
WAL
Index types
Query planner
EXPLAIN ANALYZE
Locks and deadlocks
Replication
Partitioning
Performance tuning

==========================================================================================================================================================================================



PostgreSQL Interview Questions for Freshers

1. What is PostgreSQL?
PostgreSQL, commonly referred to as Postgres, powers applications with its robust and open-source relational database management system. Developers leverage its extensive features, such as Function Overloading and Table Inheritance, to create advanced applications. PostgreSQL seamlessly operates across major operating systems, including Windows, UNIX, macOS, and Linux.

2. What are the advantages of PostgreSQL?
The advantages of PostgreSQL include:

PostgreSQL is highly fault-tolerant, owing to its feature of write-ahead logging.
It is flexible and easy to learn.
It supports a variety of replication methods.
It can be used for large-scale web applications because of its powerful and robust nature.
As the source code of PostgreSQL is available for free due to its open-source license, users can edit and modify it easily according to their business requirements.
3. Define a non-clustered index.
In a non-clustered index, the order of the index rows differs from the physical order of the real data. The leaf pages of a non-clustered index instead contain pointers to the real data rather than the actual data itself. Its main advantage is that it provides faster access to data.

4. Which data types are used in PostgreSQL?
The following data types are used in PostgreSQL:-

Numeric data type (Integer, Float)
Geometric primitives
Boolean data type
Character data type (varchar, char, text)
Monetary data type
Array
Document data type (JSON, XML, Key-value, etc.)
Date/Time data type
Customization data type (Composite, custom types, etc.)
5. What do you mean by a parallel query?
Parallel query in PostgreSQL is an advanced feature. It allows the arrangement of query plans in such a way that they can exploit multiple CPUs. This helps in answering user queries in a much faster and quicker manner.

6. What is the meaning of PgAdmin?
PgAdmin is a free open-source graphical front-end PostgreSQL database administration tool. This web-based GUI tool is prominently used to manage PostgreSQL databases. It assists in monitoring and managing numerous complex PostgreSQL and EDB database systems. PgAdmin is used to accomplish tasks like accessing, developing, and carrying out quality testing procedures.

7. Define Write-Ahead logging.
Write-Ahead Logging is a technique used to ensure the data integrity of PostgreSQL databases. It helps in maintaining the resilience or the reliability of the database. Write-ahead logging is a method wherein any changes and actions in the database are logged in a transaction log prior to the updating or modification of the database. In case there is a database crash, this feature helps the in providing the log of the database changes. In addition, it also helps the user in resuming work from where it was discontinued, after the crash.

8. What is the full form of MVCC?
The full form of MVCC is Multi-version Concurrency Control.

9. Why do companies use PostgreSQL?
Numerous high-profile organizations, such as Apple, Spotify, IMDb, Instagram, and Skype, make use PostgreSQL database, owing to its excellent features:

PostgreSQL is extremely easy to use.
It is a powerful and robust open-source tool.
PostgreSQL follows and supports the ACID properties.
It supports MVCC (Multiversion Concurrency Control).
It is highly fault-tolerant.
It runs on almost all different operating systems.
10. What is the full form of GEQO?
The full form of GEQO is Genetic Query Optimization. It enables non-exhaustive search to efficiently manage large join queries in PostgreSQL.

11. What do you mean by index in PostgreSQL?
An index in PostgreSQL is a way of increasing the speed and efficiency of the database. Databases use indexes as special lookup tables that help them retrieve data in a much quicker manner. Indexes enable the user to find specific rows in a database. They act like pointers to the data in the database, thereby enhancing the overall performance.

12. What is the main query language of PostgreSQL?
SQL or Structured Query Language is the main query language of PostgreSQL.

PostgreSQL Advanced Interview Questions
13. What do you think is the latest PostgreSQL version in the market?
As of 2022, the latest version of PostgreSQL in the market is PostgreSQL 15. It was launched on 13 October, 2022.


14. What is the full form of ORDBMS?
The full form of ORDBMS is Object-Relational Database Management System.

15. What do you mean by a string constant in PostgreSQL?
A string constant is defined as the sequence of characters that are bounded by single quotes i.e., (‘). It can be used during insertion or while passing the characters to the database objects. This is an important feature when performing the parsing of data. In the case of PostgreSQL, string constant is allowed with single quotes but embedded by a C-style backslash.

Example: ‘This is an example of a string constant bound by single quotes.’

16. What is Multi-version Control?
Multi-version Concurrency Control or MVCC is a technique to enhance database performance by handling concurrency in PostgreSQL databases. It prevents the locking of databases. MVCC reduces the delay time that users face while logging into their accounts and comes into action when someone else is accessing the contents of the account.

Inconsistency occurs when numerous transactions attempt to access the same data. To preserve data consistency, concurrency control is necessary.

Let’s take an example of an ATM machine. If concurrency is not applied in this case, different users won’t be able to access their accounts and draw money at the same time. Whereas if concurrency control is enabled, then multiple users can do so easily.

17. Explain table partitioning in PostgreSQL.
Table Partitioning in PostgreSQL is the process wherein a large table is split into smaller pieces. These smaller pieces are known as partitions. List and range partitioning is supported by PostgreSQL through its table inheritance feature.

Table partitioning helps in increasing the query performance of PostgreSQL, as it is much easier to select data from these partitions rather than selecting from one main table.

Each partition can store data according to how frequently it is used, allowing low-use data to be stored on media that may be slower or less expensive.

18. Explain the use of PostgreSQL triggers.
A trigger can be defined as a function that is called automatically when the insertion, updation, or deletion event occurs. They serve as a way to check the data integrity. Triggers are capable of handling any errors that occur in the database. Another advantage of triggers is: Any table that is present in a PostgreSQL database can be forced to receive security approvals with the use of PostgreSQL triggers.

19. Is PostgreSQL compatible with Cloud?
Yes, PostgreSQL is compatible and be run on Cloud. PostgreSQL is highly portable. Moreover, similar to other open-source databases, PostgreSQL can be effortlessly executed on virtual containers.

20. Name the different types of operators that are used in PostgreSQL.
Operators are the special characters or words that are used mainly in the WHERE clause in PostgreSQL. These operators can be used to perform a variety of functions and operations.

Operators used in PostgreSQL

The different types of operators that are used in PostgreSQL are as follows:-

Arithmetic operators
Logical operators
Comparison operators
Bitwise operators
21. What do you mean by the CTID field in PostgreSQL?
In PostgreSQL, CTIDs serve as unique identifiers for each record within a table. The CTID field enables the precise location of physical rows based on their offset and block positions, facilitating the effective distribution of data across the table. By utilizing CTID fields, users can gain insights into the actual storage positions of rows within the database table.

22. How do you start a database server in PostgreSQL?
To access the database data, the first step is to start the database server. The database server application, known as Postgres, plays a crucial role in managing the database. It actively seeks information about the data it needs to utilize, which is essential for its effective functioning. By initiating the database server and providing the necessary data location details, users can actively enable access and utilization of the database data through the Postgres software.The -D option is used to accomplish this.

Execute these commands to start the database server:

usr/local/etc/rc.d/010.pgsql.sh start
/usr/local/etc/rc.d/postgresql start
Another way to start a database server in PostgreSQL is:

Start by pressing the Windows key + R simultaneously to enter the Run Window.
To find the PostgreSQL services, type services.msc next.
Using the version that is installed, search the Postgres service.
Click on Start to start the database server.
23. State the maximum size of a table on PostgreSQL.
The maximum number of blocks in a table decides the limit of the table. As the number of blocks is 2^32 and 8192 bytes is the default size of the block, therefore, the maximum size of a table on PostgreSQL is 32TB.

24. What are the differences between PostgreSQL and MongoDB?
PostgreSQL	MongoDB
PostgreSQL is a relational database management system.	MongoDB is a non-relational database management system.
PostgreSQL was created using the C language.	MongoDB was created using the C++ language.
PostgreSQL is object-oriented.
 

MongoDB is document-oriented.
PostgreSQL stores data in the form of different tables.	MongoDB stores data in the form of key-value pairs as one record.
PostgreSQL is faster than MongoDB.	MongoDB is relatively slower than PostgreSQL.
Transform Your Skills in Data Analytics
Your Data Analytics Career Starts Here
Explore Program
quiz-icon
25. State the role of tokens in PostgreSQL.
Tokens in PostgreSQL have an important role in the parsing and interpretation of SQL statements. When SQL queries are executed, the PostgreSQL server breaks down the statements into smaller units known as tokens. These tokens represent various elements like keywords, identifiers, literals, operators, and punctuation marks.  This tokenization process ensures accurate parsing and evaluation of SQL queries, enabling the server to handle database operations efficiently.

PostgreSQL DBA Interview Questions For Experienced Professionals
26. When should a developer use PostgreSQL?
Developers should choose PostgreSQL when they require a dependable and feature-rich database management system. PostgreSQL is suitable for diverse applications, including web development, data analysis, and enterprise solutions. It provides advanced features like JSON support and geospatial capabilities. With its cross-platform compatibility and active community, developers can rely on PostgreSQL for scaling and achieving optimal performance while managing complex data.

27. Describe the history of PostgreSQL in brief.
PostgreSQL originated as a vital component of the POSTGRES project initiated by Professor Michael Stonebraker in 1986 at the University of California, Berkeley. It boasts compatibility with major operating systems such as macOS, Windows, Linux, and UNIX. 

PostgreSQL has upheld ACID properties since 2001, with continuous core platform development spanning over three decades. It encompasses noteworthy additions like the PostGIS database extender and has become the standard database for macOS. commonly known as Postgres, due to its widespread adoption of the SQL Standard among relational databases.

28. Explain the procedure to set up PgAdmin in PostgreSQL.
PgAdmin is a web-based management tool that interacts with the PostgreSQL database. It can be used to perform any database administration operations on PostgreSQL.

To set up PgAdmin in PostgreSQL, follow these steps:

Start and launch pgAdmin 4.
Then select “Add new Server” from the “Quick Link” section under the “Dashboard” menu.
Choose the “Connection” tab in the “Create-Server” box after clicking “Add new Server” in the window.
Put your server’s IP address in the “Hostname/Address” column to configure the connection.
Finally, you must define “Port” as “5432,” which is the PostgreSQL server’s default port.
29. Explain the role of table space in PostgreSQL.
Table spaces in PostgreSQL are defined as the directories where data files can be stored. They are used to store various databases as well as database objects.

Using table spaces, the disk layout of a PostgreSQL installation can be easily handled and managed.

In addition to that, tablespaces give administrators the ability to enhance performance by making use of their knowledge of the usage patterns of database objects.

30. List the disadvantages of PostgreSQL.
Despite its many advantages, PostgreSQL has numerous disadvantages. Some of these include:

PostgreSQL may have a slower speed compared to MySQL.
It supports fewer open-source applications compared to MySQL.
Market recognition for PostgreSQL has been challenging due to its lack of specific ownership.
Its performance rate may be lower than that of MySQL in certain situations.
31. Explain the term ‘Sequence’ in PostgreSQL.
The Sequence is a generator that produces a progressive number that can help synchronize the keys across multiple rows or tables and construct a single primary key automatically.

A sequence in PostgreSQL can be defined as a user-defined schema-bound object that generates an integer sequence based on a specific requirement.

Learn how to reset the primary key sequence in PostgreSQL correctly through this blog.

32. Differentiate between clustered and non-clustered indexes.
Clustered Index	Non-Clustered Index
It is faster than the non-clustered index.	It is relatively slower as compared to the clustered index.
Index is considered the main data in the clustered index.	In the case of a non-clustered index, the index is the copy of data.
The clustered index has the ability to store data naturally on the disk.	The non-clustered index cannot naturally store data on the disk.
It requires lesser memory for operations as compared to the non-clustered index.	The non-clustered index requires more memory to perform operations.
A table can consist of only one clustered index.	A table can contain multiple non-clustered indexes.
33. What is the procedure for storing binary data in PostgreSQL?
The users can store binary data in PostgreSQL in two distinct ways:

By using the data type BYTEA.
By using the Large Object feature.
34. What do you understand by the enable-debug command in PostgreSQL?
The enable-debug command in PostgreSQL is the command that assists in compiling all libraries and applications.

It has a few debugging symbols that make it easier for developers to find flaws and other issues that can arise during the script’s execution. This process can slow down or impede the system when it is being used, increasing the size of the binary file.

35. Describe the method by which you can change the column data type in PostgreSQL.
The data type of one or more columns in PostgreSQL can be changed by using the following commands along with the TYPE keyword:

<br>
ALTER TABLE<br>
ALTER COLUMN<br>
 Example:

ALTER TABLE tab_name<br>
ALTER COLUMN col_name TYPE new_data_type;<br>
36. How can the first 5 records be selected in PostgreSQL?
The LIMIT keyword can be used to select the first N records in PostgreSQL.

Example:

SELECT * FROM Employee ORDER BY Salary DESC LIMIT 5
Here, the Employee is the name of the table that contains employee data.

ORDER BY command arranges the data in descending order based on the salary of employees.

LIMIT keyword used with the number 5 prints the first 5 or the top 5 records present in the Employee table.

PostgreSQL Replication Interview Questions
37. What are the features of PostgreSQL?
PostgreSQL or Postgres is an object-relational database management system or ORDBMS. Some of its prominent features are as follows:-

Extremely high fault-tolerance
Free to download
Reliable and secure
Robust and powerful
Easy recovery process
Low maintenance cost
Easily compatible with a wide variety of platforms and languages.
High availability
Easy to use
Become a Game-Changer in Data Analytics
Learn from Top Data Analytics Experts
Explore Program
quiz-icon
38 Does PostgreSQL support Full-Text Search?
When a search is conducted on a portion of text contained in a large body of electronically recorded text, it is referred to as a full-text search, the results that are returned may include all or some of the search terms. Traditional searches,however, would only produce exact matches.

Yes, PostgreSQL supports the Full-Text Search feature. It is a powerful tool in PostgreSQL and can be enhanced by incorporating functions like result highlighting or by creating your own unique dictionaries or functions.

39. What are the physical methods used in PostgreSQL for replication?
A few replication methods are:

Physical Replication: This method includes two ways:

Streaming Replication: In this replication, there is a constant streaming of changes from a primary server to one or more standby PostgreSQL servers.
File-based Replication (pg_basebackup):  In this replication, a base backup is created of the primary server’s directory and forwarded to standby servers.
40. How many types of replications are present in PostgreSQL?
There are majorly four types of PostgreSQL replications, namely:

Physical Replication: It is of two types:
Streaming Replication 
File- based Replication (pg_basebackup)
Logical Replication: It consists of:
Build-In Logical Replication 
Third-party Tools
Bi-Directional Replication (BDR)
Custom Replication Solutions.
41. How can replication conflicts be addressed in PostgreSQL?
When data changes occur at the same time in different places, such as multi, which may lead to replication conflicts, the software automatically ensures the change that should be allowed with the help of “conflict handlers” in PostgreSQL. These conflict handlers automatically resolve the changes applicable to avoid conflicts.

42. What are the advantages of logical replication as compared to physical replication methods?
Advantages of logical replications are:

Selective replication is possible in logical replication, which makes it more flexible to choose which data to replicate.
Physical replication requires identical PostgreSQL versions on both primary servers and standby servers, whereas logical replication can work with different versions of PostgreSQL.
Logical replication offers more flexibility as compared to physical replication in handling the changes in Schema.
Logical replication is compatible with multiple applications and database maintenance tasks.
43. What is the role of replication slots in PostgreSQL’s streaming replication mechanism?
Replication slots within the streaming replication mechanism of PostgreSQL guarantee that the primary server will keep the Write Ahead Logging (WAL) files until they are received and applied by all servers. This ensures that the primary server will not delete these files prematurely.

44. How can you take the backup of a database?
PostgreSQL permits the user to take a backup of the database by using “pg_dump”.

To perform a backup on a plain-text SQL file, login into your database server and implement the following command:

pg_dump database_name > filename.sql
The database can be reconstructed using the commands available in the SQL file.

Another way to backup the database is:

/usr/local/bin/pg_dump mydatabase > mydatabase.pgdump
45. How can you stop a PostgreSQL Server? Can you stop a particular database in the PostgreSQL cluster?
To stop a PostgreSQL server implement the following steps and commands:

For Windows
The first step is to locate the PostgreSQL database directory.

After that, open the command prompt and execute the following command-

pg_ctl -D "C:Program FilesPostgreSQL9.6data" stop
An alternative way to stop the PostgreSQL server on Windows is:

Press the Windows key + R simultaneously to enter the Run Window.
Type services.msc to find the PostgreSQL services.
Using the installed version, locate the Postgres service.
Click Stop to stop the database server.
For Linux
Use the following command on Linux to stop the server-

sudo service postgresql stop
For macOS
Use the following command on macOS to stop the server manually-

pg_ctl -D /usr/local/var/postgres stop
No, PostgreSQL does not allow the user to stop a specific database in the cluster.

46. Discuss the differences between file-level and logical backups in PostgreSQL
When it comes to backing up a PostgreSQL database cluster, there are two approaches: file-level backups and logical backups.

File-level backups involve making copies of the files that make up the database cluster. This includes data files, configuration files, and transaction logs. This method is great for disaster recovery situations where you need to restore the database cluster. However, because it copies all the files, the backup files can be quite large. Require storage space.

On the other hand, logical backups export data in a format using tools like pg_dump. These backups are smaller in size. Offer flexibility when it comes to selectively restoring specific databases, tables, or subsets of data. However, they can be slower compared to file-level backups when it comes to both backup and restore operations

47. Discuss the advantages and limitations of using pg_dump for backups.
The advantages of using pg_dump for backups are:

Portability: Backup files can be effortlessly run on PostgreSQL installations.
Selective Backup: It enables the backup of databases, schemas, tables, or rows as needed.
Comprehensive: It captures both the database schema and data, ensuring a backup.
Integration: pg_dump can be seamlessly integrated into automation scripts for operation.
The limitations of using pg_dump for backups are:

Impact on performance: It is possible that there could be some load on the database server, particularly when dealing with large databases.
Storage requirements: Backups might take up an amount of space and may also require more time for transferring.
Time required for restoration: Restoring from pg_dump backups can be a time- consuming process, potentially resulting in downtime.
Limited parallelism: By default, the operation is performed using a thread, which could potentially prolong the duration.
48. How can you perform a point-in-time recovery in PostgreSQL?How can you perform a point-in-time recovery in PostgreSQL?
To perform a point-in-time recovery in PostgreSQL, you need:

Make sure that WAL archiving is enabled.
Restore the base backup.
Apply the WAL files either by using recovery.conf or via a server.
Stop the recovery process when you reach the recovery point you desire.
Allow read-write operations on the database.
49. How can you perform a selective restore of data from a PostgreSQL backup?
To selectively restore data from a backup of PostgreSQL, follow these steps:

Utilize the pg_restore command, Include the t option to indicate the table(s) you wish to restore.
If necessary, you can also use the n option to specify schemas if the tables belong to them.
Execute the pg_restore command by providing the file and any additional options that may be required.
Keep an eye on the restoration process. Ensure that you verify the restored data once it is completed.
50. What is the purpose of the pg_archivecleanup utility in PostgreSQL?
The main objective of the pg_archivecleanup tool in PostgreSQL is to get rid of WAL (Write Ahead Logging) files from the directory. This ensures that only the necessary WAL files required for Point-In-Time-Recovery (PITR) are kept intact.