PostgreSQL 10 partition Declerative Partition

   Merhabalar, PostgreSQL 10 'da Partition yapısı her versiyonunda değişiklik gösterdiği gibi diğer platformlar Oracle, MsSql gibi VeriTabanlarından da oldukça farklılık gösterir.

   Postgres 9.x versiyonlarında Tablonun partitionu bir trigger yardımı ile yapılmakta ve fiziksel olarak child tablolar yaratılmakta.

   Postgres 10 versiyonu ile önceki versiyonlarda Tabloları partition ve child tabloları adreslemek için kullanılan bu trigger yapısından kurtulmuş oluyoruz. (Kurtuluş tabi nereden baktığınıza bağlı :) )

   Declerative Partition yapısı ile yine fiziksel child tabloları manuel yaratıp Indexlerini de Local olarak bu fiziksel tablolara yaratıyoruz.

   Aşağıda bir örnekleme yaptım. Bu örnekte bir Log Tablosu ve günlere göre Range Partition yapısı mevcut.

CREATE TABLE Table_Log (

    id INTEGER NOT NULL,

    operation_date  timestamp without time zone NOT NULL,

    response_time integer

)PARTITION BY RANGE (operation_date)

TABLESPACE pg_default;

Child tabloları oluşturuyoruz.. Oracle ve MSSQL den tamamen farklı bir mantık.

CREATE TABLE Table_Log_20180131 PARTITION OF Table_Log FOR VALUES FROM ('31-JAN-18') TO ('01-FEB-18');

CREATE TABLE Table_Log_20180201 PARTITION OF Table_Log FOR VALUES FROM ('01-FEB-18') TO ('02-FEB-18');

CREATE TABLE Table_Log_20180202 PARTITION OF Table_Log FOR VALUES FROM ('02-FEB-18') TO ('03-FEB-18');

CREATE TABLE Table_Log_20180203 PARTITION OF Table_Log FOR VALUES FROM ('03-FEB-18') TO ('04-FEB-18');

Şimdi bu tablolara birer index tanımı yapalım.

CREATE TABLE Table_Log_20180131 PARTITION OF Table_Log FOR VALUES FROM ('31-JAN-18') TO ('01-FEB-18');

CREATE TABLE Table_Log_20180201 PARTITION OF Table_Log FOR VALUES FROM ('01-FEB-18') TO ('02-FEB-18');

CREATE TABLE Table_Log_20180202 PARTITION OF Table_Log FOR VALUES FROM ('02-FEB-18') TO ('03-FEB-18');

CREATE TABLE Table_Log_20180203 PARTITION OF Table_Log FOR VALUES FROM ('03-FEB-18') TO ('04-FEB-18');

Evet Range partition tablomuz basic olarak hazır. Bunun yanında List partitionda kullanabilirsiniz. Pk kullanmak isterseniz bunu child tabloları yaratırken oluşturmanız gerekiyor.

CREATE TABLE Table_Log_20180131 PARTITION OF Table_Log (operation_date, PRIMARY KEY (id)) FOR VALUES FROM ('31-JAN-18') TO ('01-FEB-18');

Aynı şekilde konstraint gerekiyorsa bunu child tablo üzerinden yapabilir siniz.

ALTER TABLE table_log_20180202 ADD CONSTRAINT check_date CHECK (operation_date >= '02-FEB-18' AND operation_date < '03-FEB-18');

Yine partition kısmını dettach-attach edebiliriz.

ALTER TABLE Table_Log DETACH PARTITION Table_Log_20180131;

ALTER TABLE Table_Log ATTACH PARTITION Table_Log_20180131 FOR VALUES FROM ('31-JAN-18') TO ('01-FEB-18');

Özetlemek gerekiyorsa bahsettiğimiz gibi Partition tabloları & Indexleri manuel yaratmamız gerekiyor. Yeni bir partition eklemek için ayrı bir rutin yazmanız yada takviminize eklemeniz gerekiyor.

Ana tablo üzerinde Primary Key ve Unique Key MASTER Tablo üzerinden olmuyor bunu child tablolardan yapmanız gerekli.

Partition tabloları şu şekilde listeyebilir siniz.

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='table_log';

parent_schema | parent | child_schema | child

