Veritabanı Performans Ayarlama: PostgreSQL UPSERT Kilit Yarışları

Veritabanı performans ayarlama için önce kilit beklemesini kanıtlayın

Yük altında yavaşlayan bir UPSERT'i yalnızca ortalama sorgu süresiyle teşhis etmeyin. PostgreSQL'de aktif oturumların wait_event_type ve wait_event alanlarını 1 saniyelik örneklerle toplayın. Aynı product_id için yoğun INSERT ... ON CONFLICT DO UPDATE çağrıları, çoğunlukla rakip işlemin commit veya rollback kararını bekleyen Lock/transactionid olayları üretir. Bu ayrım önemlidir: disk gecikmesinde indeks eklemek çözüm olabilirken, transactionid beklemesinde aynı tek satıra daha fazla eşzamanlı yazıcı göndermek kuyruğu büyütür.

SELECT now(), pid, usename, application_name,
       wait_event_type, wait_event,
       state, age(now(), query_start) AS running_for,
       left(query, 180) AS query
FROM pg_stat_activity
WHERE datname = current_database()
  AND state <> 'idle'
ORDER BY query_start;

Bu sorguyu uygulama metrikleriyle birlikte çalıştırın: istek p95 ve p99 gecikmesi, saniyedeki başarılı yazma, hata koduna göre retry sayısı ve eşzamanlı bekleyen oturum sayısını aynı zaman serisine koyun. Ardından pg_locks ile bloklayan ve bloke edilen PID'leri ilişkilendirin. Veritabanı eğitimi kapsamında sık atlanan ayrıntı şudur: pg_stat_activity anlık bir fotoğraftır; 200 ms süren beklemeleri kaçırabilir. Bu nedenle 250-1000 ms aralıkla örnek alan bir exporter veya pg_wait_sampling kullanmak, kısa fakat sık tekrarlanan kilit dalgalarını görünür kılar.

Sıcak sayaçları shard'layarak UPSERT çakışmasını azaltın

Tek bir ürün satırında stok görüntüleme sayacı, beğeni sayısı veya kota sayacı tutmak, tüm yazıcıları aynı unique indeks kaydına ve aynı heap satırına yönlendirir. ON CONFLICT önce speculative insertion yapar, çakışan unique anahtarı bulur ve rakip işlemin sonucunu bekleyebilir. Çözüm, sayacı 32 fiziksel shard'a bölmek ve okuma sırasında toplamaktır. Buradaki shard seçimi istek kimliğinden deterministik türetilmelidir; rastgele shard seçip işlemi tekrar denerseniz aynı mantıksal isteği farklı shard'larda iki kez sayabilirsiniz.

CREATE TABLE request_inbox (
  request_id uuid PRIMARY KEY,
  accepted_at timestamptz NOT NULL DEFAULT clock_timestamp()
);

CREATE TABLE product_counter_shard (
  product_id bigint NOT NULL,
  shard smallint NOT NULL CHECK (shard BETWEEN 0 AND 31),
  value bigint NOT NULL DEFAULT 0,
  PRIMARY KEY (product_id, shard)
) WITH (fillfactor = 70);

WITH accepted AS (
  INSERT INTO request_inbox (request_id)
  VALUES ($1::uuid)
  ON CONFLICT (request_id) DO NOTHING
  RETURNING request_id
)
INSERT INTO product_counter_shard (product_id, shard, value)
SELECT $2::bigint,
       (hashtextextended(request_id::text, 0) & 31)::smallint,
       1
FROM accepted
ON CONFLICT (product_id, shard) DO UPDATE
SET value = product_counter_shard.value + EXCLUDED.value;

SELECT COALESCE(sum(value), 0)
FROM product_counter_shard
WHERE product_id = $2::bigint;

