📌 Ne Zaman Kullanılır?

✅ SaaS uygulaması, GIS/mekansal veri, JSONB, analitik, güçlü SQL
⚠️ Basit CRUD için MySQL yeterli olabilir
❌ Embedded kullanım (SQLite), esnek schema (MongoDB)

Önerilen Kullanım: Node.js/Django/FastAPI + PostgreSQL (modern web stack) Alternatifler: MySQL (Laravel/PHP), CockroachDB (dağıtık), Supabase (hosted PG)

PostgreSQL

Kurulum (Installation)

Ubuntu

bash

sudo apt update
sudo apt install postgresql postgresql-contrib -y
sudo systemctl start postgresql
sudo systemctl enable postgresql

# Kullaniciya gecis / Switch to postgres user
sudo -u postgres psql

Docker

bash

docker run -d \
  --name postgres-dev \
  -e POSTGRES_USER=appuser \
  -e POSTGRES_PASSWORD=secret123 \
  -e POSTGRES_DB=myapp \
  -p 5432:5432 \
  postgres:16

# Baglanti
docker exec -it postgres-dev psql -U appuser -d myapp

psql CLI Komutları

Command	Description
`\l`	Veritabanlarini listele / List databases
`\c dbname`	Veritabanina baglan / Connect to database
`\dt`	Tablolari listele / List tables
`\d tablename`	Tablo yapisini göster / Describe table
`\di`	Indeksleri listele / List indexes
`\du`	Kullanicilari listele / List users
`\df`	Fonksiyonlari listele / List functions
`\x`	Genisletilmis çıktı / Expanded display toggle
`\timing`	Sorgu suresini göster / Show query timing
`\q`	Cikis / Quit

Veri Tipleri (MySQL ile Farklar)

PostgreSQL Type	Description	MySQL Equivalent
`SERIAL`	Auto-increment integer	`INT AUTO_INCREMENT`
`BIGSERIAL`	Auto-increment bigint	`BIGINT AUTO_INCREMENT`
`UUID`	Universally unique identifier	`VARCHAR(36)`
`BOOLEAN`	True/false (native)	`TINYINT(1)`
`JSONB`	Binary JSON (indexed, fast queries)	`JSON`
`ARRAY`	Native array type	No equivalent
`CIDR` / `INET`	IP address types	`VARCHAR`
`TSQUERY` / `TSVECTOR`	Full-text search types	`FULLTEXT`
`INTERVAL`	Time duration	No direct equivalent
`HSTORE`	Key-value pairs	No equivalent
`NUMERIC(p,s)`	Arbitrary precision	`DECIMAL(p,s)`

CRUD

sql

-- CREATE TABLE
CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) NOT NULL UNIQUE,
    tags TEXT[] DEFAULT '{}',                -- Array tipi
    metadata JSONB DEFAULT '{}',            -- JSONB tipi
    created_at TIMESTAMP DEFAULT NOW()
);

-- INSERT
INSERT INTO users (name, email, tags, metadata)
VALUES ('Ali', 'ali@test.com', ARRAY['admin','editor'], '{"role": "admin", "level": 5}');

-- SELECT
SELECT * FROM users WHERE 'admin' = ANY(tags);
SELECT * FROM users WHERE metadata->>'role' = 'admin';
SELECT * FROM users WHERE metadata @> '{"level": 5}';

-- UPDATE
UPDATE users SET metadata = metadata || '{"verified": true}' WHERE id = 1;

-- DELETE
DELETE FROM users WHERE id = 1;

-- UPSERT (INSERT ... ON CONFLICT)
INSERT INTO users (name, email) VALUES ('Ali', 'ali@test.com')
ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name;

Gelismis Ozellikler (Advanced Features)

CTE (Common Table Expressions)

sql

WITH active_users AS (
    SELECT id, name, email FROM users WHERE created_at > NOW() - INTERVAL '30 days'
),
user_orders AS (
    SELECT user_id, COUNT(*) AS order_count, SUM(total) AS total_spent
    FROM orders GROUP BY user_id
)
SELECT au.name, COALESCE(uo.order_count, 0) AS orders, COALESCE(uo.total_spent, 0) AS spent
FROM active_users au
LEFT JOIN user_orders uo ON au.id = uo.user_id
ORDER BY spent DESC;

Window Functions

sql

-- Satir numarasi, siralama / Row number, ranking
SELECT name, total,
    ROW_NUMBER() OVER (ORDER BY total DESC) AS row_num,
    RANK() OVER (ORDER BY total DESC) AS rank,
    DENSE_RANK() OVER (ORDER BY total DESC) AS dense_rank
FROM orders;

-- Kategoriye gore siralama / Partition by category
SELECT category_id, name, price,
    AVG(price) OVER (PARTITION BY category_id) AS avg_category_price,
    price - AVG(price) OVER (PARTITION BY category_id) AS diff_from_avg
FROM products;

JSONB Sorguları

sql

-- JSONB icinde arama / Query inside JSONB
SELECT * FROM users WHERE metadata ? 'role';              -- Key var mi
SELECT * FROM users WHERE metadata @> '{"role":"admin"}'; -- Icerir mi
SELECT metadata->>'role' AS role FROM users;              -- Deger cek
SELECT * FROM users WHERE metadata->'level' > '3';        -- Karsilastir

