Top 57 PostgreSQL Interview Questions + Answers in 2024

Funded by prominent players in the market, PostgreSQL, or simply Postgres, is a database management system supported by a large open-source community of contributors. Its popularity in industrial use cases over SQL Server is mainly because of its ACID transactions and object-relational features.

While you’re more likely to experience general SQL questions related to database queries and foundational knowledge in most company interviews, PostgreSQL questions are often pitched to catch you off-guard.

If you’re looking for PostgreSQL interview questions commonly asked in data and tech positions, you’ve come to the right place.

PostgreSQL is known for its reliability and extensibility, allowing customization for specific needs. Moreover, it is SQL compliant, meaning it adheres to the SQL standards, which has made it popular among developers.

When interviewing for positions in companies that use PostgreSQL, you may expect questions such as:

1. What is PostgreSQL, and what are its key features?

“PostgreSQL is an advanced open-source relational database management system (RDBMS) known for its robustness, extensibility, and standards compliance. It supports a wide range of data types, complex queries, and advanced features such as ACID transactions, multi-version concurrency control (MVCC), and extensive indexing techniques. Key features include support for JSON and XML, full-text search, advanced indexing, powerful procedural languages (PL/pgSQL, PL/Python, etc.), a sophisticated query optimizer, and extensive extension support, which allows users to add new functionality.”

2. How does PostgreSQL differ from a traditional relational database management system (RDBMS)?

“PostgreSQL differs from traditional RDBMSs in several ways. It is highly extensible, allowing users to define custom data types, operators, and index types. PostgreSQL adheres strictly to SQL standards but also includes many advanced features not found in other systems, such as table inheritance and sophisticated locking mechanisms. Additionally, PostgreSQL provides native support for NoSQL capabilities like JSONB and hstore, enabling it to handle both relational and document-oriented data efficiently. Its advanced concurrency control and extensive support for procedural languages set it apart from many traditional RDBMSs.”

3. Explain the concept of ACID transactions in PostgreSQL.

“ACID transactions in PostgreSQL ensure the reliability of database operations. ACID stands for atomicity, consistency, isolation, and durability. Atomicity guarantees that all operations within a transaction are completed; if one fails, the entire transaction is rolled back. Consistency ensures that a transaction brings the database from one valid state to another, maintaining database invariants. Isolation ensures that transactions execute independently, preventing interference from concurrent transactions through techniques like MVCC. Durability guarantees that once a transaction is committed, it will remain so even in the event of a system crash due to write-ahead logging (WAL).”

4. Describe the different data types available in PostgreSQL.

PostgreSQL supports a wide range of data types, including:

• Numeric Types: INTEGER, SMALLINT, BIGINT, NUMERIC, REAL, DOUBLE PRECISION
• Character Types: CHAR, VARCHAR, TEXT
• Binary Types: BYTEA
• Date/Time Types: DATE, TIME, TIMESTAMP, INTERVAL, etc.

5. How do you create a table in PostgreSQL?

“To create a table in PostgreSQL, use the CREATE TABLE statement. The table definition includes the table name, column definitions, data types, constraints, and optional table properties.”

CREATE TABLE bookings (
	reservation_id SERIAL PRIMARY KEY,
	guest_id INTEGER NOT NULL,
	check_in_date DATE NOT NULL,
	check_out_date DATE NOT NULL,
	CONSTRAINT chk_dates CHECK (check_in_date < check_out_date)
);

6. Let’s say we have a table representing vacation bookings. Write a query that returns columns representing the total number of bookings in the last 90 days, last 365 days, and overall.

Note: You may assume that today is January 1, 2022.

Example:

Input:

bookings table

Column	Type
reservation_id	INTEGER
guest_id	INTEGER
check_in_date	DATE
check_out_date	DATE

Output:

Column	Type
num_bookings_last90d	INTEGER
num_bookings_last365d	INTEGER
num_bookings_total	INTEGER

