PostgreSQL Technical.
Interview cheat sheet.

PostgreSQL is an advanced open-source object-relational database system known for extensibility, standards compliance, and rich feature set. It supports advanced data types (JSON, arrays, custom types), full-text search, table inheritance, and MVCC for high concurrency. Key strengths include ACID compliance, powerful query capabilities, extensible architecture, and strong consistency. Essential for applications requiring complex queries, data integrity, advanced analytics, and scenarios where SQL standards compliance and extensibility are critical.

What is PostgreSQL?

• Open-source, object-relational database management system (ORDBMS)
• ACID compliant, supports SQL standards
• Extensible with custom functions, data types, and operators

Key Features

• MVCC (Multi-Version Concurrency Control)
• Full-text search
• JSON/JSONB support
• Table inheritance
• Foreign data wrappers

Database Operations

-- Create database
CREATE DATABASE mydb;

-- Connect to database
\c mydb

-- Drop database
DROP DATABASE mydb;

-- List databases
\l

Table Operations

-- Create table
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Alter table
ALTER TABLE users ADD COLUMN age INTEGER;
ALTER TABLE users DROP COLUMN age;
ALTER TABLE users RENAME COLUMN name TO full_name;

-- Drop table
DROP TABLE users;
DROP TABLE IF EXISTS users CASCADE;

Common Data Types

• Numeric: INTEGER, BIGINT, DECIMAL, NUMERIC, REAL, DOUBLE PRECISION
• Character: VARCHAR(n), CHAR(n), TEXT
• Date/Time: DATE, TIME, TIMESTAMP, INTERVAL
• Boolean: BOOLEAN
• UUID: UUID
• JSON: JSON, JSONB
• Arrays: INTEGER[], TEXT[]

Special Types

-- SERIAL (auto-increment)
id SERIAL PRIMARY KEY

-- Arrays
tags TEXT[]

-- JSON/JSONB
data JSONB

-- UUID
id UUID DEFAULT gen_random_uuid()

-- INSERT
INSERT INTO users (name, email) VALUES ('John', 'john@email.com');
INSERT INTO users (name, email) VALUES 
    ('Alice', 'alice@email.com'),
    ('Bob', 'bob@email.com');

-- SELECT
SELECT * FROM users;
SELECT name, email FROM users WHERE age > 18;
SELECT * FROM users ORDER BY created_at DESC LIMIT 10;

-- UPDATE
UPDATE users SET name = 'Jane' WHERE id = 1;
UPDATE users SET age = age + 1 WHERE age IS NOT NULL;

-- DELETE
DELETE FROM users WHERE id = 1;
DELETE FROM users WHERE created_at < NOW() - INTERVAL '1 year';

-- INNER JOIN
SELECT u.name, o.order_date 
FROM users u 
INNER JOIN orders o ON u.id = o.user_id;

-- LEFT JOIN
SELECT u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;

-- RIGHT JOIN
SELECT * FROM orders o
RIGHT JOIN users u ON o.user_id = u.id;

-- FULL OUTER JOIN
SELECT * FROM users u
FULL OUTER JOIN orders o ON u.id = o.user_id;

-- CROSS JOIN
SELECT * FROM products CROSS JOIN categories;

Types of Indexes

• B-tree (default): Range queries, equality
• Hash: Equality only
• GiST: Geometric data, full-text search
• GIN: Arrays, JSONB, full-text search
• BRIN: Large tables with natural ordering

-- Create index
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_name_email ON users(name, email);

-- Unique index
CREATE UNIQUE INDEX idx_users_username ON users(username);

-- Partial index
CREATE INDEX idx_active_users ON users(email) WHERE active = true;

-- Expression index
CREATE INDEX idx_users_lower_email ON users(LOWER(email));

-- Drop index
DROP INDEX idx_users_email;

-- Primary Key
id SERIAL PRIMARY KEY

-- Foreign Key
user_id INTEGER REFERENCES users(id) ON DELETE CASCADE