-- JSONB guncelle / Update JSONB
UPDATE users SET metadata = jsonb_set(metadata, '{role}', '"editor"') WHERE id = 1;

ARRAY Islemleri

sql

-- Array sorgulari / Array queries
SELECT * FROM users WHERE 'admin' = ANY(tags);
SELECT * FROM users WHERE tags @> ARRAY['admin'];   -- tags, admin icerir mi
SELECT * FROM users WHERE tags && ARRAY['admin','editor']; -- herhangi biri var mi
SELECT unnest(tags) AS tag FROM users WHERE id = 1;  -- Arrayi satirlara ac

Indeks Turleri (Index Types)

sql

-- B-tree (varsayilan / default) -- esitlik ve aralik sorgulari
CREATE INDEX idx_users_email ON users(email);

-- Hash -- sadece esitlik sorgulari / equality only
CREATE INDEX idx_users_email_hash ON users USING HASH(email);

-- GIN -- JSONB, Array, full-text icin
CREATE INDEX idx_users_metadata ON users USING GIN(metadata);
CREATE INDEX idx_users_tags ON users USING GIN(tags);

-- GiST -- geometrik/mekansal veri / geometric/spatial
CREATE INDEX idx_locations_coords ON locations USING GIST(coordinates);

-- Partial index -- kosullu indeks
CREATE INDEX idx_active_users ON users(email) WHERE status = 'active';

Full-text Search

sql

-- Arama vektoru olustur / Create search vector
ALTER TABLE products ADD COLUMN search_vector tsvector;

UPDATE products SET search_vector =
    to_tsvector('english', COALESCE(name,'') || ' ' || COALESCE(description,''));

CREATE INDEX idx_products_search ON products USING GIN(search_vector);

-- Arama yap / Search
SELECT name, ts_rank(search_vector, query) AS rank
FROM products, to_tsquery('english', 'hydraulic & press') AS query
WHERE search_vector @@ query
ORDER BY rank DESC;

Extensions

sql

-- UUID desteği / UUID support
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
SELECT uuid_generate_v4();

-- Bulanik metin eslestirme / Fuzzy text matching
CREATE EXTENSION IF NOT EXISTS pg_trgm;
SELECT * FROM users WHERE name % 'Ail';  -- trigram similarity
SELECT similarity('Ali', 'Ail');         -- 0.5

-- Mekansal veri / Spatial data (PostGIS)
CREATE EXTENSION IF NOT EXISTS postgis;
SELECT ST_Distance(
    ST_MakePoint(28.9784, 41.0082)::geography,  -- Istanbul
    ST_MakePoint(32.8597, 39.9334)::geography    -- Ankara
);

Yedekleme (Backup & Restore)

bash

# Yedek al / Backup
pg_dump -U appuser -d myapp -F c -f myapp_backup.dump   # Custom format
pg_dump -U appuser -d myapp > myapp_backup.sql            # Plain SQL

# Geri yukle / Restore
pg_restore -U appuser -d myapp myapp_backup.dump
psql -U appuser -d myapp < myapp_backup.sql

Django / Flask ile Kullanım

python

# Django settings.py
DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'NAME': 'myapp',
        'USER': 'appuser',
        'PASSWORD': 'secret123',
        'HOST': '127.0.0.1',
        'PORT': '5432',
    }
}

# Flask with SQLAlchemy
from flask_sqlalchemy import SQLAlchemy
app.config['SQLALCHEMY_DATABASE_URI'] = 'postgresql://appuser:secret123@localhost:5432/myapp'

Transactions & Isolation

PostgreSQL her sorguyu varsayılan olarak bir transaction icinde calistirir. Birden fazla islemi atomik hale getirmek için acik transaction bloklari kullanilir.

BEGIN / COMMIT / ROLLBACK

sql

-- Basarili transaction
BEGIN;
    UPDATE accounts SET balance = balance - 500 WHERE id = 1;
    UPDATE accounts SET balance = balance + 500 WHERE id = 2;
COMMIT;

-- Hata durumunda geri al
BEGIN;
    UPDATE accounts SET balance = balance - 500 WHERE id = 1;
    -- Bir hata olustugunu varsayalim
ROLLBACK;

SAVEPOINT

Transaction icinde ara kayit noktalari olusturarak, tüm transaction'i geri almadan belirli bir noktaya donebilirsiniz.

sql

BEGIN;
    INSERT INTO orders (user_id, total) VALUES (1, 250);
    SAVEPOINT order_created;

    INSERT INTO order_items (order_id, product_id, qty) VALUES (1, 10, 2);
    -- Bu adimda hata olursa sadece bu noktaya don
    ROLLBACK TO SAVEPOINT order_created;

    -- Farkli bir kayit dene
    INSERT INTO order_items (order_id, product_id, qty) VALUES (1, 20, 1);
COMMIT;

Isolation Levels

PostgreSQL dort farkli isolation seviyesi destekler. Varsayilan READ COMMITTED seviyesidir.

sql

-- Transaction isolation seviyesini ayarla
BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;      -- Varsayilan
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;     -- Snapshot isolation
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;        -- En katı seviye

