PostgreSQL Extension'ları: pg_stat_statements, pg_trgm, pgcrypto, citext, unaccent ve Managed Ortamlarda Extension

Sürüm notu: Bu yazıdaki örnekler PostgreSQL 18.7 (resmî postgres:18 Docker image'ı) üzerinde, 02'de kurduğumuz Bookstore lab'ında çalıştırılmıştır; pg_cron 1.6.8 ve pg_upgrade denemeleri ayrı, geçici container'larda yapılmıştır.

Önce Tahmin Et

Sitedeki serilerin ortak örnek uygulaması Bookstore (bir online kitapçı) için Emre iki extension eklemek istiyor: e-posta karşılaştırması için citext, shared buffers'ı incelemek için pg_buffercache. Lab'da veritabanının sahibi olan, ama superuser olmayan bookstore_owner rolüyle bağlanıyor. Aynı gün bir ekip arkadaşı da yeni açtığı boş bir PostgreSQL 18 container'ında, bu sefer superuser ile, 07'de kullandığımız pg_stat_statements'ı kuruyor:

/* Emre, bookstore_owner ile */
CREATE EXTENSION citext;
CREATE EXTENSION pg_buffercache;

/* ekip arkadaşı, yeni container'da postgres ile */
CREATE EXTENSION pg_stat_statements;
SELECT count(*) FROM pg_stat_statements;

Dört komuttan hangileri hata verir? İkisi de "extension kur" komutu; biri neden çalışsın da diğeri çalışmasın?

CREATE EXTENSION
ERROR:  permission denied to create extension "pg_buffercache"
HINT:  Must be superuser to create this extension.

CREATE EXTENSION
ERROR:  pg_stat_statements must be loaded via "shared_preload_libraries"

citext kuruldu, pg_buffercache reddedildi. pg_stat_statements ise kuruldu ama kullanılamadı. Üç farklı sonucun arkasında extension mekanizmasının üç ayrı kuralı var: trusted/untrusted ayrımı, sunucu açılırken yüklenmesi gereken kütüphaneler ve CREATE EXTENSION'ın aslında yalnızca SQL nesneleri oluşturduğu gerçeği. Bu yazıda seride tek tek kullandığımız extension'ların mekanizmasını, gündelik işlerde kullanılan seti, pg_cron'u, büyük extension'ların lisans farklarını ve managed servislerdeki kısıtları görüyoruz.

Extension Nedir: Paket, Sürüm ve Bağımlılık

Bir extension, sunucunun dosya sisteminde duran bir paket: bir control dosyası (adı, varsayılan sürümü, gerekiyorsa paylaşılan kütüphanesi), her sürüm için bir SQL script'i ve çoğu zaman C ile yazılmış bir .so kütüphanesi. CREATE EXTENSION bu script'i o veritabanında çalıştırır ve oluşan function, type, operator gibi nesneleri extension'a bağlar. Yani kurulum veritabanı başına yapılır; bookstore'da kurduğun extension postgres veritabanında yoktur.

PostgreSQL ile birlikte dağıtılan extension'lara contrib modülleri (PostgreSQL kaynak kodunun contrib klasöründe geliştirilen, çekirdek ekibin bakımını yaptığı ek modüller) denir; resmî Docker image'ında hepsi hazır. pg_cron, PostGIS, pgvector gibi üçüncü parti extension'lar ise ayrıca kurulmalı. 07'den beri lab'da biriken liste ve sunucunun sunduğu seçenekler:

docker exec -it bookstore-pg psql -U bookstore_owner -d bookstore -f /lab/13-extensions.sql

        Name        | Version | Default version |   Schema   |                              Description
--------------------+---------+-----------------+------------+------------------------------------------------------------------------
 btree_gist         | 1.8     | 1.8             | public     | support for indexing common datatypes in GiST
 pg_stat_statements | 1.12    | 1.12            | public     | track planning and execution statistics of all SQL statements executed
 pg_trgm            | 1.6     | 1.6             | public     | text similarity measurement and index searching based on trigrams
 pgstattuple        | 1.5     | 1.5             | public     | show tuple-level statistics
 plpgsql            | 1.0     | 1.0             | pg_catalog | PL/pgSQL procedural language
 unaccent           | 1.1     | 1.1             | public     | text search dictionary that removes accents

 available | trusted
-----------+---------
        46 |      21

Extension sürümü ile kütüphane sürümü ayrı şeyler. Minor update (18.6'dan 18.7'ye) yeni .so dosyasını getirir, ama veritabanındaki SQL nesneleri ALTER EXTENSION ... UPDATE çalıştırılana kadar eski sürümde kalır. Bunu geçici bir veritabanında pg_trgm'yi eski sürümüyle kurarak gördük:

CREATE EXTENSION pg_trgm VERSION '1.3';   /* 37 nesne */
ALTER EXTENSION pg_trgm UPDATE;           /* 1.3 -> 1.4 -> 1.5 -> 1.6, 47 nesne */
SELECT source, target, path FROM pg_extension_update_paths('pg_trgm')
WHERE target = '1.6' AND path IS NOT NULL ORDER BY source;

 source | target |               path
--------+--------+-----------------------------------
 1.0    | 1.6    | 1.0--1.1--1.2--1.3--1.4--1.5--1.6
 1.1    | 1.6    | 1.1--1.2--1.3--1.4--1.5--1.6
 1.2    | 1.6    | 1.2--1.3--1.4--1.5--1.6
 1.3    | 1.6    | 1.3--1.4--1.5--1.6
 1.4    | 1.6    | 1.4--1.5--1.6
 1.5    | 1.6    | 1.5--1.6

Güncelleme, sürümler arasındaki script'lerin sırayla çalışmasıdır; 1.3'ten 1.6'ya giderken on yeni nesne eklendi. installed_version <> default_version olan satırları listeleyen sorgu (script'te var) upgrade öncesi kontrol listesinin ilk maddesi olmalı. Extension'a bağlı nesneler de korunur; 10'da oluşturduğumuz trigram index'i varken pg_trgm silinemiyor:

ERROR:  cannot drop extension pg_trgm because other objects depend on it
DETAIL:  index ix_books_title_trgm depends on operator class gin_trgm_ops for access method gin
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

CASCADE burada index'i de siler; production'da bu ipucunu okumadan uygulama. Extension'ın nesnelerini hangi schema'ya koyacağını CREATE EXTENSION ... SCHEMA extensions ile seçebilirsin. Lab'da hepsi public'te; ayrı bir schema, uygulama tablolarıyla extension function'larını birbirinden ayırır ama o schema'yı search_path'e eklemeyi gerektirir.

Trusted ve Untrusted: Kim Kurabilir?

PostgreSQL 13'ten beri extension'lar ikiye ayrılıyor. Trusted olanları, veritabanında CREATE yetkisi olan superuser olmayan bir rol kurabilir; script superuser yetkisiyle çalışır ama extension güvenli kabul edildiği için bu yetki dışarı sızmaz. Untrusted olanlar (sunucunun belleğini, dosya sistemini ya da diğer veritabanlarının verisini gösterebilenler) yalnızca superuser ile kurulur. Lab'daki 46 extension'ın 21'i trusted:

btree_gin, btree_gist, citext, cube, dict_int, fuzzystrmatch, hstore, intarray, isn, lo, ltree, pg_trgm, pgcrypto,
plpgsql, seg, tablefunc, tcn, tsm_system_rows, tsm_system_time, unaccent, uuid-ossp

Tahminin ilk yarısının cevabı bu: citext listede, pg_buffercache değil. Bu seride kurduğumuz pg_stat_statements, pgstattuple, pageinspect ve pg_buffercache untrusted olduğu için superuser ile kurulmuştu; pg_trgm, unaccent ve btree_gist'i ise owner rolü kendisi kurabildi. Uygulama rolü bookstore_app, trusted bir extension'ı da kuramıyor, çünkü veritabanında CREATE yetkisi yok:

ERROR:  permission denied to create extension "hstore"
HINT:  Must have CREATE privilege on current database to create this extension.

Doğru olan da bu: extension kurmak bir şema değişikliği, migration'larla birlikte owner rolüyle yapılmalı. Entity Framework Core (EF Core) kullanıyorsan provider'ın modelBuilder.HasPostgresExtension("pg_trgm") çağrısı migration'a extension'ı kuran komutu ekler; migration'ı owner rolüyle çalıştırdığımız için (12) trusted extension'larda sorun çıkmaz.

shared_preload_libraries Gerektirenler

Tahminin ikinci yarısı: CREATE EXTENSION pg_stat_statements yalnızca view'ları ve function'ları oluşturur. Sorguları sayan kod ise bir kütüphane; her backend'in sorgularını görebilmesi ve shared memory'de yer ayırabilmesi için sunucu açılırken yüklenmesi gerekiyor. Ayar shared_preload_libraries ve değişikliği ancak restart uygular. 07'de lab'da bu yüzden container'ı yeniden başlatmıştık:

ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements';
/* docker restart bookstore-pg */

Bu gruba girenler genelde arka planda çalışan ya da her sorguya dokunan extension'lar: pg_stat_statements, pg_cron, TimescaleDB, pg_partman'ın background worker'ı. auto_explain gibi yalnızca kütüphane olan modüller CREATE EXTENSION gerektirmez; 07'de session_preload_libraries ile, yeni oturumlarda yüklenecek şekilde açmıştık. Ayarın kendisi tek bir liste olduğu için ikinci bir kütüphane eklerken mevcut değeri ezme: 'pg_stat_statements,pg_cron' yaz. Listeye var olmayan bir kütüphane yazarsan sunucu açılmaz; restart öncesi değeri iki kez kontrol et.

Gündelik Set: pg_trgm, unaccent, citext, pgcrypto, btree_gist

pg_trgm (06 ve 10) ILIKE '%...%' aramasına GIN (Generalized Inverted Index; bir satırdaki birden çok değeri ayrı ayrı index'leyen yapı) index kazandırır ve benzerlik araması yapar; unaccent (10) Türkçe karaktersiz aramayı sağlar; btree_gist (04) EXCLUDE constraint'inde = ile range'i aynı GiST (Generalized Search Tree; range ve geometri gibi tipler için genel amaçlı index yapısı) index'inde birleştirmeye yarar. Bunları seride kullandık. Yeni olan ikisi:

citext büyük/küçük harfe duyarsız bir metin tipi; karşılaştırmadan önce iki tarafa lower() uygular. E-posta kolonunda unique constraint için pratik:

CREATE TABLE newsletter_subscribers (email citext PRIMARY KEY, city citext);
INSERT INTO newsletter_subscribers VALUES ('Ada@Example.com', 'İSTANBUL');
INSERT INTO newsletter_subscribers VALUES ('ada@example.com', 'istanbul');
SELECT city = 'istanbul' AS istanbul, 'IŞIK'::citext = 'ışık' AS isik FROM newsletter_subscribers;

ERROR:  duplicate key value violates unique constraint "newsletter_subscribers_pkey"
DETAIL:  Key (email)=(ada@example.com) already exists.
 istanbul | isik
----------+------
 t        | f

E-postada beklendiği gibi çalıştı. Türkçe metinde ise lower() veritabanının locale'ini kullandığı için (lab'da en_US.utf8) "IŞIK", "işik"e dönüşüyor ve "ışık" ile eşleşmiyor. 03'te gördüğümüz nondeterministic ICU (International Components for Unicode; dil kurallarını içeren Unicode kütüphanesi) collation'ı bu iş için daha esnek; PostgreSQL dokümanı da yeni tasarımlarda citext yerine nondeterministic collation'ı düşünmeyi öneriyor. citext'i daha çok eski şemalarda göreceksin.

