AdvancedTopics: partitioning.sql

-- partitioning

--======
--  product_attribute_values
--======

SET search_path TO myschema;

-- 1. create master table
CREATE TABLE PRODUCT_ATTRIBUTE_VALUES_P (
  variant_id   int8 NOT NULL,
  attribute_id int8 NOT NULL,
  attr_value   varchar(255) NOT NULL,
  -- pk contains attribute_id
  PRIMARY KEY (variant_id, attribute_id)
) PARTITION BY LIST (attribute_id);

-- 2. create partitions for main attributes
CREATE TABLE pav_processor     PARTITION OF PRODUCT_ATTRIBUTE_VALUES_P FOR VALUES IN (1);
CREATE TABLE pav_ram           PARTITION OF PRODUCT_ATTRIBUTE_VALUES_P FOR VALUES IN (2);
CREATE TABLE pav_storage       PARTITION OF PRODUCT_ATTRIBUTE_VALUES_P FOR VALUES IN (3);
CREATE TABLE pav_screen_size   PARTITION OF PRODUCT_ATTRIBUTE_VALUES_P FOR VALUES IN (4);
CREATE TABLE pav_display_type  PARTITION OF PRODUCT_ATTRIBUTE_VALUES_P FOR VALUES IN (5);
CREATE TABLE pav_battery       PARTITION OF PRODUCT_ATTRIBUTE_VALUES_P FOR VALUES IN (6);
CREATE TABLE pav_gpu           PARTITION OF PRODUCT_ATTRIBUTE_VALUES_P FOR VALUES IN (7);
CREATE TABLE pav_color         PARTITION OF PRODUCT_ATTRIBUTE_VALUES_P FOR VALUES IN (8);
CREATE TABLE pav_efficiency    PARTITION OF PRODUCT_ATTRIBUTE_VALUES_P FOR VALUES IN (9);
CREATE TABLE pav_power         PARTITION OF PRODUCT_ATTRIBUTE_VALUES_P FOR VALUES IN (10);

-- 3. default partition for other attr
CREATE TABLE pav_default       PARTITION OF PRODUCT_ATTRIBUTE_VALUES_P DEFAULT;

-- insert the data
ALTER TABLE PRODUCT_ATTRIBUTE_VALUES RENAME TO PRODUCT_ATTRIBUTE_VALUES_OLD;

INSERT INTO PRODUCT_ATTRIBUTE_VALUES_P (variant_id, attribute_id, attr_value)
SELECT variant_id, attribute_id, attr_value FROM PRODUCT_ATTRIBUTE_VALUES_OLD;

ALTER TABLE PRODUCT_ATTRIBUTE_VALUES_P RENAME TO PRODUCT_ATTRIBUTE_VALUES;

--======
--  checks
--======

EXPLAIN ANALYZE SELECT * FROM product_attribute_values WHERE attribute_id = 2;

SELECT
    p.relname AS partition_name,
    s.n_live_tup AS row_count
FROM pg_stat_user_tables s
JOIN pg_class p ON s.relid = p.oid
WHERE p.relname LIKE 'pav_%'
ORDER BY row_count DESC;

SELECT
    nmsp_parent.nspname AS parent_schema,
    parent.relname      AS parent_table,
    nmsp_child.nspname  AS partition_schema,
    child.relname       AS partition_name,
    pg_get_expr(child.relpartbound, child.oid) AS partition_expression
FROM pg_inherits
JOIN pg_class parent        ON pg_inherits.inhparent = parent.oid
JOIN pg_class child         ON pg_inherits.inhrelid  = child.oid
JOIN pg_namespace nmsp_parent ON parent.relnamespace    = nmsp_parent.oid
JOIN pg_namespace nmsp_child  ON child.relnamespace     = nmsp_child.oid
WHERE parent.relname = 'product_attribute_values';

