PostgreSQL Advanced Concepts

A. Advanced Data Types

JSONB (Binary JSON with indexing)

Explanation: JSONB stores JSON data in a binary format that supports indexing and efficient querying. Better than JSON type for most use cases.

sql

-- Create table with JSONB
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT,
    attributes JSONB,
    tags JSONB
);

-- Insert JSON data
INSERT INTO products (name, attributes) 
VALUES ('Laptop', '{"brand": "Dell", "ram": 16, "ports": ["USB-C", "HDMI"]}');

-- Query JSONB (-> and ->> operators)
SELECT name, attributes->'brand' as brand FROM products;
SELECT name, attributes->>'brand' as brand_text FROM products;

-- Check existence
SELECT * FROM products WHERE attributes ? 'brand';

-- JSONB indexing (GIN index)
CREATE INDEX idx_gin_attributes ON products USING GIN (attributes);

Arrays & Array Operators

Explanation: PostgreSQL supports arrays of any data type with rich operator support.

sql

-- Create table with arrays
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name TEXT,
    email TEXT[],
    scores INTEGER[],
    schedule TEXT[][]
);

-- Insert array data
INSERT INTO users (name, email, scores) 
VALUES ('John', ARRAY['john@email.com', 'john@work.com'], ARRAY[85, 92, 78]);

-- Query arrays
SELECT * FROM users WHERE 'john@work.com' = ANY(email);
SELECT * FROM users WHERE scores @> ARRAY[85];  -- Contains 85
SELECT name, array_length(scores, 1) as score_count FROM users;

-- Array functions
SELECT array_append(scores, 95) FROM users;
SELECT unnest(scores) as individual_score FROM users;

ENUM

Explanation: Custom enumerated type with fixed set of values, better than text for constrained values.

sql

-- Create ENUM type
CREATE TYPE user_status AS ENUM ('active', 'inactive', 'pending', 'banned');

-- Use ENUM in table
CREATE TABLE users_enum (
    id SERIAL PRIMARY KEY,
    name TEXT,
    status user_status DEFAULT 'pending'
);

-- Insert with ENUM
INSERT INTO users_enum (name, status) VALUES ('Alice', 'active');

-- Benefits: Type safety, ordering, efficient storage

HSTORE

Explanation: Key-value store within PostgreSQL for simple key-value pairs.

sql

-- Enable hstore extension
CREATE EXTENSION IF NOT EXISTS hstore;

-- Create table with hstore
CREATE TABLE product_hstore (
    id SERIAL PRIMARY KEY,
    name TEXT,
    properties HSTORE
);

-- Insert data
INSERT INTO product_hstore (name, properties) 
VALUES ('Phone', 'color=>"black", storage=>"256GB", weight=>"200g"');

-- Query hstore
SELECT * FROM product_hstore WHERE properties -> 'color' = 'black';
SELECT name, properties -> 'storage' as storage FROM product_hstore;

Range Types

Explanation: Represents a range of values (dates, numbers, timestamps) with inclusive/exclusive bounds.

sql

-- Date range
CREATE TABLE bookings (
    id SERIAL PRIMARY KEY,
    room TEXT,
    reservation DATERANGE,
    EXCLUDE USING GIST (room WITH =, reservation WITH &&)  -- Prevent overlaps
);

-- Insert ranges
INSERT INTO bookings (room, reservation) 
VALUES ('101', '[2024-01-01, 2024-01-05)');

-- Query ranges
SELECT * FROM bookings WHERE reservation @> DATE '2024-01-02';
SELECT * FROM bookings WHERE reservation && '[2024-01-03, 2024-01-07)';

-- Range operators: @> (contains), <@ (contained by), && (overlaps)

B. Functions & Expressions

Built-in Functions

Explanation: PostgreSQL provides extensive built-in functions for data manipulation.

sql

-- Math functions
SELECT ROUND(123.456, 2), CEIL(123.1), FLOOR(123.9), ABS(-100);