pgcrypto hash ve şifreleme function'ları sağlıyor (digest, hmac, crypt, pgp_sym_encrypt). Eski projelerde onu UUID (her sistemde benzersiz olacak şekilde üretilen 128 bitlik kimlik) üretmek için kurulmuş görürsen bil ki gen_random_uuid() PostgreSQL 13'ten beri çekirdekte (\df gen_random_uuid şeması pg_catalog gösteriyor). Veritabanında şifreleme yaparken dikkat edilecek nokta anahtar: anahtar SQL metninin içinde gidiyorsa log'a da gider. Superuser oturumunda log_min_duration_statement = 0 açıp denedik:

LOG:  duration: 1.273 ms  statement: SELECT length(pgp_sym_encrypt('Grace Hopper, +90 555 000 00 00', 'example-key')) > 0 AS encrypted;

pg_stat_statements sabitleri $1, $2 olarak normalize ettiği için orada görünmüyor, ama yavaş sorgu log'u değeri ve anahtarı açık yazıyor. Parametreli sorgu bunu azaltır (log'da parametre değerleri yine görünebilir), asıl çözüm şifrelemeyi uygulamada yapmak: .NET'te ASP.NET Core Data Protection ya da bir key vault ile. Parolaları ise hiç veritabanında hash'leme; ASP.NET Core Identity bunu uygulama tarafında yapıyor.

pg_cron ile Zamanlanmış İş

pg_cron, cron syntax'ıyla SQL çalıştıran bir background worker. Resmî image'da yok; PostgreSQL'in kendi Debian paket deposundan (PGDG, PostgreSQL Global Development Group'un apt deposu; resmî image zaten bu depoyu kullanıyor) kurulan küçük bir image ile denedik:

# PostgreSQL 18 + pg_cron, resmî image'ın kullandığı PGDG apt deposundan
FROM postgres:18
RUN apt-get update \
    && apt-get install -y --no-install-recommends postgresql-18-cron \
    && rm -rf /var/lib/apt/lists/*

docker build -t bookstore/postgres-cron:18 db/13-pg-cron
docker run -d --name bookstore-cron -e POSTGRES_PASSWORD=<password> bookstore/postgres-cron:18 `
    -c shared_preload_libraries=pg_cron -c cron.database_name=bookstore -c cron.timezone=UTC

pg_cron'un metadata'sı yalnızca cron.database_name ile verilen veritabanında durur ve preload şart; Azure'un dokümanı da preload edilmeden yapılan CREATE EXTENSION'ın hata verdiğini yazıyor. cron.sql, 09'daki outbox'ın küçük bir kopyasını oluşturup yedi günden eski işlenmiş mesajları silen bir job tanımlıyor:

SELECT cron.schedule('purge-processed-outbox', '* * * * *',
    $$DELETE FROM outbox_messages WHERE processed_at < now() - interval '7 days'$$);

 jobid |        jobname         | schedule  | database  | username | active
-------+------------------------+-----------+-----------+----------+--------
     1 | purge-processed-outbox | * * * * * | bookstore | postgres | t

 jobid |  status   | return_message | start_time
-------+-----------+----------------+------------
     1 | succeeded | DELETE 900     | 03:00:00

Bir dakika sonra cron.job_run_details 900 satırın silindiğini gösterdi. pg_cron, veriye yakın bakım işleri için iyi: eski kayıtları silmek, materialized view yenilemek, bir partition'ı düşürmek. Aynı job'un üst üste binmesini de engelliyor; önceki çalışma bitmeden sıradaki başlamıyor. Ama iş bir HTTP çağrısı, e-posta ya da retry ve izleme gerektiren bir akışsa yeri uygulama: Background Job'lar ve Scheduled Job'lar serisinin Hangfire ve Quartz.NET karşılaştırması bu kararın ayrıntılarını veriyor. Bir not daha: cron.job_run_details kendiliğinden temizlenmez, onu temizleyen bir job da eklemek gerekiyor.

Büyük Extension'lar: PostGIS, pgvector, TimescaleDB, Citus

Bu dördü PostgreSQL'i başka bir ürün gibi kullandıran extension'lar ve her biri kendi başına bir konu; burada yalnızca seçim yaparken bilmen gerekenler var.

PostGIS coğrafi veri için geometry/geography tipleri, mekânsal index (GiST) ve yüzlerce function ekliyor; Bookstore'da "en yakın teslimat noktası" gibi bir ihtiyaç doğarsa ilk adres. pgvector embedding'leri saklayıp benzerlik araması yapıyor; kurulumu ve HNSW (Hierarchical Navigable Small World; yaklaşık en yakın komşu araması için graf tabanlı index) index'i RAG (Retrieval-Augmented Generation; modelin cevabını aranıp bulunan belgelerle besleme) serisinin pgvector yazısında anlatıldı. İkisinin de lisansı PostgreSQL lisansına benzer şekilde serbest ve büyük managed servislerin hepsinde var.

TimescaleDB ve Citus'ta ise lisans konuşulmalı. TimescaleDB'nin kaynak kodu iki lisanslı: çekirdeği Apache 2.0, sıkıştırma gibi bazı özellikleri Timescale License (TSL; kaynak kodu açık ama başkasına hizmet olarak sunmayı kısıtlayan lisans) altında. Azure Database for PostgreSQL dokümanına göre Azure yalnızca Apache 2.0 sürümünü sunuyor; yani lokalde çalışan bir özellik managed serviste olmayabilir. Citus (tabloları birden çok node'a dağıtan extension) ise AGPL (GNU Affero General Public License; ağ üzerinden hizmet olarak sunulan değiştirilmiş kodun da paylaşılmasını şart koşan lisans) 3.0 ile dağıtılıyor. Amazon RDS (Relational Database Service; Amazon'un yönetilen veritabanı servisi) for PostgreSQL'in extension listesinde PostgreSQL 18 için TimescaleDB ve Citus yer almıyor. Sharding ve Citus, Veritabanı Performansı ve Scaling serisinin konusu.

Managed Servislerde Extension: Azure ve RDS

Bu bölümdeki bilgiler Microsoft Learn ve AWS (Amazon'un bulut platformu) dokümanlarından; bu yazı için bulut hesabı açılmadı, komutlar çalıştırılmadı. İki serviste de superuser yok ve kurulabilecek extension'lar servisin derleyip sunduklarıyla sınırlı.

Azure Database for PostgreSQL flexible server'da bir extension önce azure.extensions server parametresiyle izin listesine eklenmeli; listede olmayan bir extension için CREATE EXTENSION, ALTER EXTENSION ve DROP EXTENSION hata verir. Preload gerektirenler ayrıca shared_preload_libraries parametresine eklenir ve sunucu yeniden başlatılır. Kurulum azure_pg_admin rolünün üyesiyle yapılır. Dokümandaki CLI komutu:

az postgres flexible-server parameter set `
    --resource-group <resource_group> --server-name <server> `
    --name azure.extensions --value "pg_trgm,unaccent,btree_gist,pg_stat_statements"

Amazon RDS for PostgreSQL'de karşılığı rds.allowed_extensions parametresi. Varsayılanı *, yani servisin sunduğu her extension kurulabilir; listeyi daraltınca listede olmayanlar için CREATE EXTENSION "not specified in rds.allowed_extensions" ipucuyla reddedilir. Dinamik bir parametre, restart istemez. RDS 13 ve sonrasında trusted extension'ları CREATE yetkisi olan kullanıcı kurabilir, diğerleri rds_superuser rolü ister. pg_cron gibi preload gerektirenler parameter group'taki shared_preload_libraries'a eklenip instance yeniden başlatılır. AWS dokümanı ayrıca motor upgrade'inin extension'ları kendiliğinden güncellemediğini, bunu upgrade sonrasında elle yapmak gerektiğini belirtiyor.

Pratik sonuç: bir extension'a bağımlı bir tasarım yapmadan önce hedef servisin o major sürüm için listesine bak. Lokaldeki Docker image'ında her şey kurulabiliyor; production'da kurulamayan bir extension'ı geç fark etmek pahalı.

Üretimde Dikkat

Extension'ları migration'ın parçası yap: CREATE EXTENSION IF NOT EXISTS owner rolüyle, şema değişiklikleriyle aynı yerde. Bir ortamda elle kurulmuş, diğerinde unutulmuş extension en sık görülen "lokalde çalışıyor" sebeplerinden biri.

Minor update sonrası installed_version <> default_version sorgusunu çalıştır ve gereken ALTER EXTENSION ... UPDATE'leri planla. Release notes bazen belirli bir extension için güncelleme ya da reindex ister.

pg_stat_statements ve pg_cron gibi arka planda çalışan extension'ların bir maliyeti var: shared memory, background worker sayısı (max_worker_processes) ve kendi tabloları. Kurduğun her extension'ı ne için kurduğunu bir yerde not et.

Sık Düşülen Tuzaklar

Doğrulanmamış bir extension'ı production'a kurup major upgrade'i kilitlemek. pg_upgrade, eski cluster'ın kullandığı her kütüphanenin yeni sürümde de olmasını ister. PostgreSQL 17'de pg_cron kurulu bir cluster'ı, pg_cron paketi olmayan 18'e taşımayı denedik:

Checking for presence of required libraries                   fatal

Your installation references loadable libraries that are missing from the
new installation.  You can add these libraries to the new installation,
or remove the functions using them from the old installation.

could not load library "$libdir/pg_cron": ERROR:  could not access file "$libdir/pg_cron": No such file or directory
In database: bookstore

Lokalde çözüm paketi kurmak; managed serviste ise extension'ın yeni sürüm için desteklenmesini beklemek ya da extension'ı kaldırmak. Azure dokümanı, yerinde major upgrade'in TimescaleDB, dblink, postgres_fdw gibi bazı extension'ları her sürümde desteklemediğini belirtiyor. Az bilinen bir extension, upgrade'i aylarca erteletebilir.

Untrusted extension için uygulama rolüne superuser vermek. pg_buffercache gerekiyorsa superuser ile kur ve function'ları üzerinde yetkiyi ayrıca ver; pg_stat_statements için 07'deki gibi pg_read_all_stats rolü yeterli.

Eski bir projede uuid-ossp ya da pgcrypto'yu yalnızca UUID için görmek ve yeni projeye taşımak: gen_random_uuid() ve 18'in uuidv7()'si çekirdekte.

Küçük Bir Deneme

★☆☆ Lab'da bookstore_owner ile CREATE SCHEMA extensions oluştur, hstore'u bu schema'ya kur ve search_path'i değiştirmeden 'a=>1'::hstore sorgusunu çalıştır. Hangi hatayı aldın, ALTER ROLE ... SET search_path ile nasıl düzelttin?

★★☆ pg_cron container'ında cron.job_run_details'ten üç günden eski kayıtları silen ikinci bir job ekle. Job'u cron.alter_job ile her gece 03:30'a çek ve cron.job'da değişikliği doğrula.

★★★ Kendi kullandığın bir bulut servisinin (Azure ya da RDS) PostgreSQL 18 extension listesini bu yazıdaki \dx çıktısıyla karşılaştır. Bookstore'un ihtiyaç duyduğu extension'lardan biri eksik olsaydı tasarımı nasıl değiştirirdin?

Bir sonraki yazıda güvenlik ve operasyon temellerine geçiyoruz: role modeli ve GRANT'lar, pg_hba.conf, SCRAM (Salted Challenge Response Authentication Mechanism; parolayı ağda açık göndermeden doğrulayan yöntem) ve TLS (Transport Layer Security; bağlantıyı şifreleyen protokol), pg_dump ile yedek ve geri yükleme testi, WAL (Write-Ahead Log; değişikliklerin veri dosyalarından önce yazıldığı log) arşiviyle belirli bir ana geri dönmek ve pg_upgrade ile major upgrade.