Bo'laklash, VACUUM va zaxira nusxa

Jadval millionlab qatorga yetganda va baza yillar davomida ishlaganda yangi savollar paydo bo'ladi: eski ma'lumotni qanday saqlash, joy nega to'lib boradi va yo'qotishdan qanday himoyalanish.

CREATE TABLE sotuv (
    id     int GENERATED ALWAYS AS IDENTITY,
    sana   date NOT NULL,
    filial text NOT NULL,
    summa  numeric(10,2) NOT NULL
) PARTITION BY RANGE (sana);

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

CREATE TABLE sotuv_2025 PARTITION OF sotuv
    FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');

CREATE TABLE sotuv_2026 PARTITION OF sotuv
    FOR VALUES FROM ('2026-01-01') TO ('2027-01-01');

INSERT INTO sotuv (sana, filial, summa) VALUES
    ('2024-03-12', 'Namangan',  850000.00),
    ('2024-11-05', 'Toshkent', 1200000.00),
    ('2025-02-18', 'Namangan',  640000.00),
    ('2025-07-30', 'Toshkent',  990000.00),
    ('2025-12-24', 'Samarqand', 730000.00),
    ('2026-01-09', 'Namangan',  410000.00);

Bo'laklash nima #

Bo'laklash (partitioning) - bitta mantiqiy jadvalni bir nechta fizik jadvalga bo'lish. Foydalanuvchi uchun u baribir bitta jadval bo'lib qolaveradi.

So'rov oddiy jadvaldagidek yoziladi:

SELECT sana, filial, summa FROM sotuv ORDER BY sana;

    sana    |  filial   |   summa
------------+-----------+------------
 2024-03-12 | Namangan  |  850000.00
 2024-11-05 | Toshkent  | 1200000.00
 2025-02-18 | Namangan  |  640000.00
 2025-07-30 | Toshkent  |  990000.00
 2025-12-24 | Samarqand |  730000.00
 2026-01-09 | Namangan  |  410000.00
(6 rows)

Lekin ma'lumot uchta alohida jadvalda yotibdi:

SELECT tableoid::regclass AS bolak, count(*) AS qatorlar
FROM sotuv
GROUP BY tableoid
ORDER BY bolak;

   bolak    | qatorlar
------------+----------
 sotuv_2024 |        2
 sotuv_2025 |        3
 sotuv_2026 |        1
(3 rows)

tableoid - qator aslida qaysi jadvalda yotganini ko'rsatuvchi yashirin ustun.

Bo'laklash turlari #

CREATE TABLE mijoz (
    id       int GENERATED ALWAYS AS IDENTITY,
    ism      text NOT NULL,
    viloyat  text NOT NULL
) PARTITION BY LIST (viloyat);

CREATE TABLE mijoz_vodiy PARTITION OF mijoz
    FOR VALUES IN ('Namangan', 'Andijon', 'Fargona');

CREATE TABLE mijoz_boshqa PARTITION OF mijoz DEFAULT;

INSERT INTO mijoz (ism, viloyat) VALUES
    ('Husanboy', 'Namangan'),
    ('Malika',   'Andijon'),
    ('Kamola',   'Toshkent');

SELECT tableoid::regclass AS bolak, ism, viloyat
FROM mijoz ORDER BY ism;

CREATE TABLE
CREATE TABLE
CREATE TABLE
INSERT 0 3
    bolak     |   ism    | viloyat
--------------+----------+----------
 mijoz_vodiy  | Husanboy | Namangan
 mijoz_boshqa | Kamola   | Toshkent
 mijoz_vodiy  | Malika   | Andijon
(3 rows)

DEFAULT bo'lak muhim: usiz ro'yxatda yo'q qiymat kelganda INSERT xato beradi.

Eski ma'lumotni chiqarish #

Bo'laklashning eng katta foydasi shu yerda ko'rinadi:

ALTER TABLE sotuv DETACH PARTITION sotuv_2024;

SELECT count(*) AS asosiy_jadvalda FROM sotuv;

SELECT count(*) AS ajratilgan_jadvalda FROM sotuv_2024;

ALTER TABLE
 asosiy_jadvalda
-----------------
               4
(1 row)

 ajratilgan_jadvalda
---------------------
                   2
(1 row)

DETACH - bu bir necha soniyalik amal. Jadvalning o'zi joyida qoladi, shunchaki asosiy jadvaldan uziladi.

O'lik qatorlar #

16-bobda ko'rganimizdek, UPDATE va DELETE eski versiyani darhol o'chirmaydi - u o'lik qator bo'lib qoladi.

CREATE TABLE hisoblagich (id int PRIMARY KEY, qiymat int);
INSERT INTO hisoblagich VALUES (1, 0);