---------------+-----------+--------------+--------------------

public | table_log | public | table_log_20180131

public | table_log | public | table_log_20180201

public | table_log | public | table_log_20180202

public | table_log | public | table_log_20180203

(4 rows)

postgres=#

ORA-14099 all rows in table do not qualify for specified partition

Mevcut hata partiton switch işlemi sırasında alınmıştır. 
Yapılması gereken switch yapılacak tabloyu kontrol etmek olmalı.
PUSULA_ARCHIVE tablosunun satırlarının geçerli olduğundan emin olmalısınız. Tablosunun kolonlarının invalid, eksik yada tablonun boş olduğunu kontrol etmelisiniz. Yada tabloyu VALIDATE etmek için, ANALYSE edebilirsiniz.

Alter table pusula exchange partition part_01 with table pusula_archive;

ORA-14099: all rows in table do not qualify for specified partition

Oracle Var olan Tabloyu PartitionYapmak (Partitioning an Existing Table)

Selamlar bu yazımızda ORACLE Veri tabanında dolu bir tabloyu nasıl partition şekle getirebiliriz bunu inceleyelim.
Örneğin fazla büyümekte olan bir tablomuz var.Bu taployu arşivlemek hatta aylık yada yıllık partitionlara bölerek performans artışı sağlamak isteyebiliriz.Oracle de içinde kayıt olan tablolar için bunu yapmak, oldukça zahmetli ve risk içeren bir işlemdir.
Şimdi bunu örnek ile nasıl yapabileceğimizi tartışalım.
Yeni bir tablo yaratıp Index yaratalım ve bu tabloya yeni kayıtlar girelim.

CREATE TABLE PUSULA (
id            NUMBER(10),
created_date  NUMBER(10), 
named         VARCHAR2(50)
)TABLESPACE DATA_LARGE_TBS_01;
--Indexler
CREATE INDEX IX_PUSULA_01 ON PUSULA (ID) TABLESPACE DATA_LARGE_TBS_01;
CREATE INDEX IX_PUSULA_02 ON PUSULA (CREATED_DATE)TABLESPACE DATA_LARGE_TBS_01;
--Kayıtlar
INSERT INTO PUSULA VALUES (1, 20111001, 'ilker');
INSERT INTO PUSULA VALUES (2, 20111101, 'ilkerusta');
INSERT INTO PUSULA VALUES (3, 20111201, 'ustailker');
COMMIT;

Tablomuza bir göz atıyoruz
select * from PUSULA;

Tabi herzaman kayıtlarımız bu kadar az olmıyacak :) şimdi partition yapıda yeni bir tablo yaratalım.

Burada dikkat edilmesi gereken partition başlangıç zamanı, hangi alanın kullanılacağı ve hangi table space üzerinde create edileceği.
CREATE TABLE PUSULA2 (
id            NUMBER(10),
created_date  NUMBER(10),
named         VARCHAR2(50)
)
TABLESPACE DATA_LARGE_TBS_01
PARTITION BY RANGE (created_date)
INTERVAL(100) STORE IN (DATA_LARGE_TBS_01)
(PARTITION p0 VALUES LESS THAN (20111200) TABLESPACE DATA_LARGE_TBS_01);

ALTER TABLE PUSULA2 SET STORE IN (DATA_LARGE_TBS_01);

ALTER TABLE PUSULA2 enable row movement;

NOT: Ben 20111200 tarihininden başlattım.  LESS THAN opsiyonu ile bu tarih öncesini P0 partition içine alacak.

ENABLE ROW MOVEMENT Enable ediliyor. Çünkü partitionlar yer değiştiriyor. Ne kadar sevilen bir hareket olmasada J (i/o vs. gibi sorunlar için bu tamamen ayrı bir konu) partition key update olabilmesi için yapıyoruz.

Indexler yaratılsın. Tavsiyem isimleri rename edilecek tablo ile farklı olması. Tabiki bu Indexleri kullanan sql hint cümlecikleri yok ise.
CREATE INDEX IX_PUSULA2_01 on PUSULA2 (ID) local;
CREATE INDEX IX_PUSULA2_02 on PUSULA2 (CREATED_DATE) local;

