PostgreSQL Interview Preparation Guide Page 1

PostgreSQL
Interview Preparation Guide
From Zero to Confident — In One Guide

40+ Questions · 3 Difficulty Levels · Rapid Revision Section

Edition: April 2026

■ Beginner ■ Intermediate ■ Advanced

15 Questions 16 Questions 10 Questions

© 2026 PostgreSQL Interview Guide · For personal study use only

PostgreSQL Interview Preparation Guide Page 2

■ TABLE OF CONTENTS
Section 1 — Basic Questions (Q1–Q16) — Beginner Friendly
Section 2 — Intermediate Questions (Q1–Q16) — Core Interview Concepts
Section 3 — Advanced Questions (Q1–Q10) — Senior-Level Topics
Common Mistakes Section — Top 10 Interview Pitfalls
Rapid Revision Section — Last-Day Study Guide
· One-Liner Definitions (20 terms)
· Key Differences Table
· Must-Remember Rules (10 rules)
· Interview Mindset Tips (5 tips)

© 2026 PostgreSQL Interview Guide · For personal study use only

PostgreSQL Interview Preparation Guide Page 3

■ HOW TO USE THIS GUIDE

This guide is written like a conversation with a tutor — not a textbook. Every answer is in plain English. Every
technical term is explained the moment it appears. Work through Section 1 first. If a concept feels unclear,
re-read the ■ Example and the ■ Memory Tip. Before your interview, use the Rapid Revision Section to do a
lightning-fast review. Good luck — you've got this. ■

Symbol Meaning

■ Answer Clean, plain-English answer in 2–4 lines

■ Example Real SQL snippet or a relatable real-world analogy

■ Memory Tip One punchy line to lock the concept in your brain

■ Very Important Know this — likely to come up

■■ Must Know High-priority — interviewers love these

■■■ Advanced Bonus points — shows senior-level depth

© 2026 PostgreSQL Interview Guide · For personal study use only

PostgreSQL Interview Preparation Guide Page 4

■ SECTION 1 — BASIC QUESTIONS

These questions test your foundational knowledge. If you're new to PostgreSQL, start here. Master these
before moving on.

Q1. What is PostgreSQL?

★ Importance: ■ Very Important

■ Answer:
PostgreSQL is a free, open-source tool that stores your data in tables — like Excel, but much more
powerful. It follows the rules of SQL (Structured Query Language) and handles millions of rows without
breaking a sweat.

■ Example:
Think of it as a super-organized digital filing cabinet. Each drawer is a table. Each folder inside is a row.
Each paper in that folder is a column value.

■ Memory Tip: Postgres = Excel on steroids — powerful, free, and it doesn't crash!

Q2. What are the key features of PostgreSQL?

★ Importance: ■ Very Important

■ Answer:
PostgreSQL supports ACID transactions (safe writes), JSON storage, full-text search, custom data types,
and it runs on Windows, Mac, and Linux. It's also highly extensible — you can add your own functions.

■ Example:
Key features: - ACID compliance (data stays safe) - JSONB support (store JSON blazing fast) - Full-text
search (search inside text) - Custom types & extensions - Runs everywhere

■ Memory Tip: ACID + JSON + Custom types = PostgreSQL's superpowers.

Q3. What is a database and a table?

★ Importance: ■ Very Important

■ Answer:
A database is a container that holds all your data. A table is like a spreadsheet inside that container — it
has rows (records) and columns (fields). One database can hold hundreds of tables.

© 2026 PostgreSQL Interview Guide · For personal study use only

PostgreSQL Interview Preparation Guide Page 5

■ Example:
CREATE TABLE students (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
grade INTEGER
);

■ Memory Tip: Database = warehouse. Table = shelf inside it. Row = box on the shelf.

Q4. What data types does PostgreSQL support?

★ Importance: ■ Very Important

■ Answer:
PostgreSQL has types for text (TEXT, VARCHAR), numbers (INTEGER, BIGINT, NUMERIC), dates
(DATE, TIMESTAMP), booleans (BOOLEAN), and even JSON (JSON, JSONB). JSONB stores JSON in
binary — much faster to query.

■ Example:
-- Common data types in action:
name TEXT,
age INTEGER,
price NUMERIC(10,2),
created TIMESTAMP,
is_active BOOLEAN,
meta JSONB

■ Memory Tip: TEXT for words, INTEGER for whole numbers, NUMERIC for money, JSONB for
flexible data.

Q5. What is SERIAL and when do you use it?

★ Importance: ■ Very Important

■ Answer:
SERIAL is a shortcut that auto-generates an increasing number for a column. Every time you insert a
row, PostgreSQL picks the next number automatically. Perfect for ID columns.

■ Example:
CREATE TABLE orders (
order_id SERIAL PRIMARY KEY,
product TEXT
);
-- First row gets order_id = 1
-- Second row gets order_id = 2 (auto!)

■ Memory Tip: SERIAL = auto-numbered ticket machine at a deli counter.

© 2026 PostgreSQL Interview Guide · For personal study use only

PostgreSQL Interview Preparation Guide Page 6

Q6. What is a PRIMARY KEY?