-- Oturum bazinda varsayilan seviyeyi degistir
SET default_transaction_isolation = 'serializable';

Seviye	Dirty Read	Non-repeatable Read	Phantom Read	Kullanım Alani
`READ COMMITTED`	Hayir	Evet	Evet	Genel amacli (varsayılan)
`REPEATABLE READ`	Hayir	Hayir	Hayir*	Raporlama, tutarli okuma
`SERIALIZABLE`	Hayir	Hayir	Hayir	Finansal islemler, kritik veri butunlugu

*PostgreSQL'de REPEATABLE READ, SSI (Serializable Snapshot Isolation) kullanir ve phantom read'leri de onler.

Advisory Locks

Uygulama seviyesinde kilit mekanizmasi. Tablo veya satir kilitlemeden, belirli kaynaklari koordine etmek için kullanilir.

sql

-- Session bazli advisory lock (oturum bitene kadar kalir)
SELECT pg_advisory_lock(12345);       -- Kilitle
SELECT pg_advisory_unlock(12345);     -- Kilidi ac

-- Transaction bazli advisory lock (transaction bitince otomatik acilir)
SELECT pg_advisory_xact_lock(12345);

-- Non-blocking versiyon (beklemeden sonuc doner)
SELECT pg_try_advisory_lock(12345);   -- true/false doner

-- Ornek: Ayni anda sadece bir worker cron job calistirsin
BEGIN;
    SELECT pg_try_advisory_xact_lock(42) AS acquired;
    -- acquired = true ise isi yap, false ise atla
COMMIT;

Row Level Security (RLS)

Row Level Security, tablo satirlarina erisimi policy'ler uzerinden kontrol etmenizi sağlar. Multi-tenant uygulamalarda her kiracinin sadece kendi verisini gormesi için idealdir.

RLS Etkinlestirme

sql

-- Tablo uzerinde RLS'yi etkinlestir
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

-- Tablo sahibi (owner) varsayilan olarak RLS'den muaftir
-- Tablo sahibini de zorunlu kilmak icin:
ALTER TABLE orders FORCE ROW LEVEL SECURITY;

Policy Oluşturma

sql

-- Sadece kendi siparislerini gorebilsin
CREATE POLICY user_orders_select ON orders
    FOR SELECT
    USING (user_id = current_setting('app.current_user_id')::int);

-- INSERT icin kontrol (WITH CHECK)
CREATE POLICY user_orders_insert ON orders
    FOR INSERT
    WITH CHECK (user_id = current_setting('app.current_user_id')::int);

-- Tum islemler icin tek policy
CREATE POLICY user_orders_all ON orders
    FOR ALL
    USING (user_id = current_setting('app.current_user_id')::int)
    WITH CHECK (user_id = current_setting('app.current_user_id')::int);

Multi-tenant Uygulama Ornegi

sql

-- Tenant bazli tablo yapisi
CREATE TABLE tenant_data (
    id BIGSERIAL PRIMARY KEY,
    tenant_id INT NOT NULL,
    content TEXT,
    created_at TIMESTAMP DEFAULT NOW()
);

-- RLS etkinlestir
ALTER TABLE tenant_data ENABLE ROW LEVEL SECURITY;

-- Tenant isolation policy'si
CREATE POLICY tenant_isolation ON tenant_data
    FOR ALL
    USING (tenant_id = current_setting('app.tenant_id')::int)
    WITH CHECK (tenant_id = current_setting('app.tenant_id')::int);

-- Uygulama katmaninda tenant_id ayarla (her istekte)
SET app.tenant_id = '42';
SELECT * FROM tenant_data;  -- Sadece tenant_id = 42 olan satirlar doner

-- Admin rolu icin tum verilere erisim
CREATE POLICY admin_full_access ON tenant_data
    FOR ALL
    TO admin_role
    USING (true)
    WITH CHECK (true);

Partitioning

Büyük tablolari daha küçük, yonetilebilir parcalara bolmek için kullanilir. Sorgu performansini arttirir ve veri yonetimini kolaylastirir.

Range Partitioning

Tarih veya sayisal aralik bazli bolme. Zaman serisi verileri için idealdir.

sql

-- Ana tabloyu olustur
CREATE TABLE events (
    id BIGSERIAL,
    event_type VARCHAR(50),
    payload JSONB,
    created_at TIMESTAMP NOT NULL
) PARTITION BY RANGE (created_at);

-- Aylik partition'lar olustur
CREATE TABLE events_2025_01 PARTITION OF events
    FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');

CREATE TABLE events_2025_02 PARTITION OF events
    FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');

CREATE TABLE events_2025_03 PARTITION OF events
    FOR VALUES FROM ('2025-03-01') TO ('2025-04-01');

-- Varsayilan partition (araliga uymayan veriler icin)
CREATE TABLE events_default PARTITION OF events DEFAULT;

-- Sorgu otomatik olarak dogru partition'a yonlenir
SELECT * FROM events WHERE created_at >= '2025-02-01' AND created_at < '2025-03-01';

List Partitioning

Belirli deger listelerine gore bolme. Kategorik veriler için uygundur.