Not: Indexler Local yaratılıyor bu da demek olur ki her partition için local bir index olacak ve exchange esnasında Unusable olmasınlar.

SELECT partitioned
FROM dba_indexes
WHERE table_name = 'PUSULA2'

Yapımızı hazırladık. Şimdi tablomuzu yeni yapıya geçirelim. Bunun için mevcut tablo içerisindeki kayıtları yeni yaratılan tablomuzun ilk partitiona exchange edip daha sonra rename işlemini yapalım.

Not: Öncesinde bu tablolara depent eden tüm objelerin tespit edilip exchange ve rename sonrası compile edilmesi gerekiyor.

--Exchange işlemi
ALTER TABLE PUSULA2 EXCHANGE PARTITION p0 WITH TABLE PUSULA WITHOUT VALIDATION;
--Yeni tablo ile rename işlemi

ALTER TABLE PUSULA RENAME TO PUSULA_OLD;

ALTER TABLE PUSULA2 RENAME TO PUSULA;
--Indexler rebuild ediliyor.
ALTER INDEX IX_PUSULA2_01 rebuild partition p0 online;
ALTER INDEX IX_PUSULA2_02 rebuild partition p0 online;

Tablomuz rename edildi ve exchange yapıldı. Kontrol edelim
select * from PUSULA;

Güzel !! Şimdi tablomuz partition oldu ve eski kayıtlarımız hala, aynı isimdeki yeni yapıdaki tablomuzda.

Son olarak yeni yapıdaki partition tabloya yeni tarihli kayıtların geldiğinin ve partition dağılımı nasıl olucak bunların testini yapalım.
insert into PUSULA values (4,20111201,'ilker');
insert into PUSULA values (5,20120101,'ilkerusta');
insert into PUSULA values (6,20120201,'ustailker');
commit;

Dağılımı görmek için.
SELECT table_name, partition_name, tablespace_name
FROM dba_tab_partitions
WHERE table_name = 'PUSULA';

Şimdilik kolay gelsin :)

ORA-14039: partitioning columns must form a subset of key columns of a UNIQUE index

Mevcut hata partition bir tabloya local / partition bir index yaratılmak istenirken alınan hatadır.

create unique index USTA.MUHASEBE_IX01 on USTA.TABLE_MUHASEBE (log_guid)
  tablespace LARGE_TBS_01 local;

ORA-14039: partitioning columns must form a subset of key columns of a UNIQUE index

Partition index non-Unique olmak zorundadır. Doğru şekli:

create index USTA.MUHASEBE_IX01 on USTA.TABLE_MUHASEBE (log_guid)
  tablespace LARGE_TBS_01 local;

 

Dolu tablonun partitioning yapılması

   Merhaba , Table Partitioning yapısını daha önce anlatmıştım. Peki ya Dolu olan tablomu nasıl partition yapıya geçirebilirim ?
    Bu hangi koşullarda karşımıza çıkar derseniz örneğin milyonlarca kayıt olan bir tabloyu arşivlemem gerekiyor, ve belirli tarihten öncesini silmek zorundayım. Bu oldukça sıkıntılı bir iştir.Tablo'da lock olma ihtimalinden, çok fazla io yapmaya kadar uzanan bir çile..
   Eğer benim partition bir tablom olsaydı silinmek istenen tarih aralığını arşivdeki tablo'ya switch edip bu dertden kurtulabilirdim.
   Kısa bir örnek ile neler yapabildiğimize bakalım. Öncelikle yeni bir tablo yaratıyorum.

Create Table members

( id int,

  name char(20),

  surname char(20),

  startDate smalldatetime

)

Boş olan tabloya biraz data girelim.

Declare @x int

Declare @id int

  set @x=0

  set @id=1

 while @x < 100

 begin

  insert into members values (@id,'ilker','usta','20110101')

  set @x=@x+1

  set @id=@id+1

 end

Bakalım ne kadar yerimiz oldu

sp_spaceused 'members'