★ Importance: ■■ Must Know

■ Answer:
A primary key is a column (or group of columns) that uniquely identifies each row. No two rows can have
the same primary key. It also cannot be NULL (empty).

■ Example:
CREATE TABLE employees (
emp_id SERIAL PRIMARY KEY, -- unique, not null
emp_name TEXT NOT NULL
);
-- emp_id 1, 2, 3... never repeated

■ Memory Tip: Primary key = your passport number. Unique to you, never blank.

Q7. What is a FOREIGN KEY?

★ Importance: ■■ Must Know

■ Answer:
A foreign key links one table to another. It stores the primary key of a related row in another table. This
enforces that you can't reference something that doesn't exist.

■ Example:
CREATE TABLE orders (
order_id SERIAL PRIMARY KEY,
customer_id INTEGER REFERENCES customers(id)
);
-- customer_id must exist in customers table

■ Memory Tip: Foreign key = a hotel room key — it only works for a room that actually exists.

Q8. What are constraints in PostgreSQL?

★ Importance: ■■ Must Know

■ Answer:
Constraints are rules you set on a column to control what data is allowed. Common ones: NOT NULL
(can't be empty), UNIQUE (no duplicates), CHECK (must pass a condition), DEFAULT (use this value if
nothing is given).

© 2026 PostgreSQL Interview Guide · For personal study use only

PostgreSQL Interview Preparation Guide Page 7

■ Example:
CREATE TABLE products (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
price NUMERIC CHECK (price > 0),
stock INTEGER DEFAULT 0,
sku TEXT UNIQUE
);

■ Memory Tip: Constraints = bouncers at a club — they decide what data gets in.

Q9. What is the difference between WHERE and HAVING?

★ Importance: ■■ Must Know

■ Answer:
WHERE filters rows BEFORE grouping. HAVING filters groups AFTER grouping. You use HAVING only
with GROUP BY. Think: WHERE is for individual rows, HAVING is for summarized groups.

■ Example:
-- WHERE: filter individual rows
SELECT * FROM orders WHERE amount > 100;

-- HAVING: filter after grouping

SELECT customer_id, SUM(amount)
FROM orders
GROUP BY customer_id
HAVING SUM(amount) > 500;

■ Memory Tip: WHERE = filter before cooking. HAVING = taste after cooking.

Q10. What is the ORDER BY clause?

★ Importance: ■ Very Important

■ Answer:
ORDER BY sorts your results. Use ASC for smallest-to-largest (default) or DESC for largest-to-smallest.
You can sort by multiple columns.

■ Example:
SELECT name, salary
FROM employees
ORDER BY salary DESC, name ASC;
-- Highest salary first
-- Same salary? Sort by name A-Z

■ Memory Tip: ORDER BY = sorting your laundry by colour, then by size.

© 2026 PostgreSQL Interview Guide · For personal study use only

PostgreSQL Interview Preparation Guide Page 8

Q11. What does DISTINCT do?

★ Importance: ■ Very Important

■ Answer:
DISTINCT removes duplicate rows from your result. If 10 rows have the same city, DISTINCT returns it
only once. It applies to the full row — or specific columns you list.

■ Example:
-- Without DISTINCT (may show duplicates)
SELECT city FROM customers;

-- With DISTINCT (unique cities only)

SELECT DISTINCT city FROM customers;

■ Memory Tip: DISTINCT = a highlighter that marks each unique answer only once.

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

★ Importance: ■■ Must Know

■ Answer:
DELETE removes specific rows (you pick which). TRUNCATE removes ALL rows instantly — much
faster. DROP removes the entire table (structure + data). DELETE can be rolled back; TRUNCATE
usually cannot.

■ Example:
DELETE FROM users WHERE id = 5; -- one row gone
TRUNCATE TABLE logs; -- all rows gone, fast
DROP TABLE old_reports; -- whole table gone

■ Memory Tip: Delete = erase a word. Truncate = rip out the page. Drop = burn the book.

Q13. What is the LIMIT and OFFSET clause?

★ Importance: ■ Very Important

■ Answer:
LIMIT controls how many rows to return. OFFSET skips a number of rows before returning. Together
they implement pagination — showing data in pages.

■ Example:
-- Page 1: first 10 rows
SELECT * FROM products LIMIT 10 OFFSET 0;

-- Page 2: next 10 rows

SELECT * FROM products LIMIT 10 OFFSET 10;

© 2026 PostgreSQL Interview Guide · For personal study use only

PostgreSQL Interview Preparation Guide Page 9

■ Memory Tip: LIMIT + OFFSET = a book — LIMIT is the page size, OFFSET is which page you're
on.

Q14. What is a schema in PostgreSQL?

★ Importance: ■ Very Important

■ Answer:
A schema is a namespace — a logical folder — inside a database. It groups tables, views, and functions
together. The default schema is called 'public'. You can have multiple schemas for different teams or
modules.

■ Example:
CREATE SCHEMA sales;
CREATE TABLE [Link] (id SERIAL, ...);

CREATE SCHEMA hr;

CREATE TABLE [Link] (id SERIAL, ...);

