A common way to store documents in PostgreSQL is a table with an ID and a JSONB column. The...

PostgreSQL JSONB vs typed columns on 2 million rows: table size, aggregate speed, planner row estimates, HOT updates, and the queries a GIN index speeds up.

Her CRM projesinde bir noktada şu istek gelir: "Lead kartına bir alan daha ekleyebilir miyiz?" Ben Koray Yalçın; lead yönetimi, CRM mimarisi, dijital pazarlama ve yapay zekâ destekli otomasyon projelerinde, ağırlıklı olarak gayrimenkul ve PropTech tarafında çalışıyorum. Bu istek ilk geldiğinde tabloya bir sütun eklemek yeterli görünür. Beşinci istekte, üç farklı projenin üç farklı alan listesi istediği ortaya çıkar. Onuncu istekte ise pazarlama ekibinin bir alanın adını değiştirdiği ve bu yüzden bir gecede iki entegrasyonun sessizce boş veri göndermeye başladığı bir sabah yaşanır. Konut satışında bu ihtiyaç çok somuttur. Bir proje için "ilgilenilen oda tipi", "bütçe aralığı", "kat tercihi" ve "deniz manzarası şart mı" alanları önemlidir. Başka bir proje ticari ünite sattığı için "metrekare ihtiyacı" ve "kullanım amacı" sorulur. Yurt dışından gelen alıcılar için "tercih edilen para birimi" ve "görüşme dili" eklenir. Hepsi aynı lead tablosunda yaşamak zorundadır ve hepsi raporlarda, otomasyonlarda ve reklam platformlarına giden geri bildirimlerde kullanılır. Bu yazıda özel alanları (custom field) geliştirici gözüyle ele alıyorum: veriyi nerede tutarsınız, alanın tanımını nasıl yönetirsiniz, değerleri nasıl doğrularsınız, sorguları nasıl hızlı tutarsınız ve bir alanın yaşam döngüsünü (ekleme, yeniden adlandırma, tip değiştirme, kaldırma) nasıl güvenli hâle getirirsiniz. ## Üç depolama seçeneği ve gerçek maliyetleri Özel alan değerlerini saklamanın üç yaygın yolu var. Hangisinin "doğru" olduğu, alan sayısına, sorgu desenine ve kaç farklı müşterinin (ya da projenin) aynı şemayı paylaştığına bağlı. **Gerçek sütunlar.** Her yeni alan için `ALTER TABLE` çalıştırırsınız. Tip güvenliği, indeksleme ve raporlama en iyi bu yöntemde olur. Ama alan eklemek bir şema değişikliği, yani bir dağıtım işi hâline gelir. Çok kiracılı (multi-tenant) bir yapıda her kiracının alanlarını aynı tabloya sütun olarak eklemek ise kısa sürede yüzlerce seyrek sütun üretir. **EAV (Entity-Attribute-Value).** Her değer ayrı bir satırdır: hangi kayıt, hangi alan, hangi değer. Esnektir ve alan eklemek yalnızca bir satır eklemektir. Bedeli sorgu tarafında ödenir. "Bütçesi 150.000 sterlinin üzerinde olan ve 2+1 arayan lead'ler" gibi basit bir filtre, iki ayrı birleştirme (join) gerektirir. On alanlı bir dışa aktarma on birleştirme demektir. Değerler genellikle metin olarak tutulduğu için tip dönüşüm hataları da raporlara sızar. **JSONB sütunu.** Kaydın üzerinde tek bir `custom` sütunu vardır ve tüm özel değerler orada bir nesne olarak durur. PostgreSQL'in JSONB tipi bu iş için oldukça olgundur: GIN indeksi, ifade indeksi ve içerme operatörleri sağlar. Esneklik EAV'ye yakındır, sorgu maliyeti ise çok daha düşüktür. Zayıf yanı, veritabanının şemayı bilmemesidir; doğrulamayı sizin yapmanız gerekir. Benim sahada vardığım denge şu: Herkesin kullandığı ve raporların omurgasını oluşturan alanlar (kaynak, aşama, sahip, proje, oluşturulma zamanı) gerçek sütundur. Projeye ya da ekibe göre değişen alanlar JSONB'dedir. Bir JSONB alanı zamanla çok kritik hâle gelirse (her raporda, her filtrede kullanılıyorsa) kontrollü bir göçle gerçek sütuna terfi ettirilir. EAV'yi ise yalnızca değer başına ayrı meta veri (kim, ne zaman, hangi kaynaktan girdi) tutmanın şart olduğu durumlarda düşünüyorum; o ihtiyacı da çoğunlukla denetim kaydı karşılıyor. ## Alan tanımı: Veriden önce sözleşme JSONB'nin şemasız olması, şemanın hiç olmaması anlamına gelmemeli. Şema veritabanında değil, bir alan tanım kayıt defterinde (field registry) yaşar. Her özel alan burada bir satırdır ve bu satır, alanın sözleşmesidir. CREATE TABLE custom_field_def ( id bigserial PRIMARY KEY, tenant_id bigint NOT NULL, entity text NOT NULL CHECK (entity IN ('lead','deal','contact')), api_key text NOT NULL, -- değişmez, entegrasyonların kullandığı ad label text NOT NULL, -- ekranda görünen, değiştirilebilir ad data_type text NOT NULL CHECK (data_type IN ('text','number','money','date','bool','select','multi_select')), config jsonb NOT NULL DEFAULT '{}', -- min/max, para birimi, seçenekler is_required boolean NOT NULL DEFAULT false, is_pii boolean NOT NULL DEFAULT false, status text NOT NULL DEFAULT 'active' CHECK (status IN ('active','deprecated','archived')), version int NOT NULL DEFAULT 1, created_at timestamptz NOT NULL DEFAULT now(), UNIQUE (tenant_id, entity, api_key) ); Buradaki en önemli karar `api_key` ile `label` ayrımıdır. Ekranda "Bütçe" yazan alanın etiketi yarın "Tahmini bütçe (GBP)" olabilir; bu tamamen serbesttir. Ama `budget_gbp` anahtarı bir kez oluştuktan sonra değişmez. JSONB içindeki anahtar, webhook yükleri, reklam platformuna giden eşleme ve otomasyon kuralları hep bu anahtarı kullanır. Yukarıda anlattığım "bir gecede iki entegrasyon boş veri göndermeye başladı" vakası, tam olarak etiketin anahtar yerine kullanılmasından kaynaklanıyordu. `is_pii` bayrağı da baştan eklenmeli. Bir özel alanın kişisel veri içerip içermediği, maskeleme, dışa aktarma ve saklama süresi kararlarını doğrudan etkiler. Pazarlama ekibinin "ikinci telefon numarası" diye bir alan açması çok olağandır ve bu alan, standart telefon alanıyla aynı korumayı hak eder. ## Seçenek listeleri: Etiket değil, kararlı anahtar Açılır liste (select) alanları en çok sorun çıkaran tiptir. "2+1" seçeneğinin etiketi bir gün "2+1 (Daire)" olarak düzeltilir ve geçmiş veride hem "2+1" hem "2+1 (Daire)" görünmeye başlar. Bunu önlemenin yolu, kayda etiketi değil seçeneğin kalıcı anahtarını yazmaktır. { "options": [ { "key": "r_1_1", "label": "1+1", "status": "active" }, { "key": "r_2_1", "label": "2+1 (Daire)", "status": "active" }, { "key": "r_3_1", "label": "3+1", "status": "active" }, { "key": "villa", "label": "Villa", "status": "deprecated" } ] } Bir seçenek kaldırılmak istendiğinde silinmez, `deprecated` yapılır. Yeni kayıtlarda seçilemez ama eski kayıtlarda okunabilir kalır ve raporlar geçmişi bozmadan çalışır. Seçenek sırası ve etiketi serbestçe değişebilir; anahtar hiçbir zaman değişmez. ## Yazma yolunda doğrulama Veritabanı JSONB içine ne yazıldığını denetlemediği için doğrulama, tüm yazma yollarının geçtiği tek bir katmanda yapılmalı: arayüzden gelen düzenleme, form entegrasyonu, toplu içe aktarma ve otomasyon hepsi aynı fonksiyonu çağırır. from decimal import Decimal, InvalidOperation from datetime import date class FieldError(Exception): def __init__(self, api_key, reason): super().__init__(f"{api_key}: {reason}") self.api_key, self.reason = api_key, reason def coerce(defn, raw): t, cfg = defn["data_type"], defn["config"] if raw is None or raw == "": if defn["is_required"]: raise FieldError(defn["api_key"], "zorunlu alan boş") return None if t == "number" or t == "money": try: v = Decimal(str(raw).replace(" ", "").replace(",", ".")) except InvalidOperation: raise FieldError(defn["api_key"], "sayı değil") if "min" in cfg and v < Decimal(str(cfg["min"])): raise FieldError(defn["api_key"], "alt sınırın altında") return {"amount": str(v), "currency": cfg.get("currency", "GBP")} if t == "money" else str(v) if t == "date": return date.fromisoformat(str(raw)).isoformat() if t == "bool": return str(raw).lower() in ("1", "true", "evet", "yes") if t in ("select", "multi_select"): allowed = {o["key"] for o in cfg["options"] if o["status"] == "active"} vals = raw if isinstance(raw, list) else [raw] bad = [x for x in vals if x not in allowed] if bad: raise FieldError(defn["api_key"], f"geçersiz seçenek: {bad}") return vals if t == "multi_select" else vals[0] return str(raw)[: cfg.get("max_length", 500)] def validate_custom(defs, payload): out, errors = {}, [] by_key = {d["api_key"]: d for d in defs if d["status"] != "archived"} for k, raw in payload.items(): d = by_key.get(k) if d is None: errors.append(FieldError(k, "tanımsız alan")) continue try: out[k] = coerce(d, raw) except FieldError as e: errors.append(e) return out, errors Birkaç ayrıntı önemli. Sayılar JSON'a ondalık metin olarak yazılır, kayan nokta olarak değil; para alanlarında yuvarlama hatası raporlarda kuruş kuruş birikir. Para tipi her zaman para birimiyle birlikte saklanır, çünkü aynı projede sterlin ve euro bütçesi olan alıcılar olabilir. Tanımsız anahtar sessizce yutulmaz, hata olarak döner; aksi hâlde yazım hatası yapılmış bir form alanı aylarca boş veri biriktirir. Toplu içe aktarmada ise hata davranışı farklı olmalı. Tek bir kötü hücre yüzünden binlerce satırlık bir dosyayı reddetmek yerine, geçerli alanlar yazılır, hatalı alanlar ayrı bir hata raporunda satır numarasıyla listelenir. Bunu baştan karar olarak yazmak, içe aktarma ekranındaki tartışmaları azaltır. ## Sorgu ve indeksleme JSONB'nin asıl gücü indekslemede ortaya çıkar. Genel amaçlı filtreler için tek bir GIN indeksi çoğu eşitlik sorgusunu karşılar: CREATE INDEX lead_custom_gin ON lead USING gin (custom jsonb_path_ops); -- "2+1 arayan lead'ler" (içerme operatörü GIN indeksini kullanır) SELECT id FROM lead WHERE tenant_id = 42 AND custom @> '{"room_type": "r_2_1"}'; Aralık sorguları (bütçe, tarih) GIN ile verimli çalışmaz. Sık kullanılan bir sayısal alan için ifade indeksi tanımlarım: CREATE INDEX lead_budget_idx ON lead (tenant_id, ((custom -> 'budget_gbp' ->> 'amount')::numeric)) WHERE custom ? 'budget_gbp'; SELECT id FROM lead WHERE tenant_id = 42 AND custom ? 'budget_gbp' AND (custom -> 'budget_gbp' ->> 'amount')::numeric >= 150000; Burada sorgudaki ifade, indeks tanımındakiyle birebir aynı olmalı; aksi hâlde planlayıcı indeksi kullanmaz. Bu yüzden bu ifadeleri elle yazmak yerine, alan tanımından sorgu parçası üreten küçük bir yardımcı fonksiyon kullanmak daha güvenlidir. Hangi alanlara ifade indeksi açılacağı da tahminle değil, gerçek filtre kullanım istatistiğiyle belirlenmeli. Her alan için indeks açmak yazma maliyetini gereksiz artırır. Raporlama tarafında JSONB'yi doğrudan analitik araca açmak yerine, alan tanımından otomatik üretilen düz bir görünüm (view) ya da gece güncellenen bir tablo vermeyi tercih ediyorum. Böylece veri ekibi `custom ->> 'room_type'` yazmak zorunda kalmaz, seçenek anahtarları da etiketlere çevrilmiş olarak gelir. ## Alanın yaşam döngüsü Özel alanlar eklenir, değişir ve eskir. Bu süreç kontrolsüz bırakılırsa tablo, kimsenin ne işe yaradığını hatırlamadığı alanlarla dolar. **Yeniden adlandırma** yalnızca etikete dokunur, anahtar sabit kalır. Bu yüzden sıfır risklidir ve yönetici ekranından serbestçe yapılabilir. **Tip değiştirme** ise bir göçtür. Metin olarak açılmış "bütçe" alanını para tipine çevirmek, mevcut değerlerin bir kısmının ("150-200 bin arası", "görüşülecek") dönüştürülemeyeceği anlamına gelir. Ben bunu yerinde yapmıyorum: yeni anahtarla yeni bir alan açılır, dönüştürülebilen değerler bir arka plan işiyle taşınır, dönüştürülemeyenler bir inceleme listesine düşer, eski alan `deprecated` olur. Tanım tablosundaki `version` alanı, hangi kayıtların hangi sürüme göre doğrulandığını izlemeyi kolaylaştırır. **Kaldırma** iki aşamalıdır. Önce alan `deprecated` olur: arayüzde gizlenir, yeni yazmalar reddedilir, ama veri durur. Bu aşamada alanı hâlâ kullanan otomasyon, entegrasyon ya da rapor varsa loglarda görünür. Belirlenen bekleme süresi sonunda alan `archived` olur ve değerler, kişisel veri içeriyorsa saklama politikasına göre temizlenir. Kişisel veri içeren bir alanın verisini "belki lazım olur" diye süresiz tutmak, KVKK açısından savunulması zor bir durumdur. ## Entegrasyonlar ve otomasyonlar Özel alanlar yalnızca ekranda yaşamaz. Reklam formlarından gelen cevaplar bu alanlara yazılır, otomasyon kuralları bu alanlara göre dallanır, satış kapandığında bazı değerler reklam platformuna geri bildirilir. Bu bağlantıların hepsi `api_key` üzerinden kurulmalı ve hangi entegrasyonun hangi alanı okuduğu ya da yazdığı bir yerde kayıtlı olmalı. Ben bunun için alan tanımına bağlı basit bir "kullanım" tablosu tutuyorum: hangi form eşlemesi, hangi iş akışı, hangi dışa aktarma bu alana dokunuyor. Bir alan `deprecated` yapılmak istendiğinde yönetici ekranı bu listeyi gösterir. "Bu alanı kaldırırsanız şu iki iş akışı ve bir form eşlemesi etkilenecek" uyarısı, sessiz kırılmaların büyük kısmını engeller. Kavramın kısa tanımını ve gayrimenkul satış operasyonundaki yerini kendi sitemdeki sözlükte özetledim: Custom field nedir? Bu yazı ise o tanımın veritabanı ve uygulama tarafındaki notları. ## SSS ### Hazır bir CRM kullanıyorsam bu tasarımın ne kadarı bana kalır? Depolama ve indeksleme kararları CRM'in kendisine aittir. Size kalan kısım alan adlandırma disiplini (değişmez API adı ile değişebilir etiket ayrımı), seçenek listelerinin yönetimi, kişisel veri alanlarının işaretlenmesi ve entegrasyonların hangi alanlara bağlı olduğunun kaydıdır. Bunlar hazır CRM'lerde de aynı ölçüde önemlidir. ### Kaç özel alan "çok fazla" sayılır? Kesin bir sayı yok, ama kullanım istatistiği iyi bir göstergedir. Son birkaç ayda kayıtların çok küçük bir kısmında doldurulmuş ya da hiçbir rapor ve otomasyonda kullanılmayan alanlar, kaldırma adayıdır. Kiracı başına bir üst sınır koymak da kontrolsüz büyümeyi frenler. ### JSONB sütunu büyüdükçe performans sorunu çıkar mı? Değerler küçük olduğu sürece genellikle hayır. Sorun, özel alanlara uzun metinler ya da dosya içerikleri yazılmaya başlandığında görülür; büyük JSONB değerleri satırın dışına taşınır ve her güncelleme tüm nesneyi yeniden yazar. Uzun notlar ve ekler ayrı tablolarda tutulmalı. ### Özel alan değişikliklerini denetim kaydına nasıl yansıtırım? JSONB nesnesini bütün olarak değil, anahtar bazında farkını alarak kaydetmek gerekir. Böylece "bütçe alanı kim tarafından, hangi değerden hangi değere değiştirildi" sorusu tek bir kayıtla cevaplanır. Kişisel veri işaretli alanlarda farkın kendisi de maskelenmeli. ### Bir alanı ne zaman gerçek sütuna taşımalıyım? Alan neredeyse her kayıtta doluysa, çoğu raporda ve filtrede kullanılıyorsa ve tipi artık değişmeyecek kadar oturmuşsa, gerçek sütun daha iyi bir yuvadır. Taşıma sırasında bir süre iki yere birden yazmak ve okuma tarafını sonra çevirmek, kesintisiz geçişi sağlar. Özel alanlar, CRM'in iş birimlerine "kendi verinizi tanımlayın" dediği yerdir. Bu esnekliği güvenli kılan şey depolama tekniğinden çok sözleşmedir: değişmez anahtarlar, açık tipler, tek bir doğrulama yolu, ölçüme dayalı indeksler ve kontrollü bir yaşam döngüsü. Bunlar baştan kurulduğunda, "bir alan daha ekleyebilir miyiz?" sorusu bir risk değil, sıradan bir iş hâline gelir.

ORM [BREAKING] Fixed postgres-js transparent parsers set by drizzle() constructors breaking driver's own serializers for timestamptz, timestamp, date, time, json, jsonb types (fixes #3171, #4426, ...

PostgreSQL의 JSONB 데이터를 실제로 다룰 때 필요한 과정을 하나의 지원 티켓 예제로 정리했습니다. 중첩된 값을 조회하는 ->와 ->>의 차이, @>를 이용한 포함 검색, jsonb_set()으로 문서 전체를 교체하지 않고 특정 값만 수정하는 방법을 다룹니다. 40,004개의 레코드로 GIN 인덱스를 테