Партиционирање на податоци

Партиционирање на податоците е техника каде што една табела се дели на повеќе помали физички делови наречени партиции. Секоја партиција е посебна табела што складира дел од вкупните податоци.

Главната цел на партиционирање на податоците е:

• Подобрување на перформансите при извршување на прашалници особено кога најчесто пристапуваните редови се наоѓаат во една или повеќе партиции. Исто така ги заменува по високите нивоа на индексите така што повеќе користените индекси се во меморија.
• Подобри перформанси за прашалници кои пристапуваат голем дел од една партиција со тоа што ќе се користи секвенцијално читање наместо индекси кој што ќе прават случајни читања низ целата табела.
• Голема ефикасност при масовно бришење и вчитување на податоци, со тоа што наместо да работиме ред по ред, можеме едноставно да додадеме или отстраниме цела партиција (користејќи ATTACH PARTITION или DROP TABLE/DETACH PARTITION ).
• Поефикасно искористување на дисковите за складирање на податоците со тоа што за податоците што ретко се пристапуваат може да се префрлат на поевтини дискови.

Во PostgreSQL постојат следниве вградени типови на партиционирање:

• Партиционирање по опсег(Range Partitioning) податоците се делат според опсег на вредности. Најчесто се користи за датуми, цени, возрасти и сл.
• Партиционирање по листа(List Partitioning) податоците се делат по однапред дефинирани вредности на одредена колона.
• Партиционирање по Хeш (Hash Partitioning) податоците се распределуваат рамномерно користејќи хеш функција. За секоја партиција се дефинира модулус/делител и остаток.

Партиционирањето е најкорисно за многу големи табели. Дали некоја табела би имала придобивка од партиционирање зависи од апликацијата. Ако табелата е релативно мала и ако нема јасен критериум за поделба тогаш партиционирањето може да донесе повеќе штета отколку корист. Во такви случаи се воведува дополнителна комплексност без значително подобрување на перформансите, а понекогаш и со нивно влошување.

Примена на партиционирање во FilmRentalDB

Во FilmRentalDB партиционирањето е најкорисно за табелите rental и payment поради тоа што тие може да содржат голем број на трансакции кои континуирано расте.

Партицинориање на rental табелата

Оваа табела ќе ја партиционираме по колоната rental_date со помош на Range партиционирање. Податоците ќе ги делиме по година со што ќе се овозможи побрзо пристапување до податоци во определен временски период.

За да ги направиме партициите прво ќе треба да ја преименуваме веќе постоечката rental табела се со цел да направиме нова партиционирана rental табела.

ALTER TABLE rental RENAME TO rental_old;

Потоа ја креираме новатa rental табела

CREATE TABLE rental (
    rental_id BIGINT GENERATED ALWAYS AS IDENTITY,
    rental_date TIMESTAMP NOT NULL,
    inventory_id BIGINT NOT NULL,
    customer_id BIGINT NOT NULL,
    staff_id BIGINT NOT NULL,
    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
)
PARTITION BY RANGE (rental_date);

Тука rental_date мора да биде дел од примарниот клуч поради тоа што PostgreSQL бара колоната по која се партиционира да биде вклучена во сите UNIQUE ограничувања и примарни клучеви.

Во мојата база има 10 милиони записи движејќи се од 2020 до 2024 година. Па поради тоа правиме 5 партиции.

CREATE TABLE rental_2020 PARTITION OF rental
FOR VALUES FROM ('2020-01-01') TO ('2021-01-01');

CREATE TABLE rental_2021 PARTITION OF rental
FOR VALUES FROM ('2021-01-01') TO ('2022-01-01');

CREATE TABLE rental_2022 PARTITION OF rental
FOR VALUES FROM ('2022-01-01') TO ('2023-01-01');

CREATE TABLE rental_2023 PARTITION OF rental
FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

CREATE TABLE rental_2024 PARTITION OF rental
FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

Потоа ги внесуваме податоците од старата табела

INSERT INTO rental (rental_date, inventory_id, customer_id, staff_id, return_date, last_update)
SELECT rental_date, inventory_id, customer_id, staff_id, return_date, last_update
FROM rental_old;