پارتیشن بندی postgresql با نام های شاخص مورد نظر

ek3nk4r 2025-02-01

0 1 خواندن این مطلب 9 دقیقه زمان میبرد

پارتیشن بندی postgresql با نام های شاخص مورد نظر

پیشنهاد ویژه

خرید فالوور واقعی خرید لایک اینستاگرام خرید ویو اینستاگرام خرید فالوور اینستاگرام

مدتی به عقب ، هتی مقاله ای با عنوان Indexing Partitioned Table Disaster نوشت. در آنجا ، وی توضیح داد كه چرا عدم كنترل نام فهرست در پارتیشن های جدول PostgreSQL می تواند منجر به برخی از مسائل شود. ما کمی درباره نظرات LinkedIn بحث کردیم ، و این مقاله یک توضیح در مورد بحث است.

برای داشتن خود ، من “عدم کنترل بر روی نام فهرست ها در پارتیشن های جدول PostgreSQL” را نشان خواهم داد ، اما نه اینکه چگونه می تواند منجر به مسائل شود. برای همین ، مقاله عالی را در بالا بخوانید!

فهرست مطالب

راه اندازی

ابتدا باید یک جدول پایه با برخی از شاخص ها ایجاد کنیم. در اینجا ، من دو شاخص را در همان مجموعه ستون ها ایجاد می کنم ، اما یک فهرست یک فهرست جزئی است. این بعداً برای اثبات یک نکته استفاده می شود ، اما در برنامه های دنیای واقعی ، من اجازه نمی دهم کلید پارتیشن (created_at) برای تهی بودن ، من دو فهرست را در همان لیست ستون ها ایجاد نمی کنم (مگر اینکه البته دلیل خوبی برای انجام این کار وجود داشته باشد ، که اکنون نمی توانم به آن فکر کنم!)

drop table if exists t;

create table t (
  id bigint generated always as identity not null,
  created_at timestamptz)
partition by range (created_at);

create index t__id__created_at__idx
  on t(id, created_at);

create index t__id__created_at_nn__idx
  on t(id, created_at)
    where created_at is not null;

ایجاد یک پارتیشن

در مرحله بعد ، بیایید یک پارتیشن واحد برای جدول پایه ایجاد کنیم. این برای داده ها در فوریه 2025 ارائه می شود:

create table t_2025_02
  partition of t
    for values from ('2025-02-01') to ('2025-03-01');

شاخص های پرس و جو

من به یک پرس و جو نسبتاً پیچیده و بازگشتی CTE رسیدم تا تمام شاخص های تعریف شده در جدول “T” و پارتیشن های آن را نشان دهم ، و اینکه آیا این شاخص ها معتبر هستند یا خیر. ما در این مقاله چندین بار این پرس و جو را اجرا خواهیم کرد:

with recursive partitions as (
  -- select the base partitioned table t
  select c.oid as child_oid,
         c.relname as table_name
  from pg_class c
  where c.relname = 't'

  union all

  -- recursively select all partitions that inherit from their parent
  select c.oid,
         c.relname
  from pg_inherits i
  join partitions p on i.inhparent = p.child_oid
  join pg_class c on i.inhrelid = c.oid
)
select p.table_name,
       ci.relname as index_name,
       case
         when i.indisvalid then 'VALID'
         else 'INVALID'
       end as status
from partitions p
join pg_index i on i.indrelid = p.child_oid
join pg_class ci on ci.oid = i.indexrelid
order by p.table_name, ci.relname;

در اینجا نتیجه اجرای پرس و جو است. به وضوح نشان می دهد که پارتیشن t_2025_02 شاخص ها از جدول پایه “وراثت”.

 table_name |          index_name          | status
------------+------------------------------+--------
 t          | t__id__created_at__idx       | VALID
 t          | t__id__created_at_nn__idx    | VALID
 t_2025_02  | t_2025_02_id_created_at_idx  | VALID
 t_2025_02  | t_2025_02_id_created_at_idx1 | VALID