■ Memory Tip: Schema = a department in a company. Same office (database), different team
rooms.

Q15. What is the difference between CHAR, VARCHAR, and TEXT?

★ Importance: ■ Very Important

■ Answer:
CHAR(n) pads short strings with spaces to fill n characters — wastes space. VARCHAR(n) stores up to n
characters, no padding. TEXT has no length limit at all. In PostgreSQL, TEXT and VARCHAR perform
almost identically — use TEXT for simplicity.

■ Example:
-- CHAR(5): 'Hi' stored as 'Hi ' (padded)
-- VARCHAR(100): 'Hi' stored as 'Hi' (no waste)
-- TEXT: 'Hi' stored as 'Hi', no limit

name TEXT, -- recommended

email VARCHAR(255)

■ Memory Tip: TEXT = stretchy jeans. VARCHAR = jeans with a size limit. CHAR = stiff jeans
padded with socks.

Q16. What are aggregate functions?

★ Importance: ■■ Must Know

© 2026 PostgreSQL Interview Guide · For personal study use only

PostgreSQL Interview Preparation Guide Page 10

■ Answer:
Aggregate functions compute one value from many rows. Common ones: COUNT (how many rows),
SUM (total), AVG (average), MIN (smallest), MAX (largest). Always used with GROUP BY when breaking
down by category.

■ Example:
SELECT
department,
COUNT(*) AS total_staff,
AVG(salary) AS avg_salary,
MAX(salary) AS top_salary
FROM employees
GROUP BY department;

■ Memory Tip: Aggregates = measuring cups — they collapse many values into one
measurement.

© 2026 PostgreSQL Interview Guide · For personal study use only

PostgreSQL Interview Preparation Guide Page 11

■ SECTION 2 — INTERMEDIATE QUESTIONS

These questions are the heart of most PostgreSQL interviews. Expect at least 5–8 of these in a real
interview. Understand the WHY, not just the WHAT.

Q1. What are the types of JOINs in PostgreSQL?

★ Importance: ■■ Must Know

■ Answer:
INNER JOIN returns only rows that match in BOTH tables. LEFT JOIN returns all rows from the left table
+ matching rows from the right (NULLs where no match). RIGHT JOIN is the opposite. FULL OUTER
JOIN returns everything from both tables.

■ Example:
-- INNER JOIN: only matched rows
SELECT [Link], [Link]
FROM orders o
INNER JOIN customers c ON o.customer_id = [Link];

-- LEFT JOIN: all orders, even if customer missing

SELECT [Link], [Link]
FROM orders o
LEFT JOIN customers c ON o.customer_id = [Link];

■ Memory Tip: INNER = Venn diagram overlap only. LEFT = left circle fully + overlap.

Q2. What is a SELF JOIN?

★ Importance: ■ Very Important

■ Answer:
A self join joins a table to itself. Useful when rows in a table relate to other rows in the same table — like
an employee table where each employee has a manager who is also an employee.

■ Example:
SELECT
[Link] AS employee,
[Link] AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = [Link];
-- Same table, two different aliases

■ Memory Tip: Self join = looking in a mirror — same table, two different perspectives.

Q3. What is a View? Why use it?

★ Importance: ■■ Must Know

© 2026 PostgreSQL Interview Guide · For personal study use only

PostgreSQL Interview Preparation Guide Page 12

■ Answer:
A view is a saved query that looks like a table. You don't store data — it runs the query fresh every time.
Views simplify complex queries, restrict what columns users can see, and keep your SQL DRY (Don't
Repeat Yourself).

■ Example:
CREATE VIEW active_users AS
SELECT id, name, email
FROM users
WHERE is_active = TRUE;

-- Now use it like a table:

SELECT * FROM active_users;

■ Memory Tip: View = a window into your data — same data, just a specific angle.

Q4. What is a Materialized View? How is it different from a View?

★ Importance: ■■ Must Know

■ Answer:
A materialized view stores the query result physically on disk. Unlike a regular view, it doesn't re-run the
query every time — it's fast like a table. But you must REFRESH it manually to get updated data.

■ Example:
CREATE MATERIALIZED VIEW monthly_sales AS
SELECT month, SUM(amount) FROM sales GROUP BY month;

-- Refresh when data changes:

REFRESH MATERIALIZED VIEW monthly_sales;

■ Memory Tip: Regular view = live TV stream. Materialized view = recorded show (faster, but
needs updating).

Q5. What are Indexes? When should you use them?

★ Importance: ■■ Must Know

■ Answer:
An index is a data structure that helps PostgreSQL find rows faster — like a book index. Without it,
PostgreSQL reads every row (slow). With it, it jumps straight to the right rows. Use indexes on columns
you frequently search, filter, or join on.

© 2026 PostgreSQL Interview Guide · For personal study use only

PostgreSQL Interview Preparation Guide Page 13

■ Example:
-- Create an index on email (searched often)
CREATE INDEX idx_users_email ON users(email);

-- PostgreSQL now finds emails in milliseconds

-- instead of scanning millions of rows

■ Memory Tip: Index = table of contents. Without it, you read the whole book to find one word.

Q6. When should you NOT use an index?