-- Unique
email VARCHAR(255) UNIQUE

-- Check
age INTEGER CHECK (age >= 0 AND age <= 150)

-- Not Null
name VARCHAR(100) NOT NULL

-- Default
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP

-- Named constraints
CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id)
CONSTRAINT chk_price CHECK (price > 0)

ACID Properties

• Atomicity: All or nothing
• Consistency: Valid state to valid state
• Isolation: Concurrent transactions don't interfere
• Durability: Committed data persists

-- Transaction example
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

-- Rollback
BEGIN;
DELETE FROM users WHERE id = 1;
ROLLBACK;

-- Savepoints
BEGIN;
INSERT INTO users (name) VALUES ('Test');
SAVEPOINT my_savepoint;
DELETE FROM orders;
ROLLBACK TO my_savepoint;
COMMIT;

Isolation Levels

-- Set isolation level
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;    -- Default
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

CTEs (Common Table Expressions)

WITH user_orders AS (
    SELECT user_id, COUNT(*) as order_count
    FROM orders
    GROUP BY user_id
)
SELECT u.name, uo.order_count
FROM users u
JOIN user_orders uo ON u.id = uo.user_id;

-- Recursive CTE
WITH RECURSIVE subordinates AS (
    SELECT id, name, manager_id
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT e.id, e.name, e.manager_id
    FROM employees e
    JOIN subordinates s ON e.manager_id = s.id
)
SELECT * FROM subordinates;

Window Functions

-- ROW_NUMBER
SELECT name, salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC) as rank
FROM employees;

-- RANK and DENSE_RANK
SELECT name, department, salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) as rank,
       DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) as dense_rank
FROM employees;

-- LAG and LEAD
SELECT date, sales,
       LAG(sales, 1) OVER (ORDER BY date) as prev_sales,
       LEAD(sales, 1) OVER (ORDER BY date) as next_sales
FROM daily_sales;

-- Running total
SELECT date, amount,
       SUM(amount) OVER (ORDER BY date) as running_total
FROM transactions;

-- Create table with JSONB
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    data JSONB
);

-- Insert JSON
INSERT INTO products (data) VALUES 
    ('{"name": "Phone", "price": 599, "specs": {"ram": "8GB"}}');

-- Query JSON
SELECT data->>'name' as name FROM products;
SELECT data->'specs'->>'ram' as ram FROM products;

-- JSON operators
SELECT * FROM products WHERE data @> '{"name": "Phone"}';
SELECT * FROM products WHERE data ? 'price';
SELECT * FROM products WHERE data->'specs' ? 'ram';

-- Update JSON
UPDATE products SET data = jsonb_set(data, '{price}', '699');
UPDATE products SET data = data || '{"color": "black"}';

EXPLAIN and ANALYZE

EXPLAIN SELECT * FROM users WHERE email = 'test@email.com';
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@email.com';
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM large_table;

Query Optimization Tips

1. Use indexes on columns in WHERE, JOIN, ORDER BY
2. **Avoid SELECT *** - select only needed columns
3. Use LIMIT for large result sets
4. Avoid N+1 queries - use joins instead
5. Use prepared statements for repeated queries
6. Partition large tables

Vacuum and Analyze

-- Manual vacuum
VACUUM users;
VACUUM FULL users;

-- Update statistics
ANALYZE users;

-- Auto-vacuum settings
ALTER TABLE users SET (autovacuum_enabled = true);

-- Create view
CREATE VIEW active_users AS
SELECT * FROM users WHERE active = true;

-- Materialized view
CREATE MATERIALIZED VIEW user_statistics AS
SELECT user_id, COUNT(*) as order_count, SUM(total) as total_spent
FROM orders
GROUP BY user_id;

-- Refresh materialized view
REFRESH MATERIALIZED VIEW user_statistics;
REFRESH MATERIALIZED VIEW CONCURRENTLY user_statistics;