sql

CREATE TABLE orders_regional (
    id BIGSERIAL,
    region VARCHAR(20) NOT NULL,
    customer_id INT,
    total NUMERIC(10,2),
    created_at TIMESTAMP DEFAULT NOW()
) PARTITION BY LIST (region);

CREATE TABLE orders_europe PARTITION OF orders_regional
    FOR VALUES IN ('TR', 'DE', 'FR', 'UK');

CREATE TABLE orders_america PARTITION OF orders_regional
    FOR VALUES IN ('US', 'CA', 'BR', 'MX');

CREATE TABLE orders_asia PARTITION OF orders_regional
    FOR VALUES IN ('JP', 'KR', 'CN', 'IN');

Hash Partitioning

Verileri esit parcalara dagitirir. Belirli bir siralama gerektirmeyen büyük tablolar için kullanilir.

sql

CREATE TABLE logs (
    id BIGSERIAL,
    user_id INT NOT NULL,
    action VARCHAR(50),
    created_at TIMESTAMP DEFAULT NOW()
) PARTITION BY HASH (user_id);

CREATE TABLE logs_p0 PARTITION OF logs FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE logs_p1 PARTITION OF logs FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE logs_p2 PARTITION OF logs FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE logs_p3 PARTITION OF logs FOR VALUES WITH (MODULUS 4, REMAINDER 3);

Partition Pruning

PostgreSQL sorgu planlayicisi, WHERE kosuluna gore gereksiz partition'lari otomatik atlar. Bu ozelligi kontrol etmek için:

sql

-- Partition pruning'in aktif oldugunu dogrula (varsayilan acik)
SHOW enable_partition_pruning;

-- Sorgu planinda partition pruning'i gor
EXPLAIN ANALYZE SELECT * FROM events WHERE created_at = '2025-02-15';
-- Cikti: "Subplans Removed: 2" gibi bir ifade gosterir

Materialized Views

Normal view'lerden farkli olarak, sonuclari fiziksel olarak disk uzerinde saklar. Agir ve karmasik sorgularin sonuclarini cache'lemek için idealdir.

Oluşturma ve Yenileme

sql

-- Materialized view olustur
CREATE MATERIALIZED VIEW mv_monthly_sales AS
SELECT
    date_trunc('month', created_at) AS month,
    product_id,
    SUM(quantity) AS total_qty,
    SUM(quantity * price) AS total_revenue,
    COUNT(*) AS order_count
FROM order_items oi
JOIN orders o ON o.id = oi.order_id
WHERE o.status = 'completed'
GROUP BY date_trunc('month', created_at), product_id;

-- Materialized view uzerinde index olustur (performans icin)
CREATE UNIQUE INDEX idx_mv_monthly_sales
    ON mv_monthly_sales (month, product_id);

-- Veriyi yenile (tabloyu kilitler)
REFRESH MATERIALIZED VIEW mv_monthly_sales;

-- Eşzamanli yenileme (okuma sirasinda da calisir, UNIQUE index gerektirir)
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_monthly_sales;

Ne Zaman Kullanilir

sql

-- Dashboard sorgusu: her seferinde hesaplamak yerine MV kullan
SELECT month, SUM(total_revenue) AS revenue
FROM mv_monthly_sales
WHERE month >= '2025-01-01'
GROUP BY month
ORDER BY month;

-- MV'nin ne kadar eski oldugunu kontrol et
SELECT relname, last_refresh
FROM pg_catalog.pg_stat_user_tables
WHERE relname = 'mv_monthly_sales';

-- Cron job ile periyodik yenileme (ornek: her saat basi)
-- crontab -e
-- 0 * * * * psql -U appuser -d myapp -c "REFRESH MATERIALIZED VIEW CONCURRENTLY mv_monthly_sales;"

LISTEN / NOTIFY

PostgreSQL'in yerlesik pub/sub mekanizmasidir. Harici bir mesaj kuyruguna (Redis, RabbitMQ) ihtiyac duymadan uygulama icinde gercek zamanli bildirimler gonderilebilir.

Temel Kullanım

sql

-- Terminal 1: Kanali dinle
LISTEN order_events;

-- Terminal 2: Bildirim gonder
NOTIFY order_events, '{"order_id": 123, "status": "paid"}';

-- pg_notify() fonksiyonu ile (dinamik kanal adi icin)
SELECT pg_notify('order_events', '{"order_id": 456, "status": "shipped"}');

-- Dinlemeyi durdur
UNLISTEN order_events;
UNLISTEN *;  -- Tum kanallari durdur

Node.js ile Dinleme

javascript

const { Client } = require('pg');

const client = new Client({
  connectionString: 'postgresql://appuser:secret123@localhost:5432/myapp'
});

async function startListening() {
  await client.connect();

  // Bildirim geldiginde calisacak callback
  client.on('notification', (msg) => {
    console.log('Kanal:', msg.channel);
    const payload = JSON.parse(msg.payload);
    console.log('Veri:', payload);

    // Ornek: WebSocket ile istemciye ilet
    // wss.broadcast(payload);
  });

  await client.query('LISTEN order_events');
  console.log('order_events kanali dinleniyor...');
}

