PostgreSQL 10'da Partitioning
Merhaba,
Partitioning aslında bir tablonun belirlenen kriterler için küçük parçalara ayrılmasıdır. Parçalarına ayrılan tabloya partitioned(master) tablo denir. Partitioned tabloları oluşturan küçük tablolara da partition(child) tabloları denir.
Büyük tabloları daha küçük parçalara ayırmanın bir kaç sebebi var.
Sorgu performansının arttırılmasını sağlar. Bir tablonun erişilmiş satırların çoğu aslında tek bir partitionda yada küçük parçalar halinde biren fazla partition tablolarında yer alır. Partition tabloları index sütunlarına yol göstermeye, index boyutunu küçültmeye ve yoğun şekilde kullanılan indexleri memory ile fit etmeye yarar.
Bulk loads ve DELETE işlemleri için partition tablolarının partitioned tablolara eklenmesi ve çıkarılmasıyla başarım sağlanabilir. ALTER TABLE DETACH PARTITION yapmak yada partitioned tabloyla bağını DROP TABLE ile koparmak bulk operasyonlar için oldukça hızlıdır. DELETE komutundan dolayı VACUUM gereksinimi ortadan kalkacaktır.
PostgreSQL 10 öncesinde kullanılan partitioning yapısı ve hem kullanılan script hem de yöntem olarak oldukça farklıydı. En büyük farklılıklardan biri verilerin partition tablolarına aktarılması için trigger yapısının oluşturulmasıydı. PostgreSQL 10 ile bu gibi zahmetler ortadan kalktı.
PG10 ile paritioned tablolar üzerinde ve child tablolar üzerinde birbirinden bağımsız olarak index ekleyebileceğiz. Böylece hem partitioned tablolar hemde child tablolar üzerinde daha esnek olunması sağlanmıştır.
PG10 ile iki farklı formda partition yapılabilir. RANGE ve LIST.
RANGE, tablo belirlenen kriterler için bölünür. Tarih aralığına bölünebileceği gibi belirli objelere göre partition yapılabilir. Örneğin belirli tarih aralığı için RANGE kullanılabilir.
LIST, PARTITION KEY değeri neyse sadece o değerlere ait veriler parçalanır. Örneğin status alanı active, waiting olan değerleri içeren tüm satırlar için ayrıca partition oluşturulması için LIST kullanılır.
PostgreSQL 11 ile HASH Partitioning özelliği de eklendi. Bu partitioning yapısının detaylarını ve PostgreSQL 11 ile gelen özellikleri gelecek yazılarda paylaşacağım. HASH Partitioning ne yapar kısaca bahsetmek gerekirse partitioning tablolarınızı önceden belirlersiniz ve hangi verinin hangi partitioning tablosuna atacağını PostgreSQL kendi karar verir. Her partitioning tablosundaki row sayısı birbirine yakındır. Tüm veriyi partitioning tablolarına bölmeye eşit oranlarda böler.
Kullanılan tabloda gerçekten hangi verinin olup olmadığını görmek için ONLY ifadesi kullanılabilir.
select * from only table_name;
Partition tabloları partitioned tablolarında olmayan bir sütunu içeremez. Partition tablosu oluşturulurken yada partition tablosu oluşturulduktan sonra tablo farklı bir sütunun eklenmesi için ALTER edilemez. Bir tablo oluşturulduktan sonra başka bir tablonun partition tablosu olarak eklenecekse sütunlar kontrol edildikten sonra ALTER TABLE . . . ATTACH PARTITION ifadesi ile partitioned tabloya bağlanabilir.
Eğer partitioned tablosunda NOT NULL constraint’i bulunuyorsa partition tablolarından NOT NULL constraint’i DROP edilemez.
CHECK ve NOT NULL kriterleri her zaman partitioned tablosunun child tablolarına inherit edilir. Var olan bir partitioned tabloda CHECK CONSTRAINT, NO INHERIT olarak değişitirilemez.
PostgreSQL 10’da partitioning yapılırken ilk dikkat edilmesi gereken mevcut tablo üzerinde mi yoksa içinde veri olmayan bir tabloya mı partitioning yapılacağıdır. Eğer tablo ilk defa oluşturulacaksa CREATE scriptinin sonunda PARTITION BY ifadesi eklenmelidir.
CREATE TABLE products (
id SERIAL NOT NULL,
p_name CHARACTER VARYING NOT NULL,
p_staus CHARACTER VARYING NOT NULL,
created_date TIMESTAMP NOT NULL
)
PARTITION BY RANGE ( created_date );
Bundan sonra gelen partition tablosu aşağıdaki gibi oluşturululabilir.
CREATE TABLE products_y2018m05
PARTITION OF products (created_date, PRIMARY KEY (id))
FOR VALUES FROM ('20180501') TO ('20180601');
Bu tabloyu partititoned tablodan ayırmak için aşağıdaki script kullanılmalıdır.
ALTER TABLE products DETACH PARTITION products_y2018m05;
Ya da yukarıda belirttiğim gibi daha önceden aynı sütunlara sahip bir tablo oluşturulduysa bu tabloyu partitioned tabloya attach edebiliriz. Partition tablosunu partitioned tabloya eklemeden önce CHECK CONSTRAINT değerlerinin önceden oluşturulması postgresql.org dökümanlarında önerilmekte. Biz de ATTACTH öncesi constraint i ekleyelim.
ALTER TABLE products_y2018m05 ADD CONSTRAINT y2018m05
CHECK(created_date>='20180501' and created_date<'20180601');
ALTER TABLE products ATTACH PARTITION products_y2018m05
FOR VALUES FROM ('20180501') TO ('20180601' );
Eğer mevcut tablo üzerinde partitioning yapılacaksa aşağıdaki adımlar izlenebilir.
Örnek verebilmek için tarihleri haftalık böleceğimiz tabloyu oluşturalım.
-- drop table part_date;
-- create sequence partdate_seq start 1;
CRETAE TABLE part_date AS
SELECT NEXTVAL('partdate_seq') AS id, i,RANDOM() AS random FROM GENERATE_SERIES('2018-01-01'::TIMESTAMP,'2018-05-01'::TIMESTAMP,'1 second'::INTERVAL) AS i;
Her saniye için yeni bir satır eklenmiş oldu ve tablomuzun yapısı aşağıdaki gibi olacaktır.
moon=# SELECT * FROM part_date LIMIT 5;
id | i | random
----+---------------------+--------------------
1 | 2018-01-01 00:00:00 | 0.783119229134172
2 | 2018-01-01 00:00:01 | 0.401362804695964
3 | 2018-01-01 00:00:02 | 0.0935262320563197
4 | 2018-01-01 00:00:03 | 0.426995042245835
5 | 2018-01-01 00:00:04 | 0.81481532054022
(5 rows)
Oluşturduğumuz bu ana tablo üzerinde, partition tablolarının oluşturulması ve verilerin aktarılması süreci tamamlanmadan hiçbir değişiklik yapmayacağız.
Ana partition tablosunun CREATE scriptini kullanarak yeni bir tablo oluşturalım ve PG10 da olması gerektiği gibi PARTITION BY ifadesini ekleyelim. CHECK ve NOT NULL kriterleri her zaman partitioned tablosunun child tablolarından gelir. Dolayısıyla CREATE scriptinde herhangi bir CHECK CONSTRAINT kullanılmayacaktır. Bu kopya tablonun CREATE scriptinde sadece sütunlar ve data type’ları yer alacakrtır. Tabi bunun yaninda hangi partition formunun kullanılacağı belirtilmelidir, RANGE veya LIST. PRIMARY KEY alanı child tablolar oluşturulurken kullanılacaktır.
-- drop table part_date_;
CREATE TABLE public.part_date_
(
id bigint,
i timestamp without time zone,
random double precision
)PARTITION BY RANGE (i);
Şu an için hiç veri yok bu tablomuzda.
SELECT * FROM part_date_;
i sütunu yani tarih alanını haftalık böleceğimiz yeni partition tablolarımızı oluşturalım. Bu kısmı şimdilik manuel yapacağız. Daha sonrasında tablodaki verilere göre CREATE TABLE PARTITION OF ifadesini oluşturmak için bir script hazırladım. Çok fazla veri varsa haftalık bölmek için kullanılabilir.
CREATE TABLE public.part_date_2018_p1 PARTITION OF part_date_(i , PRIMARY KEY (id)) FOR VALUES FROM ('2018-01-01') to ('2018-01-08');
CREATE TABLE public.part_date_2018_p2 PARTITION OF part_date_(i , PRIMARY KEY (id)) FOR VALUES FROM ('2018-01-08') to ('2018-01-15');
CREATE TABLE public.part_date_2018_p3 PARTITION OF part_date_(i , PRIMARY KEY (id)) FOR VALUES FROM ('2018-01-15') to ('2018-01-22');
INSERT INTO public.part_date_
SELECT *
FROM public.part_date
WHERE i<'2018-01-22';
Where koşulunu eklememin sebebi şimdilik üç adet partition tablosu oluşturdum. Insert sırasında bu üç partition tablosuna da eklenemeyen veriler olduğunda hata verecek. Hata almamak için where koşulu şimdilik orada kalsın.
Tüm partition tablolarına insert işlemi tamamlandıktan sonra bu ana tablonun kopyası olan part_date_ tablosuna part_date tablosuna ait partition tablolarına index’leri ekleyebiliriz.
Artık part_date yani asıl tablomuz üzerinde değişiklik yapmamız gereken yerdeyiz. Bu tablonun ismini değiştireceğiz.
ALTER TABLE part_date RENAME TO part_date_old;
Şimdi de ana tablonun kopyasının ismini değiştirelim.
ALTER TABLE part_date_ RENAME TO part_date;
Aşağıda sizle paylaştığım script tabloda bulunan timestamp alanları haftalık böler ve partition tablolarını oluşturmak için gereken create scriptini bizim için oluşturacak. Scriptten sql tümcesi üretilirken en dışta kullandığımız tablonun ana tablo olduğu gözden kaçırılmamalıdır.
SELECT MIN(i), MAX(i), EXTRACT(WEEK FROM i),
'CREATE TABLE public.part_date_'
|| EXTRACT(YEAR FROM i)
|| '_' || EXTRACT(MONTH FROM i)
|| '_p' || EXTRACT(WEEK FROM i)
|| ' PARTITION OF public.part_date_(i) FOR VALUES FROM ('''
|| MIN(i)::DATE
|| E'\') TO (\''
|| MAX(i)::DATE
|| E'\');'
FROM part_date
GROUP BY EXTRACT(year FROM i), EXTRACT(month FROM i), EXTRACT(WEEK FROM i)
LIMIT 10;
Sevgiler,
Partitioning aslında bir tablonun belirlenen kriterler için küçük parçalara ayrılmasıdır. Parçalarına ayrılan tabloya partitioned(master) tablo denir. Partitioned tabloları oluşturan küçük tablolara da partition(child) tabloları denir.
Büyük tabloları daha küçük parçalara ayırmanın bir kaç sebebi var.
Sorgu performansının arttırılmasını sağlar. Bir tablonun erişilmiş satırların çoğu aslında tek bir partitionda yada küçük parçalar halinde biren fazla partition tablolarında yer alır. Partition tabloları index sütunlarına yol göstermeye, index boyutunu küçültmeye ve yoğun şekilde kullanılan indexleri memory ile fit etmeye yarar.
Bulk loads ve DELETE işlemleri için partition tablolarının partitioned tablolara eklenmesi ve çıkarılmasıyla başarım sağlanabilir. ALTER TABLE DETACH PARTITION yapmak yada partitioned tabloyla bağını DROP TABLE ile koparmak bulk operasyonlar için oldukça hızlıdır. DELETE komutundan dolayı VACUUM gereksinimi ortadan kalkacaktır.
PostgreSQL 10 öncesinde kullanılan partitioning yapısı ve hem kullanılan script hem de yöntem olarak oldukça farklıydı. En büyük farklılıklardan biri verilerin partition tablolarına aktarılması için trigger yapısının oluşturulmasıydı. PostgreSQL 10 ile bu gibi zahmetler ortadan kalktı.
PG10 ile paritioned tablolar üzerinde ve child tablolar üzerinde birbirinden bağımsız olarak index ekleyebileceğiz. Böylece hem partitioned tablolar hemde child tablolar üzerinde daha esnek olunması sağlanmıştır.
Declerative Partitions
Tüm satırlar partitioned tabloya yani belirlenen PARTITION KEY değeri baz alınarak partition tablolarına INSERT edilir.PG10 ile iki farklı formda partition yapılabilir. RANGE ve LIST.
RANGE, tablo belirlenen kriterler için bölünür. Tarih aralığına bölünebileceği gibi belirli objelere göre partition yapılabilir. Örneğin belirli tarih aralığı için RANGE kullanılabilir.
LIST, PARTITION KEY değeri neyse sadece o değerlere ait veriler parçalanır. Örneğin status alanı active, waiting olan değerleri içeren tüm satırlar için ayrıca partition oluşturulması için LIST kullanılır.
PostgreSQL 11 ile HASH Partitioning özelliği de eklendi. Bu partitioning yapısının detaylarını ve PostgreSQL 11 ile gelen özellikleri gelecek yazılarda paylaşacağım. HASH Partitioning ne yapar kısaca bahsetmek gerekirse partitioning tablolarınızı önceden belirlersiniz ve hangi verinin hangi partitioning tablosuna atacağını PostgreSQL kendi karar verir. Her partitioning tablosundaki row sayısı birbirine yakındır. Tüm veriyi partitioning tablolarına bölmeye eşit oranlarda böler.
Kullanılan tabloda gerçekten hangi verinin olup olmadığını görmek için ONLY ifadesi kullanılabilir.
select * from only table_name;
Partition tabloları partitioned tablolarında olmayan bir sütunu içeremez. Partition tablosu oluşturulurken yada partition tablosu oluşturulduktan sonra tablo farklı bir sütunun eklenmesi için ALTER edilemez. Bir tablo oluşturulduktan sonra başka bir tablonun partition tablosu olarak eklenecekse sütunlar kontrol edildikten sonra ALTER TABLE . . . ATTACH PARTITION ifadesi ile partitioned tabloya bağlanabilir.
Eğer partitioned tablosunda NOT NULL constraint’i bulunuyorsa partition tablolarından NOT NULL constraint’i DROP edilemez.
CHECK ve NOT NULL kriterleri her zaman partitioned tablosunun child tablolarına inherit edilir. Var olan bir partitioned tabloda CHECK CONSTRAINT, NO INHERIT olarak değişitirilemez.
PostgreSQL 10’da partitioning yapılırken ilk dikkat edilmesi gereken mevcut tablo üzerinde mi yoksa içinde veri olmayan bir tabloya mı partitioning yapılacağıdır. Eğer tablo ilk defa oluşturulacaksa CREATE scriptinin sonunda PARTITION BY ifadesi eklenmelidir.
CREATE TABLE products (
id SERIAL NOT NULL,
p_name CHARACTER VARYING NOT NULL,
p_staus CHARACTER VARYING NOT NULL,
created_date TIMESTAMP NOT NULL
)
PARTITION BY RANGE ( created_date );
Bundan sonra gelen partition tablosu aşağıdaki gibi oluşturululabilir.
CREATE TABLE products_y2018m05
PARTITION OF products (created_date, PRIMARY KEY (id))
FOR VALUES FROM ('20180501') TO ('20180601');
Bu tabloyu partititoned tablodan ayırmak için aşağıdaki script kullanılmalıdır.
ALTER TABLE products DETACH PARTITION products_y2018m05;
Ya da yukarıda belirttiğim gibi daha önceden aynı sütunlara sahip bir tablo oluşturulduysa bu tabloyu partitioned tabloya attach edebiliriz. Partition tablosunu partitioned tabloya eklemeden önce CHECK CONSTRAINT değerlerinin önceden oluşturulması postgresql.org dökümanlarında önerilmekte. Biz de ATTACTH öncesi constraint i ekleyelim.
ALTER TABLE products_y2018m05 ADD CONSTRAINT y2018m05
CHECK(created_date>='20180501' and created_date<'20180601');
ALTER TABLE products ATTACH PARTITION products_y2018m05
FOR VALUES FROM ('20180501') TO ('20180601' );
Kısıtlamalar
- Partition üzerinde oluşturulan indexlerin diğer partitionlarda otomatik oluşturacak bir yapı yoktur. INDEX’ler her partition için ayrı ayrı oluşturulmalıdır. Bunun diğer bir anlamı PRIMARY KEY, UNIQUE KEY ‘in tüm partitionlarda oluşturulması ya da tüm partitionlardan çıkarılması durumu söz konusu değildir. Bunu yapmanın tek yolu her partition tablosu üzerinde aynı kısıtları tanımlamaktan geçer.
- Herhangi bir partition tablosundaki satırı başka bir partition tablosuna taşımak için yapılan UPDATE işlemi hata verecek.
- Eğer row trigger gerekiyorsa bunu partitioned tabloda değil partition tablolarında yapılması gerekmekte.
Eğer mevcut tablo üzerinde partitioning yapılacaksa aşağıdaki adımlar izlenebilir.
- Ana tablonun create scriptinden yardım alarak ve PG10 a uygun partition by ifadesi eklenip ana tablonun kopyası oluşturulur. (kopya tablo)
- Kopyalanan tablo üzerinde partition tabloları oluşturulur.
- Ana tablodan kopyalanan tabloya veri insert edilir.
- Ana tablo üzerindeki indexler kopya tabloya ait partition tablolarında oluşturulur.
- Ana tablonun ismi değiştirilir.
- Kopyalan tablonun ismine ana tablonun ismi verilir.
Örnek verebilmek için tarihleri haftalık böleceğimiz tabloyu oluşturalım.
-- drop table part_date;
-- create sequence partdate_seq start 1;
CRETAE TABLE part_date AS
SELECT NEXTVAL('partdate_seq') AS id, i,RANDOM() AS random FROM GENERATE_SERIES('2018-01-01'::TIMESTAMP,'2018-05-01'::TIMESTAMP,'1 second'::INTERVAL) AS i;
Her saniye için yeni bir satır eklenmiş oldu ve tablomuzun yapısı aşağıdaki gibi olacaktır.
moon=# SELECT * FROM part_date LIMIT 5;
id | i | random
----+---------------------+--------------------
1 | 2018-01-01 00:00:00 | 0.783119229134172
2 | 2018-01-01 00:00:01 | 0.401362804695964
3 | 2018-01-01 00:00:02 | 0.0935262320563197
4 | 2018-01-01 00:00:03 | 0.426995042245835
5 | 2018-01-01 00:00:04 | 0.81481532054022
(5 rows)
Oluşturduğumuz bu ana tablo üzerinde, partition tablolarının oluşturulması ve verilerin aktarılması süreci tamamlanmadan hiçbir değişiklik yapmayacağız.
Ana partition tablosunun CREATE scriptini kullanarak yeni bir tablo oluşturalım ve PG10 da olması gerektiği gibi PARTITION BY ifadesini ekleyelim. CHECK ve NOT NULL kriterleri her zaman partitioned tablosunun child tablolarından gelir. Dolayısıyla CREATE scriptinde herhangi bir CHECK CONSTRAINT kullanılmayacaktır. Bu kopya tablonun CREATE scriptinde sadece sütunlar ve data type’ları yer alacakrtır. Tabi bunun yaninda hangi partition formunun kullanılacağı belirtilmelidir, RANGE veya LIST. PRIMARY KEY alanı child tablolar oluşturulurken kullanılacaktır.
-- drop table part_date_;
CREATE TABLE public.part_date_
(
id bigint,
i timestamp without time zone,
random double precision
)PARTITION BY RANGE (i);
Şu an için hiç veri yok bu tablomuzda.
SELECT * FROM part_date_;
i sütunu yani tarih alanını haftalık böleceğimiz yeni partition tablolarımızı oluşturalım. Bu kısmı şimdilik manuel yapacağız. Daha sonrasında tablodaki verilere göre CREATE TABLE PARTITION OF ifadesini oluşturmak için bir script hazırladım. Çok fazla veri varsa haftalık bölmek için kullanılabilir.
CREATE TABLE public.part_date_2018_p1 PARTITION OF part_date_(i , PRIMARY KEY (id)) FOR VALUES FROM ('2018-01-01') to ('2018-01-08');
CREATE TABLE public.part_date_2018_p2 PARTITION OF part_date_(i , PRIMARY KEY (id)) FOR VALUES FROM ('2018-01-08') to ('2018-01-15');
CREATE TABLE public.part_date_2018_p3 PARTITION OF part_date_(i , PRIMARY KEY (id)) FOR VALUES FROM ('2018-01-15') to ('2018-01-22');
INSERT INTO public.part_date_
SELECT *
FROM public.part_date
WHERE i<'2018-01-22';
Where koşulunu eklememin sebebi şimdilik üç adet partition tablosu oluşturdum. Insert sırasında bu üç partition tablosuna da eklenemeyen veriler olduğunda hata verecek. Hata almamak için where koşulu şimdilik orada kalsın.
Tüm partition tablolarına insert işlemi tamamlandıktan sonra bu ana tablonun kopyası olan part_date_ tablosuna part_date tablosuna ait partition tablolarına index’leri ekleyebiliriz.
Artık part_date yani asıl tablomuz üzerinde değişiklik yapmamız gereken yerdeyiz. Bu tablonun ismini değiştireceğiz.
ALTER TABLE part_date RENAME TO part_date_old;
Şimdi de ana tablonun kopyasının ismini değiştirelim.
ALTER TABLE part_date_ RENAME TO part_date;
Aşağıda sizle paylaştığım script tabloda bulunan timestamp alanları haftalık böler ve partition tablolarını oluşturmak için gereken create scriptini bizim için oluşturacak. Scriptten sql tümcesi üretilirken en dışta kullandığımız tablonun ana tablo olduğu gözden kaçırılmamalıdır.
SELECT MIN(i), MAX(i), EXTRACT(WEEK FROM i),
'CREATE TABLE public.part_date_'
|| EXTRACT(YEAR FROM i)
|| '_' || EXTRACT(MONTH FROM i)
|| '_p' || EXTRACT(WEEK FROM i)
|| ' PARTITION OF public.part_date_(i) FOR VALUES FROM ('''
|| MIN(i)::DATE
|| E'\') TO (\''
|| MAX(i)::DATE
|| E'\');'
FROM part_date
GROUP BY EXTRACT(year FROM i), EXTRACT(month FROM i), EXTRACT(WEEK FROM i)
LIMIT 10;
Sevgiler,
Comments
Post a Comment