Kodėl jūsų duomenų bazė lėtėja kaip senas traktoriaus variklis
Dirbate su PostgreSQL ir pastebėjote, kad užklausos, kurios anksčiau veikdavo akimirksniu, dabar užtrunka sekundes ar net minutes? Lentelė išaugo iki kelių milijonų įrašų, o kiekvienas SELECT jau primena kantrybės testą? Sveiki atvykę į klubą – jūs susidūrėte su klasikine problema, kurią sprendžia particionavimas.
Kai lentelė tampa itin didelė (sakykime, dešimtys milijonų įrašų ar daugiau), PostgreSQL pradeda kentėti. Indeksai tampa milžiniški, vakuuming užtrunka amžinybę, o užklausų planuotojas kartais tiesiog pasiduoda ir nusprendžia nuskaityti visą lentelę. Tai natūralu – net ir pažangiausia duomenų bazė turi fizinių apribojimų.
Particionavimas – tai būdas padalinti vieną didelę lentelę į mažesnius, lengviau valdomus gabalus. Įsivaizduokite, kad turite milžinišką archyvą su dokumentais. Vietoj to, kad viską krautumėte į vieną spintą, logiška skirstyti pagal metus, mėnesius ar kitą kriterijų. Tas pats principas veikia ir su duomenimis.
Kaip veikia particionavimas PostgreSQL pasaulyje
PostgreSQL particionavimas nėra kažkas nauja – funkcionalumas egzistuoja jau seniai, bet nuo 10-os versijos jis tapo tikrai patogus ir efektyvus. Esmė paprasta: sukuriate pagrindinę lentelę (parent table), kuri veikia kaip šablonas, o tada kuriate atskiras partijas (child tables), kurios paveldi pagrindinės lentelės struktūrą.
Svarbiausia čia suprasti, kad pagrindinė lentelė iš tikrųjų nelaiko jokių duomenų – ji tik koordinuoja partijas. Kai įterpiame duomenis į pagrindinę lentelę, PostgreSQL automatiškai nukreipia juos į tinkamą partiją pagal jūsų apibrėžtą logiką. Kai vykdome užklausas, duomenų bazė protingai nusprendžia, kurias partijas reikia tikrinti, o kurias galima visiškai ignoruoti (tai vadinama partition pruning).
Yra trys pagrindiniai particionavimo metodai:
Range particionavimas – skirstoma pagal reikšmių intervalus. Pavyzdžiui, užsakymai pagal datas: 2023 metų sausis vienoje partijoje, vasaris kitoje ir t.t. Tai populiariausias metodas laiko serijos duomenims.
List particionavimas – skirstoma pagal konkrečias reikšmes. Tarkime, klientai pagal šalis: Lietuva vienoje partijoje, Latvija kitoje, Estija trečioje.
Hash particionavimas – duomenys paskirstomi pagal hash funkciją. Naudinga, kai neturite aiškaus loginio skirstymo kriterijaus, bet norite tolygiai paskirstyti apkrovą.
Praktinis pavyzdys: užsakymų lentelės particionavimas
Paimkime realų scenarijų. Turite e-parduotuvės užsakymų lentelę, kurioje jau susikaupė 50 milijonų įrašų per pastaruosius 5 metus. Dažniausiai dirba su naujausiais duomenimis, bet kartais reikia pasižiūrėti ir į senuosius. Idealus atvejis range particionavimui pagal datą.
Štai kaip tai atrodytų:
CREATE TABLE orders (
order_id BIGSERIAL,
customer_id INTEGER NOT NULL,
order_date DATE NOT NULL,
total_amount NUMERIC(10,2),
status VARCHAR(50)
) PARTITION BY RANGE (order_date);
Pastebėkite PARTITION BY RANGE (order_date) – tai nurodo PostgreSQL, kad ši lentelė bus particionuota pagal užsakymo datą. Dabar sukuriame konkrečias partijas:
CREATE TABLE orders_2023_q1 PARTITION OF orders
FOR VALUES FROM ('2023-01-01') TO ('2023-04-01');
CREATE TABLE orders_2023_q2 PARTITION OF orders
FOR VALUES FROM ('2023-04-01') TO ('2023-07-01');
CREATE TABLE orders_2023_q3 PARTITION OF orders
FOR VALUES FROM ('2023-07-01') TO ('2023-10-01');
CREATE TABLE orders_2023_q4 PARTITION OF orders
FOR VALUES FROM ('2023-10-01') TO ('2024-01-01');
Svarbu suprasti, kad FROM yra įtraukiama, o TO – ne (kaip Python range funkcija). Taigi ‘2023-04-01’ jau patenka į antrą ketvirtį, ne į pirmą.
Dabar galite dirbti su orders lentele kaip įprastai:
INSERT INTO orders (customer_id, order_date, total_amount, status)
VALUES (12345, '2023-05-15', 99.99, 'completed');
PostgreSQL automatiškai įdės šį įrašą į orders_2023_q2 partiją. O kai vykdysite užklausą:
SELECT * FROM orders
WHERE order_date BETWEEN '2023-05-01' AND '2023-05-31';
Duomenų bazė protingai tikrins tik orders_2023_q2 partiją, ignoruodama visas kitas. Galite patikrinti tai su EXPLAIN:
EXPLAIN SELECT * FROM orders
WHERE order_date BETWEEN '2023-05-01' AND '2023-05-31';
Rezultate pamatysite, kad tikrinama tik viena partija – tai ir yra partition pruning magija.
Indeksai ir particionuotose lentelėse: ką būtina žinoti
Čia prasideda įdomesnė dalis. Indeksai particionuotose lentelėse veikia šiek tiek kitaip nei tikitės. Kai sukuriate indeksą pagrindinėje lentelėje, PostgreSQL automatiškai sukuria atitinkamus indeksus visose partijose:
CREATE INDEX idx_orders_customer ON orders(customer_id);
Ši komanda sukurs idx_orders_customer indeksą kiekvienoje partijoje atskirai. Tai puiku, nes kiekvienas indeksas bus mažesnis ir efektyvesnis. Tačiau yra niuansų.
Pirma, jei turite daug partijų, indeksų sukūrimas gali užtrukti. Antra, jei vėliau pridėsite naują partiją, turėsite nepamiršti, kad indeksai bus sukurti automatiškai (nuo PostgreSQL 11 versijos). Trečia, unikalumo apribojimai (UNIQUE constraints) turi įtraukti particionavimo raktą.
Pavyzdžiui, tai veiks:
ALTER TABLE orders ADD CONSTRAINT orders_unique
UNIQUE (order_id, order_date);
Bet tai neveiks:
ALTER TABLE orders ADD CONSTRAINT orders_unique
UNIQUE (order_id); -- Klaida! order_date nėra įtraukta
Kodėl? Nes PostgreSQL negali garantuoti unikalumo per visas partijas, jei particionavimo raktas nėra įtrauktas. Tai gali būti problema, jei jūsų verslo logika reikalauja unikalumo be particionavimo rakto. Tokiu atveju teks ieškoti alternatyvų arba persvarstyti particionavimo strategiją.
Automatinis partijų valdymas: pg_partman išgelbėjimas
Rankiniu būdu kurti partijas kiekvienam mėnesiui ar ketvirčiui – tai kaip perrašinėti tą patį kodą šimtą kartų. Čia į pagalbą ateina pg_partman – puikus plėtinys, kuris automatizuoja partijų kūrimą ir valdymą.
Įdiegus pg_partman, galite nustatyti, kad naujos partijos būtų kuriamos automatiškai:
SELECT partman.create_parent(
p_parent_table := 'public.orders',
p_control := 'order_date',
p_type := 'native',
p_interval := 'monthly',
p_premake := 3
);
Parametras p_premake := 3 nurodo, kad visada turėtų būti sukurtos 3 būsimos partijos iš anksto. Taigi, jei dabar 2024 sausis, bus sukurtos partijos vasariui, kovui ir balandžiui. Kai ateis vasaris, automatiškai sukurs gegužės partiją.
Dar geriau – pg_partman gali automatiškai ištrinti ar archyvuoti senas partijas:
UPDATE partman.part_config
SET retention = '12 months',
retention_keep_table = false
WHERE parent_table = 'public.orders';
Tai reiškia, kad partijos, senesnės nei 12 mėnesių, bus automatiškai ištrintos. Jei nustatysite retention_keep_table = true, partijos bus atjungtos nuo pagrindinės lentelės, bet neištrintos – galėsite jas archyvuoti ar perkelti į kitą saugyklą.
Nepamirškite paleisti pg_partman maintenance funkciją reguliariai (pavyzdžiui, per cron):
SELECT partman.run_maintenance();
Ši funkcija pasirūpins naujų partijų kūrimu ir senų šalinimu pagal jūsų nustatytas taisykles.
Migravimas: kaip perkelti esamą lentelę į particionuotą
Gerai, sakote, particionavimas skamba puikiai, bet kas, jei jau turiu milžinišką lentelę su duomenimis? Ar reikia viską ištrinti ir pradėti iš naujo? Žinoma, ne, bet procesas nėra trivialus.
Yra keli būdai. Pirmasis – naudoti pg_partman migraciją, kuri gali padaryti procesą gana sklandų. Antrasis – rankinis būdas, kuris suteikia daugiau kontrolės.
Rankinis procesas atrodo maždaug taip:
1. Sukuriate naują particionuotą lentelę su ta pačia struktūra
2. Nukopijuojate duomenis iš senosios lentelės į naująją
3. Perjungiate aplikaciją naudoti naują lentelę
4. Ištrinat seną lentelę
Štai supaprastintas pavyzdys:
-- 1. Pervadinti seną lentelę
ALTER TABLE orders RENAME TO orders_old;
-- 2. Sukurti naują particionuotą lentelę
CREATE TABLE orders (
-- ta pati struktūra kaip anksčiau
) PARTITION BY RANGE (order_date);
-- 3. Sukurti partijas
-- (kaip parodyta anksčiau)
-- 4. Kopijuoti duomenis
INSERT INTO orders SELECT * FROM orders_old;
-- 5. Patikrinti, ar viskas gerai
SELECT count(*) FROM orders;
SELECT count(*) FROM orders_old;
-- 6. Ištrinti seną lentelę
DROP TABLE orders_old;
Problema su šiuo metodu – INSERT INTO orders SELECT * FROM orders_old gali užtrukti labai ilgai didelėms lentelėms ir užrakinti duomenis. Geriau kopijuoti dalimis:
DO $$
DECLARE
start_date DATE := '2019-01-01';
end_date DATE := '2024-01-01';
current_date DATE := start_date;
BEGIN
WHILE current_date < end_date LOOP
INSERT INTO orders
SELECT * FROM orders_old
WHERE order_date >= current_date
AND order_date < current_date + INTERVAL '1 month';
current_date := current_date + INTERVAL '1 month';
COMMIT;
END LOOP;
END $$;
Taip duomenys kopijuojami po mėnesį, kas leidžia geriau kontroliuoti procesą ir sumažina užrakinimo laiką.
Našumo optimizavimas ir stebėjimas
Sukūrėte particionuotą lentelę – puiku! Bet kaip žinoti, ar tai tikrai pagerina našumą? Ir kaip išspausti maksimumą iš šios architektūros?
Pirmiausia, visada naudokite EXPLAIN ANALYZE palyginimui. Paleiskite tipiškiausias užklausas prieš ir po particionavimo:
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders
WHERE order_date >= '2023-11-01'
AND order_date < '2023-12-01'
AND status = 'completed';
Atkreipkite dėmesį į:
- Planning Time – kiek laiko užtruko užklausos planavimas
- Execution Time – faktinis vykdymo laikas
- Partitions scanned – kiek partijų buvo tikrinama
Idealiu atveju turėtumėte matyti, kad tikrinama tik viena ar kelios partijos, ne visos. Jei matote, kad PostgreSQL vis tiek skanuoja visas partijas, tikėtina, kad partition pruning neveikia. Dažniausios priežastys:
- WHERE sąlygoje nenaudojate particionavimo rakto
- Naudojate funkcijas ant particionavimo rakto stulpelio (pvz., WHERE EXTRACT(year FROM order_date) = 2023)
- Particionavimo raktas yra nullable ir naudojate IS NULL patikrinimus
Dar vienas svarbus aspektas – constraint_exclusion parametras. Nuo PostgreSQL 11, jis turėtų būti nustatytas į partition (tai default), bet verta patikrinti:
SHOW constraint_exclusion;
Jei matote off, pakeiskite į partition:
ALTER SYSTEM SET constraint_exclusion = partition;
SELECT pg_reload_conf();
Stebėjimui naudokite pg_stat_user_tables peržiūrėti, kaip naudojamos atskiros partijos:
SELECT schemaname, tablename,
seq_scan, seq_tup_read,
idx_scan, idx_tup_fetch
FROM pg_stat_user_tables
WHERE tablename LIKE 'orders_%'
ORDER BY tablename;
Tai parodys, kurios partijos yra dažniausiai naudojamos ir ar indeksai veikia efektyviai.
Particionavimas nėra sidabrinė kulka, bet labai arti to
Išbandę particionavimą realiais projektais, galiu pasakyti – tai vienas iš galingiausių įrankių PostgreSQL arsenale. Užklausos, kurios anksčiau užtrukdavo 30 sekundžių, dabar vyksta per sekundę ar net greičiau. Vakuuming ir analizė tampa valdomi procesai, o ne naktiniai košmarai.
Bet particionavimas nėra sprendimas visoms problemoms. Jei jūsų lentelė turi tik milijoną įrašų, greičiausiai dar nereikia particionuoti – paprastas indeksavimas puikiai veiks. Jei neturite aiškaus kriterijaus skirstymui (datos, geografijos, kategorijos), particionavimas gali būti per daug sudėtingas.
Pradėkite paprastai. Jei turite laiko serijos duomenis, range particionavimas pagal datą yra saugiausias pasirinkimas. Naudokite pg_partman automatizavimui – nereikia išradinėti dviračio. Stebėkite našumą su EXPLAIN ir pg_stat lentelėmis. Ir svarbiausia – testuokite savo konkrečias užklausas prieš diegiant produkcinėje aplinkoje.
Particionavimas reikalauja šiek tiek planavimo ir pradinio darbo, bet kai sistema pradeda veikti sklandžiai su milijardais įrašų, suprasite, kad tai buvo verta kiekvienos minutės. Jūsų duomenų bazė daugiau nebus tas lėtas traktorius – ji taps gerai sutepta mašina, kuri dirba greitai ir efektyviai, nesvarbu, kiek duomenų į ją supilsite.
