PostgreSQL Partitioning

PostgreSQL Partitioning

Nedir?

Bir tablonun belli bir kritere göre parçalanarak, ancak tablo bütünlüğünü bozmadan, performans ve sürdürülebilirliğini arttırma amaçlı kurulan bir yapıdır.

Nerede Uygulanabilir?

- 1 ayda 160 GB’a ulaşan bir tablom vardı. İçine saniyede yüzlerce log basılıyordu. 160 GB’a gelen tablo kolay kolay okunamaz oluyor haliyle. Geçmişe dönük veri silmeye kalktığında ise fazlasıyla zorlanıyor, silinse dahi yeteri kadar performans gösteremiyor. Dolayısıyla bir çözüm olarak günlük partitioning yapıldı. Her bir günlük tablo 5-6 GB veri barındırır oldu. Geçmişe dönük veri silineceği zaman alt tabloları düşürüp gereksiz loglardan hızlıca ve zahmetsizce kurtulmamı sağladı.

Neye Göre Yapılır?

Tablo bölümlemesi için başlıca kriterlerimiz şu şekildedir.

  • Tarihe göre:
    Örn: Günlük, Aylık, 3 Aylık, Yıllık
  • Sıralı giden sayı dizisine göre:
    Örn: Sequence’i olan bir id kolonu mesela.
    1-9.999.999 arası id bir tabloya, 10.000.000 – 19.999.999 arası id başka bir tabloya bölünecek şekilde.
  • Sınıflandırmaya göre:
    Örn: Ürünlerin kargo bilgilerinin yazıldığı bir tablo olduğunu, anlaşmalı 10 kargo kargo firması olduğunu ve içinde milyonlarca kayıt olduğunu varsayalım. Kargo firması ismine göre tabloyu bölümlendirebilirsiniz.
    NOT: Yeni bir kargo firmasıyla anlaşma yapılırsa buna göre bir alt tablonun eklenip trigger fonksiyonunu güncellemek gerekeceğini unutmamak gerekir.

Nasıl Yapılır?

Ufak bir partitioning nasıl yapılır örnekleyelim.
Öncelikle tablo yaratıp içine çeşitli verilerle dolduralım.

CREATE TABLE sales(id SERIAL, productid INTEGER, createdat TIMESTAMP); INSERT INTO sales (productid, createdat) VALUES (GENERATESERIES(400,500), GENERATESERIES('2013-01-01'::timestamp,'2013-01-05'::timestamp,'1 day'));

Asıl tablomuz olan sales tablomuzun ismi değişmeden partitioning işlemini tamamlamamız gerekiyorsa var olanı salestmp olarak yeniden adlandırıp sales diye yeni boş bir tablo oluşturmamız lazım ki alt tabloları da bu yeni oluşturduğumuz tabloya bağlayabilelim. Aynı zamanda bir sorun olursa bu işten vazgeçerek salestmp tablomuzu yeniden adlandırarak sorunsuz ve hızlı bir geri dönüş yapabiliriz.

ALTER TABLE sales RENAME TO salestmp; CREATE TABLE sales ( LIKE salestmp INCLUDING ALL ) ;

Ana tablomuz olan sales tablosunu yarattık, şimdi ise sıra partitioning yapacağımız zaman dilimine göre alt tablolarımıızı yaratmaya geldi. Burada günlük kayıt tutacak şekilde yaratıyoruz.

