PostgreSQL 11 Yenilikleri 2 - Alter Table Add Column
Merhaba,
Bir tabloya yeni bir sütun nasıl eklenir? Bir sütunun eklenmesi ne demek ve nasıl olur? Bahsedeceğim yeniliği anlatmadan önce bunu anlatmak isterim.
Bir tabloya default değeri null olmayan yeni bir sütun eklenmesi o tablonun baştan yazılması anlamına gelir. Tüm tabloyu yeniden yazmak demek tablonun locklanması anlamına da gelir. Büyük tablolar için maliyetli bir işlemdir. Şöyle düşünün, diyelim ki 10 sütunlu bir tablonuz var ve default değeriyle birlikte yeni bir sütun daha ekleyeceğiz. Bunun anlamı o tabloya eklenen yeni sütunun tüm değerlerinin add column ile belirlediğimiz default değerine eşit olması gerektiğidir. Yani o tablonun mevcut verileriyle ve yeni eklediğiniz sütunun default değeriyle birlikte yeniden oluşturulması anlamına gelir.
Bu derece maliyetli bir işlemin önüne geçmek için mevcut pg_attribute tablosuna iki yeni sütun daha eklendi; sütunun default değerini saklamak için attmissingval sütunu ve sonradan eklenen sütun olup olmadığını gösteren sütun atthasmissing.
pg_attribute tablosuna eklenen attmissingval sütunu, tabloya NULL dışında bir default değer ile eklenen yeni sütunun değerini tutar. Default bir değerle birlikte tabloya yeni bir sütun eklenmesi ve mevcut sütunun veri tipinin değiştirilmesi tüm tabloyu ve onun indexini yeniden yazmasına sebep olur. PostgreSQL 11 ‘de tabloya yeni bir sütun eklediğinizde aslında o değer pg_attribute tablosuna yazılır. Verinin kendisi asıl tabloda değildir. Asıl veri pg_attribute tablosunda saklanır. Böylece tablonun yeniden yazılması önlenir.
Özetle tabloya sütun eklenmesi ve silinmesi tüm tablonun ve üzerindeki indexlerin yeniden yazılması anlamına gelir. Tablonun yeniden yazılması geçici olarak daha fazla disk alanına gereksinim duyacaktır.
Tabloya sütun ekleneceği zaman default değerinin attibute tablosuna yazması, tüm tabloyu indexleri ile birlikte yeniden yazmasını önleyecek. Tablonun yazılması sürecinde yaşanacak lock’ın önüne geçilecek ama bu durumda locklanma durumunun tamamen kalkmayacağı unutulmamalı.
Sonuç olarak pg_attribute tablosuna eklenen bu yeni iki sütun ilei tablonun luzun süreli locklanmasını önleyecek, alter table add column scripti daha hızlı bitecek ve tablo ve üzerindeki indexlerin yeniden diske yazılmasını önleyecek.
Tablonun yeniden yazılması tablo üzerinde tüm verilerin okunması anlamına da gelir aynı zamanda. Tüm tablo üzerinde okuma yapıldığında sequential scan değeri istatistik tablolarından biri olan pg_stat_user_tables tablosundaki seq_scan sütunundaki değeri bir arttırır. Seq_scan, update ve delete işlemlerinde de bir artar. Çünkü bu sırada tüm tabloyu okuması gerekir.
ALTER TABLE ADD COLUMN yapıldığında PostgreSQL 10 ve PostgreSQL 11’i karşılaştıralım;
PostgreSQL 10 ile
PostgreSQL 11 ile
Default değeri verilen bir sütunun tabloya eklenmesi PostgreSQL 10 da tablonun oluşturulması için geçen sürenin iki katından fazla sürdü. Bu süre tabi ki değişken. Bu kadar uzun sürmesinin sebebi daha önceden de bahsettiğim gibi tabloyu okuması, tabloyu ve üzerindeki index’leri yeniden yazmaya çalışması.
PostgreSQL 11 de ise tabloya default değeri olan bir sütunun eklenmesi işlemi sadece milisaniyeleri aldı. Bu süre de değişken tabi. Tüm tabloyu okumak ve eklenen sütunun default değeriyle birlikte tabloyu yeni baştan yazmak yerine tabloya eklenen yeni sütunun değerini pg_attribute tablosundaki attmissingval sütunundan okur. Tabloya select attığımızda gördüğümüz default değerini pg_attribute tablosundan çeker.
Her iki versiyondaki farklılığı karşılaştırmak için istatistik tablosu olan pg_stat_user_tables ALTER TABLE scriptini çalıştırmadan önceki seq_scan sütun değerlerine bakıp karşılaştırabilirsiniz. PostgreSQL 10 da ALTER scripti sonrası seq_scan bir değer artarken, PostgreSQL 11 de seq_scan değeri değişmeyecektir. Yine aynı şekilde her iki PostgreSQL versiyonunda tabloların boyutlarını karşılaştırıp aradaki farkı görebilirsiniz.
PostgreSQL 10 da oluşturduğum tablonun boyutu;
PostgreSQL 11 de oluşturduğum tablonun boyutu;
Aradaki farkın sebebi en başından bahsettiğim gibi veriyi pg_attribute tablosundan okumasıdır.
Eklenen sütun UPDATE edildiğinde yeni veriler artık tabloya yazılır. UPDATE olan sütunlara ait veriler o tablodan gelirken, sütun eklendikten sonra güncellenmemiş veriler pg_attribute tablosundan gelir.
PostgreSQL 11 üzerindeki örnek için default değeri ‘not-null’ olarak vermiştim. g sütununu ‘not-null’ olarak güncelleyip (tabiki vacuum full yaptıktan sonra) tablo boyutuna baktığımızda PostgreSQL 10 daki boyutla aynı olduğunu göreceksiniz.
Sevgiler,
Bir tabloya yeni bir sütun nasıl eklenir? Bir sütunun eklenmesi ne demek ve nasıl olur? Bahsedeceğim yeniliği anlatmadan önce bunu anlatmak isterim.
Bir tabloya default değeri null olmayan yeni bir sütun eklenmesi o tablonun baştan yazılması anlamına gelir. Tüm tabloyu yeniden yazmak demek tablonun locklanması anlamına da gelir. Büyük tablolar için maliyetli bir işlemdir. Şöyle düşünün, diyelim ki 10 sütunlu bir tablonuz var ve default değeriyle birlikte yeni bir sütun daha ekleyeceğiz. Bunun anlamı o tabloya eklenen yeni sütunun tüm değerlerinin add column ile belirlediğimiz default değerine eşit olması gerektiğidir. Yani o tablonun mevcut verileriyle ve yeni eklediğiniz sütunun default değeriyle birlikte yeniden oluşturulması anlamına gelir.
Bu derece maliyetli bir işlemin önüne geçmek için mevcut pg_attribute tablosuna iki yeni sütun daha eklendi; sütunun default değerini saklamak için attmissingval sütunu ve sonradan eklenen sütun olup olmadığını gösteren sütun atthasmissing.
pg_attribute tablosuna eklenen attmissingval sütunu, tabloya NULL dışında bir default değer ile eklenen yeni sütunun değerini tutar. Default bir değerle birlikte tabloya yeni bir sütun eklenmesi ve mevcut sütunun veri tipinin değiştirilmesi tüm tabloyu ve onun indexini yeniden yazmasına sebep olur. PostgreSQL 11 ‘de tabloya yeni bir sütun eklediğinizde aslında o değer pg_attribute tablosuna yazılır. Verinin kendisi asıl tabloda değildir. Asıl veri pg_attribute tablosunda saklanır. Böylece tablonun yeniden yazılması önlenir.
Özetle tabloya sütun eklenmesi ve silinmesi tüm tablonun ve üzerindeki indexlerin yeniden yazılması anlamına gelir. Tablonun yeniden yazılması geçici olarak daha fazla disk alanına gereksinim duyacaktır.
Tabloya sütun ekleneceği zaman default değerinin attibute tablosuna yazması, tüm tabloyu indexleri ile birlikte yeniden yazmasını önleyecek. Tablonun yazılması sürecinde yaşanacak lock’ın önüne geçilecek ama bu durumda locklanma durumunun tamamen kalkmayacağı unutulmamalı.
Sonuç olarak pg_attribute tablosuna eklenen bu yeni iki sütun ilei tablonun luzun süreli locklanmasını önleyecek, alter table add column scripti daha hızlı bitecek ve tablo ve üzerindeki indexlerin yeniden diske yazılmasını önleyecek.
Tablonun yeniden yazılması tablo üzerinde tüm verilerin okunması anlamına da gelir aynı zamanda. Tüm tablo üzerinde okuma yapıldığında sequential scan değeri istatistik tablolarından biri olan pg_stat_user_tables tablosundaki seq_scan sütunundaki değeri bir arttırır. Seq_scan, update ve delete işlemlerinde de bir artar. Çünkü bu sırada tüm tabloyu okuması gerekir.
ALTER TABLE ADD COLUMN yapıldığında PostgreSQL 10 ve PostgreSQL 11’i karşılaştıralım;
PostgreSQL 10 ile
PostgreSQL 11 ile
Default değeri verilen bir sütunun tabloya eklenmesi PostgreSQL 10 da tablonun oluşturulması için geçen sürenin iki katından fazla sürdü. Bu süre tabi ki değişken. Bu kadar uzun sürmesinin sebebi daha önceden de bahsettiğim gibi tabloyu okuması, tabloyu ve üzerindeki index’leri yeniden yazmaya çalışması.
PostgreSQL 11 de ise tabloya default değeri olan bir sütunun eklenmesi işlemi sadece milisaniyeleri aldı. Bu süre de değişken tabi. Tüm tabloyu okumak ve eklenen sütunun default değeriyle birlikte tabloyu yeni baştan yazmak yerine tabloya eklenen yeni sütunun değerini pg_attribute tablosundaki attmissingval sütunundan okur. Tabloya select attığımızda gördüğümüz default değerini pg_attribute tablosundan çeker.
Her iki versiyondaki farklılığı karşılaştırmak için istatistik tablosu olan pg_stat_user_tables ALTER TABLE scriptini çalıştırmadan önceki seq_scan sütun değerlerine bakıp karşılaştırabilirsiniz. PostgreSQL 10 da ALTER scripti sonrası seq_scan bir değer artarken, PostgreSQL 11 de seq_scan değeri değişmeyecektir. Yine aynı şekilde her iki PostgreSQL versiyonunda tabloların boyutlarını karşılaştırıp aradaki farkı görebilirsiniz.
PostgreSQL 10 da oluşturduğum tablonun boyutu;
PostgreSQL 11 de oluşturduğum tablonun boyutu;
Aradaki farkın sebebi en başından bahsettiğim gibi veriyi pg_attribute tablosundan okumasıdır.
Eklenen sütun UPDATE edildiğinde yeni veriler artık tabloya yazılır. UPDATE olan sütunlara ait veriler o tablodan gelirken, sütun eklendikten sonra güncellenmemiş veriler pg_attribute tablosundan gelir.
PostgreSQL 11 üzerindeki örnek için default değeri ‘not-null’ olarak vermiştim. g sütununu ‘not-null’ olarak güncelleyip (tabiki vacuum full yaptıktan sonra) tablo boyutuna baktığımızda PostgreSQL 10 daki boyutla aynı olduğunu göreceksiniz.
Sevgiler,
Comments
Post a Comment