هرچند یک احتیاط وجود دارد: ما هیچ کنترلی در مورد چگونگی نامگذاری این فهرست ها نداشتیم:

t_2025_02_id_created_at_idx
t_2025_02_id_created_at_idx1

PostgreSQL از نام پارتیشن و نام ستون برای شکل دادن به نام فهرست استفاده می کند ، و اگر این نتیجه در همان نام شاخص (مانند بالا) باشد ، شروع به اضافه کردن پسوند 1 ، 2 ، 3 ، …

آیا می توانیم بر روی نام شاخص ها کنترل داشته باشیم؟ جواب مثبت است ، اگرچه پیچیدگی کمی در جدول به ارمغان می آورد. ما دو مورد را در نظر خواهیم گرفت:

مورد 1: پارتیشن هنوز وجود ندارد. ما می خواهیم آن را بسازیم و خودمان را نامگذاری کنیم.
مورد 2: پارتیشن وجود دارد و به جدول پایه وصل شده است. ما می خواهیم یک شاخص جدید به جدول پایه اضافه کنیم و کنترل نام فهرست را در پارتیشن کنترل کنیم.

بیایید جدول فوق را رها کنیم ، تا از یک تخته سنگ تمیز که در آن پارتیشن وجود ندارد ، شروع کنیم:

drop table if exists t_2025_02;

ایجاد یک پارتیشن

ما پارتیشن را ایجاد می کنیم LIKE جدول والدین ، اما مستثنی است indexesبشر ما همچنین باید اشیاء دیگری مانند آن را حذف کنیم identity همانطور که postgres آنها را ممنوع می کند:

create table t_2025_02 (
  like t including all excluding indexes excluding identity
);

حال ، بیایید با نام هایی که می خواهیم شاخص هایی ایجاد کنیم:

create index t_2025_02__id__created_at__idx
  on t_2025_02(id, created_at);

create index t_2025_02__id__created_at_nn__idx
  on t_2025_02(id, created_at)
  where created_at is not null;

در آخر ، پارتیشن را به جدول پایه وصل کنید:

alter table t
  attach partition t_2025_02
    for values from ('2025-02-01') to ('2025-03-01');

برای نشان دادن شاخص ها ، پرس و جو را اجرا کنید (برای پرس و جو به بخش “شاخص های پرس و جو” در بالا مراجعه کنید):

 table_name |            index_name             | status
------------+-----------------------------------+--------
 t          | t__id__created_at__idx            | VALID
 t          | t__id__created_at_nn__idx         | VALID
 t_2025_02  | t_2025_02__id__created_at__idx    | VALID
 t_2025_02  | t_2025_02__id__created_at_nn__idx | VALID

PostgreSQL هوشمندانه شاخص مربوطه را در جدول والدین می یابد و شاخص کودک را به آن “ضمیمه می کند”. ما می توانیم از PSQL استفاده کنیم \d+ متا را بررسی کنید تا بررسی کند که هر شاخص والدین فرزند مناسب دارد:

شرح فهرست t__id__created_at__idx

البته اگر جدول کودک دارای شاخص مربوطه نباشد ، Postgres آن را با استفاده از کنوانسیون نامگذاری پیش فرض ایجاد می کند.

در این بخش ، یک جدول والدین را با یک پارتیشن موجود در نظر می گیریم. ما می خواهیم ضمن کنترل نام فهرست در پارتیشن ، یک شاخص جدید در مورد والدین ایجاد کنیم. بیایید ابتدا جدول والدین را رها کنیم:

drop table if exists t;

و سپس والدین و یک پارتیشن را برای آن ایجاد کنید ، بدون هیچ گونه شاخص:

create table t (
  id bigint generated always as identity not null,
  created_at timestamptz)
partition by range (created_at);