CREATE TABLE salesy2013m01d01 ( LIKE sales INCLUDING ALL, CHECK ( createdat >= '2013-01-01'::timestamp AND createdat < '2013-01-02'::timestamp ) ) INHERITS (sales); CREATE TABLE salesy2013m01d02 ( LIKE sales INCLUDING ALL, CHECK ( createdat >= '2013-01-02'::timestamp AND createdat < '2013-01-03'::timestamp ) ) INHERITS (sales); CREATE TABLE salesy2013m01d03 ( LIKE sales INCLUDING ALL, CHECK ( createdat >= '2013-01-03'::timestamp AND createdat < '2013-01-04'::timestamp ) ) INHERITS (sales); CREATE TABLE salesy2013m01d04 ( LIKE sales INCLUDING ALL, CHECK ( createdat >= '2013-01-04'::timestamp AND createdat < '2013-01-05'::timestamp ) ) INHERITS (sales); CREATE TABLE salesy2013m01d05 ( LIKE sales INCLUDING ALL, CHECK ( createdat >= '2013-01-05'::timestamp AND created_at < '2013-01-06'::timestamp ) ) INHERITS (sales);

Gelen yeni verilerin hangi kritere göre hangi tabloya yazılacağını karar veren – yönlendiren – bir fonksiyonumuzun olması gerekiyor.
NOT: Fonksiyon NULL döndürmesi gerekiyor yoksa şöyle bir sorunla karşılaşabilirsiniz.

CREATE OR REPLACE FUNCTION salesinserttrigger() RETURNS TRIGGER AS $$ BEGIN IF ( NEW.createdat >= DATE '2013-01-01' AND NEW.createdat < DATE '2013-01-02' ) THEN INSERT INTO salesy2013m01d01 VALUES (NEW.*); ELSIF ( NEW.createdat >= DATE '2013-01-02' AND NEW.createdat < DATE '2013-01-03' ) THEN INSERT INTO salesy2013m01d02 VALUES (NEW.); ELSIF ( NEW.created_at >= DATE '2013-01-03' AND NEW.created_at < DATE '2013-01-04' ) THEN INSERT INTO sales_y2013m01d03 VALUES (NEW.); ELSIF ( NEW.createdat >= DATE '2013-01-04' AND NEW.createdat < DATE '2013-01-05' ) THEN INSERT INTO salesy2013m01d04 VALUES (NEW.*); ELSIF ( NEW.createdat >= DATE '2013-01-05' AND NEW.createdat < DATE '2013-01-06' ) THEN INSERT INTO salesy2013m01d05 VALUES (NEW.*); END IF; RETURN NULL; END; $$ LANGUAGE plpgsql;

Gelen veriyi hangi tabloya yazacağını yönlendiren fonksiyonumuz hazır ancak birinin de bu fonksiyonu bir şekilde ana tabloya yeni kayıt yapılmadan tetiklemesi gerekiyor. Bunu da BEFORE INSERT TRIGGER‘ı ile yapıyoruz.

CREATE TRIGGER salestrigger BEFORE INSERT ON sales FOR EACH ROW EXECUTE PROCEDURE salesinsert_trigger();

Artık var olan verilerimizin aktarılması dışında herşey hazır, kullanılabilir durumdadır. Yeni bir kayıt girileceği zaman, yazmış olduğumuz fonksiyonun yönlendirmesine göre belirtilen tabloya yazacağız.
Son aşama; var olan verilerimizi alt tablolara dağıtma işlemini yapttıktan sonra işimizi bitirmiş olacağız.

INSERT INTO sales ( SELECT * FROM salestmp WHERE createdat >= '2013-01-01' AND createdat < '2013-01-02' ); INSERT INTO sales ( SELECT * FROM salestmp WHERE createdat >= '2013-01-02' AND createdat < '2013-01-03' ); INSERT INTO sales ( SELECT * FROM salestmp WHERE createdat >= '2013-01-03' AND createdat < '2013-01-04' ); INSERT INTO sales ( SELECT * FROM salestmp WHERE createdat >= '2013-01-04' AND createdat < '2013-01-05' ); INSERT INTO sales ( SELECT * FROM salestmp WHERE createdat >= '2013-01-05' AND created_at < '2013-01-06' );

Herşeyin düzgün çalıştığından emin olduktan sonra sales_tmp tablosuna ihtiyacımız kalmadığından silmeliyiz.

DROP TABLE sales_tmp;

Erkin Çakar

PostgreSQL DBA & Software developer