UPDATE hisoblagich SET qiymat = qiymat + 1;
UPDATE hisoblagich SET qiymat = qiymat + 1;
UPDATE hisoblagich SET qiymat = qiymat + 1;

SELECT pg_stat_force_next_flush();

SELECT count(*) AS korinadigan_qator FROM hisoblagich;

SELECT n_dead_tup AS olik_qator FROM pg_stat_user_tables
WHERE relname = 'hisoblagich';

CREATE TABLE
INSERT 0 1
UPDATE 1
UPDATE 1
UPDATE 1
 pg_stat_force_next_flush
--------------------------

(1 row)

 korinadigan_qator
-------------------
                 1
(1 row)

 olik_qator
------------
          3
(1 row)

Jadvalda bitta qator ko'rinadi, lekin diskda to'rtta versiya yotibdi: uchtasi o'lik, bittasi tirik.

VACUUM #

VACUUM o'lik qatorlarni topib, ularning joyini qayta ishlatishga tayyorlaydi:

CREATE TABLE hisoblagich (id int PRIMARY KEY, qiymat int);
INSERT INTO hisoblagich VALUES (1, 0);

UPDATE hisoblagich SET qiymat = qiymat + 1;
UPDATE hisoblagich SET qiymat = qiymat + 1;
UPDATE hisoblagich SET qiymat = qiymat + 1;

SELECT pg_stat_force_next_flush();

SELECT n_dead_tup AS vacuumdan_oldin FROM pg_stat_user_tables
WHERE relname = 'hisoblagich';

VACUUM (ANALYZE) hisoblagich;

SELECT pg_stat_force_next_flush();

SELECT n_dead_tup AS vacuumdan_keyin FROM pg_stat_user_tables
WHERE relname = 'hisoblagich';

CREATE TABLE
INSERT 0 1
UPDATE 1
UPDATE 1
UPDATE 1
 pg_stat_force_next_flush
--------------------------

(1 row)

 vacuumdan_oldin
-----------------
               3
(1 row)

VACUUM
 pg_stat_force_next_flush
--------------------------

(1 row)

 vacuumdan_keyin
-----------------
               0
(1 row)

Uchta o'lik qator yo'qoldi - ularning joyi endi yangi qatorlar uchun ishlatilishi mumkin.

Autovacuum #

Odatda VACUUM ni qo'lda chaqirish shart emas - buni autovacuum jarayoni bajaradi.

SHOW autovacuum;

 autovacuum
------------
 on
(1 row)

U jadvaldagi o'zgarishlar ma'lum chegaradan oshganda ishga tushadi. Chegara ikki qismdan iborat: qat'iy son (autovacuum_vacuum_threshold) va jadval hajmiga nisbatan ulush (autovacuum_vacuum_scale_factor).

Transaction wraparound #

Tranzaksiya raqami cheksiz emas. U aylanib kelganda eski qatorlar "kelajakdan" ko'rinib qolishi mumkin - bu ma'lumotning buzilishi demakdir.

VACUUM buni ham oldini oladi: u eski qatorlarni "abadiy ko'rinadigan" deb belgilaydi.

ERROR:  database is not accepting commands to avoid
        wraparound data loss in database "dokon"

Zaxira nusxa #

Ikki xil yondashuv bor:

Eng oddiy holat:

pg_dump -Fc -d dokon -f dokon.dump

pg_restore -d dokon_yangi dokon.dump

pg_dump ishlab turgan bazada bemalol chaqiriladi: u MVCC tufayli izchil nusxa oladi va hech kimni to'smaydi.

Xulosa #

• Bo'laklash bitta mantiqiy jadvalni bir nechta fizik jadvalga bo'ladi.
• RANGE sana uchun, LIST ro'yxat uchun, HASH teng taqsimlash uchun.
• Baza faqat kerakli bo'lakni o'qiydi - buni bo'lak kesish deyiladi.
• LIST da DEFAULT bo'lak bo'lmasa, noma'lum qiymat xato beradi.
• Eski ma'lumotni DELETE emas, DETACH PARTITION bilan chiqaring.
• UPDATE va DELETE o'lik qator qoldiradi - ular joy egallaydi.
• VACUUM bu joyni qayta ishlatishga ochadi va hech kimni to'smaydi.
• VACUUM FULL jadvalni to'liq qulflaydi - ishlab turgan tizimda ishlatilmaydi.
• autovacuum ni hech qachon o'chirmang - wraparound bazani to'xtatib qo'yadi.
• Tiklab ko'rilmagan zaxira - zaxira emas; uni muntazam sinab turing.

Keyingi bo'limda o'rgangan hamma narsani bitta amaliy loyihada birlashtiramiz.