-- rows in partitions
SELECT 'pav_processor' as partition, count(*) FROM pav_processor
UNION ALL SELECT 'pav_ram', count(*) FROM pav_ram
UNION ALL SELECT 'pav_storage', count(*) FROM pav_storage
UNION ALL SELECT 'pav_screen_size', count(*) FROM pav_screen_size
UNION ALL SELECT 'pav_display_type', count(*) FROM pav_display_type
UNION ALL SELECT 'pav_battery', count(*) FROM pav_battery
UNION ALL SELECT 'pav_gpu', count(*) FROM pav_gpu
UNION ALL SELECT 'pav_color', count(*) FROM pav_color
UNION ALL SELECT 'pav_efficiency', count(*) FROM pav_efficiency
UNION ALL SELECT 'pav_power', count(*) FROM pav_power
UNION ALL SELECT 'pav_default (Others)', count(*) FROM pav_default;


INSERT INTO product_attribute_values (variant_id, attribute_id, attr_value)
VALUES (9999999999, 2, '128GB');

SELECT * FROM pav_ram WHERE variant_id = 9999999999;

-- does new attr_id go to default
INSERT INTO product_attribute_values (variant_id, attribute_id, attr_value)
VALUES (9999999999, 99, 'Special_Value');

SELECT * FROM pav_default WHERE variant_id = 9999999999;

INSERT INTO product_attribute_values (variant_id, attribute_id, attr_value)
VALUES (77777777, 999, 'Custom_Value');
SELECT * FROM pav_default WHERE variant_id = 77777777;

--color test
INSERT INTO product_attribute_values (variant_id, attribute_id, attr_value)
VALUES (88888888, 8, 'Deep Purple');

SELECT * FROM pav_color WHERE variant_id = 88888888;
EXPLAIN ANALYZE
SELECT * FROM product_attribute_values
WHERE attribute_id = 7 AND attr_value = 'RTX 4080';

EXPLAIN ANALYZE
SELECT * FROM product_attribute_values
WHERE attribute_id = 8 AND attr_value = 'Silver';

EXPLAIN ANALYZE
SELECT * FROM product_attribute_values
WHERE attr_value = 'White';

SELECT
    parent.relname      AS master_table,
    child.relname       AS partition_name,
    pg_get_expr(child.relpartbound, child.oid) AS partition_definition
FROM pg_inherits
JOIN pg_class parent ON pg_inherits.inhparent = parent.oid
JOIN pg_class child  ON pg_inherits.inhrelid  = child.oid
WHERE parent.relname = 'product_attribute_values'
ORDER BY child.relname;

--======
--  product_price_history
--======

-- new partitioned table
CREATE TABLE PRODUCT_PRICE_HISTORY_P (
  history_id  int8 NOT NULL,
  variant_id  int8 NOT NULL,
  old_price   numeric(12, 2) NOT NULL,
  new_price   numeric(12, 2) NOT NULL,
  change_date timestamp with time zone NOT NULL,
  -- pk includes date
  PRIMARY KEY (history_id, change_date)
) PARTITION BY RANGE (change_date);


SELECT
    date_trunc('month', change_date) AS month_start,
    count(*) AS num_records
FROM myschema.product_price_history
GROUP BY month_start
ORDER BY month_start;

-- monthly partitions
CREATE TABLE pph_2025_05 PARTITION OF PRODUCT_PRICE_HISTORY_P FOR VALUES FROM ('2025-05-01') TO ('2025-06-01');
CREATE TABLE pph_2025_06 PARTITION OF PRODUCT_PRICE_HISTORY_P FOR VALUES FROM ('2025-06-01') TO ('2025-07-01');


CREATE TABLE pph_2025_07 PARTITION OF PRODUCT_PRICE_HISTORY_P FOR VALUES FROM ('2025-07-01') TO ('2025-08-01');
CREATE TABLE pph_2025_08 PARTITION OF PRODUCT_PRICE_HISTORY_P FOR VALUES FROM ('2025-08-01') TO ('2025-09-01');
CREATE TABLE pph_2025_09 PARTITION OF PRODUCT_PRICE_HISTORY_P FOR VALUES FROM ('2025-09-01') TO ('2025-10-01');
CREATE TABLE pph_2025_10 PARTITION OF PRODUCT_PRICE_HISTORY_P FOR VALUES FROM ('2025-10-01') TO ('2025-11-01');
CREATE TABLE pph_2025_11 PARTITION OF PRODUCT_PRICE_HISTORY_P FOR VALUES FROM ('2025-11-01') TO ('2025-12-01');
CREATE TABLE pph_2025_12 PARTITION OF PRODUCT_PRICE_HISTORY_P FOR VALUES FROM ('2025-12-01') TO ('2026-01-01');