-- Aggregate functions
SELECT array_agg(name) as all_names, string_agg(name, ', ') as csv_names 
FROM employees;

-- Window functions
SELECT name, salary, RANK() OVER (ORDER BY salary DESC) as salary_rank 
FROM employees;

-- System functions
SELECT VERSION(), CURRENT_USER, CURRENT_TIMESTAMP, txid_current();

Conditional Logic (CASE)

Explanation: SQL's IF-THEN-ELSE statement for conditional logic in queries.

sql

-- Simple CASE
SELECT name, salary,
    CASE 
        WHEN salary > 100000 THEN 'High'
        WHEN salary > 50000 THEN 'Medium'
        ELSE 'Low'
    END as salary_level
FROM employees;

-- Searched CASE in aggregate
SELECT 
    department,
    COUNT(*) as total,
    SUM(CASE WHEN salary > 100000 THEN 1 ELSE 0 END) as high_earners,
    AVG(CASE WHEN gender = 'M' THEN salary END) as avg_male_salary
FROM employees
GROUP BY department;

-- NULL handling with COALESCE
SELECT name, COALESCE(bonus, 0) as bonus_or_zero FROM employees;

String & Date Functions

Explanation: Powerful functions for string and date manipulation.

sql

-- String functions
SELECT 
    UPPER(name) as upper_name,
    LOWER(name) as lower_name,
    LENGTH(name) as name_length,
    SUBSTRING(name FROM 1 FOR 3) as first_3_chars,
    REPLACE(name, ' ', '_') as name_no_spaces,
    CONCAT(first_name, ' ', last_name) as full_name
FROM employees;

-- Date functions
SELECT 
    CURRENT_DATE as today,
    CURRENT_TIMESTAMP as now,
    AGE('2024-12-31', CURRENT_DATE) as time_until_eoy,
    EXTRACT(YEAR FROM hire_date) as hire_year,
    DATE_TRUNC('month', hire_date) as hire_month,
    hire_date + INTERVAL '1 year' as anniversary
FROM employees;

C. Views

Views

Explanation: Virtual tables based on SQL queries. Data is not stored separately.

sql

-- Create view
CREATE OR REPLACE VIEW active_employees AS
SELECT id, name, department, salary
FROM employees
WHERE is_active = true
  AND termination_date IS NULL;

-- Use view like a table
SELECT * FROM active_employees WHERE department = 'Engineering';

-- Updateable view (if simple enough)
UPDATE active_employees SET salary = salary * 1.05;

-- View with check option
CREATE VIEW high_paid_employees AS
SELECT * FROM employees WHERE salary > 100000
WITH CHECK OPTION;  -- Prevents inserts that don't satisfy WHERE

Materialized Views

Explanation: Physical snapshot of query results that can be refreshed periodically. Faster but stale data.

sql

-- Create materialized view
CREATE MATERIALIZED VIEW department_stats AS
SELECT 
    department,
    COUNT(*) as employee_count,
    AVG(salary) as avg_salary,
    SUM(salary) as total_salary
FROM employees
GROUP BY department;

-- Query materialized view (fast)
SELECT * FROM department_stats ORDER BY avg_salary DESC;

-- Refresh strategies
REFRESH MATERIALIZED VIEW department_stats;  -- Full refresh
REFRESH MATERIALIZED VIEW CONCURRENTLY department_stats;  -- Concurrent (PostgreSQL 9.4+)

-- Add indexes to materialized views
CREATE UNIQUE INDEX idx_dept_stats ON department_stats (department);

Refresh Strategies

Explanation: When and how to refresh materialized views.

sql

-- Manual refresh
REFRESH MATERIALIZED VIEW sales_summary;

-- Scheduled via cron/pgAgent
-- */5 * * * * psql -c "REFRESH MATERIALIZED VIEW hourly_stats"

