PostgreSQL, çerçeve sınırlarını mevcut satırın sıralama sütunundan mantıksal değer kaymaları değerlendirerek uygulamaktadır. Çerçeve sınırları bir interval türünü (örneğin, INTERVAL '1 saat' ÖNCE) içerdiğinde, yürütücü fiziksel satır sayıları ile çerçeve üyeliğini belirleyemez çünkü o zaman diliminde yer alan satır sayısı veri kümesi genelinde dinamik olarak değişir. Doğruluğu sağlamak için motor, tam sıralı partiği bir iş tablosuna (ya work_mem içinde ya da diske dökülecek şekilde) malzeme etmekte, bunun sonucunda her mevcut satıra göre belirtilen aralıkta hangi değerlerin yer aldığını belirlemek için tüm satırları taramakta, bu da O(parti boyutu) bellek karmaşıklığına yol açmaktadır.
ROWS çerçevesini sadece ORDER BY ifadesinin partisyon içindeki her satır için benzersiz bir anahtar oluşturması durumunda güvenle değiştirebilirsiniz. Sıralama sütunu tekrar eden değerler içermiyorsa (veya birincil anahtar gibi bir ikincil benzersiz sütun ile genişletilmişse), fiziksel satır kayması (ROWS), mantıksal değer kayması (RANGE) ile anlamsal olarak özdeş hale gelir. Bu benzersizlik garantisi, çerçevenin kesinlikle amaçlanan satırları içerdiğini ve motorun değer eşleşen akranları taramasını gerektirmediğini garanti ederek, sabit boyutlu bir halka tamponu kullanarak bir akış yürütme modeli sağlamaktadır ve bellek kullanımını O(çerçeve boyutu) ile sınırlamaktadır.
Yüksek frekanslı bir ticaret platformu, bir nanoteknik hassasiyetle piyasa tık verilerini işleyerek, önceki 50 milisaniye içinde teklif-teklif spread'leri için hareketli bir ortalama gerektirmiştir. İlk analitik sorgu AVG(spread) OVER (PARTITION BY symbol ORDER BY nanos_ts RANGE BETWEEN INTERVAL '50 ms' PRECEDING AND CURRENT ROW) biçimindeydi. Piyasa oynaklığında bu, work_mem tükenmesine yol açtı, bu da PostgreSQL'in iş tablolarını diske dökmesine ve sorgu gecikmesinin milisaniyelerden onlara kadar çıkmasına neden oldu, bu da gerçek zamanlı algoritmik ticaret için kabul edilemezdi.
Mühendislik ekibi, ilk olarak veritabanı sunucularını dikey olarak ölçeklendirmeyi, en büyük partileri (yüksek hacimli semboller) tamamen bellekte tutacak yeterli RAM sağlayarak gerçekleştirmeyi düşündü. Bu disk dökme sorununu ortadan kaldıracak olsa da, maliyet inanılmaz derecede yüksekti; en büyük semboller yüz milyonlarca tık içeriyordu, her veritabanı bağlantısı için terabaytlarca RAM gerektiriyordu ve çözüm, binlerce eşzamanlı ticaret algoritmasında yatay ölçeklenmiyordu.
İkinci bir öneri, 50 milisaniyelik pencereyi, ortalama tık yoğunluğundan hesaplanan sabit bir ROWS kayması kullanarak yaklaşık olarak belirlemekti (örneğin, 1000 satırın 50ms'e eşit olduğunu varsayarak). Bu yaklaşım, parti boyutundan bağımsız olarak sabit bellek kullanımını garanti edecekti. Ancak, tık yoğunluğu piyasa çöküşü sırasında (milisaniyede binlerce tık) ile sakin dönemler (tıklar arasında dakikalar) arasında oldukça dalgalanarak, satır sayısındaki yaklaşık tahmini keyfi olarak hatalı hale getirdi ve denetleme izleri için kesin zaman pencere hesaplamalarını gerektiren finansal düzenlemeleri ihlal etme riski oluşturdu.
Seçilen çözüm, nanos_ts ile tick_id'nin birleşik bir benzersiz anahtar oluşturduğunu fark etti. Ekip sorguyu ORDER BY nanos_ts, tick_id şeklinde yeniden düzenledi ve ROWS BETWEEN 1000 PRECEDING AND CURRENT ROW'ya geçti. Tarih damgası benzersizliği, mantıksal 50 milisaniyelik sınırın, normal piyasa koşulları altında öngörülebilir fiziksel satır kaymasına daima uyum sağlamasını garanti ettiğinden, hesaplama doğru kalmıştı ve PostgreSQL satırları sınırlı bir tampon aracılığıyla akıtabiliyordu. Sorgu gecikmesi milisaniye altı seviyelere düştü, bellek ayak izi O(1)'de stabil hale geldi ve sistem, diske dökmeden milyar satırlık partileri işleyebildi.
ORDER BY sütununda tekrar eden değerler bulunduğunda, varsayılan çerçeve ifadesi (RANGE UNBOUNDED PRECEDING) ile ROWS UNBOUNDED PRECEDING neden farklı koşullu toplamlar üretir?
Bir pencere fonksiyonu açık bir çerçeve ifadesi atladığında, PostgreSQL varsayılan olarak RANGE UNBOUNDED PRECEDING kullanır. Bu mod, aynı ORDER BY değerini paylaşan tüm satırları tek bir akran grubu olarak değerlendirir ve hepsini bir çerçeve içinde aynı anda dahil eder. Bu sonuçta, bir kullanıcının aynı gün içinde üç işlemi olduğunda, bu üç satır için koşullu toplam aynı olacak ve hepsinin toplamı, önceki günler ile birlikte gösterilecektir. Buna karşılık, ROWS UNBOUNDED PRECEDING toplamı kademeli olarak hesaplar: günün ilk işlemi sadece kendisini ve önceki günleri içerirken, ikincisi ilk iki işlemi içerir ve bu böyle devam eder. Adaylar genellikle bu varsayılan davranışı atlayarak, gün içindeki koşullu toplamların, o günün tüm satırları için günün son toplamında "takılmış" göründüğü raporlarla sonuçlanır ve zaman serisi analitiğini bozar.
PostgreSQL, RANGE çerçevelerini değerlendirirken ORDER BY sütunundaki NULL değerleri nasıl işler ve bu neden hesaplamalardan satırların sessizce çıkarılmasına yol açabilir?
SQL'de üç değerli mantıkta, NULL ile karşılaştırmalar BİLİNMEZ sonucunu verir, eşitlik değil. RANGE çerçeveleri için, PostgreSQL genellikle sonlu aralık pencerelerinden (örneğin, BETWEEN 1 PRECEDING AND 1 FOLLOWING) NULL sıralama değerine sahip satırları hariç tutar çünkü NULL ile aritmetik karşılaştırmalar başarısız olur. Bu satırlar, bitişik satırların çerçevelerine görünmeyen izole akran grupları oluşturur. Eğer bir veri kümesinde NULL zaman damgaları varsa (geçmiş veya bekleyen verileri temsil eden), RANGE kullanan hareketli ortalama bu satırları sessizce düşürürken, ROWS çerçeveleri fiziksel konuma göre dahil eder, bu da analitik toplamları saptırabilir.
ORDER BY sütunu garanti olarak benzersiz olduğunda, neden açık ROWS çerçevesi yine büyük veri kümesi için RANGE'den daha tercih edilir ve hangi iç işlemi önler?
Benzersizlik, ROWS ve RANGE arasında anlamsal eşdeğerliği sağlasa bile, yalnızca RANGE anahtar kelimesinin varlığı, PostgreSQL yürütücüsünü potansiyel akran grubu taraması için hazırlanmaya zorlar. Bu, tüm sıralı partiği bir iş tablosuna önceden önbelleğe almak için Materialize düğümünü tetikler (bellek tüketimi O(N)) ve satırları yayar. ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ifadesini açıkça belirtmek, plancıya yalnızca fiziksel satırlardan oluşan bir kayar pencerenin gerektiğini bildirir. Bu, pahalı malzeme aşamasını atlayarak ve bellekteki kullanımı O(çerçeve boyutu) ile azaltarak, milyar satırlık partileri diske dökmeden işlemek için kritik öneme haizdir.