CREATE TABLE pph_2026_01 PARTITION OF PRODUCT_PRICE_HISTORY_P FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
CREATE TABLE pph_2026_02 PARTITION OF PRODUCT_PRICE_HISTORY_P FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');
CREATE TABLE pph_2026_03 PARTITION OF PRODUCT_PRICE_HISTORY_P FOR VALUES FROM ('2026-03-01') TO ('2026-04-01');
CREATE TABLE pph_2026_04 PARTITION OF PRODUCT_PRICE_HISTORY_P FOR VALUES FROM ('2026-04-01') TO ('2026-05-01');

CREATE TABLE pph_default PARTITION OF PRODUCT_PRICE_HISTORY_P DEFAULT;

INSERT INTO PRODUCT_PRICE_HISTORY_P
SELECT * FROM product_price_history;

EXPLAIN ANALYZE
SELECT * FROM PRODUCT_PRICE_HISTORY_P
WHERE change_date BETWEEN '2025-11-01' AND '2025-11-30';

--======
--  product_price_history
--======

SET search_path TO myschema;

-- master table
CREATE TABLE ORDER_STATUS_HISTORY_P (
  history_id  int8 NOT NULL,
  order_id    int8 NOT NULL,
  old_status  varchar(255) NOT NULL,
  new_status  varchar(255) NOT NULL,
  change_date timestamp with time zone NOT NULL,
  -- pk contains date
  PRIMARY KEY (history_id, change_date)
) PARTITION BY RANGE (change_date);

-- 2025
CREATE TABLE osh_2025_05 PARTITION OF ORDER_STATUS_HISTORY_P FOR VALUES FROM ('2025-05-01') TO ('2025-06-01');
CREATE TABLE osh_2025_06 PARTITION OF ORDER_STATUS_HISTORY_P FOR VALUES FROM ('2025-06-01') TO ('2025-07-01');
CREATE TABLE osh_2025_07 PARTITION OF ORDER_STATUS_HISTORY_P FOR VALUES FROM ('2025-07-01') TO ('2025-08-01');
CREATE TABLE osh_2025_08 PARTITION OF ORDER_STATUS_HISTORY_P FOR VALUES FROM ('2025-08-01') TO ('2025-09-01');
CREATE TABLE osh_2025_09 PARTITION OF ORDER_STATUS_HISTORY_P FOR VALUES FROM ('2025-09-01') TO ('2025-10-01');
CREATE TABLE osh_2025_10 PARTITION OF ORDER_STATUS_HISTORY_P FOR VALUES FROM ('2025-10-01') TO ('2025-11-01');
CREATE TABLE osh_2025_11 PARTITION OF ORDER_STATUS_HISTORY_P FOR VALUES FROM ('2025-11-01') TO ('2025-12-01');
CREATE TABLE osh_2025_12 PARTITION OF ORDER_STATUS_HISTORY_P FOR VALUES FROM ('2025-12-01') TO ('2026-01-01');

-- 2026
CREATE TABLE osh_2026_01 PARTITION OF ORDER_STATUS_HISTORY_P FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
CREATE TABLE osh_2026_02 PARTITION OF ORDER_STATUS_HISTORY_P FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');
CREATE TABLE osh_2026_03 PARTITION OF ORDER_STATUS_HISTORY_P FOR VALUES FROM ('2026-03-01') TO ('2026-04-01');
CREATE TABLE osh_2026_04 PARTITION OF ORDER_STATUS_HISTORY_P FOR VALUES FROM ('2026-04-01') TO ('2026-05-01');

-- DEFAULT
CREATE TABLE osh_default PARTITION OF ORDER_STATUS_HISTORY_P DEFAULT;

INSERT INTO ORDER_STATUS_HISTORY_P
SELECT * FROM order_status_history;

ALTER TABLE order_status_history RENAME TO order_status_history_old;
ALTER TABLE ORDER_STATUS_HISTORY_P RENAME TO order_status_history;

--check

EXPLAIN ANALYZE
SELECT * FROM order_status_history
WHERE order_id = 1789
  AND change_date >= '2025-06-14'
  AND change_date < '2025-07-01'
 AND old_status <> 'CART';