SELECT
SUM(CASE WHEN DATE_ADD(check_in_date, INTERVAL 3 MONTH) >= '2022-01-01' THEN 1 ELSE 0 END) AS num_bookings_last90d,
SUM(CASE WHEN DATE_ADD(check_in_date, INTERVAL 1 YEAR) >= '2022-01-01' THEN 1 ELSE 0 END) AS num_bookings_last365d,
COUNT(reservation_id) AS num_bookings_total
FROM bookings;

7. Say you work for an e-commerce website that receives clickstream data from Kafka daily. You’re asked to design a data analytics solution for storing and querying raw data with a two-year retention period. The current data volume is around 600 million events daily. What kind of data analytics solution would you design, keeping costs in mind?

“To design a cost-effective data analytics solution for storing and querying large volumes of clickstream data, I would recommend using a combination of Apache Kafka for real-time data ingestion and Apache Hadoop or Amazon S3 for long-term storage. PostgreSQL can be used for querying and analytics, leveraging columnar storage for efficient querying. Implementing data partitioning based on date or user ID can optimize performance. Regularly compress and archive older data to manage storage costs.”

8. Explain the purpose and benefits of using indexes in PostgreSQL.

“Indexes in PostgreSQL are used to speed up the retrieval of rows by creating a data structure that allows faster searches. They reduce the need for full table scans, thus improving query performance, especially for large datasets.”

9. Given a table of flights, extract the 2nd flight with the longest duration between each pair of cities. Order the flights by the flight id ascending.

Note: For any cities X and Y,  (source_location=X, destination_location=Y) and (source_location=Y, destination_location=X) are counted as the same pair of cities.

Note: If there are fewer than two flights between two cities, there is no 2nd longest flight.

Example:

Input:

flights table

Column	Type
id	INTEGER
destination_location	VARCHAR
source_location	VARCHAR
plane_id	INTEGER
flight_start	DATETIME
flight_end	DATETIME

Output:

Column	Type
id	INTEGER
destination_location	VARCHAR
source_location	VARCHAR
flight_start	DATETIME
flight_end	DATETIME

WITH cte_1 AS (
SELECT  id, destination_location AS x, source_location AS y, flight_start, flight_end, TIMESTAMPDIFF(SECOND, flight_start, flight_end) AS diff, 1 AS helper FROM flights
UNION ALL 
SELECT  id, source_location AS x, destination_location AS y, flight_start, flight_end, TIMESTAMPDIFF(SECOND, flight_start, flight_end) AS diff, 2 AS helper FROM flights
), 
cte_2 AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY x,y ORDER BY diff DESC) AS ranking FROM cte_1 
) 
SELECT id, x AS destination_location, y AS source_location, flight_start, flight_end FROM cte_2 WHERE ranking = 2 AND helper= 1
ORDER BY id ASC

10. Suppose your company needs to cut costs due to an economic downturn. During a coffee break, you hear a rumor that a lot of money goes to employees who don’t do their work, and you decide to find out if the rumor is true.

Given two tables: employees and projects, find the sum of the salaries of all the employees who didn’t complete any of their projects.

Clarification:

We will consider a project unfinished if it has no end date (its End_dt is NULL).

Given this, we’ll say an employee didn’t finish any of their projects when:

• They were assigned at least one project.
• None of their projects have an End_dt.

Example:

Input:

employees table

id	salary
INTEGER	FLOAT

projects table

employee_id	project_id	Start_dt	End_dt
INTEGER	INTEGER	DATETIME	DATETIME

Output:

total_slack_salary

---

INTEGER

---

WITH slack_salaries AS (
    SELECT e.salary
    FROM employees e
    INNER JOIN projects p
    ON e.id = p.employee_id
    GROUP BY e.id
    HAVING COUNT(p.End_dt) = 0
)

SELECT sum(salary) AS total_slack_salary
FROM slack_salaries

11. How do you perform aggregations (e.g., COUNT, SUM, AVG) on data in PostgreSQL?

”In PostgreSQL, aggregations are performed using aggregate functions in conjunction with the GROUP BY clause to summarize data. Common aggregate functions include COUNT, SUM, AVG, MAX, and MIN.“

12. A hospital is studying patient flows to optimize their resource placement.

Write a query to find all dates on which the hospital released more patients than the day prior.

Note: You may assume that the table has no missing dates.

