Changes between Version 3 and Version 4 of Phase1_Partitioning

Timestamp:

12/10/25 01:55:56 (3 weeks ago)

Author:

226052

Comment:

--

Phase1_Partitioning

@@ -40 +40 @@
     return_date TIMESTAMP,
     last_update TIMESTAMP DEFAULT now() NOT NULL,
-    PRIMARY KEY (rental_id, rental_date),
-    CONSTRAINT fk_rental_inventory FOREIGN KEY (inventory_id) REFERENCES inventory(inventory_id) ON UPDATE CASCADE ON DELETE RESTRICT,
-    CONSTRAINT fk_rental_customer FOREIGN KEY (customer_id) REFERENCES customer(customer_id) ON UPDATE CASCADE ON DELETE RESTRICT,
-    CONSTRAINT fk_rental_staff FOREIGN KEY (staff_id) REFERENCES staff(staff_id) ON UPDATE CASCADE ON DELETE RESTRICT
+    PRIMARY KEY (rental_id, rental_date)
 )
 PARTITION BY RANGE (rental_date);
@@ -77 +74 @@
 }}}
 
+На крај ги додаваме ограничувањата за надворешни клучеви.
+{{{
+ALTER TABLE rental ADD CONSTRAINT fk_rental_inventory FOREIGN KEY (inventory_id) REFERENCES inventory(inventory_id) ON UPDATE CASCADE ON DELETE RESTRICT;
+ALTER TABLE rental ADD CONSTRAINT fk_rental_customer FOREIGN KEY (customer_id) REFERENCES customer(customer_id) ON UPDATE CASCADE ON DELETE RESTRICT;
+ALTER TABLE rental ADD CONSTRAINT fk_rental_staff FOREIGN KEY (staff_id) REFERENCES staff(staff_id) ON UPDATE CASCADE ON DELETE RESTRICT;
+}}}
+
+
 Сега ќе извршиме пример прашалник на старата табела('''rental_old''') каде што немаме партиции.
 
@@ -88 +93 @@
 [[Image(rental_query.png)]]
 
-Од тука може да забележиме дека PostgreSQL мора да направи целосен Sequential Scan на табелата и бидејќи табелата не е партиционирана query optimizer - от нема да може да ги прескокне редовите што не припаѓаат на бараниот датумски опсег, па затоа ја скенира цела табела и елеминира повеќе од 8 милиони записи со помош на филтер.
+Од тука може да забележиме дека PostgreSQL мора да направи целосен Sequential Scan на табелата и бидејќи табелата не е партиционирана query optimizer-от нема да може да ги прескокне редовите што не припаѓаат на бараниот датумски опсег, па затоа ја скенира цела табела и елеминира повеќе од 8 милиони записи со помош на филтер.
 
 
@@ -102 +107 @@
 [[Image(rental_query_partition.png)]]
 
-По партиционирањето, PostgreSQL веќе не ја скенира целата табела, туку со помош на '''partition pruning''' кој што во поновите верзии на PostgreSQL е пуштен по default PostgreSQL не ги скенира партициите во кој што WHERE условот не е исполнет. Ја скенира само конкретната партиција '''rental_2022''' и со тоа наместо да обработуваат 10 милиони редови, се обработуваат само податоците во релевантната партиција, времето на извршување е скоро преполовено и со помош на филтер се елеминираат околу 5 илјади редови наместо 8 милиони.
+По партиционирањето, PostgreSQL веќе не ја скенира целата табела, туку со помош на '''partition pruning''' кој што во поновите верзии на PostgreSQL е пуштен по default PostgreSQL не ги скенира партициите во кој што WHERE условот не е исполнет. Ја скенира само конкретната партиција '''rental_2022''' и со тоа наместо да обработуваат 10 милиони редови, се обработуваат само податоците во релевантната партиција, времето на извршување е скоро преполовено и со помош на филтер се елиминираат околу 5 илјади редови наместо 8 милиони.
+
+=== Партицинориање на payment табелата
+
+Постапката е иста како и кај rental табелата.
+
+{{{ ALTER TABLE payment RENAME TO payment_old; }}}
+
+{{{
+CREATE TABLE payment (
+    payment_id BIGINT GENERATED ALWAYS AS IDENTITY,
+    customer_id BIGINT NOT NULL,
+    staff_id BIGINT NOT NULL,
+    rental_id BIGINT NOT NULL,
+    amount NUMERIC(5,2) NOT NULL,
+    payment_date TIMESTAMP NOT NULL,
+    PRIMARY KEY (payment_id, payment_date)
+)
+PARTITION BY RANGE (payment_date);
+}}}
+
+Тука правиме партиционирање по месец па така ќе имаме 5 години x 12 месеци = 60 партиции.
+{{{
+DO $$
+DECLARE
+    y int;
+    m int;
+    start_date date;
+    end_date date;
+    partition_name text;
+BEGIN
+    FOR y IN 2020..2024 LOOP
+        FOR m IN 1..12 LOOP
+            start_date := make_date(y, m, 1);
+            end_date := (make_date(y, m, 1) + interval '1 month')::date;
+
+            partition_name := format('payment_%s_%s', y, lpad(m::text, 2, '0'));
+
+            EXECUTE format(
+                'CREATE TABLE %I PARTITION OF payment
+                 FOR VALUES FROM (%L) TO (%L);',
+                partition_name,
+                start_date,
+                end_date
+            );
+        END LOOP;
+    END LOOP;
+END $$;
+}}}
+
+{{{
+INSERT INTO payment (customer_id, staff_id, rental_id, amount, payment_date)
+SELECT customer_id, staff_id, rental_id, amount, payment_date
+FROM payment_old;
+}}}
+
+{{{
+ALTER TABLE payment ADD CONSTRAINT fk_payment_customer FOREIGN KEY (customer_id) REFERENCES customer(customer_id) ON UPDATE CASCADE ON DELETE RESTRICT;
+ALTER TABLE payment ADD CONSTRAINT fk_payment_staff FOREIGN KEY (staff_id) REFERENCES staff(staff_id) ON UPDATE CASCADE ON DELETE RESTRICT;
+}}}
 
 
 
+
+
+