★ Importance: ■ Very Important

■ Answer:
Indexes slow down INSERT, UPDATE, and DELETE because PostgreSQL must update the index too.
Avoid indexes on columns with very few unique values (like a 'gender' column with only M/F). Also avoid
on very small tables — a full scan is just as fast.

■ Example:
-- BAD index (only 2 unique values - useless)
CREATE INDEX idx_gender ON users(gender);

-- GOOD index (millions of unique emails)

CREATE INDEX idx_email ON users(email);

■ Memory Tip: Too many indexes = too many signposts — they slow you down when you're
walking (writing).

Q7. What are the types of Indexes in PostgreSQL?

★ Importance: ■ Very Important

■ Answer:
B-Tree (default) works for =, <, >, BETWEEN — great for most cases. Hash is only for = comparisons,
very fast. GIN (Generalized Inverted Index) is for JSONB and full-text search. GiST is for geometric data
and text similarity.

■ Example:
-- B-Tree (default, most common)
CREATE INDEX ON products(price);

-- GIN for JSONB queries

CREATE INDEX ON products USING GIN(attributes);

-- GIN for full-text search

CREATE INDEX ON articles USING GIN(to_tsvector('english', body));

© 2026 PostgreSQL Interview Guide · For personal study use only

PostgreSQL Interview Preparation Guide Page 14

■ Memory Tip: B-Tree = jack of all trades. GIN = specialist for JSON/text search.

Q8. What are Transactions and ACID?

★ Importance: ■■ Must Know

