PostgreSQL'e Özgü SQL ve Constraint'ler: RETURNING, ON CONFLICT, DISTINCT ON, Writable CTE, EXCLUDE ve Generated Column

Sürüm notu: Bu yazıdaki örnekler PostgreSQL 18.7 (resmî postgres:18 Docker image'ı) üzerinde, 02'de kurduğumuz Bookstore lab'ında, şema yeniden yüklendikten hemen sonra çalıştırılmıştır.

Önce Tahmin Et

Sitedeki serilerin ortak örnek uygulaması Bookstore (bir online kitapçı) için sepete kitap ekleme kodunu PostgreSQL'e taşıyoruz. SQL Server'da bu iş genelde "satır varsa güncelle, yoksa ekle" diye iki adımda ya da MERGE ile yazılır. PostgreSQL'de tek statement yetiyor. Sepet tablosu ve aynı kitabı üç kez ekleyen üç komut:

CREATE TABLE cart_items
(
    cart_id  int NOT NULL,
    book_id  int NOT NULL REFERENCES books (book_id),
    quantity int NOT NULL CHECK (quantity > 0),
    added_at timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (cart_id, book_id)
);

INSERT INTO cart_items (cart_id, book_id, quantity) VALUES (1, 7, 1)
ON CONFLICT (cart_id, book_id) DO UPDATE SET quantity = cart_items.quantity + EXCLUDED.quantity
RETURNING book_id, old.quantity AS old_qty, new.quantity AS new_qty, old IS NULL AS inserted;

INSERT INTO cart_items (cart_id, book_id, quantity) VALUES (1, 7, 2)
ON CONFLICT (cart_id, book_id) DO UPDATE SET quantity = cart_items.quantity + EXCLUDED.quantity
RETURNING book_id, old.quantity AS old_qty, new.quantity AS new_qty, old IS NULL AS inserted;

INSERT INTO cart_items (cart_id, book_id, quantity) VALUES (1, 2, 1), (1, 2, 3)
ON CONFLICT (cart_id, book_id) DO UPDATE SET quantity = cart_items.quantity + EXCLUDED.quantity;

İkinci komuttan sonra sepette kaç adet var? Üçüncü komut, aynı kitabı tek seferde iki satırla gönderiyor; sonuç 4 adet mi olur, 3 mü, yoksa hata mı?

 book_id | old_qty | new_qty | inserted
---------+---------+---------+----------
       7 |         |       1 | t

 book_id | old_qty | new_qty | inserted
---------+---------+---------+----------
       7 |       1 |       3 | f

ERROR:  ON CONFLICT DO UPDATE command cannot affect row a second time
HINT:  Ensure that no rows proposed for insertion within the same command have duplicate constrained values.

İlk komut satırı ekledi, ikincisi çakışmayı yakalayıp adedi 3'e çıkardı ve PostgreSQL 18'in RETURNING OLD/NEW desteği sayesinde bize hem eski hem yeni değeri döndürdü. Üçüncü komut ise hata verdi: aynı statement içinde bir satır iki kez güncellenemiyor, çünkü sonucun hangi sırayla oluşacağı belirsiz. Bu yazıda PostgreSQL'in SQL Server'da olmayan ya da farklı çalışan SQL özelliklerine bakıyoruz: RETURNING, upsert, MERGE, DISTINCT ON, LATERAL, writable CTE (Common Table Expression; WITH ile yazılan, veri de değiştirebilen sorgu bloğu) ve SQL Server'da karşılığı olmayan constraint'ler.

RETURNING ve RETURNING OLD/NEW

INSERT, UPDATE, DELETE ve MERGE, etkiledikleri satırları RETURNING ile döndürebilir. SQL Server'daki OUTPUT inserted.* ve OUTPUT deleted.*'in karşılığı; PostgreSQL 18'e kadar yalnızca yeni değer dönebiliyordu, 18 ile old ve new takma adları geldi. Bookstore'da stok rezervasyonu tam bu özelliğe uygun bir iş:

BEGIN;
INSERT INTO authors (name, country) VALUES ('Ursula K. Le Guin', 'US') RETURNING author_id, name;

UPDATE stock_items
SET quantity_on_hand = quantity_on_hand - 2,
    quantity_reserved = quantity_reserved + 2,
    updated_at = now()
WHERE book_id = 1 AND quantity_on_hand >= 2
RETURNING book_id, old.quantity_on_hand AS before, new.quantity_on_hand AS after, new.quantity_reserved AS reserved;

UPDATE stock_items
SET quantity_on_hand = quantity_on_hand - 500
WHERE book_id = 1 AND quantity_on_hand >= 500
RETURNING book_id, old.quantity_on_hand AS before, new.quantity_on_hand AS after;
ROLLBACK;

 author_id |       name
-----------+-------------------
      5001 | Ursula K. Le Guin

 book_id | before | after | reserved
---------+--------+-------+----------
       1 |     36 |    34 |        2

 book_id | before | after
---------+--------+-------
(0 rows)

Rezervasyon tek statement: koşul (quantity_on_hand >= 2) ve güncelleme aynı anda, satır lock'u altında yapılıyor; iki kullanıcı son iki kitabı aynı anda isterse yalnızca biri satır alır, diğeri boş sonuç görür. Uygulama tarafında "önce oku, sonra yaz" yapmaya ve bunun yarattığı lost update sorununa gerek kalmıyor. Boş sonuç, "stok yetmedi" demek; ayrıca bir SELECT atmadan bunu anlıyoruz. Eskiden önceki değeri görmek için ya trigger ya da ayrı bir okuma gerekiyordu.

Bir ayrıntı: transaction'ı geri aldık ama author_id 5001 harcandı. Sequence'lar transaction'a bağlı değil; rollback olsa da verilen numara geri alınmaz. SQL Server'daki IDENTITY boşluklarıyla aynı davranış; id'lerde boşluk olmayacağını varsayan kod yazma.

ON CONFLICT ile Upsert

"Önce Tahmin Et"teki komutlarda ON CONFLICT (cart_id, book_id) çakışmanın hangi constraint üzerinden yakalanacağını söylüyor. EXCLUDED, eklenmeye çalışılan ama çakışan satırı temsil ediyor; cart_items.quantity ise tablodaki mevcut değer. Çakışma hedefi bir unique constraint'e, unique index'e ya da exclusion constraint'e denk gelmek zorunda:

INSERT INTO cart_items (cart_id, book_id, quantity) VALUES (1, 7, 5)
ON CONFLICT (cart_id, book_id) DO NOTHING
RETURNING book_id, quantity;

INSERT INTO cart_items (cart_id, book_id, quantity) VALUES (1, 7, 5)
ON CONFLICT (book_id) DO NOTHING;

 book_id | quantity
---------+----------
(0 rows)

ERROR:  there is no unique or exclusion constraint matching the ON CONFLICT specification

DO NOTHING çakışmada hiçbir şey yapmıyor ve RETURNING boş dönüyor; "zaten vardı" bilgisini buradan çıkarabilirsin. Yalnızca book_id üzerinde unique bir constraint olmadığı için ikinci komut hiç çalışmadı. ON CONFLICT'in asıl değeri eşzamanlılıkta: aynı anahtarı aynı anda ekleyen iki oturumdan biri ekler, diğeri bekler ve ardından güncelleme koluna geçer; unique violation hatası almaz. SQL Server'da aynı garantiyi almak için MERGE'e HOLDLOCK eklemek ya da serializable transaction kullanmak gerekir.

MERGE ve ON CONFLICT Farkı

PostgreSQL 15 ile standart MERGE geldi, 17 ile RETURNING ve hangi kolun çalıştığını söyleyen merge_action() eklendi. Tedarikçiden gelen bir fiyat dosyasını kataloğa uygulayalım:

CREATE TEMP TABLE price_feed (isbn varchar(13), price numeric(10,2));
INSERT INTO price_feed VALUES ('9780000000001', 29.90), ('9780000000002', 39.00), ('9780000099999', 12.50);

BEGIN;
MERGE INTO books AS b
USING price_feed AS f ON b.isbn = f.isbn
WHEN MATCHED AND b.price <> f.price THEN UPDATE SET price = f.price
WHEN MATCHED THEN DO NOTHING
WHEN NOT MATCHED THEN INSERT (isbn, title, author_id, category, price, published_year)
                      VALUES (f.isbn, 'Unknown #' || f.isbn, 1, 'Classic', f.price, 2026)
RETURNING merge_action() AS action, b.book_id, f.isbn, old.price AS old_price, new.price AS new_price;
ROLLBACK;

 action | book_id |     isbn      | old_price | new_price
--------+---------+---------------+-----------+-----------
 UPDATE |       1 | 9780000000001 |     32.77 |     29.90
 INSERT |   40001 | 9780000099999 |           |     12.50

İkinci kitabın fiyatı zaten 39,00 olduğu için DO NOTHING koluna düştü ve sonuçta görünmüyor. Hangisini ne zaman kullanmalı? MERGE bir kaynak kümeyi hedef tabloyla karşılaştırıp birden fazla koşula göre güncelleme, ekleme ve silme yapmak için: toplu senkronizasyon, ETL (veriyi bir kaynaktan alıp dönüştürerek başka bir yere yükleme). ON CONFLICT ise tek tek satırları eşzamanlı ortamda güvenle eklemek ya da güncellemek için: sepet, sayaç, idempotent mesaj işleme. Dokümantasyon da eşzamanlı bir insert durumunda güncelleme yapabilme garantisi için ON CONFLICT'i öneriyor; MERGE normal isolation kurallarıyla çalışır, aynı anahtarı başka bir oturum eklediyse unique violation alabilirsin.

DISTINCT ON, LATERAL, generate_series ve FILTER

SQL Temelleri serisinde "grup başına en yeni" sorusunu window function ile çözmüştük. PostgreSQL'de bunun kısa bir yolu var: DISTINCT ON, ORDER BY'a göre her grubun ilk satırını tutar.

SELECT DISTINCT ON (category) category, book_id, title, published_year
FROM books
ORDER BY category, published_year DESC, book_id;

 category | book_id |       title       | published_year
----------+---------+-------------------+----------------
 Classic  |     531 | Ulysses #531      |           2015
 Fantasy  |   13682 | The Hobbit #13682 |           2015
 Horror   |   15030 | Dracula #15030    |           2015
 SciFi    |    1960 | Dune #1960        |           2015

ORDER BY'ın ilk kolonları DISTINCT ON'daki ifadelerle aynı olmak zorunda; sonraki kolonlar hangi satırın "ilk" sayılacağını belirliyor. Aynı yılda birden fazla kitap olduğu için book_id'yi de ekledik; eklemesek sonuç her çalıştırmada aynı olmayabilirdi. Grup başına birden fazla satır gerekiyorsa LATERAL kullanılır; SQL Server'daki CROSS APPLY'ın karşılığı. Her müşterinin son iki siparişi:

SELECT c.customer_id, c.first_name, o.order_number, o.created_at, o.total_amount
FROM customers AS c
CROSS JOIN LATERAL (
    SELECT order_number, created_at, total_amount
    FROM orders
    WHERE orders.customer_id = c.customer_id
    ORDER BY created_at DESC
    LIMIT 2
) AS o
WHERE c.customer_id IN (2, 3)
ORDER BY c.customer_id, o.created_at DESC;

 customer_id | first_name | order_number |       created_at       | total_amount
-------------+------------+--------------+------------------------+--------------
           2 | Linus      | BK-00999989  | 2026-10-20 20:56:00+00 |        67.36
           2 | Linus      | BK-00999983  | 2026-10-20 20:44:00+00 |        56.75
           3 | Margaret   | BK-00999864  | 2026-10-20 16:46:00+00 |        39.37
           3 | Margaret   | BK-00999724  | 2026-10-20 12:06:00+00 |       119.71

Raporlarda sık karşılaşılan iki ihtiyaç daha var: boş günlerin de görünmesi ve tek geçişte koşullu sayımlar. generate_series tarih aralığı üretir, aggregate'lere eklenen FILTER (WHERE ...) ise SUM(CASE WHEN ... END) kalıbının okunur hâli:

SELECT d::date AS day,
       count(o.order_id) AS orders,
       count(o.order_id) FILTER (WHERE o.status = 'Cancelled') AS cancelled,
       coalesce(sum(o.total_amount) FILTER (WHERE o.status <> 'Cancelled'), 0) AS revenue
FROM generate_series(timestamptz '2026-10-18', timestamptz '2026-10-22', interval '1 day') AS d
LEFT JOIN orders AS o ON o.created_at >= d AND o.created_at < d + interval '1 day'
GROUP BY d
ORDER BY d;

    day     | orders | cancelled | revenue
------------+--------+-----------+----------
 2026-10-18 |    720 |        29 | 54800.61
 2026-10-19 |    720 |        29 | 55140.97
 2026-10-20 |    640 |        25 | 49863.71
 2026-10-21 |      0 |         0 |        0
 2026-10-22 |      0 |         0 |        0

Lab verisi 20 Ekim akşamı bitiyor; 21 ve 22 Ekim sipariş tablosunda hiç yok ama raporda sıfırla görünüyor. SQL Server 2022'de gelen GENERATE_SERIES yalnızca sayı üretir, tarih için ek hesap gerekir; PostgreSQL'de timestamptz ve interval ile doğrudan çalışıyor.

Writable CTE: Tek Statement'ta Arşivle ve Sil

PostgreSQL'de WITH içindeki sorgu bir DELETE, UPDATE ya da INSERT olabilir ve RETURNING ile ürettiği satırları sonraki adıma verebilir. 2023 Ocak'ında teslim edilmiş kargoları arşiv tablosuna taşıyıp ana tablodan silen tek statement:

BEGIN;
CREATE TABLE shipments_archive (LIKE shipments);
WITH archived AS (
    DELETE FROM shipments
    WHERE delivered_at < timestamptz '2023-02-01'
    RETURNING *
), inserted AS (
    INSERT INTO shipments_archive SELECT * FROM archived
    RETURNING shipment_id
)
SELECT count(*) AS moved, min(shipment_id) AS first_id, max(shipment_id) AS last_id FROM inserted;
SELECT (SELECT count(*) FROM shipments) AS live, (SELECT count(*) FROM shipments_archive) AS archived;
ROLLBACK;

 moved | first_id | last_id
-------+----------+---------
 17966 |        1 |   19297

  live  | archived
--------+----------
 936789 |    17966

Silme ve ekleme aynı statement'ta, aynı snapshot ile çalışıyor; arada başka bir oturumun yeni eklediği bir kargo yanlışlıkla silinmiyor, ya da arşive girip ana tabloda kalmıyor. SQL Server'da DELETE ... OUTPUT deleted.* INTO archive aynı işi görür; PostgreSQL'de bunu birden fazla adıma zincirleyebilirsin. Arşiv tablosunu (LIKE shipments) ile oluşturduk; INCLUDING ALL deseydik identity kolonu da kopyalanırdı ve INSERT ... SELECT * 02'de gördüğümüz GENERATED ALWAYS hatasını verirdi. Milyonlarca satır taşınacaksa bu statement'ı parça parça (örneğin günlük) çalıştırmak gerekir; tek seferde çok sayıda satırı kilitleyen ve WAL'ı (Write-Ahead Log; değişikliklerin önce yazıldığı log) şişiren bir işlem olmamalı.

Constraint'ler: Deferrable Foreign Key ve EXCLUDE

SQL Server'da constraint'ler her statement'tan sonra kontrol edilir. PostgreSQL'de bir constraint DEFERRABLE tanımlanırsa kontrol commit'e ertelenebilir. Bir sipariş satırını siparişten önce eklemeyi deneyelim:

ALTER TABLE order_items ALTER CONSTRAINT fk_order_items_orders DEFERRABLE INITIALLY IMMEDIATE;

BEGIN;
SET CONSTRAINTS fk_order_items_orders DEFERRED;
INSERT INTO order_items (order_id, book_id, quantity, unit_price) VALUES (1000001, 1, 1, 32.77);
INSERT INTO orders (order_id, order_number, customer_id, status, created_at, total_amount, currency)
OVERRIDING SYSTEM VALUE VALUES (1000001, 'BK-01000001', 42, 'Pending', now(), 32.77, 'TRY');
SET CONSTRAINTS fk_order_items_orders IMMEDIATE;
ROLLBACK;

BEGIN;
SET CONSTRAINTS fk_order_items_orders DEFERRED;
INSERT INTO order_items (order_id, book_id, quantity, unit_price) VALUES (1000002, 1, 1, 32.77);
COMMIT;

INSERT 0 1
INSERT 0 1
SET CONSTRAINTS
ROLLBACK
...
INSERT 0 1
ERROR:  insert or update on table "order_items" violates foreign key constraint "fk_order_items_orders"
DETAIL:  Key (order_id)=(1000002) is not present in table "orders".

İlk transaction'da kontrol SET CONSTRAINTS ... IMMEDIATE anına kadar ertelendi ve o anda sipariş var olduğu için geçti. İkincisinde sipariş hiç eklenmedi; satır kabul edildi ama COMMIT hata verdi ve transaction geri alındı. Ertelenebilir constraint, karşılıklı referans veren tabloları ya da sırası uygulamanın kontrolünde olmayan toplu yüklemeleri mümkün kılar. Varsayılan hâli NOT DEFERRABLE bırakmak doğru; hatayı commit'e kadar ertelemek, hatalı satırı üreten kodu bulmayı zorlaştırır. Denemeden sonra constraint'i NOT DEFERRABLE'a geri aldık.

SQL Server'da hiç karşılığı olmayan bir constraint türü de var: exclusion constraint. Unique constraint "aynı değerden iki tane olamaz" der; exclusion constraint ise "verilen operator'lerin hepsi true dönen iki satır olamaz" der. 03'teki kampanya tablosunda "aynı kategoride çakışan iki kampanya olamaz" kuralı:

ALTER TABLE campaigns ADD CONSTRAINT ex_campaigns_category_period
    EXCLUDE USING gist (category WITH =, period WITH &&);

ERROR:  data type text has no default operator class for access method "gist"
HINT:  You must specify an operator class for the index or define a default operator class for the data type.

Exclusion constraint bir GiST (Generalized Search Tree; range ve geometri gibi tipleri index'leyebilen yapı) index'i üzerinde çalışıyor; range'ler için GiST desteği hazır ama text üzerinde eşitliği GiST ile kontrol etmek için btree_gist extension'ı gerekiyor. Bu extension "trusted" işaretli; superuser olmadan, database üzerinde CREATE yetkisi olan owner rolü kurabiliyor:

CREATE EXTENSION btree_gist;
ALTER TABLE campaigns ADD CONSTRAINT ex_campaigns_category_period
    EXCLUDE USING gist (category WITH =, period WITH &&);
INSERT INTO campaigns (name, category, discount, period) VALUES
    ('Back to School', 'Classic', 0.10, '[2026-09-01,2026-09-15)'),
    ('Black Friday',   'SciFi',   0.25, '[2026-11-27,2026-11-30]'),
    ('Winter Reads',   'Classic', 0.15, '[2026-12-01,2027-01-01)');
INSERT INTO campaigns (name, category, discount, period)
VALUES ('Cyber Monday', 'SciFi', 0.30, '[2026-11-30,2026-12-02)');

CREATE EXTENSION
ALTER TABLE
INSERT 0 3
ERROR:  conflicting key value violates exclusion constraint "ex_campaigns_category_period"
DETAIL:  Key (category, period)=(SciFi, [2026-11-30,2026-12-02)) conflicts with existing key (category, period)=(SciFi, [2026-11-27,2026-12-01)).

Black Friday'in kanonik biçimi 1 Aralık'a kadar uzandığı için 30 Kasım'da başlayan Cyber Monday reddedildi. Bu kuralı trigger ya da uygulama koduyla yazmak, iki eşzamanlı insert'in birbirini görmemesi yüzünden güvenilir değildir; constraint ise index seviyesinde, eşzamanlılıkta da doğru çalışır. Aynı yapı otel odası, randevu ya da depo rampası rezervasyonu gibi her "çakışan aralık olmasın" kuralına uyar.

PostgreSQL 18: Temporal Key'ler, NOT NULL NOT VALID ve Virtual Generated Column

PostgreSQL 18, exclusion constraint'in en yaygın kullanımını standart sözdizimine taşıdı: WITHOUT OVERLAPS ile temporal primary key ve PERIOD ile temporal foreign key. Bookstore'da bir kitabın fiyatı dönemlere göre değişiyor ve kampanyaya giren kitabın, kampanya süresince geçerli bir fiyatı olmalı:

CREATE TABLE book_prices
(
    book_id      int NOT NULL REFERENCES books (book_id),
    valid_during daterange NOT NULL,
    price        numeric(10,2) NOT NULL,
    PRIMARY KEY (book_id, valid_during WITHOUT OVERLAPS)
);
INSERT INTO book_prices VALUES
    (1, '[2026-01-01,2026-07-01)', 32.77),
    (1, '[2026-07-01,2027-01-01)', 34.90);
INSERT INTO book_prices VALUES (1, '[2026-12-01,2027-02-01)', 36.00);

CREATE TABLE campaign_items
(
    campaign_id  int NOT NULL REFERENCES campaigns (campaign_id),
    book_id      int NOT NULL,
    valid_during daterange NOT NULL,
    PRIMARY KEY (campaign_id, book_id),
    FOREIGN KEY (book_id, PERIOD valid_during) REFERENCES book_prices (book_id, PERIOD valid_during)
);
INSERT INTO campaign_items VALUES (1, 1, '[2026-06-15,2026-07-15)');
INSERT INTO campaign_items VALUES (3, 1, '[2026-12-20,2027-01-10)');

INSERT 0 2
ERROR:  conflicting key value violates exclusion constraint "book_prices_pkey"
DETAIL:  Key (book_id, valid_during)=(1, [2026-12-01,2027-02-01)) conflicts with existing key (book_id, valid_during)=(1, [2026-07-01,2027-01-01)).
CREATE TABLE
INSERT 0 1
ERROR:  insert or update on table "campaign_items" violates foreign key constraint "campaign_items_book_id_valid_during_fkey"
DETAIL:  Key (book_id, valid_during)=(1, [2026-12-20,2027-01-10)) is not present in table "book_prices".

Primary key, aynı kitap için çakışan iki fiyat dönemini reddetti; hata mesajında "exclusion constraint" yazması tesadüf değil, pg_indexes'e bakınca primary key'in bir GiST index'i olduğu görülüyor (bu yüzden btree_gist burada da gerekli). Foreign key tarafı daha ilginç: 15 Haziran - 15 Temmuz aralığı iki ayrı fiyat dönemine yayılıyor ve kabul edildi, çünkü kontrol aralığın fiyat dönemlerinin birleşimi tarafından kapsanıp kapsanmadığına bakıyor. 2027'ye taşan aralık ise reddedildi; 1 Ocak 2027'den sonrası için fiyat yok.

18'in ikinci yeniliği NOT NULL constraint'ini NOT VALID olarak eklemek. Büyük bir tabloya zorunlu bir kolon eklerken eski satırları hemen doldurmak mümkün değilse, kuralı yeni satırlar için hemen başlatıp eskileri sonra doğrulayabilirsin. Kolonu bir transaction içinde ekleyip sonunda geri aldık:

ALTER TABLE customers ADD COLUMN phone text;
UPDATE customers SET phone = '+90 555 000 ' || lpad(customer_id::text, 4, '0') WHERE customer_id <= 10;
ALTER TABLE customers ADD CONSTRAINT customers_phone_not_null NOT NULL phone NOT VALID;

INSERT INTO customers (first_name, last_name, email, country, city, created_at)
VALUES ('Ada', 'Lovelace', 'new-ada@example.com', 'GB', 'London', now());
SELECT count(*) FILTER (WHERE phone IS NULL) AS old_rows_without_phone FROM customers;
ALTER TABLE customers VALIDATE CONSTRAINT customers_phone_not_null;

ERROR:  null value in column "phone" of relation "customers" violates not-null constraint
 old_rows_without_phone
------------------------
                  99990

ERROR:  column "phone" of relation "customers" contains null values

Yeni satır telefonsuz eklenemedi, ama 99.990 eski satır olduğu gibi kaldı ve constraint convalidated = f durumunda. Eski satırlar doldurulduktan sonra VALIDATE CONSTRAINT tabloyu tarar; bu tarama yazma işlemlerini engelleyen ağır bir lock almaz. SQL Server'da WITH NOCHECK ile eklenip sonra WITH CHECK CHECK CONSTRAINT ile doğrulanan constraint'in karşılığı.

Üçüncü yenilik generated column'larla ilgili. PostgreSQL 12'den beri hesaplanan değeri diske yazan stored generated column vardı; 18 ile hesaplamayı okuma anında yapan virtual generated column geldi ve varsayılan oldu. 250 bin sipariş satırlık bir kopya üzerinde ikisini de ekledik ve tablonun fiziksel dosyasını izledik:

CREATE TABLE gen_demo AS SELECT order_item_id, order_id, quantity, unit_price FROM order_items WHERE order_id <= 100000;
SELECT pg_relation_filenode('gen_demo') AS filenode;
ALTER TABLE gen_demo ADD COLUMN line_total numeric(12,2) GENERATED ALWAYS AS (quantity * unit_price);
SELECT pg_relation_filenode('gen_demo') AS filenode_after_virtual;
ALTER TABLE gen_demo ADD COLUMN line_total_stored numeric(12,2) GENERATED ALWAYS AS (quantity * unit_price) STORED;
SELECT pg_relation_filenode('gen_demo') AS filenode_after_stored;
CREATE INDEX ix_gen_demo_line_total ON gen_demo (line_total);
CREATE INDEX ix_gen_demo_line_total_stored ON gen_demo (line_total_stored);

 filenode
----------
    19546

 filenode_after_virtual
------------------------
                  19546

 filenode_after_stored
-----------------------
                 19551

ERROR:  indexes on virtual generated columns are not supported
CREATE INDEX

Virtual kolon eklemek yalnızca catalog'u değiştirdi; stored kolon tabloyu yeni bir dosyaya baştan yazdı. Buna karşılık virtual kolona index konamıyor; filtrelemek ya da sıralamak için index gerekiyorsa STORED yazmak zorundasın. SQL Server'daki computed column'larla eşlemesi: varsayılan (virtual) computed column ile PERSISTED computed column. Farkı, SQL Server'ın persisted olmayan computed column'a da belirli koşullarda index koyabilmesi.

Üretimde Dikkat

Exclusion constraint ve temporal key'ler GiST index kullanır; B-tree'ye göre eklemesi genellikle daha pahalı ve boyutu daha büyüktür. Rezervasyon gibi yüksek yazma hızlı tablolarda bu maliyeti ölç. ON CONFLICT çakışma olduğunda bile identity sequence'ından bir numara harcar: aynı anahtarla üç kez upsert edip yeni bir anahtar eklediğimiz bir denemede yeni satır 4 numarasını aldı. Sık çakışan bir upsert, id'lerde büyük boşluklar ve int kolonlarda beklenenden erken taşma demektir. Sayaç gibi tablolarda bigint kullan ya da önce güncellemeyi deneyen bir sıra izle.

Writable CTE ve MERGE büyük veri kümelerinde tek transaction ve tek uzun lock demek. Toplu silme ve taşımaları küçük parçalara böl; aksi halde WAL birikir, replica'lar geride kalır ve uzun transaction autovacuum'u durdurur. Deferrable unique constraint'ler ON CONFLICT hedefi olamaz ("ON CONFLICT does not support deferrable unique constraints/exclusion constraints as arbiters" hatası) ve planner tekillik varsayımı gereken optimizasyonlarda onlara güvenmez; ihtiyaç yoksa varsayılanı koru.

Sık Düşülen Tuzaklar

ON CONFLICT hedefini constraint'le eşleştirmemek: çakışma hedefi tam olarak bir unique index'in kolonlarına (ve partial index ise WHERE koşuluna) denk gelmeli. Kolon sırası önemli değil ama eksik ya da fazla kolon hata verir.

DISTINCT ON'da sıralamayı eksik bırakmak: ORDER BY grup içindeki sırayı tam belirlemiyorsa hangi satırın döneceği belirsizdir ve plan değişince sonuç da değişir.

Mevcut değeri nitelemeyi unutmak: SET quantity = quantity + EXCLUDED.quantity yazınca PostgreSQL hangi quantity'nin kastedildiğini bilemez ve column reference "quantity" is ambiguous hatası verir. Eşitliğin sağındaki mevcut değeri tablo adıyla (cart_items.quantity) ya da bir alias ile nitele.

Virtual generated column'a index'li bir performans beklemek: PostgreSQL 18'de STORED yazmazsan kolon virtual olur. Eski sürümlerden taşınan şemalarda bu varsayılan farkını unutma.

Küçük Bir Deneme

★☆☆ cart_items tablosuna, aynı kitabı yeniden eklerken adedi artırmak yerine added_at'i güncelleyen ve RETURNING ile "eklendi" ya da "güncellendi" bilgisini dönen bir upsert yaz.

★★☆ DISTINCT ON sorgusunu SQL Temelleri'nde gördüğümüz ROW_NUMBER() OVER (PARTITION BY category ...) ile yeniden yaz ve iki sorgunun EXPLAIN çıktısını karşılaştır.

★★★ Bir depo rampası rezervasyon tablosu tasarla: aynı rampada çakışan iki zaman aralığı olmasın, ama iptal edilmiş rezervasyonlar kurala dahil olmasın. İpucu: exclusion constraint'e de WHERE koşulu eklenebilir.

Bir sonraki yazıda PostgreSQL'in belki de en çok kullanılan özelliğine geçiyoruz: JSONB (JSON'ın ayrıştırılıp binary olarak saklandığı tip). Operator'ler, SQL/JSON fonksiyonları, GIN (çok değerli kolonlar için index türü) index ve JSONB'nin ne zaman yanlış tercih olduğu.