Input:

released_patients table

Column	Type
release_date	DATE
released_patients	INTEGER

Output:

Column	Type
release_date	DATE
released_patients	INTEGER

WITH cte AS
  (SELECT *,
          LAG(released_patients, 1) OVER (ORDER BY release_date) AS prev_released_patients
   FROM released_patients)

SELECT release_date,
       released_patients
FROM cte
WHERE released_patients > prev_released_patients

13. Given a table of transactions and a table of users, write a query to determine if users tend to order more to their primary address versus other addresses.

Note: Return the percentage of transactions ordered to their home address as home_address_percent.

Example:

Input:

transactions table:

Columns	Type
id	INTEGER
user_id	INTEGER
created_at	DATETIME
shipping_address	VARCHAR

users table:

Columns	Type
id	INTEGER
name	VARCHAR
address	VARCHAR

Example Output:

home_address_percent	0.76

SELECT
ROUND(
SUM(CASE WHEN u.address = t.shipping_address THEN 1 END)
/ COUNT(t.id)
,2)  as home_address_percent
FROM transactions as t
JOIN users as u
ON t.user_id = u.id

14. Write a query to create a new table, named flight routes, that displays unique pairs of two locations.

Example:

Note: Duplicate pairs from the flights table, such as Dallas to Seattle and Seattle to Dallas, should have one entry in the flight routes table.

flights table

Column	Type
id	INTEGER
source_location	VARCHAR
destination_location	VARCHAR

Output:

Column	Type
destination_one	VARCHAR
destination_two	VARCHAR

WITH locations AS (
SELECT id,
        LEAST(source_location, destination_location) AS point_A,
        GREATEST(destination_location, source_location) AS point_B
    FROM flights
    ORDER BY 2,3 
)

SELECT point_A AS destination_one,
        point_B AS destination_two
FROM locations
GROUP BY point_A, point_B
ORDER BY point_A, point_B

15. What are subqueries, and how can they be used in PostgreSQL?

“Subqueries are queries nested inside another query, which can be in the SELECT, FROM, or WHERE clause. They are used to perform complex queries in a modular way. Subqueries can be used to filter data, compute aggregate values, or even generate temporary result sets that can be joined with other tables.”

16. Explain the concept of views in PostgreSQL and their advantages.

“Views in PostgreSQL are virtual tables that encapsulate complex queries and present them as simplified tables. They are defined using the CREATE VIEW statement. Views can simplify query complexity, enhance security by restricting direct access to underlying tables, and provide a level of abstraction that can help with maintaining queries over time. “

17. We’re given a table of product purchases. Each row represents an individual user product purchase.

Write a query to get the number of customers that were upsold, or in other words, the number of users who bought additional products after their first purchase.

Note: If the customer purchased two things on the same day, that does not count as an upsell, as they were purchased within a similar timeframe.

Example:

Input:

transactions table

Column	Type
id	INTEGER
user_id	INTEGER
created_at	DATETIME
product_id	INTEGER
quantity	INTEGER

Output:

Column	Type
num_of_upsold_customers	INTEGER

SELECT COUNT(*) AS num_of_upsold_customers
FROM (
    SELECT user_id
    FROM (
        SELECT 
            user_id
            , DATE(created_at) AS date
        FROM transactions
        GROUP BY 1,2
    ) AS t
    GROUP BY 1
    -- Filter out users that only bought once 
    HAVING COUNT(*) > 1
) AS s

18. Given an employees and departments table, select the top 3 departments with at least ten employees and rank them according to the percentage of their employees making over $100,000.

Example:

Input:

employees table

Columns	Type
id	INTEGER
first_name	VARCHAR
last_name	VARCHAR
salary	INTEGER
department_id	INTEGER

departments table

Columns	Type
id	INTEGER
name	VARCHAR

Output:

Column	Type
percentage_over_100k	FLOAT
department_name	VARCHAR
number_of_employees	INTEGER

SELECT COUNT(*) AS num_of_upsold_customers
FROM (
    SELECT user_id
    FROM (
        SELECT 
            user_id
            , DATE(created_at) AS date
        FROM transactions
        GROUP BY 1,2
    ) AS t
    GROUP BY 1
    -- Filter out users that only bought once 
    HAVING COUNT(*) > 1
) AS s

