PostgreSQL Advanced Features

JSONB

PostgreSQL’s JSONB stores JSON as a binary format — indexable, fast to query, and space-efficient.

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

INSERT INTO products (name, attributes) VALUES
('Laptop', '{"brand": "Dell", "specs": {"ram": 16, "storage": 512}, "tags": ["portable", "work"]}');

-- Query JSONB fields
SELECT name, attributes->>'brand' AS brand FROM products;
SELECT name, attributes->'specs'->>'ram' AS ram FROM products;

-- Filter by JSONB value
SELECT * FROM products WHERE attributes->>'brand' = 'Dell';
SELECT * FROM products WHERE (attributes->'specs'->>'ram')::int > 8;

-- Array contains
SELECT * FROM products WHERE attributes->'tags' ? 'portable';

-- Object contains (@> operator)
SELECT * FROM products WHERE attributes @> '{"brand": "Dell"}';

-- Index on JSONB (GIN — enables @>, ?, ?|, ?& operators)
CREATE INDEX idx_products_attributes ON products USING GIN (attributes);

-- Index on a specific JSONB path
CREATE INDEX idx_products_brand ON products ((attributes->>'brand'));

---

Full-Text Search

-- Basic full-text search
SELECT title FROM articles
WHERE to_tsvector('english', body) @@ to_tsquery('english', 'postgresql & performance');

-- Store pre-computed tsvector for performance
ALTER TABLE articles ADD COLUMN search_vector TSVECTOR;

UPDATE articles SET search_vector = to_tsvector('english', coalesce(title,'') || ' ' || coalesce(body,''));

-- GIN index on the vector
CREATE INDEX idx_articles_search ON articles USING GIN (search_vector);

-- Keep it up to date automatically
CREATE TRIGGER articles_search_update
BEFORE INSERT OR UPDATE ON articles
FOR EACH ROW EXECUTE FUNCTION
    tsvector_update_trigger(search_vector, 'pg_catalog.english', title, body);

-- Ranked results
SELECT title,
       ts_rank(search_vector, query) AS rank,
       ts_headline('english', body, query) AS snippet
FROM articles, to_tsquery('english', 'postgresql') query
WHERE search_vector @@ query
ORDER BY rank DESC
LIMIT 10;

---

Table Partitioning

Split large tables into smaller physical pieces while keeping them as one logical table.

Range partitioning (by date):

CREATE TABLE orders (
    id BIGSERIAL,
    customer_id INT,
    amount DECIMAL(10,2),
    created_at TIMESTAMPTZ NOT NULL
) PARTITION BY RANGE (created_at);

-- Create partitions per quarter
CREATE TABLE orders_2024_q1 PARTITION OF orders
    FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');

CREATE TABLE orders_2024_q2 PARTITION OF orders
    FOR VALUES FROM ('2024-04-01') TO ('2024-07-01');

-- Indexes on partitioned table apply to all partitions
CREATE INDEX ON orders (created_at);
CREATE INDEX ON orders (customer_id);

-- Queries automatically use only relevant partitions (partition pruning)
SELECT * FROM orders WHERE created_at >= '2024-01-01' AND created_at < '2024-04-01';

List partitioning (by region):

CREATE TABLE events (
    id BIGSERIAL,
    region TEXT NOT NULL,
    payload JSONB
) PARTITION BY LIST (region);

CREATE TABLE events_us PARTITION OF events FOR VALUES IN ('us-east', 'us-west');
CREATE TABLE events_eu PARTITION OF events FOR VALUES IN ('eu-west', 'eu-north');
CREATE TABLE events_other PARTITION OF events DEFAULT;

Dropping old partitions (fast data retention):

-- Drop an entire partition — instant, no row-by-row DELETE
DROP TABLE orders_2022_q1;

-- Or detach and archive
ALTER TABLE orders DETACH PARTITION orders_2022_q1;

---

Triggers

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

-- Attach trigger to table
CREATE TRIGGER set_updated_at
BEFORE UPDATE ON orders
FOR EACH ROW EXECUTE FUNCTION update_modified_at();