Bu tasarımda request_inbox idempotency sınırıdır: CTE'nin accepted kısmı satır döndürmezse sayaç artışı da gerçekleşmez. Uygulama aynı request_id ile retry yaptığında ikinci çağrı güvenle no-op olur. 32 shard sabit bir kural değildir. Önce tek sıcak anahtar için eşzamanlı yazıcı sayısını ve p99'u ölçün; 8, 16, 32 ve 64 shard ile aynı yükte karşılaştırın. Shard sayısını sonradan değiştirmek gerekiyorsa hem eski hem yeni shard aralığını toplayan bir geçiş sorgusu veya kontrollü backfill gerekir.

Database indexleme: conflict arbiter indeksini ve HOT update'i birlikte inceleyin

Bu örnekte PRIMARY KEY (product_id, shard), hem conflict arbiter hem de ürün bazlı toplama sorgusunun erişim yoludur; ayrıca bir product_id indeksi eklemek sol önek nedeniyle gereksizdir. Her ek indeks, UPSERT'in INSERT kolunda yeni indeks girdisi üretir ve UPDATE kolunda indekslenen bir alan değişirse daha fazla indeks bakımı doğurur. Bu nedenle database indexleme kararını yalnızca SELECT planına bakarak değil, yazma satırının indeks listesiyle verin.

EXPLAIN (ANALYZE, BUFFERS)
SELECT sum(value)
FROM product_counter_shard
WHERE product_id = 4242;

SELECT indexrelname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
WHERE relname = 'product_counter_shard'
ORDER BY indexrelname;

SELECT n_tup_upd, n_tup_hot_upd, n_dead_tup
FROM pg_stat_user_tables
WHERE relname = 'product_counter_shard';

value alanını INCLUDE (value) olarak dahi ek indekse koymayın; sayaç artırılırken bu alan değiştiği için PostgreSQL'in heap-only tuple yani HOT update oluşturma olasılığını düşürür. fillfactor = 70, aynı heap sayfasında yeni tuple sürümü için boşluk bırakır ve HOT zincirinin aynı sayfada kalmasına yardım eder. Bu kesin bir garanti değildir: uzun HOT zincirleri, sayfa doluluğu ve eşzamanlı erişim yine farklı davranış üretebilir. Bu yüzden değişiklikten önce ve sonra n_tup_hot_upd / n_tup_upd oranını, indeks sayfa yazımlarını ise pg_stat_io veya pg_stat_statements çağrı istatistikleriyle karşılaştırın.

SQL eğitimi için pgbench ile önce-sonra deneyini tekrarlanabilir kurun

Veritabanı optimizasyonu iddiasını aynı veri dağılımı ve aynı eşzamanlılıkla sınayın. Tek satırlı sayaç şemasını ve shard'lı şemayı ayrı test veritabanlarında kurun. Aşağıdaki pgbench betiğinde :rid uygulamanın ürettiği benzersiz istek kimliğini temsil eder; gerçek testte istemci tarafında UUID üretin veya betiği bu parametreyi sağlayacak bir sürücüyle çalıştırın. Amaç yalnızca TPS değil, p95/p99 latency, failed transaction sayısı ve kilit bekleme örneklerinin birlikte değişimini görmektir.

cat > upsert.sql <<'SQL'
\set product_id random(1, 100)
BEGIN;
WITH accepted AS (
  INSERT INTO request_inbox (request_id)
  VALUES (gen_random_uuid())
  ON CONFLICT DO NOTHING
  RETURNING request_id
)
INSERT INTO product_counter_shard (product_id, shard, value)
SELECT :product_id,
       (hashtextextended(request_id::text, 0) & 31)::smallint,
       1
FROM accepted
ON CONFLICT (product_id, shard) DO UPDATE
SET value = product_counter_shard.value + 1;
COMMIT;
SQL

pgbench -n -c 64 -j 8 -T 300 -P 10 -f upsert.sql appdb