-- Function
CREATE OR REPLACE FUNCTION get_user_count() 
RETURNS INTEGER AS $$
BEGIN
    RETURN (SELECT COUNT(*) FROM users);
END;
$$ LANGUAGE plpgsql;

-- Function with parameters
CREATE OR REPLACE FUNCTION calculate_age(birth_date DATE)
RETURNS INTEGER AS $$
BEGIN
    RETURN DATE_PART('year', AGE(birth_date));
END;
$$ LANGUAGE plpgsql;

-- Trigger function
CREATE OR REPLACE FUNCTION update_modified_time()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Create trigger
CREATE TRIGGER update_users_modtime
BEFORE UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION update_modified_time();

-- Create user
CREATE USER myuser WITH PASSWORD 'password';

-- Grant permissions
GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;
GRANT SELECT, INSERT, UPDATE ON users TO myuser;
GRANT USAGE ON SCHEMA public TO myuser;

-- Revoke permissions
REVOKE DELETE ON users FROM myuser;

-- Row Level Security
ALTER TABLE users ENABLE ROW LEVEL SECURITY;

CREATE POLICY user_policy ON users
FOR SELECT
USING (id = current_user_id());

# Backup database
pg_dump dbname > backup.sql
pg_dump -Fc dbname > backup.dump  # Custom format

# Restore database
psql dbname < backup.sql
pg_restore -d dbname backup.dump

# Backup specific tables
pg_dump -t users -t orders dbname > tables_backup.sql

# Point-in-time recovery
# Requires WAL archiving configuration

Partitioning

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

-- Create partitions
CREATE TABLE sales_2023 PARTITION OF sales
    FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

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

-- List partitioning
CREATE TABLE users_by_region (
    id SERIAL,
    name TEXT,
    region TEXT
) PARTITION BY LIST (region);

CREATE TABLE users_us PARTITION OF users_by_region
    FOR VALUES IN ('US', 'USA');

Advanced JSONB Operations

-- Complex JSON queries
SELECT id, data->'user'->>'name' as username
FROM events 
WHERE data @> '{"type": "login"}';

-- JSON path queries (PostgreSQL 12+)
SELECT jsonb_path_query(data, '$.users[*].age ? (@ > 18)')
FROM user_data;

-- JSON aggregation
SELECT jsonb_agg(
    jsonb_build_object('name', name, 'email', email)
) as users_json
FROM users;

-- Update nested JSON
UPDATE products 
SET data = jsonb_set(data, '{specs,cpu}', '"Intel i7"')
WHERE id = 1;

Custom Types and Domains

-- Create custom type
CREATE TYPE address AS (
    street TEXT,
    city TEXT,
    zipcode TEXT
);

-- Use custom type
CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    name TEXT,
    address address
);

-- Create domain with constraints
CREATE DOMAIN email_domain AS TEXT
CHECK (VALUE ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email email_domain
);

Common Query Patterns

Efficient Pagination

-- Cursor-based pagination (more efficient)
SELECT * FROM posts 
WHERE id > 1000  -- last_seen_id
ORDER BY id 
LIMIT 20;

-- Offset pagination (less efficient for large offsets)
SELECT * FROM posts 
ORDER BY created_at DESC 
LIMIT 20 OFFSET 100;

Bulk Operations

-- Bulk insert with conflict resolution
INSERT INTO users (email, name) 
VALUES 
    ('user1@email.com', 'User 1'),
    ('user2@email.com', 'User 2')
ON CONFLICT (email) 
DO UPDATE SET name = EXCLUDED.name;

-- Bulk update with JOIN
UPDATE users 
SET status = 'premium'
FROM orders 
WHERE users.id = orders.user_id 
AND orders.total > 1000;

Advanced Analytics

-- Time series analysis
SELECT 
    date_trunc('day', created_at) as day,
    count(*) as daily_count,
    sum(count(*)) OVER (ORDER BY date_trunc('day', created_at)) as running_total
FROM orders 
GROUP BY date_trunc('day', created_at)
ORDER BY day;