create table t_2025_02
  partition of t
    for values from ('2025-02-01') to ('2025-03-01');

ایجاد شاخص ها فقط در جدول والدین

ما ابتدا یک شاخص را در مورد والدین ایجاد خواهیم کرد ، و نه در هیچ یک از پارتیشن های آن. ترفند استفاده از نحو Postgres است:

create index ... on ONLY table ...

از PostgreSQL اسناد فهرست ایجاد کنید:

کی CREATE INDEX در یک جدول تقسیم شده فراخوانی شده است ، رفتار پیش فرض این است که به همه پارتیشن ها بپردازید تا اطمینان حاصل شود که همه آنها دارای شاخص های تطبیق هستند. هر پارتیشن ابتدا برای تعیین اینکه آیا یک شاخص معادل در حال حاضر وجود دارد ، بررسی می شود و اگر چنین است ، این شاخص به عنوان یک شاخص پارتیشن به شاخص ایجاد شده وصل می شود ، که به شاخص والدین آن تبدیل می شود. اگر شاخص تطبیق وجود نداشته باشد ، شاخص جدیدی ایجاد می شود و به طور خودکار پیوست می شود. نام شاخص جدید در هر پارتیشن مشخص می شود که گویی هیچ نام فهرست در دستور مشخص نشده است. اگر تنها گزینه مشخص شود ، هیچ تلاشی انجام نمی شود و شاخص نامعتبر است. (ALTER INDEX ... ATTACH PARTITION این شاخص را معتبر می کند ، هنگامی که همه پارتیشن ها شاخص های تطبیق را بدست می آورند.) توجه داشته باشید که هر پارتیشن که در آینده با استفاده از آن ایجاد شده است CREATE TABLE ... PARTITION OF بدون در نظر گرفتن اینکه به طور خودکار یک شاخص تطبیق خواهد داشت ONLY مشخص شده است

بیایید این کار را انجام دهیم:

create index t__id__created_at__idx
  on ONLY t(id, created_at);

create index t__id__created_at_nn__idx
  on ONLY t(id, created_at)
    where created_at is not null;

پرس و جو را از بخش “شاخص های پرس و جو” در بالا اجرا کنید ، و خواهید دید که فهرست ها در INVALID حالت:

 table_name |        index_name         | status
------------+---------------------------+---------
 t          | t__id__created_at__idx    | INVALID
 t          | t__id__created_at_nn__idx | INVALID

ایجاد فهرست در پارتیشن

در اینجا ، ما به صورت عادی پیش می رویم و با هر اسمی که می خواهیم مستقیماً روی پارتیشن ایجاد می کنیم:

create index t_2025_02__id__created_at__idx
  on t_2025_02(id, created_at);

create index t_2025_02__id__created_at_nn__idx
  on t_2025_02(id, created_at)
  where created_at is not null;

پرس و جو از فهرست ها ، می بینیم که شاخص های پارتیشن در VALID حالت ، اما شاخص های والدین هنوز نامعتبر هستند. ما آن را در بخش بعدی اصلاح خواهیم کرد.

 table_name |            index_name             | status
------------+-----------------------------------+---------
 t          | t__id__created_at__idx            | INVALID
 t          | t__id__created_at_nn__idx         | INVALID
 t_2025_02  | t_2025_02__id__created_at__idx    | VALID
 t_2025_02  | t_2025_02__id__created_at_nn__idx | VALID

پیوستن به شاخص های پارتیشن به والدین

از مستندات فهرست alter postgresql:

ALTER INDEX <name> ATTACH PARTITION <index_name>;
باعث می شود که شاخص نامگذاری شده (احتمالاً دارای مجوز SCHEMA) به شاخص تغییر یافته متصل شود. شاخص نامگذاری شده باید در یک بخش از جدول حاوی شاخص تغییر یافته باشد و تعریف معادل آن را داشته باشد. یک شاخص پیوست به خودی خود نمی تواند کاهش یابد و در صورت کاهش شاخص والدین آن ، به طور خودکار از بین می رود.