19. Let’s say you work for an e-commerce company. Vendors can send products to the company’s warehouse to be listed on the website. Users are able to order any in-stock products and submit returns for refunds if they’re not satisfied.

The website’s front end includes a vendor portal that provides sales data at daily, weekly, monthly, quarterly, and yearly intervals.

The company wants to expand worldwide. They put you in charge of designing its end-to-end architecture, so you need to know what significant factors to consider. What clarifying questions would you ask?

What kind of end-to-end architecture would you design for this company (both for ETL and reporting)?

“For the e-commerce company’s global expansion, I’d ask about product volume, data storage needs, global traffic distribution, and compliance regulations. The architecture would likely involve a scalable cloud-based PostgreSQL database, separate ETL pipelines for real-time and historical data, a cloud data warehouse for storing historical sales by timeframe, and user-friendly reporting tools for vendors—all designed for scalability, security, and global compliance.”

20. Describe the different types of user accounts and access control mechanisms in PostgreSQL.

“In PostgreSQL, roles can represent users or groups and are assigned privileges for database objects. User roles can log in, while group roles manage privileges collectively. Privileges like SELECT, INSERT, and UPDATE control access. Roles can inherit privileges from other roles. Authentication methods include passwords, GSSAPI, SSPI, LDAP, and certificates. Row-level security (RLS) allows fine-grained access control by restricting which rows a user can access. These mechanisms ensure secure, controlled database access.”

21. Explain the concept of transactions and how they are implemented in PostgreSQL.

“Transactions in PostgreSQL are sequences of operations performed as a single logical unit of work. They adhere to the ACID properties of atomicity, consistency, isolation, and durability. A transaction begins with BEGIN and ends with either COMMIT to save changes or ROLLBACK to undo them. This ensures that the database remains in a consistent state even in case of errors or crashes.”

22. How can you back up and restore a PostgreSQL database?

“You can back up a PostgreSQL database using the pg_dump utility, which generates a script file containing SQL commands to recreate the database. To restore, you use the psql utility to execute the script file.“

23. What are the different logging mechanisms available in PostgreSQL and their purposes?

“PostgreSQL provides several logging mechanisms, including error logs to capture errors and warnings, query logs to record executed queries for performance analysis and debugging, and connection Logs to track connection attempts and disconnections.”

24. Write a query to identify the manager with the biggest team size.

You may assume there is only one manager with the largest team size.

Example:

Input:

employees table

Column	Type
id	INTEGER
name	VARCHAR
manager_id	INTEGER

managers table

Column	Type
id	INTEGER
name	VARCHAR
team	VARCHAR

Output:

Column	Type
manager	VARCHAR
team_size	INTEGER

SELECT 
  m.name AS manager,
  COUNT(e.id) AS team_size
FROM managers m 
LEFT JOIN employees e
      ON e.manager_id = m.id
GROUP BY m.id
ORDER BY COUNT(e.id) DESC
LIMIT 1

25. What are functions in PostgreSQL, and how can they be used to extend SQL functionality?

“Functions in PostgreSQL are reusable SQL or PL/pgSQL code blocks that encapsulate logic for various tasks, such as calculations, data manipulation, and complex queries. They can also extend SQL functionality by providing custom operations, returning values, or sets of rows. Functions enhance modularity and code reuse.”

26. Describe triggers in PostgreSQL and how they can be used to automate tasks.

“Triggers in PostgreSQL are special procedures that automatically execute in response to certain events on a table or view, such as INSERT, UPDATE, or DELETE. Triggers can be used to enforce business rules, validate data, update related tables, or log changes. They help automate tasks and ensure data integrity.”

27. Explain the concept of replication in PostgreSQL and its benefits for data availability.

“Replication in PostgreSQL involves copying data from one database server (master) to one or more standby servers. It can be synchronous or asynchronous. Benefits include improved data availability, load balancing, and disaster recovery. Standby servers can take over if the master fails, ensuring minimal downtime.”