-- Cohort analysis
WITH cohorts AS (
    SELECT 
        user_id,
        date_trunc('month', created_at) as cohort_month
    FROM users
),
user_activities AS (
    SELECT 
        user_id,
        date_trunc('month', activity_date) as activity_month
    FROM user_activities
)
SELECT 
    c.cohort_month,
    COUNT(DISTINCT c.user_id) as cohort_size,
    COUNT(DISTINCT ua.user_id) as active_users
FROM cohorts c
LEFT JOIN user_activities ua ON c.user_id = ua.user_id
GROUP BY c.cohort_month, ua.activity_month;

Index Strategy

-- Composite index column order matters
CREATE INDEX idx_users_status_created ON users(status, created_at);

-- Good: Uses index
SELECT * FROM users WHERE status = 'active' AND created_at > '2024-01-01';

-- Less efficient: Only partially uses index
SELECT * FROM users WHERE created_at > '2024-01-01';

-- Include index for covering queries
CREATE INDEX idx_users_email_include 
ON users(email) INCLUDE (name, status);

Query Optimization

-- Use EXISTS instead of IN for subqueries
-- Better performance
SELECT * FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

-- Can be slower
SELECT * FROM users 
WHERE id IN (SELECT user_id FROM orders);

-- Use LATERAL joins for correlated subqueries
SELECT u.name, recent_orders.order_count
FROM users u
CROSS JOIN LATERAL (
    SELECT COUNT(*) as order_count
    FROM orders o 
    WHERE o.user_id = u.id 
    AND o.created_at > CURRENT_DATE - INTERVAL '30 days'
) recent_orders;

Connection and Resource Management

-- Monitor active connections
SELECT state, count(*) 
FROM pg_stat_activity 
GROUP BY state;

-- Find long-running queries
SELECT pid, now() - pg_stat_activity.query_start as duration, query
FROM pg_stat_activity
WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes';

-- Terminate problematic queries
SELECT pg_terminate_backend(pid) 
FROM pg_stat_activity 
WHERE pid = <problem_pid>;

PostgreSQL vs MySQL

PostgreSQL vs MongoDB

Architecture Questions

1. MVCC (Multi-Version Concurrency Control)

   • How PostgreSQL handles concurrent reads/writes
   • No read locks, writers don't block readers
   • Each transaction sees consistent snapshot

2. WAL (Write-Ahead Logging)

   • Changes logged before data pages modified
   • Enables crash recovery and replication
   • Point-in-time recovery capabilities

3. Vacuum Process

   • Reclaims space from deleted/updated rows
   • Prevents transaction ID wraparound
   • Auto-vacuum vs manual vacuum

Performance Questions

1. Index Types and Usage

   • B-tree: Default, range queries
   • Hash: Equality only
   • GIN: Full-text, JSONB
   • GiST: Geometric, full-text

2. Query Planning

   • Cost-based optimizer
   • Reading EXPLAIN output
   • Statistics importance

3. Common Bottlenecks

   • Missing indexes
   • Inefficient queries
   • Lock contention
   • Connection limits

Data Modeling Questions

1. Normalization vs Denormalization
2. When to use JSONB vs separate tables
3. Partitioning strategies
4. Inheritance vs composition

Development

1. Use appropriate data types - Don't use TEXT for everything
2. Index foreign keys and frequently queried columns
3. Use prepared statements to prevent SQL injection
4. Handle NULL values explicitly with COALESCE/NULLIF
5. Use transactions for data consistency

Operations

1. Regular maintenance - VACUUM, ANALYZE, REINDEX
2. Monitor performance - pg_stat_statements, slow query log
3. Connection pooling - pgBouncer, pgpool-II
4. Backup strategy - pg_dump, WAL archiving, replicas
5. Security - Row-level security, SSL, proper user permissions

Remember: PostgreSQL's strength lies in its extensibility and standards compliance. Focus on understanding MVCC, advanced data types, and performance optimization techniques for interviews.