PostgreSQL, veritabanı partitioning modelini değiştirmek için, 10. sürümde beyan edici partitioning sistemini tanıttı. Sorgu planlayıcısı, sorgu öngörülerini partition sınırları ile karşılaştırarak planlama aşamasında statik partition temizliği gerçekleştirir, ancak hangi partitionların ortadan kaldırılabileceğini belirlemek için ifadelerin plan zamanında sabit değerlere değerlendirilebilir olmasını gerektirir.
Temel mimari kısıtlama, now() veya current_timestamp gibi STABLE fonksiyonların planlama sırasında değerlendirilmemesidir, çünkü sonuçları planlama ve yürütme veya hatta sorgu yürütmesi sırasında farklı olabilir. Bu nedenle, planlayıcı, bu fonksiyonlarla ilgili öngörüleri siyah kutular olarak değerlendirdiğinden, belirli partitionların eşleşen satırları içermediğini kanıtlayamaz ve bu da tüm partitionların taranmasına neden olur.
Çözüm, öngörüyü IMMUTABLE fonksiyonlar veya literal sabitler kullanacak şekilde yeniden yazmayı veya PostgreSQL 11 ve sonrasında mevcut olan çalışma zamanı partition temizliğine güvenmeyi içerir. enable_partition_pruning ayarını açık yaparak, yürütücü, yürütme zamanında STABLE fonksiyon sonuçlarını partition sınırlarıyla karşılaştırır ve başlangıç planlama aşamasından sonra ilgisiz partitionları dinamik olarak atlar.
Bir finansal analitik şirketi, ticaret tablosunu günlük aralıklarla TIMESTAMPTZ üzerinden execution_time kolonuna göre partitionlamıştı. Analistler sık sık son etkinlikleri WHERE execution_time >= now() - interval '1 hour' ile sorgulamış ancak bu sorguların korkunç performans düşüklüğü yaşadığını görmekteydi, çünkü tüm 365 günlük partitionı taramak zorunda kalıyorlardı, sadece en son olanı değil.
İlk yaklaşım, uygulama katmanının zaman damgası sınırını hesaplayıp bunu literal bir sabit olarak enjekte etmesi üzerineydi. Bu, hemen statik temizliği sağladı ve sorgu süresini 45 saniyeden 80 milisaniyeye düşürdü. Ancak, bu, mevcut SQL sorgularını değiştirilmesi zor olan üçüncü taraf BI araçları içinde bozan bir değişiklikti.
İkinci yaklaşım, güncel tarihe göre sabit bir zaman damgası döndüren özel bir değişmez fonksiyon oluşturmaktı. Ancak bu, veritabanı işlemi gece yarısı sınırında açık kaldığı takdirde yanlış sonuçlar üreteceği için reddedildi; bu da işlemler içerisinde STABLE sözleşmesini ihlal etmek anlamına geliyordu. Böyle bir ihlal, planlayıcının güncel olmayan bir partition aralığını önbelleğe alması durumunda, sessizce yanlış sorgu sonuçlarına yol açabilirdi.
Seçilen çözüm, PostgreSQL 12'nin çalışma zamanı partition temizleme yeteneklerini kullanmak oldu. Veritabanı yöneticileri, enable_partition_pruning ayarını etkinleştirdi ve uygulamayı SQL metni oluşturma değişikliği gerektirmeden, zaman sınırını parametre olarak geçecek şekilde hazırlıklı ifadeler kullanacak şekilde yapılandırdı. Bu, yürütücünün gerçek parametre değerini yürütme zamanında kullanarak partitionları dinamik olarak temizlemesine olanak tanıdı ve eski raporlama araçlarında SQL metni oluşturma süreçlerine değişiklik gerektirmeden neredeyse optimal performansa ulaşıldı.
PostgreSQL'nin miras alınan tablolar için kısıtlama dışlama mekanizması ile yerel partition temizliği arasında ne fark var ve neden ilki açık GUC yapılandırması gerektirir, ikincisi ise gerektirmez?**
Kısıtlama dışlama, beyan edici partitionlamadan önce kullanılan partitioning yöntemiydi ve ilgili satırların bulunmadığını kanıtlamak için çocuk tablolara CHECK kısıtlamalarına dayanıyordu. Yüzlerce tablo olduğunda bu kısıtlamaların her miras alınan tabloya karşı değerlendirilmesi pahalı olduğu için kontrol edilme işlemi constraint_exclusion parametresi ile kontrol altına alınmıştır; bu parametre varsayılan olarak partition olarak belirlenmiş (yalnızca miras alarak sorgulama yapıldığında kontrol eder). Yerel partition temizliği, planlayıcıda partition hiyerarşisini doğrudan anlayan özel veri yapıları kullanır, bu da daha hızlıdır ve daima etkin durumdadır; doğru işlevsellik için herhangi bir GUC ayarı gerektirmez.
Bir satırı partitionlar arasında partition anahtarını değiştirerek taşımak üzere güncellerken, neden PostgreSQL içsel olarak bir DELETE ve INSERT işlemi gerçekleştirir; bunun tetikleyici durumları üzerindeki etkisi nedir?
Her partition ayrı bir yığın ilişkisi olup farklı fiziksel depolama alanlarına sahiptir, bu nedenle partition anahtarını değiştirmek, bir dosyadan diğerine tuple taşımayı gerektirir. PostgreSQL, bu geçişi kaynak partitiondan satırı silerek ve hedef partitiona ekleyerek gerçekleştirir. Bu, satır düzeyinde BEFORE DELETE, AFTER DELETE, BEFORE INSERT ve AFTER INSERT tetikleyicilerinin tek bir UPDATE işlemi gibi görünen süreçte tetiklenmesi anlamına gelir. Ayrıca, mantıksal replikasyon, bunu iki ayrı WAL girişi (silme ve ekleme) olarak akıtır; bu da eğer replikanın kimliği doğru yapılandırılmamışsa abone üzerinde çatışmalara yol açabilir.
Yeni bir partitionı bir DEFAULT partitiona sahip bir tabloya iliştirirken hangi özel kilitleme ve doğrulama yükü oluşur ve birinin mevcut DEFAULT partitionın tam taramasından nasıl kaçınılabilir?
DEFAULT partitiona sahip, aralık veya liste bazlı bir partitionlanmış tabloya yeni bir partition eklenirken, PostgreSQL yeni, daha spesifik partitiona ait olması gereken satırlar olmadığına dair doğrulama yapmak için tüm DEFAULT partitionı taramak zorundadır. Bu doğrulama taraması, partitionlanmış tablo üzerinde ACCESS EXCLUSIVE kilidi alır ve büyük varsayılan partitionlar için saatler alabilir. Bunu önlemek için, yeni partitionı eklemeden önce DEFAULT partitionı ayırmak, ardından çelişkili satırların olmadığından emin olduktan sonra DEFAULT partitionı yeniden eklemek gereklidir; ya da alternatif olarak, yeni boş bir partition oluşturmak için CREATE TABLE ... PARTITION OF kullanarak ve belirli partition aralığını filtreleyen bir WHERE ifadesi ile INSERT ... SELECT kullanarak veriyi aktarıp, varsayılan partitionın içeriklerinin doğrulama taramasını atlamak mümkündür.