AdvancedTopic: partitioning.sql

DROP TABLE IF EXISTS time_slot_partitioned CASCADE;
DROP TABLE IF EXISTS appointment_partitioned CASCADE;

-- ============================================================
-- TIME_SLOT PARTITIONED BY QUARTERS
-- ============================================================

CREATE TABLE time_slot_partitioned (
                                       slot_id BIGINT NOT NULL,
                                       employee_id INT NOT NULL,
                                       business_id INT NOT NULL,
                                       date DATE NOT NULL,
                                       start_time TIME NOT NULL,
                                       end_time TIME NOT NULL,
                                       is_available BOOLEAN NOT NULL,

                                       PRIMARY KEY (slot_id, date)
) PARTITION BY RANGE (date);


CREATE TABLE time_slot_2026_q2 PARTITION OF time_slot_partitioned
    FOR VALUES FROM ('2026-04-01') TO ('2026-07-01');

CREATE TABLE time_slot_2026_q3 PARTITION OF time_slot_partitioned
    FOR VALUES FROM ('2026-07-01') TO ('2026-10-01');

CREATE TABLE time_slot_2026_q4 PARTITION OF time_slot_partitioned
    FOR VALUES FROM ('2026-10-01') TO ('2027-01-01');

CREATE TABLE time_slot_2027_q1 PARTITION OF time_slot_partitioned
    FOR VALUES FROM ('2027-01-01') TO ('2027-04-01');

CREATE TABLE time_slot_2027_q2 PARTITION OF time_slot_partitioned
    FOR VALUES FROM ('2027-04-01') TO ('2027-07-01');


INSERT INTO time_slot_partitioned (
    slot_id,
    employee_id,
    business_id,
    date,
    start_time,
    end_time,
    is_available
)
SELECT
    slot_id,
    employee_id,
    business_id,
    date,
    start_time,
    end_time,
    is_available
FROM time_slot
WHERE date >= '2026-04-01'
  AND date < '2027-07-01';


-- Index for available slots query
CREATE INDEX idx_time_slot_partitioned_available_business_date
    ON time_slot_partitioned (business_id, date)
    WHERE is_available = TRUE;


-- Count by quarter
SELECT 'time_slot_2026_q2' AS partition_name, COUNT(*) FROM time_slot_2026_q2
UNION ALL
SELECT 'time_slot_2026_q3', COUNT(*) FROM time_slot_2026_q3
UNION ALL
SELECT 'time_slot_2026_q4', COUNT(*) FROM time_slot_2026_q4
UNION ALL
SELECT 'time_slot_2027_q1', COUNT(*) FROM time_slot_2027_q1
UNION ALL
SELECT 'time_slot_2027_q2', COUNT(*) FROM time_slot_2027_q2;


EXPLAIN ANALYZE
SELECT *
FROM time_slot_partitioned
WHERE business_id = 1
  AND date = '2026-05-20'
  AND is_available = TRUE;



-- ============================================================
-- APPOINTMENT PARTITIONED BY SAME QUARTERS
-- ============================================================

CREATE TABLE appointment_partitioned (
                                         appointment_id BIGINT NOT NULL,
                                         customer_id INT NOT NULL,
                                         employee_id INT NOT NULL,
                                         business_id INT NOT NULL,
                                         service_id INT NOT NULL,
                                         slot_id BIGINT NOT NULL,
                                         status VARCHAR NOT NULL,
                                         created_at TIMESTAMP NOT NULL,

                                         PRIMARY KEY (appointment_id, created_at)
) PARTITION BY RANGE (created_at);


CREATE TABLE appointment_2026_q2 PARTITION OF appointment_partitioned
    FOR VALUES FROM ('2026-04-01') TO ('2026-07-01');

CREATE TABLE appointment_2026_q3 PARTITION OF appointment_partitioned
    FOR VALUES FROM ('2026-07-01') TO ('2026-10-01');

CREATE TABLE appointment_2026_q4 PARTITION OF appointment_partitioned
    FOR VALUES FROM ('2026-10-01') TO ('2027-01-01');

CREATE TABLE appointment_2027_q1 PARTITION OF appointment_partitioned
    FOR VALUES FROM ('2027-01-01') TO ('2027-04-01');

CREATE TABLE appointment_2027_q2 PARTITION OF appointment_partitioned
    FOR VALUES FROM ('2027-04-01') TO ('2027-07-01');


INSERT INTO appointment_partitioned (
    appointment_id,
    customer_id,
    employee_id,
    business_id,
    service_id,
    slot_id,
    status,
    created_at
)
SELECT
    appointment_id,
    customer_id,
    employee_id,
    business_id,
    service_id,
    slot_id,
    status,
    created_at
FROM appointment
WHERE created_at >= '2026-04-01'
  AND created_at < '2027-07-01';


-- Index for customer appointments query
CREATE INDEX idx_appointment_partitioned_customer
    ON appointment_partitioned (customer_id);

-- Optional but useful if you often filter customer + status
CREATE INDEX idx_appointment_partitioned_customer_status
    ON appointment_partitioned (customer_id, status);


-- Count by quarter
SELECT 'appointment_2026_q2' AS partition_name, COUNT(*) FROM appointment_2026_q2
UNION ALL
SELECT 'appointment_2026_q3', COUNT(*) FROM appointment_2026_q3
UNION ALL
SELECT 'appointment_2026_q4', COUNT(*) FROM appointment_2026_q4
UNION ALL
SELECT 'appointment_2027_q1', COUNT(*) FROM appointment_2027_q1
UNION ALL
SELECT 'appointment_2027_q2', COUNT(*) FROM appointment_2027_q2;


EXPLAIN ANALYZE
SELECT *
FROM appointment_partitioned
WHERE customer_id = 100;