startListening().catch(console.error);

Trigger ile Otomatik Bildirim

sql

-- Yeni siparis geldiginde otomatik bildirim gonder
CREATE OR REPLACE FUNCTION notify_new_order()
RETURNS TRIGGER AS $$
BEGIN
    PERFORM pg_notify('order_events', json_build_object(
        'action', TG_OP,
        'order_id', NEW.id,
        'user_id', NEW.user_id,
        'total', NEW.total
    )::text);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_order_notify
    AFTER INSERT ON orders
    FOR EACH ROW
    EXECUTE FUNCTION notify_new_order();

-- Artik her INSERT isleminde order_events kanalina bildirim gider

Kullanım Senaryolari

Siparis durumu degisikliklerinde anlik bildirim
Cache invalidation (MV yenileme tetikleme)
Dashboard'larda canli veri güncelleme
Microservice'ler arasi hafif mesajlasma (payload 8000 byte siniri)

Performance

EXPLAIN ANALYZE Okuma

Sorgu planini anlamak, yavas sorguları optimize etmenin ilk adimidir.

sql

EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'ali@test.com';

-- Ornek cikti:
-- Index Scan using idx_users_email on users  (cost=0.29..8.31 rows=1 width=120) (actual time=0.015..0.016 rows=1 loops=1)
--   Index Cond: ((email)::text = 'ali@test.com'::text)
-- Planning Time: 0.085 ms
-- Execution Time: 0.032 ms

Terim	Açıklama
`Seq Scan`	Tüm tablo tarandi (büyük tablolarda yavas)
`Index Scan`	Index kullanildi (hızlı)
`Index Only Scan`	Sadece index'ten okundu, tabloya gidilmedi (en hızlı)
`Bitmap Heap Scan`	Birden fazla index birlestirilerek okundu
`cost`	Tahmini maliyet (baslangic..toplam)
`rows`	Tahmini satir sayisi
`actual time`	Gercek çalışma süresi (ms)
`loops`	Islemin kac kez tekrarlandigi

sql

-- Detayli analiz icin (buffer kullanimi dahil)
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT * FROM orders WHERE total > 1000;

-- JSON formatinda (programatik islemek icin)
EXPLAIN (ANALYZE, FORMAT JSON) SELECT * FROM orders WHERE total > 1000;

pg_stat_statements

En yavas ve en sik çalışan sorguları tespit etmek için kullanilir.

sql

-- Extension'i etkinlestir
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
-- postgresql.conf: shared_preload_libraries = 'pg_stat_statements' (yeniden baslatma gerekir)

-- En yavas 10 sorgu
SELECT
    query,
    calls,
    round(total_exec_time::numeric, 2) AS total_ms,
    round(mean_exec_time::numeric, 2) AS avg_ms,
    rows
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

-- Istatistikleri sifirla
SELECT pg_stat_statements_reset();

Index Turleri Karsilastirmasi

Index Turu	Kullanım Alani	Operatorler	Örnek
B-tree	Esitlik, aralik, siralama	`=`, `<`, `>`, `BETWEEN`, `IN`, `LIKE 'abc%'`	`CREATE INDEX idx ON t(col)`
Hash	Sadece esitlik	`=`	`CREATE INDEX idx ON t USING HASH(col)`
GIN	JSONB, array, full-text	`@>`, `?`, `?&`, `@@`	`CREATE INDEX idx ON t USING GIN(jsonb_col)`
GiST	Geometrik, mekansal, aralik	`<<`, `>>`, `@>`, `<@`, `&&`	`CREATE INDEX idx ON t USING GIST(geom_col)`
BRIN	Büyük, dogal siralanmis veriler	`<`, `>`, `=`, `BETWEEN`	`CREATE INDEX idx ON t USING BRIN(created_at)`

BRIN index'ler cok düşük alan kaplar ve zaman serisi verileri için idealdir. Tablonun fiziksel siralamasiyla korelasyonlu kolonlarda en iyi performansi verir.

Partial Index ve Expression Index

sql

-- Partial index: sadece belirli kosulu saglayan satirlar indekslenir
-- Aktif kullanicilarin e-postalarini hizli aramak icin
CREATE INDEX idx_active_emails ON users(email)
    WHERE status = 'active';

-- Silenmemis kayitlarin tarih bazli araması
CREATE INDEX idx_not_deleted_orders ON orders(created_at)
    WHERE deleted_at IS NULL;

-- Expression index: hesaplanmis deger uzerinde index
-- Buyuk/kucuk harf duyarsiz e-posta aramasi
CREATE INDEX idx_lower_email ON users(LOWER(email));

-- Kullanim
SELECT * FROM users WHERE LOWER(email) = 'ali@test.com';

-- JSONB icindeki belirli bir key uzerinde index
CREATE INDEX idx_metadata_role ON users((metadata->>'role'));

Connection Pooling (pgBouncer)

PostgreSQL her bağlantı için bir isletim sistemi sureci olusturur. Bu nedenle varsayılan max_connections degeri (genellikle 100) sinirlidir. pgBouncer, bağlantı havuzu olusturarak bu sorunu cozer.

ini

;; pgbouncer.ini
[databases]
myapp = host=127.0.0.1 port=5432 dbname=myapp

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt

;; Pool ayarlari
pool_mode = transaction          ; session | transaction | statement
default_pool_size = 20
max_client_conn = 1000
min_pool_size = 5

Pool Mode	Açıklama	Kullanım
`session`	Istemci baglantiyi kapatana kadar ayni backend	Prepared statements, LISTEN/NOTIFY
`transaction`	Transaction bitince backend havuza doner	Web uygulamalari (önerilen)
`statement`	Her sorgudan sonra backend degisebilir	Basit sorgular (autocommit)

bash

# pgBouncer baslat
pgbouncer -d /etc/pgbouncer/pgbouncer.ini

# Uygulama pgBouncer portuna baglanir (6432)
psql -h 127.0.0.1 -p 6432 -U appuser -d myapp

VACUUM & ANALYZE

PostgreSQL MVCC (Multi-Version Concurrency Control) kullanir. UPDATE ve DELETE islemleri eski satirlari silmez, "dead tuple" olarak isaretler. VACUUM bu dead tuple'lari temizler.

sql

-- Manuel VACUUM (dead tuple'lari temizle)
VACUUM orders;

-- VACUUM FULL (tabloyu yeniden yazar, tablo kilitlenir)
VACUUM FULL orders;

-- ANALYZE (istatistikleri guncelle, sorgu planlayicisi icin)
ANALYZE orders;

-- Ikisini birlikte calistir
VACUUM ANALYZE orders;

-- Dead tuple sayisini kontrol et
SELECT relname, n_dead_tup, n_live_tup,
    round(n_dead_tup::numeric / NULLIF(n_live_tup, 0) * 100, 2) AS dead_pct
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

Autovacuum varsayılan olarak aktiftir. Ayarlarini kontrol etmek için:

sql

-- Autovacuum ayarlarini gor
SHOW autovacuum;
SHOW autovacuum_vacuum_threshold;        -- Varsayilan: 50
SHOW autovacuum_vacuum_scale_factor;     -- Varsayilan: 0.2 (%20)
SHOW autovacuum_analyze_threshold;       -- Varsayilan: 50
SHOW autovacuum_analyze_scale_factor;    -- Varsayilan: 0.1 (%10)

-- Tablo bazinda autovacuum ayari (cok aktif tablolar icin)
ALTER TABLE orders SET (
    autovacuum_vacuum_scale_factor = 0.05,   -- %5'te tetikle
    autovacuum_analyze_scale_factor = 0.02   -- %2'de analiz et
);

Güvenlik (Security)

pg_hba.conf

Host-based authentication dosyasi. PostgreSQL'e kimlerin, nereden, hangi yontemle baglanabilecegini belirler.

ini

# TYPE  DATABASE  USER        ADDRESS           METHOD
local   all       postgres                      peer
local   all       all                           md5
host    all       all         127.0.0.1/32      scram-sha-256
host    all       all         10.0.0.0/8        scram-sha-256
host    all       all         0.0.0.0/0         reject

Method	Açıklama
`peer`	OS kullanici adi ile PG kullanici adi eslesir (sadece local)
`md5`	MD5 hash ile sifre doğrulama
`scram-sha-256`	Modern ve güvenli sifre doğrulama (önerilen)
`cert`	SSL istemci sertifikasi
`reject`	Bağlantı reddedilir

bash

# pg_hba.conf konumunu bul
psql -U postgres -c "SHOW hba_file;"

# Degisiklikten sonra yapilandirmayi yeniden yukle
sudo systemctl reload postgresql
-- veya psql icinden:
SELECT pg_reload_conf();

Role Management

sql

-- Yeni role olustur (login yetkili)
CREATE ROLE app_user WITH LOGIN PASSWORD 'guclu_sifre_123';

-- Veritabani yetkisi ver
GRANT CONNECT ON DATABASE myapp TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;

-- Tablo yetkileri
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA public TO app_user;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_user;

-- Sadece okuma yetkisi olan role
CREATE ROLE readonly_user WITH LOGIN PASSWORD 'read_only_pass';
GRANT CONNECT ON DATABASE myapp TO readonly_user;
GRANT USAGE ON SCHEMA public TO readonly_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;

-- Gelecekte olusturulacak tablolar icin varsayilan yetki
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT SELECT ON TABLES TO readonly_user;

-- Yetki kaldir
REVOKE INSERT, UPDATE ON ALL TABLES IN SCHEMA public FROM app_user;

-- Role sil
DROP ROLE app_user;

SSL Bağlantı

sql

-- SSL durumunu kontrol et
SHOW ssl;
SELECT * FROM pg_stat_ssl;

-- postgresql.conf'da SSL'i etkinlestir
-- ssl = on
-- ssl_cert_file = '/etc/ssl/certs/server.crt'
-- ssl_key_file = '/etc/ssl/private/server.key'

bash

# SSL ile baglanti (istemci tarafi)
psql "host=db.example.com dbname=myapp user=appuser sslmode=require"

# sslmode degerleri:
# disable   - SSL kullanma
# allow     - Mumkunse SSL, degilse duz baglanti
# prefer    - Mumkunse SSL (varsayilan)
# require   - SSL zorunlu, sertifika dogrulamasi yok
# verify-ca - SSL + CA sertifikasi dogrula
# verify-full - SSL + CA + hostname dogrula (en guvenli)

pgcrypto Extension

sql

CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- UUID olustur
SELECT gen_random_uuid();

-- Sifre hash'le (bcrypt)
SELECT crypt('kullanici_sifresi', gen_salt('bf', 12));

-- Sifre dogrula
SELECT (crypt('kullanici_sifresi', stored_hash) = stored_hash) AS is_valid;

-- Simetrik sifreleme (AES)
-- Hassas veriyi sifrele
SELECT pgp_sym_encrypt('gizli-veri', 'sifreleme-anahtari');

-- Sifreyi coz
SELECT pgp_sym_decrypt(
    pgp_sym_encrypt('gizli-veri', 'sifreleme-anahtari'),
    'sifreleme-anahtari'
);

pgAudit Extension

Veritabani islemlerinin denetim kaydini tutar. Uyumluluk gereksinimleri (SOC2, HIPAA) için onemlidir.

sql

-- Extension'i etkinlestir
CREATE EXTENSION IF NOT EXISTS pgaudit;

-- postgresql.conf ayarlari:
-- shared_preload_libraries = 'pgaudit'
-- pgaudit.log = 'write, ddl'        -- INSERT/UPDATE/DELETE + DDL logla
-- pgaudit.log_catalog = off
-- pgaudit.log_relation = on

-- Role bazli denetim
SET pgaudit.role = 'auditor';
CREATE ROLE auditor;
GRANT SELECT, INSERT, UPDATE, DELETE ON orders TO auditor;
-- Artik orders tablosundaki tum islemler loglanir

Password Hashing

sql

-- Varsayilan sifre hash yontemini degistir
SET password_encryption = 'scram-sha-256';

-- Kullanici sifresi belirlerken otomatik scram-sha-256 kullanilir
CREATE ROLE secure_user WITH LOGIN PASSWORD 'guclu_sifre';

-- Mevcut kullanicinin sifresini guncelle
ALTER ROLE app_user WITH PASSWORD 'yeni_guclu_sifre';

-- Hash yontemini kontrol et
SELECT rolname, rolpassword ~ '^SCRAM-SHA-256' AS is_scram
FROM pg_authid WHERE rolcanlogin;

Tips

VACUUM/ANALYZE Duzenliligi

sql

-- Autovacuum'un duzgun calisip calismadigini kontrol edin
SELECT relname, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY last_autovacuum NULLS FIRST;

-- Cok guncellenen tablolarda autovacuum'u agresif ayarlayin
ALTER TABLE high_traffic_table SET (
    autovacuum_vacuum_cost_delay = 10,
    autovacuum_vacuum_scale_factor = 0.01
);

pgBouncer ile max_connections Yönetimi

sql

-- Mevcut baglanti sayisi ve limit
SELECT count(*) AS active, max_conn FROM pg_stat_activity,
    (SELECT setting::int AS max_conn FROM pg_settings WHERE name = 'max_connections') s
GROUP BY max_conn;

-- Uygulama pgBouncer'a baglanir, pgBouncer az sayida PG baglantisi kullanir
-- 1000 istemci -> pgBouncer (20 pool) -> PostgreSQL (20 baglanti)

Partial ve Expression Index Önerileri

sql

-- Soft-delete tablolarda: sadece aktif kayitlari indeksle
CREATE INDEX idx_active_products ON products(name, price)
    WHERE deleted_at IS NULL;

-- Buyuk/kucuk harf duyarsiz arama
CREATE INDEX idx_lower_username ON users(LOWER(username));
SELECT * FROM users WHERE LOWER(username) = 'ali';

-- JSON icindeki alana gore index
CREATE INDEX idx_settings_theme ON user_settings((settings->>'theme'));

COALESCE ve NULLIF

sql

-- COALESCE: ilk NULL olmayan degeri doner
SELECT name, COALESCE(nickname, name) AS display_name FROM users;
SELECT product, COALESCE(discount_price, original_price) AS final_price FROM products;

-- NULLIF: iki deger esitse NULL doner (sifira bolme hatasi onleme)
SELECT total / NULLIF(quantity, 0) AS unit_price FROM order_items;

generate_series() ile Test Verisi

sql

-- 100.000 test kaydı olustur
INSERT INTO users (name, email, created_at)
SELECT
    'User_' || i,
    'user_' || i || '@test.com',
    NOW() - (random() * INTERVAL '365 days')
FROM generate_series(1, 100000) AS i;

-- Tarih serisi olustur (raporlama icin bos gunleri doldurmak)
SELECT date::date
FROM generate_series('2025-01-01', '2025-12-31', INTERVAL '1 day') AS date;

-- Saatlik zaman dilimleri
SELECT ts FROM generate_series(
    '2025-01-01 00:00'::timestamp,
    '2025-01-02 00:00'::timestamp,
    INTERVAL '1 hour'
) AS ts;

pg_stat_user_tables ile Tablo Istatistikleri

sql

-- Tablo bazinda istatistikler
SELECT
    relname AS tablo,
    seq_scan,                          -- Sequential scan sayisi
    idx_scan,                          -- Index scan sayisi
    n_tup_ins AS inserts,              -- Toplam INSERT
    n_tup_upd AS updates,             -- Toplam UPDATE
    n_tup_del AS deletes,             -- Toplam DELETE
    n_live_tup AS live_rows,          -- Canli satir sayisi
    n_dead_tup AS dead_rows           -- Dead tuple sayisi
FROM pg_stat_user_tables
ORDER BY seq_scan DESC;

-- seq_scan >> idx_scan ise o tabloda eksik index olabilir
-- dead_rows cok yuksekse VACUUM gerekebilir

Sik Kullanilan Komutlar (Quick Reference)

Command	Description
`\l`	Veritabanlarini listele
`\dt`	Tablolari listele
`\d+ table`	Detayli tablo yapisi
`EXPLAIN ANALYZE SELECT ...`	Sorgu planini analiz et
`SELECT pg_size_pretty(pg_database_size('myapp'));`	Veritabani boyutu
`SELECT * FROM pg_stat_activity;`	Aktif baglantilari göster
`VACUUM ANALYZE;`	Tablo istatistiklerini güncelle

PostgreSQL ​

Kurulum (Installation) ​

Ubuntu ​

Docker ​

psql CLI Komutları ​

Veri Tipleri (MySQL ile Farklar) ​

CRUD ​

Gelismis Ozellikler (Advanced Features) ​

CTE (Common Table Expressions) ​

Window Functions ​

JSONB Sorguları ​

ARRAY Islemleri ​

Indeks Turleri (Index Types) ​

Full-text Search ​

Extensions ​

Yedekleme (Backup & Restore) ​

Django / Flask ile Kullanım ​

Transactions & Isolation ​

BEGIN / COMMIT / ROLLBACK ​

SAVEPOINT ​

Isolation Levels ​

Advisory Locks ​

Row Level Security (RLS) ​

RLS Etkinlestirme ​

Policy Oluşturma ​

Multi-tenant Uygulama Ornegi ​

Partitioning ​

Range Partitioning ​

List Partitioning ​

Hash Partitioning ​

Partition Pruning ​

Materialized Views ​

Oluşturma ve Yenileme ​

Ne Zaman Kullanilir ​

LISTEN / NOTIFY ​

Temel Kullanım ​

Node.js ile Dinleme ​

Trigger ile Otomatik Bildirim ​

Kullanım Senaryolari ​

Performance ​

EXPLAIN ANALYZE Okuma ​

pg_stat_statements ​

Index Turleri Karsilastirmasi ​

Partial Index ve Expression Index ​

Connection Pooling (pgBouncer) ​

VACUUM & ANALYZE ​

Güvenlik (Security) ​

pg_hba.conf ​

Role Management ​

SSL Bağlantı ​

pgcrypto Extension ​

pgAudit Extension ​

Password Hashing ​

Tips ​

VACUUM/ANALYZE Duzenliligi ​

pgBouncer ile max_connections Yönetimi ​

Partial ve Expression Index Önerileri ​

COALESCE ve NULLIF ​

generate_series() ile Test Verisi ​

pg_stat_user_tables ile Tablo Istatistikleri ​

Sik Kullanilan Komutlar (Quick Reference) ​

Ilgili Rehberler ​

Veritabani ​

Diger Kategoriler ​

PostgreSQL

Kurulum (Installation)

Ubuntu

Docker

psql CLI Komutları

Veri Tipleri (MySQL ile Farklar)

CRUD

Gelismis Ozellikler (Advanced Features)

CTE (Common Table Expressions)

Window Functions

JSONB Sorguları

ARRAY Islemleri

Indeks Turleri (Index Types)

Full-text Search

Extensions

Yedekleme (Backup & Restore)

Django / Flask ile Kullanım

Transactions & Isolation

BEGIN / COMMIT / ROLLBACK

SAVEPOINT

Isolation Levels

Advisory Locks

Row Level Security (RLS)

RLS Etkinlestirme

Policy Oluşturma

Multi-tenant Uygulama Ornegi

Partitioning

Range Partitioning

List Partitioning

Hash Partitioning

Partition Pruning

Materialized Views

Oluşturma ve Yenileme

Ne Zaman Kullanilir

LISTEN / NOTIFY

Temel Kullanım

Node.js ile Dinleme

Trigger ile Otomatik Bildirim

Kullanım Senaryolari

Performance

EXPLAIN ANALYZE Okuma

pg_stat_statements

Index Turleri Karsilastirmasi

Partial Index ve Expression Index

Connection Pooling (pgBouncer)

VACUUM & ANALYZE

Güvenlik (Security)

pg_hba.conf

Role Management

SSL Bağlantı

pgcrypto Extension

pgAudit Extension

Password Hashing

Tips

VACUUM/ANALYZE Duzenliligi

pgBouncer ile max_connections Yönetimi

Partial ve Expression Index Önerileri

COALESCE ve NULLIF

generate_series() ile Test Verisi

pg_stat_user_tables ile Tablo Istatistikleri

Sik Kullanilan Komutlar (Quick Reference)

Ilgili Rehberler

Veritabani

Diger Kategoriler