Advanced SQL: JSONB, Materialized Views, Partitioning & Triggers

                           JSON vs. JSONB Storage Types
                                        │
        ┌───────────────────────────────┴───────────────────────────────┐
        ▼                                                               ▼
JSON (Raw Text Storage)                                JSONB (Decomposed Binary Storage)
Stores exact raw JSON text representation               Decomposes JSON into binary format at write time
Preserves duplicate keys & whitespace                  Strips duplicate keys, re-orders for speed
Slower query execution; no GIN index support            Ultra-fast querying; supports GIN Inverted Indexes

-- Sample Table with JSONB Payload
CREATE TABLE user_profiles (
    user_id     INT PRIMARY KEY,
    attributes  JSONB NOT NULL
);

INSERT INTO user_profiles VALUES
(101, '{"name": "Alice", "role": "admin", "skills": ["SQL", "Python"], "address": {"city": "Seattle"}}'),
(102, '{"name": "Bob", "role": "user", "skills": ["Java"], "address": {"city": "Austin"}}');

-- Querying JSONB arrays and nested fields
SELECT user_id, 
       attributes ->> 'name' AS user_name,
       attributes #>> '{address, city}' AS city
FROM user_profiles
WHERE attributes @> '{"role": "admin"}';

-- Create GIN index on entire JSONB document
CREATE INDEX idx_profiles_attributes_gin ON user_profiles USING GIN (attributes);

-- This query now executes sub-millisecond GIN index lookups!
SELECT * FROM user_profiles 
WHERE attributes @> '{"role": "admin"}';

                            Virtual Views vs Materialized Views
                                            │
        ┌───────────────────────────────────┴───────────────────────────────────┐
        ▼                                                                       ▼
Virtual View (CREATE VIEW)                              Materialized View (CREATE MATERIALIZED VIEW)
Virtual query definition saved in catalog               Physically caches query result dataset on disk
Re-executes underlying query on every read               Sub-second reads for complex multi-table joins
Zero storage cost; 100% real-time data                  Requires manual/scheduled REFRESH to update

-- 1. Virtual View: Always live, zero disk storage
CREATE VIEW v_active_users AS
SELECT user_id, email, status FROM users WHERE status = 'ACTIVE';

-- 2. Materialized View: Cached table snapshot on disk
CREATE MATERIALIZED VIEW mv_monthly_sales_summary AS
SELECT DATE_TRUNC('month', sale_date) AS month, 
       SUM(amount) AS total_revenue
FROM sales
GROUP BY DATE_TRUNC('month', sale_date);

-- Fast analytical read directly from cached disk snapshot
SELECT * FROM mv_monthly_sales_summary WHERE month >= '2024-01-01';

-- 3. Concurrent Refresh Strategy (Requires unique index on materialized view)
CREATE UNIQUE INDEX idx_mv_month ON mv_monthly_sales_summary(month);
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_monthly_sales_summary;

                            Table Partitioning Types
                                       │
        ┌──────────────────────────────┼──────────────────────────────┐
        ▼                              ▼                              ▼
Range Partitioning             List Partitioning              Hash Partitioning
Partition by numeric/date ranges Partition by explicit values  Distributes tuples via hash function
e.g., order_date by Quarter      e.g., region ('US', 'EU')      e.g., tenant_id % 4 buckets

-- Creating a Range-Partitioned Parent Table
CREATE TABLE partition_orders (
    order_id     BIGINT,
    order_date   DATE NOT NULL,
    amount       NUMERIC(10, 2)
) PARTITION BY RANGE (order_date);

-- Creating Child Partitions
CREATE TABLE orders_2024_q1 PARTITION OF partition_orders
    FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');

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

-- Stored Procedure with explicit transaction management
CREATE OR REPLACE PROCEDURE transfer_funds(
    sender_id INT, 
    receiver_id INT, 
    amount NUMERIC
)
LANGUAGE plpgsql AS $$
BEGIN
    UPDATE accounts SET balance = balance - amount WHERE account_id = sender_id;
    UPDATE accounts SET balance = balance + amount WHERE account_id = receiver_id;
    
    -- Explicitly commit work unit inside procedure
    COMMIT;
END;
$$;

-- Calling the procedure
CALL transfer_funds(101, 202, 500.00);

-- 1. Create Audit Log Table
CREATE TABLE audit_log (
    log_id      SERIAL PRIMARY KEY,
    emp_id      INT,
    old_salary  NUMERIC,
    new_salary  NUMERIC,
    changed_at  TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

-- 2. Create Trigger Function
CREATE OR REPLACE FUNCTION log_salary_change()
RETURNS TRIGGER AS $$
BEGIN
    IF OLD.salary IS DISTINCT FROM NEW.salary THEN
        INSERT INTO audit_log(emp_id, old_salary, new_salary)
        VALUES (OLD.emp_id, OLD.salary, NEW.salary);
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 3. Bind Trigger to Table
CREATE TRIGGER trg_salary_audit
AFTER UPDATE ON employees
FOR EACH ROW
EXECUTE FUNCTION log_salary_change();