PostgreSQL Table Partitioning Yapısı ve Performans Değişimleri
Merhabalar, bu yazımda PostgreSQL üzerinde table partition yapısı ile geleneksel table yapısı arasındaki farklardan ve performans anlamındaki değişimleri tartışmaya çalışacağım. Bununla birlikte table partition yapısının ve uygulanması için gerekli teknik adımları anlatmaya çalışacağım.
Table Partitioning Nedir ?
Table partitioning, ilişkisel veritabanında tablolar halinde tutulan verilerin mantıksal olarak birden çok tabloya dağılması işlemidir. Yani, bir tablo içerisindeki veriyi belirli şartlara göre mantıksal olarak başka tablolara dağıtmak anlamına gelmektedir. Örnek olarak,
CREATE TABLE public.payment_new
(
payment_id integer NOT NULL DEFAULT nextval('payment_new_payment_id_seq'::regclass),
customer_id smallint NOT NULL,
staff_id smallint NOT NULL,
rental_id integer NOT NULL,
amount numeric(5,2) NOT NULL,
payment_date timestamp without time zone NOT NULL,
CONSTRAINT payment_new_pkey PRIMARY KEY (payment_id,payment_date),
CONSTRAINT payment_customer_2_id_fkey FOREIGN KEY (customer_id)
REFERENCES public.customer (customer_id) MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE RESTRICT,
CONSTRAINT payment_new_rental_id_fkey FOREIGN KEY (rental_id)
REFERENCES public.rental (rental_id) MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE SET NULL,
CONSTRAINT payment_new_staff_id_fkey FOREIGN KEY (staff_id)
REFERENCES public.staff (staff_id) MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE RESTRICT
)yukarıdaki tablo üzerinde şu şekilde bir SELECT sorgusu çalıştırılıyor;
SELECT customer_id,amount FROM payment_new WHERE payment_date='2007-02-15 22:25:46.996577'Burada tabloyu payment_date alanına göre partition etmek bizim için mantıklı olacaktır. payment_date alanında 2007 yılına ait kayıtlar mevcuttur. payment_new tablosunu payment_date alanına göre mantıksal olarak tablolara bölmek ve bu tabloları kullanmak mümkündür. Tabloları mantıksal olarak belirli bir koşula göre bölme ve sonrasında elde edilen sonuç table partition süreci olarak isimlendirilmektedir.
Table Partition Yapılması
PostgreSQL üzerinde table partition yapmadan önce bilinmesi gereken bazı kurallar vardır. Bu kurallar table partition öncesinde gözden geçirilmeli ve eksikler varsa tamamlanmalıdır.
- Tablodaki primary key içerisinde partition yapılacak alanın olması gerekmektedir.
- Tabloda unique index kullanılacaksa, bu index’in içerisinde kesinlikle partition key alanı da olmalıdır.
- Index nesnelerini oluştururken tablespace belirtilmemelidir.
PostgreSQL Range Partition
Range partition işlemi, özellikle tarih alanı üzerinden yapılan partition’larda çok kullanışlıdır. Burada veriyi ait olduğu zaman değerine göre mantıksal gruplara ayırarak gelen sorgularda da bu zaman alanı üzerinden sorgulama yaparak performans kazanımı mümkündür.
Range partition işlemine başlamak önce yeni tablomuzu oluşturmamız gerekiyor. Burada, partition key alanının da tüm PK ve unique constraint nesnelerinde olduğundan emin olmamız gerekiyor.
CREATE TABLE public.payment_new
(
payment_id integer NOT NULL DEFAULT nextval('payment_new_payment_id_seq'::regclass),
customer_id smallint NOT NULL,
staff_id smallint NOT NULL,
rental_id integer NOT NULL,
amount numeric(5,2) NOT NULL,
payment_date timestamp without time zone NOT NULL,
CONSTRAINT payment_new_pkey PRIMARY KEY (payment_id,payment_date),
CONSTRAINT payment_customer_2_id_fkey FOREIGN KEY (customer_id)
REFERENCES public.customer (customer_id) MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE RESTRICT,
CONSTRAINT payment_new_rental_id_fkey FOREIGN KEY (rental_id)
REFERENCES public.rental (rental_id) MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE SET NULL,
CONSTRAINT payment_new_staff_id_fkey FOREIGN KEY (staff_id)
REFERENCES public.staff (staff_id) MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE RESTRICT
) PARTITION BY RANGE (payment_date);Tabloyu bu şekilde oluşturduktan sonra partitionlarını oluşturmaya başlayabiliriz. Aşağıdaki gibi tabloya 4 adet partition ekleyebiliriz.
CREATE TABLE payment_new_y2007m02 PARTITION OF payment_new
FOR VALUES FROM ('2007-02-01') TO ('2007-03-01');CREATE TABLE payment_new_y2007m03 PARTITION OF payment_new
FOR VALUES FROM ('2007-03-01') TO ('2007-04-01');CREATE TABLE payment_new_y2007m04 PARTITION OF payment_new
FOR VALUES FROM ('2007-04-01') TO ('2007-05-01');CREATE TABLE payment_new_y2007m05 PARTITION OF payment_new
FOR VALUES FROM ('2007-05-01') TO ('2007-06-01');
Tüm bu işlemlerden sonra PostgreSQL 12 ile birlikte eğer tabloda index kullanacaksak parent tablo dediğimiz nesneye yani ana tabloya aşağıdaki gibi indexlerimizi oluşturabiliriz.
CREATE INDEX idx_fk_customer_id_2
ON public.payment_new USING btree
(customer_id ASC NULLS LAST)CREATE INDEX idx_fk_rental_id_2
ON public.payment_new USING btree
(rental_id ASC NULLS LAST)CREATE INDEX idx_fk_staff_id_2
ON public.payment_new USING btree
(staff_id ASC NULLS LAST)
Tüm bu işlemlerden sonra partition halde tablo oluşturulmuş oldu. Daha sonrasında eğer mevcut bir tabloyu transfer etmek istiyorsak PLSQL ya da farklı araçlar ile iki tablo arasındaki veri transferini gerçekleştirebiliriz. (Bu yazımda bundan bahsetmedim kapsam dışına çıkıp konuyu uzatmamak adına)
Oluşturulan partitionları ise aşağıdaki şekilde kontol edebiliriz.
SELECT
nmsp_parent.nspname AS parent_schema,
parent.relname AS parent,
nmsp_child.nspname AS child_schema,
child.relname AS child
FROM pg_inherits
JOIN pg_class parent ON pg_inherits.inhparent = parent.oid
JOIN pg_class child ON pg_inherits.inhrelid = child.oid
JOIN pg_namespace nmsp_parent ON nmsp_parent.oid = parent.relnamespace
JOIN pg_namespace nmsp_child ON nmsp_child.oid = child.relnamespace
WHERE parent.relname='payment_new';
PostgreSQL List Partition
PostgreSQL List partition ise, belirli bir sütunun aldığı değerlere göre liste halinde partition oluşturmak için kullanılmaktadır. Örnek olarak, durum belirten alanlarda tüm durumlara uygun olacak şekilde partition oluşturulabilir. Aşağıdaki örnekte, müşteri tablosunda aktif ya da pasif olan müşteriler için iki farklı partition oluşturulmuştur.
CREATE TABLE public.customer_new
(
customer_new_id integer NOT NULL DEFAULT nextval('customer_new_customer_new_id_seq'::regclass),
store_id smallint NOT NULL,
first_name character varying(45) COLLATE pg_catalog."default" NOT NULL,
last_name character varying(45) COLLATE pg_catalog."default" NOT NULL,
email character varying(50) COLLATE pg_catalog."default",
address_id smallint NOT NULL,
activebool boolean NOT NULL DEFAULT true,
create_date date NOT NULL DEFAULT ('now'::text)::date,
last_update timestamp without time zone DEFAULT now(),
active integer,
CONSTRAINT customer_new_pkey PRIMARY KEY (customer_new_id,active),
CONSTRAINT customer_new_address_id_fkey FOREIGN KEY (address_id)
REFERENCES public.address (address_id) MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE RESTRICT
) PARTITION BY LIST (active);-- Create Partitions for TABLECREATE TABLE customer_active PARTITION OF customer_new
FOR VALUES IN (1);CREATE TABLE customer_inactive PARTITION OF customer_new
FOR VALUES IN (0);
Daha sonrasında ise, yine tüm parent ve child tablolarda olmak üzere index nesneleri oluşturulmuştur.
CREATE INDEX idx_fk_address_id_2
ON public.customer_new USING btree
(address_id ASC NULLS LAST)CREATE INDEX idx_fk_store_id_2
ON public.customer_new USING btree
(store_id ASC NULLS LAST)CREATE INDEX idx_last_name_2
ON public.customer_new USING btree
(last_name COLLATE pg_catalog."default" ASC NULLS LAST)
PostgreSQL Hash Partition
PostgreSQL Hash partition tipinde, tablo doğrudan mod fonksiyonuna göre bölünmektedir. Yani, örnek vermek gerekirse bir tabloda İD değerinin mod-3 işlemine sokulması ve ID sayısının 3 e bölümünden kalan değere göre uygun partition a yerleştirilmesi işlemi yapılmaktadır. Bu partition tipini, genelde veriyi mantıksal olarak eşit bölemediğimizde tercih ederiz. Çünkü bu sayede, tabloyu incremental id alanına göre doğrudan bölmüş oluruz. Bu nedenle de hash partition özellikle büyük boyutlu ve PK alanı integer tabanlı olan tablolarda kullanışlıdır.
Hash partition yapmak için yine tablo oluştururuken hash partition hint değeri verilmektedir.
-- Create Table for Partition
CREATE TABLE public.rental_new
(
rental_new_id integer NOT NULL DEFAULT nextval('rental_new_rental_new_id_seq'::regclass),
rental_new_date timestamp without time zone NOT NULL,
inventory_id integer NOT NULL,
customer_id smallint NOT NULL,
return_date timestamp without time zone,
staff_id smallint NOT NULL,
last_update timestamp without time zone NOT NULL DEFAULT now(),
CONSTRAINT rental_new_pkey PRIMARY KEY (rental_new_id),
CONSTRAINT rental_new_customer_id_fkey FOREIGN KEY (customer_id)
REFERENCES public.customer (customer_id) MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE RESTRICT,
CONSTRAINT rental_new_inventory_id_fkey FOREIGN KEY (inventory_id)
REFERENCES public.inventory (inventory_id) MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE RESTRICT,
CONSTRAINT rental_new_staff_id_key FOREIGN KEY (staff_id)
REFERENCES public.staff (staff_id) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION
) PARTITION BY HASH(rental_new_id);
-- Create Partition Tables for the Table
CREATE TABLE rental_new_1 PARTITION OF rental_new
FOR VALUES WITH (MODULUS 3, REMAINDER 0);
CREATE TABLE rental_new_2 PARTITION OF rental_new
FOR VALUES WITH (MODULUS 3, REMAINDER 1);
CREATE TABLE rental_new_3 PARTITION OF rental_new
FOR VALUES WITH (MODULUS 3, REMAINDER 2);
-- Create Indexes for Table
CREATE INDEX idx_fk_inventory_id_2
ON public.rental_new USING btree
(inventory_id ASC NULLS LAST);
CREATE UNIQUE INDEX idx_unq_rental_new_rental_new_date_inventory_id_customer_id
ON public.rental_new USING btree
(rental_new_id ASC,rental_new_date ASC NULLS LAST, inventory_id ASC NULLS LAST, customer_id ASC NULLS LAST);Hash partition ile birlikte PostgreSQL üzerinde mümkün olan tüm partition tiplerinin pratik kısımları tamamlanmış oldu. Bu partition tiplerini ihtiyacınıza ve elinizdeki gereksinimlere göre tercih etmek mümkündür. Ancak dikkat edilmesi gereken nokta her zaman veri modellemesidir. Burada veriyi modellerken ve partitionlara bölerken her zaman eşit,küçük bloklar halinde ve yeri geldiğinde ölçeklendirilebilecek bir partition yapısı seçilmelidir. PostgreSQL özelinde ise partition yapısını 11 ve üzeri versiyonlarda yapmak diğer versiyonlara hem daha az efor gerektirmektedir hem de performans açısından ilerlemeler mevcuttur.
Table Partition vs Standart Table Performans Karşılaştırılması
Table partition tarzı süreçleri aslında özellikle ilişkisel veritabanlarını ölçeklendirmek ve temelde sonsuza kadar scale etmek için tercih ederiz. Sonsuza kadar scale etmek tümcesi biraz iddiaalı ancak doğru veri modelleme ve doğru partition seçimleri ile birlikte mümkün.
Bir tabloyu partition hale getirmek için gerekli önkoşulları ve teknik detaylara çalışmıştık yukarıda. Burada ise bu teknik çalışma sonrası ortaya çıkan durumdan elde edebileceğimiz kazanımlar üzerine çalışacağız. Yaptığımız değişimin çıktısını görmek için bir yük testi ile çalışacağız ve sonuçlarını tartışacağız.
PostgreSQL’de table partition yapısına geçtiğimizde hissedilen ilk fark sorgu planlarındaki değişimdir. Bu değişim tüm tablo ya da index’i taramak yerine sadece ilgili partition’a ait tabloyu ve index’i taramaktadır. Aşağıdaki örnekte hash partition yapılmış bir tabloda ve normal bir tabloda çalışan aynı sorgu için kullanılan planlar mevcuttur.
Table Partition Yapısı Sorgu Planı
explain analyze select * from rental_new where rental_new_id = 16049
Normal Tablo Yapısı Sorgu Planı
explain analyze select * from rental where rental_id = 16049
Görüleceği üzere sorgu planlarında farklılık vardır. İlk sorguda, tabloda doğrudan sadece ilgili partition üzerinden okurken ikinci sorguda tüm index’i tarayarak bir sonuç üretilmektedir. Execution time süreleri de bununla birlikte farklılık göstermektedir ve partition yapıdaki tablonun üzerinde çalışan sorgunun çalışma süresi çok daha kısadır.
Bunun en temel sebebi, ilk sorguda kullanılan partition(tablo) içerisindeki kayıt sayısı ile ikinci tablodaki kayıt sayısı arasındaki farktır. Çünkü ikinci versiyondaki tabloda çok daha fazla kayıt taranması gerekmektedir. Buda sistemde daha fazla I/O isteği anlamına gelir ve performans kaybına yol açar.
rental_new(partition olan) ve rental(partition olmayan) arasındaki farkı göstermek gerekirse ilgili sorguda kullanılan rental_new_3 partition’ı ve rental tablosundaki kayıt sayılarına bakılabilir.
select count(*) from rental_new_3 : 5411
select count(*) from rental : 16044
Görüleceği üzere, aynı sorgu iki farklı modelde çok farklı response time değerlerine sahip. rental_new_3 tablosundaki uğraşılması gereken kayıt sayısı rental tablosundaki toplam kayıt sayısından %60 daha az. Bu nedenle de performans kazanımı sağlanmaktadır.
PostreSQL Partition Pruning
PostgreSQL partition pruning sorgu planını iyileştirmek ve performans kazanımı elde etmek için kullanılan bir seçenektir. PostgreSQL 11 ve sonrasındaki versiyonlarda aktif olarak kullanılmaktadır. Bu opsiyon açıldığında. PostgreSQL SELECT sorgusu için taranmaması partitionları belirler o partitionlar sorgu planından çıkarılır. Böylece sorgu performansında artış olur.
Query planner gereksiz olan bir partition ı ispatlandığında doğrudan plandan çıkarır. Partition pruning için aşağıdaki ayar kullanılabilir.
Partition Pruning Açılması : SET enable_partition_pruning = on;
Partition Pruning Kapatılması : SET enable_partition_pruning = off;
Aşağıdaki örneklerde aynı sorgunun hem partition pruning kapatılarak hem de açılarak ürettiği iki farklı execution plan ve çalışma süresi mevcut. Buradan partition pruning’in de PostgreSQL’ de gerekliliği görülebilir.
SET enable_partition_pruning = off;
explain analyze select count(*) from payment_new where payment_date>’2007–02–15 22:25:46.996577' and payment_date<’2007–03–15 22:25:46.996577'
SET enable_partition_pruning = on;
explain analyze select count(*) from payment_new where payment_date>’2007–02–15 22:25:46.996577' and payment_date<’2007–03–15 22:25:46.996577'
Bu çalışmada PostgreSQL veritabanında table partitioning altyapısı için gerekli teknik gereksinimleri,table partition yapısını sisteme uygulamayı ve table partition’ın mimari anlamda getirdiği kazanımları sebebpleriyle beraber tartışmaya çalıştım. İlişkisel veritabanları için özellikle veri boyutu ve kayıt sayısı fazla( fazlayı tanımlamak biraz zor olduğu için performans sorunu doğrudan tablodaki veri boyutu ve satır sayısı ile ilgili durumlarda bu tabloda fazla veri var diyebiliriz) olduğu tablolarda ölçeklenebilirlik anlamında kullanılabilir. Veriyi yeterince küçük eşit parçalara bölecek şekilde bir partition ve sorgu yapısı ile birlikte performans kazanımı sağlanabilir.
Bir diğer nokta ise, tablodaki partition sayısıdır. Sistemde tabloya ait partition sayısı arttığında sorgu için gerekli planlama süreleri uzar ve buna bağlı olarak performans kaybı da yaşanabilir. Tüm bunlarla birlikte partition sayısının gereğinden fazla olması sistem üzerinde bellek tüketimini de olumsuz etkiler. O nedenle partition işlemini düşünürken verinin eşit ve yetereli miktarda bölünmesi faktörünü göz önünde bulundurmak gerekiyor.
Sevgiler,
Demir.