Şimdi tablomuzun özelliğine bakalım

   Gördüğümüz gibi tablo PRIMARY yani default file gurup üzerinde yaratıldı. Şimdi gelelim asıl sorunumuza. Tablomuzda herhangi bir index yok. (olsada birşey değişmez) tabloya partition scheme üzerinde yeni bir index yaratırsak tablo primary den çıkıp partition yapıya geçmiş olur.

   Not : Kullanılan Partition scheme ve function daha önce yaratılmış type olarak smalldatetime dır. (startDate columundan partitions yapıyoruz.)

CREATE CLUSTERED INDEX [CIX] ON [dbo].[Members]

(

      [id] ASC

)WITH (SORT_IN_TEMPDB = ON, ONLINE = ON)

ON [PS_MONTHLY_RANGE]([startDate])

GO

Şimdi tablomuzun özelliğine yeniden göz atalım

   Gördük ki tablomuz PS_MONTHLY_RANGE üzerine geçti. Artık tablomuza gelecek yeni kayıtlar startDate üzerinden aylık olarak faklı partitionlar da olucaktır......     Kolay gelsin :) 

Table partitioning

   Partitioning table yapısı benim kullanmasını en çok sevdiğim ve faydasına çok inandığım bir özelliktir. MS Sql server 2005 versiyonu ile gelen bu özellik pek çok yapıda kullanılabilir. 

   MS SQL Server Table Partitioning yapısı isminden de anlaşılacağı üzere tabloyu fiziksel olarak parçalamaktır. Buda hem io kullanımı hem disk kapasitesi ,tabloya gelen sorgu performansı bakımından çok önemli faydalar sağlamaktadır. Tablonun bu şekilde belirli tarih yada sınıf bakımından arşivlenmesi oldukça kolaylaştırır.

   Örneğin tablomuza tarihe göre bir sorgu geldiğini düşünelim ve tablomuzun yapısı tarihlere göre partition yapıda,  Sql server sorguyu uygun partitiona götürür ve sorgu bu fiziksel dosyada arar bulur. Buda sorgu performansını ciddi boyutlarda arttırmış olur.

   Şimdi gelin birlikte ufak bir uygulama yaratalım.

   Öncelikle partition function yaratalım ,ben ay ay partition kullanıcam ,bu yıl yıl yada belirlenen sınıflara göre de ayrılmış olabilir.

CREATE PARTITION FUNCTION [PF_MONTHLY_RANGE](smalldatetime) AS RANGE RIGHT FOR VALUES (N'2011-01-01T00:00:00', N'2011-02-01T00:00:00', N'2011-03-01T00:00:00', N'2011-04-01T00:00:00', N'2011-05-01T00:00:00')

  Burda dikkat edilmesi gereken functionun kullanacağı column, Tarih columnu kullanacağımıza göre type smalldatetime verilmelidir. Şimdi sıra functionu kullanacak scheme yı yaratmakda. 

  Tabi bunun öncesinde her ay için yeni bir file gurup yaratıp bunlar için ayrı ayrı data file yaratmak gerekir , yada siz kendi yapınıza göre hepsini primary file gurupta yada herhangi bir file gurupta yaratabilirsiniz.

   Şimdi scheme yaratalım.

CREATE PARTITION SCHEME [PS_MONTHLY_RANGE] AS PARTITION [PF_MONTHLY_RANGE] TO([filegr], [filegr1], [filegr2], [filegr3], [filegr4], [filegr5])

   Dikkat ederseniz function da 2011 ocak dan başladık ve 5 partition numarası vardı. Scheme de ise 6 tane file gurup var, bu şu demek oluyor 2011 ocaktan küçük tarihler filegr dosyasına yazılır.

   Şimdi scheme üzerinde tablomuzu yaratalım.

CREATE TABLE CUS (id int, Tarih smalldatetime ) on PS_MONTHLY_RANGE(Tarih);

   Tablomuzu partition olarak yaratmış bulunuyoruz. Bu tabloya gelecek kayıtlar artık ay bazında farklı fiziksel dosyalara kayıt edilecekler.

   Hangi partition numarasında ne kadar kayıt olduğunu şu sorgudan görebilirsiniz.

select t.name,p.object_id,p.partition_id,p.index_id,p.partition_number,p.rows from sys.partitions p join sys.tables t

on p.object_id=t.object_id WHERE p.partition_number <> 1

   Table partitioning konusunda şimdilik anlatılacaklar bu kadar kolay gelsin :)