18-table-partitioning.sql

-- ============================================================
-- SQL Masterclass
-- Chapter 18: Table Partitioning
-- ============================================================
-- Level: Expert (Database Admin)
-- Dependencies: PostgreSQL (Requires ecommerce DB from setup)
-- 
-- Concepts Covered:
-- 1. Declarative Partitioning
-- 2. Range Partitioning (By Date)
-- 3. Attaching/Detaching Partitions
-- 4. Speeding up massive analytical queries
-- ============================================================

-- ============================================================
-- 1. The Partitioning Concept
-- ============================================================
-- When tables hit hundreds of gigabytes (or terabytes), simply
-- B-Tree indexing them isn't enough. Inserts slow down, and
-- deleting old data (`DELETE FROM...`) causes massive table locks.
--
-- Postgres lets you "Partition" a parent table into smaller child
-- tables completely transparently. Let's create a huge orders table
-- partitioned by the physical year!

-- First, drop if exists
DROP TABLE IF EXISTS orders_part_2016;
DROP TABLE IF EXISTS orders_part_2017;
DROP TABLE IF EXISTS orders_part_2018;
DROP TABLE IF EXISTS orders_part_2019;
DROP TABLE IF EXISTS orders_partitioned CASCADE;

-- 1a. Create the Parent Table structure
CREATE TABLE orders_partitioned (
    order_id                      VARCHAR(64),
    customer_id                   VARCHAR(64),
    order_status                  VARCHAR(20),
    order_purchase_timestamp      TIMESTAMP,
    order_approved_at             TIMESTAMP,
    order_delivered_carrier_date  TIMESTAMP,
    order_delivered_customer_date TIMESTAMP,
    order_estimated_delivery_date TIMESTAMP
) PARTITION BY RANGE (order_purchase_timestamp);

-- No data can be inserted into the parent table until we attach
-- physical child partitions that cover exactly the date ranges we need!

-- 1b. Create the Physical Date Partitions (2016 to 2018)
CREATE TABLE orders_part_2016 PARTITION OF orders_partitioned
    FOR VALUES FROM ('2016-01-01') TO ('2017-01-01');

CREATE TABLE orders_part_2017 PARTITION OF orders_partitioned
    FOR VALUES FROM ('2017-01-01') TO ('2018-01-01');

CREATE TABLE orders_part_2018 PARTITION OF orders_partitioned
    FOR VALUES FROM ('2018-01-01') TO ('2019-01-01');


-- ============================================================
-- 2. Loading the Data Stream
-- ============================================================
-- Notice that we INSERT INTO the *parent* table `orders_partitioned`.
-- PostgreSQL automatically routes the row into the correct physical
-- partition based on the `order_purchase_timestamp`!

INSERT INTO orders_partitioned
SELECT * FROM orders
WHERE order_purchase_timestamp >= '2016-01-01' 
  AND order_purchase_timestamp < '2019-01-01';

-- When we query the parent, we get everything!
SELECT COUNT(*) FROM orders_partitioned;

-- But we can query the actual physical files to prove they routed correctly:
SELECT COUNT(*) FROM orders_part_2018;


-- ============================================================
-- 3. Partition Pruning (Speed)
-- ============================================================
-- If you query for 2018 data, the Postgres planner instantly ignores
-- the 2016 and 2017 tables completely. 

EXPLAIN ANALYZE
SELECT order_status, COUNT(*) FROM orders_partitioned
WHERE order_purchase_timestamp >= '2018-06-01'
  AND order_purchase_timestamp < '2018-07-01'
GROUP BY order_status;

-- Look at the EXPLAIN output! You will see it only scans `orders_part_2018`.
-- It never touches `orders_part_2016` or `orders_part_2017`.


-- ============================================================
-- 4. Instant Data Deletion
-- ============================================================
-- GDPR or company data retention policy requires deleting data
-- older than 2017. 
--
-- Running `DELETE FROM orders WHERE date < '2017-01-01'` on a 1TB
-- table will freeze the database for hours. 
--
-- With partitioning, you simply DETACH or DROP the partition instantly!

-- Detach it from the main table immediately (sub-millisecond lock):
ALTER TABLE orders_partitioned 
DETACH PARTITION orders_part_2016;

-- Verify it is gone from the main table:
SELECT COUNT(*) FROM orders_partitioned
WHERE EXTRACT(YEAR FROM order_purchase_timestamp) = 2016;

-- You can now archive `orders_part_2016` to cold storage, or drop it:
DROP TABLE orders_part_2016;


-- ============================================================
-- Exercises
-- ============================================================
-- Exercise 1. Your company decides to keep 2019 data. Create a new 
-- partition for `orders_part_2019` extending to `2020-01-01`.

-- CREATE TABLE ...


-- ============================================================
-- Solutions
-- ============================================================
/*
-- Solution 1:
CREATE TABLE orders_part_2019 PARTITION OF orders_partitioned
    FOR VALUES FROM ('2019-01-01') TO ('2020-01-01');
*/