-- Audit log trigger
CREATE TABLE audit_log (
    id BIGSERIAL PRIMARY KEY,
    table_name TEXT,
    operation TEXT,
    old_data JSONB,
    new_data JSONB,
    changed_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE OR REPLACE FUNCTION audit_changes()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO audit_log (table_name, operation, old_data, new_data)
    VALUES (TG_TABLE_NAME, TG_OP,
            CASE WHEN TG_OP = 'DELETE' THEN row_to_json(OLD)::JSONB END,
            CASE WHEN TG_OP IN ('INSERT','UPDATE') THEN row_to_json(NEW)::JSONB END);
    RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER orders_audit
AFTER INSERT OR UPDATE OR DELETE ON orders
FOR EACH ROW EXECUTE FUNCTION audit_changes();

---

Stored Procedures and Functions

-- Function — returns a value, can be used in SELECT
CREATE OR REPLACE FUNCTION calculate_order_total(order_id INT)
RETURNS DECIMAL(10,2) AS $$
DECLARE
    total DECIMAL(10,2);
BEGIN
    SELECT SUM(quantity * unit_price)
    INTO total
    FROM order_items
    WHERE order_id = $1;

    RETURN COALESCE(total, 0);
END;
$$ LANGUAGE plpgsql;

SELECT id, calculate_order_total(id) AS total FROM orders;

-- Procedure — can use COMMIT/ROLLBACK (PostgreSQL 11+)
CREATE OR REPLACE PROCEDURE process_refund(p_order_id INT, p_amount DECIMAL)
LANGUAGE plpgsql AS $$
BEGIN
    UPDATE orders SET refunded_amount = refunded_amount + p_amount
    WHERE id = p_order_id;

    INSERT INTO refund_log (order_id, amount, processed_at)
    VALUES (p_order_id, p_amount, NOW());

    COMMIT;
END;
$$;

CALL process_refund(42, 25.00);

---

Generated Columns and Constraints

-- Generated column (computed automatically)
CREATE TABLE rectangles (
    width FLOAT,
    height FLOAT,
    area FLOAT GENERATED ALWAYS AS (width * height) STORED
);

-- Check constraints
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL CHECK (LENGTH(name) >= 2),
    price DECIMAL(10,2) CHECK (price >= 0),
    discount DECIMAL(5,2) CHECK (discount BETWEEN 0 AND 100),
    stock INT DEFAULT 0 CHECK (stock >= 0)
);

-- Exclusion constraint (prevent overlapping reservations)
CREATE TABLE reservations (
    room_id INT,
    during TSTZRANGE,
    EXCLUDE USING GIST (room_id WITH =, during WITH &&)
);

---

pg_cron (Scheduled Jobs)

-- Install extension
CREATE EXTENSION pg_cron;

-- Schedule a job (runs every hour)
SELECT cron.schedule('hourly-cleanup', '0 * * * *',
    'DELETE FROM sessions WHERE expires_at < NOW()');

-- Daily vacuum at 3am
SELECT cron.schedule('nightly-vacuum', '0 3 * * *',
    'VACUUM ANALYZE orders');

-- List scheduled jobs
SELECT * FROM cron.job;

-- Remove a job
SELECT cron.unschedule('hourly-cleanup');

---

Useful Extensions

-- UUID generation
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
SELECT uuid_generate_v4();

-- Or use built-in (PostgreSQL 13+)
SELECT gen_random_uuid();

-- pg_stat_statements — track query performance
CREATE EXTENSION pg_stat_statements;
SELECT query, calls, total_exec_time, mean_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

-- hstore — key-value pairs
CREATE EXTENSION hstore;
SELECT 'color => red, size => large'::hstore -> 'color';

-- earthdistance + cube — geographical distance
CREATE EXTENSION cube;
CREATE EXTENSION earthdistance;
SELECT earth_distance(ll_to_earth(51.5, -0.1), ll_to_earth(48.9, 2.3)) / 1000 AS km;