AI ile Database Tasarımı: Schema, Index, Migration

Database tasarımı = 6 ay sonra pişmanlık veya gönenç. Doğru schema + index = 10x performance. AI ile yapısal hızlı.

Schema Tasarımı

Rol: Sen senior database architect.

Görev: Aşağıdaki domain için PostgreSQL schema:

Domain: Multi-tenant SaaS HR platform
Entities: Tenant, User, Employee, Department, Salary, Performance Review

Scale: 1000 tenant × 500 employee = 500K employee
Workload: 80% read, 20% write
Tenant izolasyon: shared schema + tenant_id

Çıktı:
1. ER diagram (text)
2. Schema (Prisma + raw SQL)
3. Constraint (FK, unique, check)
4. Index strategy
5. Multi-tenant pattern (RLS / column / schema)
6. Soft delete (deleted_at)
7. Audit log
8. Migration order
9. Performance considerations
10. GDPR (right to delete)

Normalization

1NF

✅ Atomic values (no repeating group)
❌ phone: "555-1234, 555-5678"
✅ phone (separate table)

2NF

✅ No partial dependency
❌ order_items (order_id, product_id, product_name, qty)
   product_name depends only on product_id
✅ order_items (order_id, product_id, qty)
   products (id, name)

3NF

✅ No transitive dependency
❌ employees (id, dept_id, dept_name, dept_manager)
✅ employees (id, dept_id) + departments (id, name, manager)

When to Denormalize

"Aşağıdaki schema denormalize gerekli mi?

[schema + query patterns]

Analiz:
- Read frequency vs write
- JOIN cost
- Cache alternative
- Materialized view alternative
- Eventual consistency tolerance

Karar matrix."

Indexes

"Aşağıdaki tablo için index strategy:

CREATE TABLE posts (
  id UUID PRIMARY KEY,
  user_id UUID,
  status TEXT,
  category TEXT,
  published_at TIMESTAMP,
  title TEXT,
  body TEXT
);

Top queries:
1. SELECT * FROM posts WHERE user_id = ? ORDER BY published_at DESC LIMIT 20
2. SELECT * FROM posts WHERE status = 'published' AND category = ? ORDER BY published_at DESC
3. Full text search on title + body

Çıktı:
- Index list
- Order matter (column order in index)
- Partial index opportunities
- Covering index (INCLUDE clause)
- GIN index (full text)
- Trade-off (write speed)
"

Örnek output:

-- Query 1
CREATE INDEX idx_posts_user_published 
  ON posts(user_id, published_at DESC);

-- Query 2 (partial - only published)
CREATE INDEX idx_posts_category_published 
  ON posts(category, published_at DESC) 
  WHERE status = 'published';

-- Query 3 (full text)
CREATE INDEX idx_posts_search 
  ON posts USING GIN(
    to_tsvector('turkish', title || ' ' || body)
  );

Query Optimization

"Slow query optimize:

Query:
SELECT u.*, COUNT(p.id) as post_count, AVG(p.likes) as avg_likes
FROM users u
LEFT JOIN posts p ON p.user_id = u.id
WHERE u.created_at > NOW() - INTERVAL '30 days'
GROUP BY u.id
ORDER BY post_count DESC
LIMIT 100;

EXPLAIN ANALYZE: 3 seconds

Çıktı:
1. EXPLAIN okuma (her node)
2. Bottleneck (seq scan? hash join? sort?)
3. Missing index
4. Subquery rewrite
5. Window function alternative
6. Materialized view
7. Application-level cache
8. Read replica
9. Target: < 200ms
"

Detay: SQL Sorgu

Migrations

"Aşağıdaki schema change için migration:

Change: posts table'a category_id (FK to categories) ekle, 
        eski 'category' (text) kolon kaldır

Risk: 1M rows, zero downtime gerekli

Migration steps:
1. ALTER TABLE posts ADD COLUMN category_id UUID
2. Backfill (chunks of 10K)
   - UPDATE posts SET category_id = (
       SELECT id FROM categories WHERE name = posts.category
     )
3. Add FK constraint
4. Application read both
5. Application write new column
6. Application read new only
7. Drop old column

Each step: blue/green deploy verify.
Rollback plan her aşamada.
"

Multi-Tenancy

Shared Schema + tenant_id

CREATE POLICY tenant_isolation ON employees
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

ALTER TABLE employees ENABLE ROW LEVEL SECURITY;