■ Answer:
A transaction groups multiple SQL statements into one safe unit. Either ALL statements succeed, or
NONE of them do. ACID stands for: Atomicity (all or nothing), Consistency (data rules always hold),
Isolation (transactions don't interfere), Durability (saved changes survive crashes).

■ Example:
BEGIN;
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;
COMMIT; -- both happen together

-- If something goes wrong:

ROLLBACK; -- neither happens

■ Memory Tip: ACID = the rules of a bank transfer. Money never disappears in transit.

Q9. What is ROLLBACK and SAVEPOINT?

★ Importance: ■ Very Important

■ Answer:
ROLLBACK undoes all changes in the current transaction back to the BEGIN. SAVEPOINT creates a
checkpoint inside a transaction — you can rollback to it without cancelling the whole transaction.

■ Example:
BEGIN;
INSERT INTO orders VALUES (1, 'Laptop');
SAVEPOINT after_insert;

UPDATE stock SET qty = qty - 1; -- oops, wrong update

ROLLBACK TO after_insert; -- undo just the update

COMMIT; -- insert still happens

■ Memory Tip: SAVEPOINT = a video game checkpoint — mess up, reload from there, not the
start.

Q10. What is Normalization? Explain 1NF, 2NF, 3NF.

★ Importance: ■■ Must Know

© 2026 PostgreSQL Interview Guide · For personal study use only

PostgreSQL Interview Preparation Guide Page 15

■ Answer:
Normalization organizes tables to reduce duplicate data. 1NF: each cell has one value (no lists in a
column). 2NF: every non-key column depends on the WHOLE primary key. 3NF: no column depends on
another non-key column (no indirect dependencies).

■ Example:
-- BAD (not 1NF): phones column has multiple values
id | name | phones
1 | Alice | 111,222

-- GOOD (1NF): one value per cell

id | name | phone
1 | Alice | 111
2 | Alice | 222

-- 2NF/3NF: split into separate tables to remove repeats

■ Memory Tip: 1NF = one thing per box. 2NF = no partial dependency. 3NF = no indirect
dependency.

Q11. What is the difference between a Function and a Stored Procedure?

★ Importance: ■■ Must Know

■ Answer:
A function MUST return a value. A stored procedure does NOT have to return anything. Functions can be
used inside SELECT queries. Procedures are called with CALL. From PostgreSQL 11+, procedures can
manage transactions (COMMIT/ROLLBACK inside).

■ Example:
-- Function: must return something
CREATE FUNCTION get_tax(price NUMERIC)
RETURNS NUMERIC AS $$
SELECT price * 0.18;
$$ LANGUAGE SQL;

SELECT get_tax(100); -- used in SELECT

-- Procedure: no return needed

CREATE PROCEDURE archive_logs()
LANGUAGE plpgsql AS $$
BEGIN
DELETE FROM logs WHERE created < NOW() - INTERVAL '1 year';
END; $$;
CALL archive_logs();

■ Memory Tip: Function = vending machine (always gives you something). Procedure =
maintenance worker (does a job, may not give you anything).

© 2026 PostgreSQL Interview Guide · For personal study use only

PostgreSQL Interview Preparation Guide Page 16

Q12. What is JSONB and how does it differ from JSON?

★ Importance: ■■ Must Know

■ Answer:
JSON stores data as plain text — it preserves exact formatting and key order. JSONB stores data in
binary format — it's faster to query and supports GIN indexes. JSONB removes duplicate keys and
doesn't preserve key order. For querying, always prefer JSONB.

■ Example:
-- Store user preferences as JSONB
CREATE TABLE users (
id SERIAL PRIMARY KEY,
prefs JSONB
);

INSERT INTO users(prefs)

VALUES ('{"theme": "dark", "lang": "en"}');

-- Query inside JSONB

SELECT prefs->>'theme' FROM users; -- returns: dark

■ Memory Tip: JSON = photo. JSONB = compressed photo. Same image, JSONB loads faster and
you can search inside it.

Q13. What is a CTE (Common Table Expression)?

★ Importance: ■ Very Important

■ Answer:
A CTE is a temporary named result set defined at the top of a query with WITH. It makes complex
queries more readable by breaking them into named chunks. CTEs can even reference themselves
(recursive CTEs).

■ Example:
WITH high_earners AS (
SELECT id, name, salary
FROM employees
WHERE salary > 80000
)
SELECT [Link], d.dept_name
FROM high_earners h
JOIN departments d ON h.dept_id = [Link];

■ Memory Tip: CTE = give a long calculation a nickname, then use that nickname in the rest of
your query.

Q14. What are Window Functions?

★ Importance: ■■ Must Know

© 2026 PostgreSQL Interview Guide · For personal study use only

PostgreSQL Interview Preparation Guide Page 17

■ Answer:
Window functions perform calculations across rows related to the current row — without collapsing them
like GROUP BY does. Common ones: ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, SUM
OVER. The OVER() clause defines the 'window'.

■ Example:
SELECT
name,
salary,
RANK() OVER (ORDER BY salary DESC) AS salary_rank,
AVG(salary) OVER () AS company_avg
FROM employees;
-- Each row still appears! No grouping.

■ Memory Tip: Window function = scoring everyone in a race while keeping each runner visible.

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

★ Importance: ■ Very Important

■ Answer:
UNION combines results from two queries and removes duplicates. UNION ALL combines results and
keeps ALL rows — including duplicates. UNION ALL is faster because it skips the duplicate-check step.

■ Example:
-- UNION: removes duplicates (slower)
SELECT city FROM customers
UNION
SELECT city FROM suppliers;

-- UNION ALL: keeps duplicates (faster)

SELECT city FROM customers
UNION ALL
SELECT city FROM suppliers;

■ Memory Tip: UNION = unique names at roll call. UNION ALL = every name read aloud, even if
repeated.

Q16. What is a Subquery?

★ Importance: ■ Very Important

■ Answer:
A subquery is a query nested inside another query. The inner query runs first and its result is used by the
outer query. You can use subqueries in SELECT, FROM, or WHERE clauses.

© 2026 PostgreSQL Interview Guide · For personal study use only

PostgreSQL Interview Preparation Guide Page 18

■ Example:
-- Subquery in WHERE
SELECT name FROM employees
WHERE salary > (
SELECT AVG(salary) FROM employees
);

-- Subquery in FROM (derived table)

SELECT dept, avg_sal
FROM (SELECT dept, AVG(salary) avg_sal FROM employees GROUP BY dept) sub
WHERE avg_sal > 60000;

■ Memory Tip: Subquery = Russian nesting doll — query inside a query.

© 2026 PostgreSQL Interview Guide · For personal study use only

PostgreSQL Interview Preparation Guide Page 19

■ SECTION 3 — ADVANCED QUESTIONS

These are senior-level topics. Don't panic if you don't know every detail. Showing you understand the
concept and WHY it matters is enough to impress.

Q1. How does PostgreSQL handle MVCC (Multi-Version Concurrency

Control)?
★ Importance: ■■■ Advanced

■ Answer:
MVCC means PostgreSQL keeps multiple versions of the same row. When you update a row,
PostgreSQL doesn't overwrite it — it creates a NEW version and marks the old one as expired. This lets
readers and writers work at the same time without locking each other out.

■ Example:
-- Transaction A reads row (sees version 1)
-- Transaction B updates row (creates version 2)
-- Transaction A still sees version 1 (its snapshot)
-- No lock! Both run simultaneously

-- VACUUM cleans up old versions:

VACUUM ANALYZE users;

■ Memory Tip: MVCC = each reader gets their own snapshot of reality. No queue needed.

Q2. What is EXPLAIN and EXPLAIN ANALYZE?

★ Importance: ■■■ Advanced

■ Answer:
EXPLAIN shows the query execution plan — how PostgreSQL PLANS to run your query. EXPLAIN
ANALYZE actually RUNS the query and shows real timing data. Use it to find slow operations (Seq Scan
= full table scan = slow on big tables).

■ Example:
EXPLAIN SELECT * FROM orders WHERE customer_id = 5;
-- Output shows: Seq Scan or Index Scan

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 5;

-- Shows actual time: Planning: 0.2ms Execution: 1.8ms
-- Seq Scan = no index being used (slow)
-- Index Scan = fast!

■ Memory Tip: EXPLAIN = GPS route preview. EXPLAIN ANALYZE = GPS tracking your actual
drive.

Q3. What are PostgreSQL isolation levels?

© 2026 PostgreSQL Interview Guide · For personal study use only

PostgreSQL Interview Preparation Guide Page 20

★ Importance: ■■■ Advanced

■ Answer:
Isolation levels control how much one transaction can see of another's uncommitted work. READ
COMMITTED (default): see committed data only. REPEATABLE READ: data doesn't change
mid-transaction. SERIALIZABLE: strictest — transactions behave as if run one-by-one.

■ Example:
-- Set isolation level for a transaction
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE id = 1;
-- Balance won't change even if others commit
COMMIT;

-- Default (READ COMMITTED):

BEGIN; -- sees latest committed data each statement

■ Memory Tip: READ COMMITTED = newspaper (today's news). REPEATABLE READ = book
(same edition throughout).

Q4. What is table partitioning in PostgreSQL?

★ Importance: ■■■ Advanced

■ Answer:
Partitioning splits one large table into smaller physical pieces (partitions) based on a column value — like
splitting a yearly log table into monthly chunks. PostgreSQL queries only the relevant partition, making
them much faster.

■ Example:
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 ('2025-01-01');

-- Query auto-routes to the right partition!

■ Memory Tip: Partitioning = organizing files into yearly folders. Find 2024 data? Open the 2024
folder only.

Q5. What is the difference between PostgreSQL and MySQL?

★ Importance: ■■ Must Know

© 2026 PostgreSQL Interview Guide · For personal study use only

PostgreSQL Interview Preparation Guide Page 21

■ Answer:
PostgreSQL is more feature-rich: supports JSONB, full ACID compliance, advanced window functions,
table inheritance, and custom types. MySQL is simpler and historically faster for read-heavy web apps.
PostgreSQL is stricter with data integrity; MySQL can silently truncate data or allow bad values by
default.

■ Example:
PostgreSQL WINS at: - JSONB (much faster than MySQL JSON) - Window functions - Full-text search -
Data types (arrays, hstore) - Strict data validation MySQL WINS at: - Simpler setup for small apps -
Slightly faster simple reads (historically) - Wider shared hosting support

■ Memory Tip: PostgreSQL = strict engineer. MySQL = friendly intern. Both good — pick based
on your needs.

Q6. What is a Trigger in PostgreSQL?

★ Importance: ■■■ Advanced

■ Answer:
A trigger is a function that runs automatically when a specific event happens on a table — like INSERT,
UPDATE, or DELETE. Triggers can run BEFORE or AFTER the event. They're used for audit logging,
enforcing rules, or auto-filling columns.

■ Example:
-- Log every update to a separate table
CREATE FUNCTION log_changes() RETURNS TRIGGER AS $$
BEGIN
INSERT INTO audit_log(table_name, action, changed_at)
VALUES (TG_TABLE_NAME, TG_OP, NOW());
RETURN NEW;
END; $$ LANGUAGE plpgsql;

CREATE TRIGGER after_employee_update

AFTER UPDATE ON employees
FOR EACH ROW EXECUTE FUNCTION log_changes();

■ Memory Tip: Trigger = a motion-sensor light — something happens, it reacts automatically.

Q7. What is connection pooling and why does PostgreSQL need it?
★ Importance: ■■■ Advanced

■ Answer:
PostgreSQL creates a new OS process for every connection. At scale, 1000 connections = 1000
processes = system overload. A connection pooler (like PgBouncer) sits between your app and
PostgreSQL — it reuses a small pool of real connections for thousands of app requests.

© 2026 PostgreSQL Interview Guide · For personal study use only

PostgreSQL Interview Preparation Guide Page 22

■ Example:
Without pooling:
App has 500 users → 500 PostgreSQL processes → server dies

With PgBouncer:
500 users → PgBouncer → 20 PostgreSQL connections
→ server stays healthy

-- PgBouncer config (simplified):

[databases]
mydb = host=localhost port=5432 dbname=mydb
[pgbouncer]
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20

■ Memory Tip: Connection pool = airport shuttle — 200 people, but only 10 vans. Smart
scheduling keeps everyone moving.

Q8. What is VACUUM in PostgreSQL?

★ Importance: ■■■ Advanced

■ Answer:
Because of MVCC, dead row versions pile up over time. VACUUM reclaims that space. VACUUM
ANALYZE also updates table statistics so the query planner makes smarter decisions. Auto-vacuum runs
in the background automatically — but you can run it manually too.

■ Example:
-- Manual vacuum
VACUUM users;

-- Vacuum + update statistics

VACUUM ANALYZE users;

-- FULL vacuum (reclaims disk space, locks table)

VACUUM FULL users;

-- Check if auto-vacuum is keeping up:

SELECT relname, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables;

■ Memory Tip: VACUUM = garbage collection. PostgreSQL hoards old row versions — VACUUM
throws them out.

Q9. What is a Recursive CTE?

★ Importance: ■■■ Advanced

© 2026 PostgreSQL Interview Guide · For personal study use only

PostgreSQL Interview Preparation Guide Page 23

■ Answer:
A recursive CTE is a CTE that references itself. It's used for hierarchical data — like an org chart
(employee → manager → their manager) or category trees. It has two parts: the anchor (starting point)
and the recursive part (next level).

■ Example:
WITH RECURSIVE org_chart AS (
-- Anchor: start with top-level (no manager)
SELECT id, name, manager_id, 1 AS level
FROM employees
WHERE manager_id IS NULL

UNION ALL

-- Recursive: find direct reports

SELECT [Link], [Link], e.manager_id, [Link] + 1
FROM employees e
JOIN org_chart o ON e.manager_id = [Link]
)
SELECT * FROM org_chart ORDER BY level;

■ Memory Tip: Recursive CTE = peeling an onion layer by layer, starting from the outside.

Q10. How does PostgreSQL handle full-text search?

★ Importance: ■■■ Advanced

■ Answer:
PostgreSQL converts text into searchable tokens called tsvector (a sorted list of unique stemmed words).
Your search query becomes a tsquery. The @@ operator matches them. Add a GIN index for fast search
across millions of rows.

■ Example:
-- Convert article body to search tokens
SELECT to_tsvector('english', 'Cats are great pets') ;
-- Returns: 'cat':1 'great':3 'pet':4

-- Search for 'cat'

SELECT title FROM articles
WHERE to_tsvector('english', body) @@ to_tsquery('cat');

-- Add GIN index for speed

CREATE INDEX ON articles
USING GIN(to_tsvector('english', body));

■ Memory Tip: Full-text search = a smart librarian who understands 'cats' and 'cat' are the same
word.

© 2026 PostgreSQL Interview Guide · For personal study use only

PostgreSQL Interview Preparation Guide Page 24

■■ TOP 10 MISTAKES IN POSTGRESQL

INTERVIEWS
These are the most common ways students lose marks in interviews. Read each one carefully — don't be
that person.

■ Mistake #1: Confusing DELETE with TRUNCATE

Why it's wrong: Students say TRUNCATE is 'the same as DELETE but faster' — that's misleading.
TRUNCATE removes ALL rows, cannot use WHERE, and in some setups can't be rolled back.
■ Correct understanding: Use DELETE when you need WHERE conditions. Use TRUNCATE only
when you want to wipe everything.

■ Mistake #2: Using CHAR instead of TEXT

Why it's wrong: CHAR pads values with spaces to fill its length. This wastes space and causes
hard-to-find bugs (e.g., 'hello' != 'hello '). In PostgreSQL, TEXT is just as fast.
■ Correct understanding: Just use TEXT or VARCHAR. Forget CHAR exists — it's a relic from ancient
databases.

■ Mistake #3: Ignoring NULL behaviour in comparisons

Why it's wrong: NULL = NULL returns NULL (not TRUE). Students write WHERE col = NULL and get
no results. This is a very common interview trip-wire.
■ Correct understanding: Always use IS NULL or IS NOT NULL. Never use = NULL or != NULL.

■ Mistake #4: Indexing every column 'just in case'

Why it's wrong: Too many indexes slow down INSERT/UPDATE/DELETE operations. Each index must
be maintained on every write.
■ Correct understanding: Index only the columns you actively filter, join, or sort on — and measure
before adding.

■ Mistake #5: Putting business logic in the wrong place

Why it's wrong: Beginners mix up when to use triggers vs application code. Triggers are hard to debug
and can cause hidden side effects.
■ Correct understanding: Use triggers for audit logging and simple auto-fills. Keep complex business
logic in your application layer.

■ Mistake #6: Not understanding JOIN behaviour with NULLs

Why it's wrong: INNER JOIN silently drops rows where the join column is NULL. Students expect all
rows and are confused when some disappear.
■ Correct understanding: Always ask: 'Do I want rows even if there's no match on the other side?' If
yes — use LEFT JOIN.

© 2026 PostgreSQL Interview Guide · For personal study use only

PostgreSQL Interview Preparation Guide Page 25

■ Mistake #7: Forgetting COMMIT inside transactions

Why it's wrong: Beginners open a transaction, make changes, close their terminal — and wonder why
nothing was saved. Without COMMIT, everything is rolled back.
■ Correct understanding: Always end a manual transaction with COMMIT. Or use ROLLBACK to
discard changes on purpose.

■ Mistake #8: Saying 'Views are faster than queries'

Why it's wrong: A regular view re-runs its query every time. It is NOT cached. Only a MATERIALIZED
view caches the result.
■ Correct understanding: Regular view = shortcut, not a cache. Materialized view = snapshot stored on
disk.

■ Mistake #9: Confusing GROUP BY with ORDER BY

Why it's wrong: GROUP BY groups rows for aggregation. ORDER BY sorts the final result. They do
completely different things.
■ Correct understanding: GROUP BY = combine rows by category. ORDER BY = sort the final list.
They can be used together.

■ Mistake #10: Not using EXPLAIN ANALYZE before adding indexes

Why it's wrong: Students add indexes blindly without checking if PostgreSQL is even using them. An
index on the wrong column wastes disk space.
■ Correct understanding: Always run EXPLAIN ANALYZE first. Check for 'Seq Scan' on large tables —
that's your sign an index might help.

© 2026 PostgreSQL Interview Guide · For personal study use only

PostgreSQL Interview Preparation Guide Page 26

■ RAPID REVISION — LAST-DAY STUDY GUIDE

Read this the morning of your interview. It won't teach you new things — it'll remind you of everything you
already know.

■ 1. One-Liner Definitions (20 Key Terms)

PostgreSQL: An open-source, ACID-compliant relational database with advanced features like JSONB and
full-text search.

ACID: Atomicity, Consistency, Isolation, Durability — the four guarantees that keep your data safe.

PRIMARY KEY: A column (or columns) that uniquely identifies every row. Cannot be NULL. Cannot repeat.

FOREIGN KEY: A column that points to the PRIMARY KEY of another table, enforcing relational integrity.

INDEX: A data structure that speeds up SELECT queries at the cost of slower writes.

VIEW: A saved SQL query that looks like a table — no data stored, re-runs every time.

MATERIALIZED VIEW: Like a view, but results are stored on disk. Must be manually REFRESH-ed.

TRANSACTION: A group of SQL statements that all succeed or all fail together.

ROLLBACK: Undo all changes in the current transaction, going back to before BEGIN.

SAVEPOINT: A checkpoint inside a transaction you can partially roll back to.

MVCC: PostgreSQL keeps multiple versions of rows so readers and writers never block each other.

VACUUM: Cleans up dead row versions left behind by MVCC, reclaiming disk space.

SERIAL: Auto-incrementing integer — PostgreSQL picks the next number automatically.

JSONB: Binary JSON — faster to query than JSON, supports GIN indexes, removes duplicate keys.

CTE: A named temporary result set defined with WITH — makes complex queries readable.

Window Function: Calculates values across related rows without collapsing them — uses OVER().

1NF: One value per cell — no comma-separated lists inside columns.

3NF: Every non-key column depends on the key, the whole key, and nothing but the key.

EXPLAIN ANALYZE: Runs your query and shows the real execution plan with actual timing data.

GIN Index: Specialized index for JSONB and full-text search — fast at finding values inside complex data.

■ 2. Key Differences at a Glance

Concept A Concept B Key Difference

PRIMARY KEY UNIQUE constraint Primary key: one per table, never NULL. Unique:
many per table, allows one NULL.

VIEW MATERIALIZED VIEW View: re-runs query every time. Materialized:

stores result, needs REFRESH.

FUNCTION STORED PROCEDURE Function: must return a value, usable in SELECT.

Procedure: optional return, called with CALL.

© 2026 PostgreSQL Interview Guide · For personal study use only

PostgreSQL Interview Preparation Guide Page 27

PostgreSQL MySQL PostgreSQL: feature-rich, strict, great for complex

queries. MySQL: simpler, wider legacy support.

DELETE TRUNCATE DELETE: row-by-row, can use WHERE, fully

transactional. TRUNCATE: all rows, faster, no
WHERE.

WHERE HAVING WHERE: filters before grouping. HAVING: filters

groups after GROUP BY.

UNION UNION ALL UNION: removes duplicates (slower). UNION ALL:

keeps all rows (faster).

JSON JSONB JSON: stored as text, preserves order. JSONB:

binary, faster queries, supports indexes.

INDEX No Index Index: fast reads, slower writes. No index: slow

reads on big tables, fast writes.

INNER JOIN LEFT JOIN INNER: only matching rows. LEFT: all left rows +
matching right rows (NULLs for gaps).

■ 3. Must-Remember Rules
1. NULL = NULL is always NULL — never TRUE. Use IS NULL.

2. SERIAL is shorthand for SEQUENCE — PostgreSQL creates a sequence object behind the scenes.

3. Every PRIMARY KEY is automatically a UNIQUE + NOT NULL constraint.

4. FOREIGN KEY prevents orphan records — you cannot reference a row that doesn't exist.

5. B-Tree index is the default and works for =, <, >, BETWEEN, LIKE 'prefix%'.

6. VACUUM is essential in heavy write workloads — auto-vacuum handles it, but know it exists.

7. Window functions use OVER() and do NOT collapse rows (unlike GROUP BY + aggregate).

8. EXPLAIN ANALYZE runs the query for real — never use it on a slow query against production without
caution.

9. ROLLBACK only works inside an active transaction (between BEGIN and COMMIT).

10. Materialized views must be REFRESH-ed manually — they don't auto-update when base data changes.

■ 4. Interview Mindset Tips

Think out loud
Interviewers want to see your reasoning, not just the final answer. Say 'I'd first check if there's an index
on that column because...' — narrate your thought process.

© 2026 PostgreSQL Interview Guide · For personal study use only

PostgreSQL Interview Preparation Guide Page 28

Admit what you don't know — then bridge it

Say: 'I haven't used that specific feature in production, but my understanding is...' This shows honesty
AND that you're not completely blank.

Ask a clarifying question first

When given a scenario, ask one clarifying question. 'Is this table expected to have millions of rows?'
shows senior-level thinking.

Use analogies to explain concepts

If you can explain MVCC using a bank snapshot analogy, you prove you truly understand it — not just
memorized it.

End with trade-offs

For any solution you give, mention a trade-off. 'I'd add an index here, but that would slow down bulk
inserts' shows real-world maturity.

You've reviewed the whole guide. Now close it, take a deep breath, and
trust yourself. ■

© 2026 PostgreSQL Interview Guide · For personal study use only