-- Trigger-based refresh (example)
CREATE OR REPLACE FUNCTION refresh_mv_on_employee_change()
RETURNS TRIGGER AS $$
BEGIN
    REFRESH MATERIALIZED VIEW CONCURRENTLY department_stats;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

-- Note: PostgreSQL doesn't have automatic refresh built-in

D. Transactions

BEGIN, COMMIT, ROLLBACK

Explanation: ACID transaction control - group operations as atomic units.

sql

-- Start transaction
BEGIN;

-- Multiple operations
INSERT INTO accounts (name, balance) VALUES ('Alice', 1000);
UPDATE accounts SET balance = balance - 100 WHERE name = 'Alice';
UPDATE accounts SET balance = balance + 100 WHERE name = 'Bob';

-- Either commit or rollback
COMMIT;  -- Save all changes
-- OR
ROLLBACK;  -- Discard all changes since BEGIN

-- Savepoints within transactions
BEGIN;
INSERT INTO table1 VALUES (1);
SAVEPOINT my_savepoint;
INSERT INTO table1 VALUES (2);
ROLLBACK TO my_savepoint;  -- Undo only second insert
COMMIT;  -- First insert remains

ACID Properties

Explanation: Four guarantees for reliable transactions.

sql

-- Atomicity: All or nothing
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- Both succeed or both fail
COMMIT;

-- Consistency: Database rules maintained
BEGIN;
-- Foreign key constraints, unique constraints, check constraints
-- are validated at COMMIT
COMMIT;

-- Isolation: Concurrent transactions don't interfere
-- Set via: SET TRANSACTION ISOLATION LEVEL ...

-- Durability: Committed changes survive crashes
-- PostgreSQL WAL (Write Ahead Logging) ensures this

Isolation Levels

Explanation: Control how transaction isolation is enforced.

sql

-- Set isolation level per transaction
BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- Or
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- Four levels in PostgreSQL:
-- 1. READ UNCOMMITTED (treated as READ COMMITTED in PostgreSQL)
-- 2. READ COMMITTED (default) - See only committed data
-- 3. REPEATABLE READ - Snapshot at transaction start
-- 4. SERIALIZABLE - Full serialization, most strict

-- Example: Prevent non-repeatable reads
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE id = 1;  -- Returns 1000
-- Another transaction updates balance to 900
SELECT balance FROM accounts WHERE id = 1;  -- Still returns 1000
COMMIT;

MVCC (Multi-Version Concurrency Control) ⭐

Explanation: PostgreSQL's core mechanism for handling concurrent access without locking.

sql

-- Each row has system columns
SELECT xmin, xmax, ctid, * FROM employees LIMIT 1;

-- xmin: Transaction ID that created the row
-- xmax: Transaction ID that deleted/updated the row (or 0)
-- ctid: Physical location of the row

-- How MVCC works:
-- 1. UPDATE creates new row version, marks old with xmax
-- 2. DELETE marks row with xmax
-- 3. Each transaction sees snapshot at start time

-- Check transaction IDs
SELECT txid_current(), pg_backend_pid();

-- Monitor MVCC bloat
SELECT 
    schemaname, tablename,
    n_dead_tup as dead_rows,
    n_live_tup as live_rows
FROM pg_stat_user_tables;

-- Clean up dead rows (VACUUM)
VACUUM ANALYZE employees;  -- Manual cleanup
-- Or enable autovacuum (default)

-- Transaction visibility example
-- Transaction A:
BEGIN;
UPDATE employees SET salary = 50000 WHERE id = 1;
-- Row is marked with xmax for other transactions
-- Transaction B sees old value until A commits

Key MVCC Benefits:

• Readers don't block writers

• Writers don't block readers

• No dirty reads possible

• Automatic snapshot isolation

MVCC Trade-offs:

• Table bloat from old row versions

• Need for VACUUM maintenance

• Transaction ID wraparound risk