Schema-per-tenant

CREATE SCHEMA tenant_acme;
CREATE TABLE tenant_acme.employees (...);

Database-per-tenant

Tenant 1 → DB 1
Tenant 2 → DB 2

"Multi-tenant decision:

Tenant count: 1000
Average tenant size: 500 employee
Compliance: GDPR (some tenant EU)
Custom schema per tenant: %20

Karşılaştır:
| | Shared | Schema | DB |
| Setup | Easy | Medium | Hard |
| Cost | Low | Medium | High |
| Isolation | Logic | Schema | Physical |
| GDPR delete | Hard | Easy | Easiest |
| Backup per tenant | Hard | Medium | Easy |
| Noisy neighbor | Risk | Less | None |

Hybrid:
- Small tenants: shared
- Enterprise: dedicated DB
"

Partition

"Aşağıdaki tablo partition:

Table: events (1B rows, 50M / month)
Pattern: TTL 90 days, query by date range

Strategy:
- RANGE partition by created_at (monthly)
- Auto-create partition (cron)
- DETACH + DROP old (90+ days)

Pros:
- Query speed (partition pruning)
- Drop old fast (no DELETE)
- Index per partition smaller

Setup:
CREATE TABLE events (
  id UUID,
  created_at TIMESTAMP,
  ...
) PARTITION BY RANGE (created_at);

CREATE TABLE events_2026_05 PARTITION OF events
  FOR VALUES FROM ('2026-05-01') TO ('2026-06-01');
"

Sharding

"Sharding plan:

Tablo: orders
Volume: 500M / year, 5K writes/sec peak
Single DB hot: yes (CPU > 80%)

Shard key:
- user_id (most queries by user)
- Hash distribution
- 16 shards initially
- 4 physical DB × 4 logical shards

Routing:
- Application layer (shard manager)
- Vitess (MySQL) / Citus (Postgres)
- ProxySQL

Cross-shard query:
- Limited (analytics only)
- Read from data warehouse

Resharding:
- Hard, plan from day 1
- 'Vehicle change while driving'
"

Backup & Recovery

"Backup strategy:

Production PostgreSQL 200GB

Hot backup:
- pg_basebackup (daily, 02:00)
- WAL archive (continuous)
- S3 versioned
- Encryption at rest

Point-in-time recovery:
- WAL replay
- RPO: 1 minute
- RTO: 15 minutes

Test:
- Monthly restore test
- Document procedure
- DR drill (yıllık)

Off-site:
- Different region
- Different cloud (opsiyonel)
"

Data Modeling Patterns

Soft Delete

deleted_at TIMESTAMP NULL

Audit Log

CREATE TABLE audit_log (
  id BIGSERIAL,
  table_name TEXT,
  record_id UUID,
  operation TEXT, -- INSERT, UPDATE, DELETE
  changes JSONB,
  user_id UUID,
  created_at TIMESTAMP
);

Event Sourcing

CREATE TABLE events (
  id UUID,
  aggregate_id UUID,
  type TEXT,
  payload JSONB,
  version INT,
  created_at TIMESTAMP
);

-- Aggregate state = replay events

CQRS

Write model: normalized, OLTP
Read model: denormalized, projection
Event-driven sync

NoSQL Use Cases

"NoSQL ne zaman?

MongoDB:
- Document model (catalog with varying fields)
- Nested data
- Rapid prototype

Redis:
- Cache
- Session
- Rate limit
- Pub/sub
- Leaderboard (sorted set)

DynamoDB:
- Key-value at scale
- Serverless
- AWS native

Cassandra:
- Time series
- Write-heavy
- Multi-datacenter

ClickHouse:
- Analytics OLAP
- Columnar
- 1B+ rows
"

Yaygın Hatalar

1. No index: Slow query forever
2. Too many index: Write slow
3. Premature sharding: Complexity ekstra
4. No constraint: Data integrity loss
5. NULL semantics ignored: NULL + anything = NULL
6. N+1 from ORM: Query waterfall
7. Backup yok or test yok: Day 0 disaster

Sonraki Adımlar

• SQL Sorgu
• Backend Geliştirme
• Veri Analizi
• Refactoring
• DevOps + AI

Özet

Database + AI = schema yapısal, query optimal, migration safe. PostgreSQL default. Normalize-then-denormalize. Index where query hurts. Anahtar: backup + monitoring + EXPLAIN ANALYZE daily.