28. How do you optimize the performance of PostgreSQL queries (e.g., indexing, explaining queries)?

“To optimize PostgreSQL queries, use indexing to speed up data retrieval, analyze and explain queries using the EXPLAIN command to understand execution plans, and avoid unnecessary full-table scans. Partitioning tables and optimizing join operations also improve performance. Regular maintenance like vacuuming and analyzing statistics helps keep the database efficient.”

29. What are some best practices for designing and maintaining a PostgreSQL database?

“For optimal design and maintenance of a PostgreSQL database, normalize data to reduce redundancy, use appropriate data types, and apply indexes strategically to enhance query performance. Regularly back up your data and monitor performance to identify bottlenecks. Perform routine maintenance like vacuuming and analyzing statistics. Implement security measures such as role-based access control and encryption. Use connection pooling to manage database connections efficiently.”

30. Given a transactions table with date timestamps, sample every 4th row ordered by the date.

Example:

Input:

transactions table

Columns	Type
id	INTEGER
user_id	INTEGER
created_at	DATETIME
product_id	INTEGER
quantity	INTEGER

Output:

Columns	Type
created_at	DATETIME
product_id	INTEGER

SELECT
    created_at, product_id
FROM (
    SELECT
        created_at
        , product_id
        , ROW_NUMBER() OVER (ORDER BY created_at) AS row_num
    FROM transactions
) AS t
-- mod function
WHERE row_num % 4 = 0

31. Why is it standard practice to explicitly put foreign key constraints on related tables instead of creating a normal BIGINT field? When considering foreign key constraints, when should you consider a cascade delete or a set null?

“Using foreign key constraints in PostgreSQL ensures data integrity by enforcing the relationships between tables. It prevents orphan records and maintains consistency across related tables. Foreign keys also enable cascading actions. Use CASCADE DELETE when you want related records to be automatically removed when the parent record is deleted, and SET NULL when you want to retain related records but nullify the foreign key value.”

32. Let’s say you work at an advertising firm. You have a table of users’ impressions of ad campaigns over time.

Each impression_id consists of values of user engagement specified by Excited, OK, and Bored. Write a query to find all users that are currently “Excited” and have never been “Bored” with a campaign.

Example:

Input:

ad_impressions table:

Column	Type
user_id	INTEGER
dt	DATETIME
campaign_id	INTEGER
impression_id	TEXT

Output:

Column	Type
user_id	INTEGER

SELECT
    DISTINCT user_id
FROM ad_impressions
WHERE impression_id = 'Excited'
AND user_id NOT IN (
    SELECT user_id
    FROM ad_impressions
    WHERE impression_id = 'Bored'
)

33. How can you ensure data integrity and consistency in a PostgreSQL database?

“To ensure data integrity and consistency in PostgreSQL, use constraints such as primary keys, foreign keys, unique constraints, and check constraints. Implement transactions to handle multiple operations atomically. Utilize triggers to enforce business rules and use appropriate data types and normalization techniques. Regularly back up data and monitor the database for anomalies.”

34. Explain how you would migrate data from another database system to PostgreSQL.

“To migrate data from another database to PostgreSQL, export the source database schema and data. Use tools like pg_dump for PostgreSQL or equivalent tools for other databases. Create the schema in PostgreSQL using the exported SQL script, then import the data. Tools like pgAdmin, psql, or third-party ETL tools can facilitate this process. Verify data integrity post-migration.”

35. Given the transactions table, write a query to get the max quantity purchased for each distinct product_id, every year.

The output should include the year, product_id, and max_quantity for that product, sorted by year and ascending product_id.

Example:

Input:

transactions table

Column	Type
id	INTEGER
user_id	INTEGER
created_at	DATETIME
product_id	INTEGER
quantity	INTEGER

Output:

Column	Type
year	INTEGER
product_id	INTEGER
max_quantity	INTEGER

WITH cte AS (
	SELECT
		id,
		created_at,
		quantity,
		product_id,
		dense_rank() OVER 
			(PARTITION BY product_id,
			year(created_at)
			ORDER BY
				quantity DESC) AS max_rank
		FROM