Testi önce ürün kimliği aralığını random(1, 1) yaparak en kötü sıcak anahtarda, sonra random(1, 100) yaparak gerçekçi dağılımda yürütün. Her koşulda en az 5 dakika çalıştırın; kısa testler connection ramp-up ve cache ısınmasını sonuç sanabilir. SQL eğitimi materyallerinde yaygın hata, sadece toplam TPS raporlamaktır. Aynı TPS'de p99'un 40 ms'den 2 s'ye çıkması, kullanıcıların gördüğü kuyruk oluşumunu gizler. Test boyunca ilk bölümdeki bekleme örneklerini ve pg_stat_statements içindeki çağrı başına ortalama yürütme süresini kaydedin.

NoSQL eğitimi ve SQL Server eğitimi perspektifinden aynı problem

NoSQL eğitimi bağlamında bu desenin karşılığı, örneğin DynamoDB'de tek partition key yerine yazıları dağıtan shard suffix kullanmak ve okurken fan-out toplamı almaktır. Ancak DynamoDB'de shard sayısını artırmak okuma başına daha fazla Query çağrısı ve capacity tüketimi demektir; PostgreSQL'de ise maliyet çoğunlukla aynı ürün için 32 satırı toplamak olur. Hangi depolama motoru seçilirse seçilsin, tek bir mantıksal anahtarın fiziksel yazma hedefini dağıtma mekanizması ölçülmelidir.

SQL Server eğitimi tarafında benzer teşhis için sys.dm_exec_requests, sys.dm_tran_locks ve bekleme türleri kullanılabilir. LCK_M_X veya LCK_M_U artıyorsa WITH (NOLOCK) eklemek doğru çözüm değildir; dirty read üretebilir ve sayaç doğruluğunu bozabilir. SQL Server'da da sayaç shard tablosunu bileşik clustered key ile kurup, yükten önce ve sonra sys.dm_db_index_operational_stats üzerinden leaf-level lock wait ve page latch ölçümlerini kıyaslayın. Motorlar farklı DMV ve kilit uygulamalarına sahip olsa da tek satır yazma sıcaklığı, veri modeliyle azaltılmadıkça sorgu ipucuyla kalıcı olarak ortadan kalkmaz.

Sık Sorulan Sorular

Veritabanı performans ayarlama sırasında PostgreSQL UPSERT kilidi nasıl bulunur?

Yük testi sırasında pg_stat_activity'de wait_event_type ve wait_event alanlarını 250-1000 ms aralıklarla kaydedin. Lock/transactionid örneklerini pg_locks ile PID bazında eşleştirin. Aynı anda pg_stat_statements'tan calls, mean_exec_time ve toplam yürütme süresini alın; sadece anlık sorgu çıktısına bakmak kısa beklemeleri kaçırır.

Database indexleme UPSERT sorgusunu neden bazen yavaşlatır?

ON CONFLICT için unique indeks zorunludur, fakat bunun dışındaki her indeks INSERT kolunda ek indeks yazımı getirir. UPDATE edilen value alanını anahtar veya INCLUDE alanı yapmak HOT update olasılığını azaltabilir. pg_stat_user_indexes ile indeks kullanımını, pg_stat_user_tables ile n_tup_hot_upd ve n_tup_upd değerlerini birlikte inceleyerek gereksiz indeksi belirleyin.

SQL eğitimi kapsamında ON CONFLICT retry nasıl güvenli yapılır?

40001 serialization_failure veya 40P01 deadlock_detected için tüm transaction'ı retry edin; tek SQL ifadesini körlemesine tekrar etmeyin. Her mantıksal istek için sabit bir request_id kullanın ve request_inbox gibi unique kısıtlı bir idempotency tablosuna önce kayıt atın. Kabul edilmeyen tekrar istekte sayaç güncellemesini çalıştırmayın.

NoSQL eğitimi için counter sharding ne zaman gereklidir?

Tek partition key veya tek belge üzerindeki yazma hızı servis limitine, lock kuyruğuna ya da p99 gecikme hedefinize yaklaşıyorsa gereklidir. Önce anahtar başına yazma dağılımını ölçün. Shard sayısını artırmadan önce okuma tarafındaki fan-out maliyetini hesaplayın; örneğin 32 shard, toplam okumada 32 fiziksel değer birleştirme ihtiyacı doğurur.