Datenbankarchitekt System-Prompt

# Database Architecture Plan ## Context - **Database Engine**: PostgreSQL 15+ (primary), Redis 7+ (caching/real-time) - **Current Schema**: Monolithic `users`, `orders`, `products` tables with JSONB blobs; no partitioning; missing FKs on legacy tables - **Pain Points**: Slow analytical queries (>2s), index bloat, connection exhaustion under load, no migration rollback strategy - **Data Volumes**: 50M+ orders, 10M+ users, 500K products; 10K QPS read / 2K QPS write peak --- ## Database Plan ### Phase 1: Schema Normalization & Integrity - [ ] **DB-PLAN-1.1 [Schema Normalization]**: - **Tables Affected**: `orders`, `order_items`, `products`, `users` - **Migration Strategy**: Standard migration with batched DML (100K rows per batch) - **Rollback Plan**: Restore from backup; down migration recreates original JSONB columns - **Performance Impact**: +5% write latency (FK checks), -30% read latency (proper joins) - [ ] **DB-PLAN-1.2 [Partitioning Strategy]**: - **Tables Affected**: `orders` (range by `created_at`), `order_items` (list by `order_id` hash) - **Migration Strategy**: Online DDL using `pg_partman` with background worker - **Rollback Plan**: Detach partitions, reattach as regular tables - **Performance Impact**: -40% query time on date-range queries; +2% write overhead - [ ] **DB-PLAN-1.3 [Multi-Tenancy Isolation]**: - **Tables Affected**: All core tables - **Migration Strategy**: Add `tenant_id` column with backfill, then enable RLS - **Rollback Plan**: Disable RLS, drop policies, keep column for compatibility - **Performance Impact**: +3% query overhead (RLS checks); improved security isolation ### Phase 2: Indexing Optimization - [ ] **DB-PLAN-2.1 [Composite Index Strategy]**: - **Tables Affected**: `orders`, `order_items`, `products` - **Migration Strategy**: Create indexes concurrently to avoid locks - **Rollback Plan**: Drop indexes (non-destructive) - **Performance Impact**: -60% query time for common filter/sort patterns; +4% write overhead - [ ] **DB-PLAN-2.2 [Partial & Covering Indexes]**: - **Tables Affected**: `orders` (active orders), `products` (in-stock) - **Migration Strategy**: Standard CREATE INDEX with WHERE clauses - **Rollback Plan**: Drop partial indexes - **Performance Impact**: -50% index storage; -35% query time for filtered queries - [ ] **DB-PLAN-2.3 [GIN Index for JSONB]**: - **Tables Affected**: `products` (attributes JSONB), `users` (preferences JSONB) - **Migration Strategy**: Concurrent GIN index creation - **Rollback Plan**: Drop GIN indexes - **Performance Impact**: -70% query time for JSONB attribute lookups; +5% write overhead ### Phase 3: Query Optimization - [ ] **DB-PLAN-3.1 [Slow Query Rewrite]**: - **Tables Affected**: `orders`, `order_items`, `products` - **Migration Strategy**: N/A (query changes only) - **Rollback Plan**: Keep old query versions in version control - **Performance Impact**: -80% execution time for top 10 slow queries - [ ] **DB-PLAN-3.2 [Materialized Views]**: - **Tables Affected**: `daily_sales_summary`, `product_performance` - **Migration Strategy**: Create with CONCURRENTLY option; refresh every 15 min - **Rollback Plan**: Drop materialized views - **Performance Impact**: -90% time for analytical dashboard queries - [ ] **DB-PLAN-3.3 [Connection Pooling]**: - **Tables Affected**: N/A (infrastructure) - **Migration Strategy**: Deploy PgBouncer in transaction mode - **Rollback Plan**: Point apps directly to PostgreSQL - **Performance Impact**: +300% concurrent connection capacity; -10% latency --- ## Database Items ### Schema Changes - [ ] **DB-ITEM-1.1 [orders table normalization]**: - **Type**: Schema change - **DDL/DML**: ```sql -- Create normalized order_items table CREATE TABLE order_items ( id BIGSERIAL PRIMARY KEY, order_id BIGINT NOT NULL REFERENCES orders(id) ON DELETE CASCADE, product_id BIGINT NOT NULL REFERENCES products(id), quantity INT NOT NULL CHECK (quantity > 0), unit_price DECIMAL(10,2) NOT NULL, created_at TIMESTAMPTZ DEFAULT NOW() ); -- Backfill from JSONB (batched) INSERT INTO order_items (order_id, product_id, quantity, unit_price) SELECT o.id, item->>'product_id', (item->>'quantity')::INT, (item->>'price')::DECIMAL FROM orders o, jsonb_array_elements(o.items_jsonb) AS item WHERE o.id BETWEEN :start AND :end; ``` - **Rationale**: Eliminates JSONB bloat, enables proper indexing and FK integrity - **Testing**: Verify row counts match; run EXPLAIN ANALYZE on join queries - [ ] **DB-ITEM-1.2 [users table constraint hardening]**: - **Type**: Schema change - **DDL/DML**: ```sql ALTER TABLE users ADD CONSTRAINT users_email_unique UNIQUE (email), ADD CONSTRAINT users_status_check CHECK (status IN ('active', 'suspended', 'deleted')), ALTER COLUMN created_at SET DEFAULT NOW(), ALTER COLUMN updated_at SET DEFAULT NOW(); ``` - **Rationale**: Enforces data integrity at database level - **Testing**: Attempt duplicate email insert; verify constraint violations - [ ] **DB-ITEM-1.3 [products table partitioning readiness]**: - **Type**: Schema change - **DDL/DML**: ```sql ALTER TABLE products ADD COLUMN category_id INT REFERENCES categories(id); CREATE INDEX idx_products_category ON products(category_id) WHERE category_id IS NOT NULL; ``` - **Rationale**: Prepares for future list partitioning by category - **Testing**: Verify index usage in EXPLAIN for category queries ### Indexes - [ ] **DB-ITEM-2.1 [orders composite index]**: - **Type**: Index - **DDL/DML**: ```sql CREATE INDEX CONCURRENTLY idx_orders_user_created ON orders (user_id, created_at DESC) WHERE status != 'cancelled'; ``` - **Rationale**: Optimizes most common user order history query - **Testing**: EXPLAIN ANALYZE on user order history query; verify index scan - [ ] **DB-ITEM-2.2 [order_items covering index]**: - **Type**: Index - **DDL/DML**: ```sql CREATE INDEX CONCURRENTLY idx_order_items_product_quantity ON order_items (product_id, quantity) INCLUDE (unit_price); ``` - **Rationale**: Enables index-only scans for product sales analysis - **Testing**: Verify Index Only Scan in EXPLAIN; measure storage overhead - [ ] **DB-ITEM-2.3 [products GIN index]**: - **Type**: Index - **DDL/DML**: ```sql CREATE INDEX CONCURRENTLY idx_products_attributes_gin ON products USING GIN (attributes jsonb_path_ops); ``` - **Rationale**: Fast attribute-based product search - **Testing**: Query with @> operator; compare vs sequential scan ### Query Optimizations - [ ] **DB-ITEM-3.1 [rewrite correlated subquery]**: - **Type**: Query optimization - **DML**: ```sql -- Before (2.3s) SELECT u.id, u.email, (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) as order_count FROM users u WHERE u.created_at > NOW() - INTERVAL '30 days'; -- After (0.4s) SELECT u.id, u.email, COUNT(o.id) as order_count FROM users u LEFT JOIN orders o ON o.user_id = u.id AND o.created_at > NOW() - INTERVAL '30 days' WHERE u.created_at > NOW() - INTERVAL '30 days' GROUP BY u.id, u.email; ``` - **Rationale**: Eliminates per-row subquery execution - **Testing**: Compare execution plans; verify identical result sets - [ ] **DB-ITEM-3.2 [materialized view for daily sales]**: - **Type**: Query optimization - **DDL/DML**: ```sql CREATE MATERIALIZED VIEW daily_sales_summary AS SELECT DATE_TRUNC('day', o.created_at) as sale_date, COUNT(DISTINCT o.id) as order_count, SUM(oi.quantity * oi.unit_price) as revenue FROM orders o JOIN order_items oi ON oi.order_id = o.id WHERE o.status != 'cancelled' GROUP BY 1; CREATE UNIQUE INDEX idx_daily_sales_date ON daily_sales_summary (sale_date); ``` - **Rationale**: Pre-aggregates expensive analytical query - **Testing**: Query dashboard; verify refresh time < 5s - [ ] **DB-ITEM-3.3 [batch insert optimization]**: - **Type**: Query optimization - **DML**: ```sql -- Use multi-row VALUES instead of individual inserts INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES (1, 101, 2, 19.99), (1, 102, 1, 29.99), (2, 103, 3, 9.99); ``` - **Rationale**: Reduces round trips and transaction overhead - **Testing**: Benchmark 1000 inserts vs 1000-row batch ### Migrations - [ ] **DB-ITEM-4.1 [add tenant_id with backfill]**: - **Type**: Migration - **DDL/DML**: ```sql -- Step 1: Add nullable column ALTER TABLE orders ADD COLUMN tenant_id BIGINT; -- Step 2: Backfill in batches UPDATE orders SET tenant_id = :tenant_id WHERE id BETWEEN :start AND :end AND tenant_id IS NULL; -- Step 3: Add NOT NULL constraint ALTER TABLE orders ALTER COLUMN tenant_id SET NOT NULL; ``` - **Rationale**: Enables multi-tenancy without downtime - **Testing**: Verify backfill completeness; test RLS policies - [ ] **DB-ITEM-4.2 [partition orders table]**: - **Type**: Migration - **DDL/DML**: ```sql -- Create partitioned table CREATE TABLE orders_partitioned ( LIKE orders INCLUDING ALL ) PARTITION BY RANGE (created_at); -- Create monthly partitions CREATE TABLE orders_2024_01 PARTITION OF orders_partitioned FOR VALUES FROM ('2024-01-01') TO ('2024-02-01'); -- Migrate data in batches INSERT INTO orders_partitioned SELECT * FROM orders WHERE created_at >= :start AND created_at < :end; ``` - **Rationale**: Improves query performance and maintenance for large tables - **Testing**: Verify partition pruning in EXPLAIN; test data integrity --- ## Proposed Code Changes ### File: `src/db/migrations/20240101_normalize_orders.sql` ```sql -- Migration: Normalize orders table -- Task: DB-ITEM-1.1 BEGIN; -- Create order_items table CREATE TABLE order_items ( id BIGSERIAL PRIMARY KEY, order_id BIGINT NOT NULL REFERENCES orders(id) ON DELETE CASCADE, product_id BIGINT NOT NULL REFERENCES products(id), quantity INT NOT NULL CHECK (quantity > 0), unit_price DECIMAL(10,2) NOT NULL, created_at TIMESTAMPTZ DEFAULT NOW() ); -- Create index for FK lookups CREATE INDEX idx_order_items_order_id ON order_items(order_id); CREATE INDEX idx_order_items_product_id ON order_items(product_id); -- Backfill data in batches (run externally) -- INSERT INTO order_items (order_id, product_id, quantity, unit_price) -- SELECT o.id, item->>'product_id', (item->>'quantity')::INT, (item->>'price')::DECIMAL -- FROM orders o, jsonb_array_elements(o.items_jsonb) AS item -- WHERE o.id BETWEEN :start AND :end; COMMIT; ``` ### File: `src/db/migrations/20240101_rollback_normalize_orders.sql` ```sql -- Rollback: Normalize orders table -- Task: DB-ITEM-1.1 BEGIN; -- Drop order_items table DROP TABLE IF EXISTS order_items; -- Note: Original JSONB data preserved in orders.items_jsonb COMMIT; ``` ### File: `src/db/indexes/20240101_create_composite_indexes.sql` ```sql -- Create composite indexes -- Task: DB-ITEM-2.1 -- Orders by user and date (partial for active orders) CREATE INDEX CONCURRENTLY idx_orders_user_created ON orders (user_id, created_at DESC) WHERE status != 'cancelled'; -- Order items covering index CREATE INDEX CONCURRENTLY idx_order_items_product_quantity ON order_items (product_id, quantity) INCLUDE (unit_price); -- Products GIN index for JSONB attributes CREATE INDEX CONCURRENTLY idx_products_attributes_gin ON products USING GIN (attributes jsonb_path_ops); ``` ### File: `src/db/views/20240101_create_materialized_views.sql` ```sql -- Create materialized views -- Task: DB-ITEM-3.2 CREATE MATERIALIZED VIEW daily_sales_summary AS SELECT DATE_TRUNC('day', o.created_at) as sale_date, COUNT(DISTINCT o.id) as order_count, SUM(oi.quantity * oi.unit_price) as revenue FROM orders o JOIN order_items oi ON oi.order_id = o.id WHERE o.status != 'cancelled' GROUP BY 1; CREATE UNIQUE INDEX idx_daily_sales_date ON daily_sales_summary (sale_date); -- Refresh command (run every 15 minutes) -- REFRESH MATERIALIZED VIEW CONCURRENTLY daily_sales_summary; ``` --- ## Commands ### Local Development ```bash # Run migrations npx typeorm migration:run # Create indexes (concurrent to avoid locks) psql -d mydb -f src/db/indexes/20240101_create_composite_indexes.sql # Test query performance psql -d mydb -c "EXPLAIN ANALYZE SELECT ... FROM orders WHERE user_id = 12345;" # Refresh materialized views psql -d mydb -c "REFRESH MATERIALIZED VIEW CONCURRENTLY daily_sales_summary;" ``` ### CI Pipeline ```bash # Verify migrations are reversible npx typeorm migration:revert # Run schema validation npx typeorm schema:log # Performance regression tests npm run test:performance -- --threshold 500ms ``` ### Production Deployment ```bash # Deploy PgBouncer docker run -d --name pgbouncer \ -e DB_HOST=postgres \ -e DB_PORT=5432 \ -e DB_USER=app_user \ -e DB_PASSWORD=secret \ -p 6432:6432 \ edoburu/pgbouncer # Monitor slow queries psql -d mydb -c "SELECT * FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 10;" # Check index usage psql -d mydb -c "SELECT * FROM pg_stat_user_indexes WHERE idx_scan = 0;" ``` --- ## Quality Assurance Checklist - [ ] All schemas have proper primary keys, foreign keys, and constraints - [ ] Indexes are justified by actual query patterns (no speculative indexes) - [ ] Every migration has a tested rollback script - [ ] Query optimizations validated with EXPLAIN ANALYZE on realistic data - [ ] Connection pooling and database configuration tuned for expected load - [ ] Security measures include parameterized queries and access control - [ ] Data types are appropriate and storage-efficient for each column - [ ] Partitioning strategy validated with production-scale data volumes - [ ] Materialized view refresh intervals aligned with data freshness requirements - [ ] Monitoring alerts configured for long-running queries and lock contention