بیایید فهرست ها را ضمیمه کنیم:

alter index t__id__created_at__idx
  attach partition t_2025_02__id__created_at__idx;

alter index t__id__created_at_nn__idx
  attach partition t_2025_02__id__created_at_nn__idx;

اکنون ، شاخص های پرس و جو ، و voilà! ما همه شاخص های مورد نظر را در حالت معتبر داریم.

table_name |            index_name             | status
------------+-----------------------------------+--------
t          | t__id__created_at__idx            | VALID
t          | t__id__created_at_nn__idx         | VALID
t_2025_02  | t_2025_02__id__created_at__idx    | VALID
t_2025_02  | t_2025_02__id__created_at_nn__idx | VALID

همه عاشق اتوماسیون هستند! در اینجا ، ما عملکردی داریم که به طور خودکار تمام شاخص ها را از والدین بر روی کودک ایجاد می کند و باعث می شود کودک آماده شود تا به والدین وصل شود. این شاخص ها را به عنوان “کلید اصلی” https://dev.to/ “منحصر به فرد” مشخص می کند ، اما ممکن است بخواهید با این بخش بازی کنید:

create or replace function create_indexes_on_partition(base_table text, part_table text)
returns void as
$$
declare
    rec record;
    sql text;
begin
    for rec in
        select idx.oid as index_oid,
               idx.relname as index_name
        from pg_index ind
        join pg_class idx on ind.indexrelid = idx.oid
        join pg_class tbl on tbl.oid = ind.indrelid
        where tbl.relname = base_table
          -- all indexes, except primary keys or distinct indexes
          and ind.indisunique = false
          and ind.indisprimary = false
    loop
        sql := format(
            'create index %i_%i on %i %s;',
            part_table,
            ltrim(rec.index_name, base_table),
            part_table,
             -- index method and column list
            substring(pg_get_indexdef(rec.index_oid) from 'using.*')
        );
        raise notice 'executing: %', sql;
        execute sql;
    end loop;
end;
$$ language plpgsql;

تست

تمام مراحل موجود در بخش “تنظیم” را در بالا انجام دهید و یک پارتیشن ایجاد کنید:

create table t_2025_02 (
  like t including all excluding indexes excluding identity
);

سپس عملکرد را اجرا کنید:

select create_indexes_on_partition('t','t_2025_02');

خروجی:

NOTICE:  Executing: CREATE INDEX t_2025_02___id__created_at__idx ON t_2025_02 USING btree (id, created_at);
NOTICE:  Executing: CREATE INDEX t_2025_02___id__created_at_nn__idx ON t_2025_02 USING btree (id, created_at) WHERE (created_at IS NOT NULL);
 create_indexes_on_partition
-----------------------------

در آخر ، پارتیشن را به جدول پایه وصل کنید:

alter table t
  attach partition t_2025_02
    for values from ('2025-02-01') to ('2025-03-01');

برای نشان دادن شاخص ها ، پرس و جو را اجرا کنید (برای پرس و جو به بخش “شاخص های پرس و جو” در بالا مراجعه کنید):

 table_name |             index_name             | status
------------+------------------------------------+--------
 t          | t__id__created_at__idx             | VALID
 t          | t__id__created_at_nn__idx          | VALID
 t_2025_02  | t_2025_02___id__created_at__idx    | VALID
 t_2025_02  | t_2025_02___id__created_at_nn__idx | VALID

شما می توانید وظیفه ایجاد پارتیشن ها ، اضافه کردن شاخص ها را برنامه ریزی کرده و سپس آنها را با استفاده از PG_CRON یا هر مکانیزم برنامه ریزی دیگری که دوست دارید به جدول پایه وصل کنید!

ek3nk4r 2025-02-01

0 1 خواندن این مطلب 9 دقیقه زمان میبرد