Database schema design & query optimisation

Small teams often ship databases with ad-hoc schemas, missing indexes and ORM-generated queries nobody has profiled, and only discover it when production slows down. AI assistants can propose normalised schemas from requirements, write safe migrations, read execution plans and suggest rewrites and indexes. Database-native advisors and LLM agents connected to a non-production copy make DBA-level know-how available to developers, with the DBA still approving every change.

Problem & value

• Today: schemas grow organically; developers copy patterns, ORMs emit N+1 queries, and tuning is done by a scarce DBA reading EXPLAIN output. Migrations on large tables cause locks and outages.
• AI value: faster first drafts (ER model, DDL, constraints, naming conventions), plan interpretation in plain language, candidate rewrites and index suggestions, and migration review (lock risk, backward compatibility).
• Evidence: text-to-SQL benchmarks show the gap between toy and real databases. Spider 2.0 (enterprise workflows on BigQuery/Snowflake) reported only about 21% task success for the best code agent in the original paper, versus 91% on Spider 1.0 [1]. BIRD adds a Valid Efficiency Score that penalises correct-but-slow SQL [2]. Vendor tuning advisors are generally available, for example index tuning in Azure Database for PostgreSQL based on Query Store [3]. No universal speed-up figure exists; gains depend on workload.

Variants

• Greenfield schema design: requirements to ER model to DDL, including multi-tenant and GDPR-aware designs (separate PII tables, retention columns, erasure paths).
• Query tuning: rewrite a slow query, remove N+1 patterns, choose join order hints, materialised views.
• Index and physical design advisor: workload-based index, partitioning and statistics recommendations.
• Migration assistant: expand/contract migrations, zero-downtime changes, rollback scripts; engine migration (Oracle to PostgreSQL) overlaps with CODE-005.
• NoSQL / analytics modelling: document models, star schemas and dbt models (see CODE-022).
• Private/hobby use: a SQLite or Postgres design for a personal project with a chat assistant.

How it works

1. requirements / existing DDL / slow-query log
2. context assembly (schema, stats, row counts, EXPLAIN plans, pg_stat_statements)
3. LLM proposes schema, migration or rewritten query
4. automated checks on a clone (parse, lint, EXPLAIN, hypothetical indexes, result-equivalence test)
5. benchmark before/after on representative data
6. human review (developer + DBA)
7. migration pipeline
8. staged rollout

• Grounding is the key: the model must see real DDL, cardinalities and plans, not guess. Tools expose these through MCP servers or function calls.
• Verification beats trust: a rewrite is accepted only if it returns identical results on a test set and the measured plan is cheaper. Hypothetical-index tools let you test an index without building it.
• Human-in-the-loop: DDL and production changes are never executed directly by the agent; it works read-only against a replica or anonymised clone and opens a pull request with the migration.

Data preparation

The AI needs context rather than training data: DDL and migration history (Git, ORM models), slow-query logs and statistics (pg_stat_statements, Query Store), EXPLAIN plans and representative row counts. In an SMB these live in the application repository, the database server and a few people's heads; documentation is often missing.

Typical gaps: undocumented tables and columns, no non-production clone, production data with personal data that must not reach an LLM, no test set to prove that a rewritten query returns identical results, and DB credentials that are far too broad.

Preparation steps: - Export the schema with comments and add short descriptions for cryptic tables and columns (PREP-012 for a shared glossary). - Build a masked or synthetic clone with realistic cardinalities (PREP-022); never connect the agent to production. - Create a read-only role limited to the clone and to statistics views. - Collect 20-50 representative queries with expected results as regression tests (PREP-018). - Check whether the logs contain literal personal values and strip them (PREP-025).

Effort share: low to medium, roughly 20-30 % of the project, mostly the clone and documentation. See also GUIDE-003 for a readiness check and DATA-001 for related data-engineering work.

Implementation paths

Which path for whom

• SMB: Path B, because a small team needs a read-only agent wired to its own schema and a clone, and no product fits every database without integration.
• Enterprise: Path B, because a central DBA team can build one verified harness (clone, EXPLAIN checks, CI) on a managed API and reuse it across many databases.
• Developer: Path C, because schemas and slow-query logs reveal internal structure, and an open-weight code model on HW2 keeps them local.

Path A — Buy (off-the-shelf)

• Database-native advisors: Azure Database for PostgreSQL index tuning and autonomous tuning [3]; Amazon RDS/Aurora Performance Insights and DevOps Guru for RDS; Google Cloud SQL / AlloyDB Insights and index advisor; Oracle Autonomous Database automatic indexing; SQL Server Query Store automatic tuning.
• Third-party: Percona Monitoring and Management (open core), pganalyze, EverSQL, Datadog Database Monitoring, SolarWinds DPA; JetBrains DataGrip AI Assistant and Navicat for IDE-side help. EU-based: Percona is US-headquartered, so check hosting; Scaleway, OVHcloud, IONOS and STACKIT offer managed PostgreSQL in EU data centres (tuning mostly manual).
• Choose A when: you already run one managed database and want low-effort recommendations.

Path B — Build on cloud APIs

• Models: frontier code/reasoning models (Claude, GPT, Gemini) or Mistral (EU provider, Codestral) via EU regions, for schema drafting and plan explanation.
• Integration: an agent in the IDE or CI (see CODE-001, CODE-002) with a read-only MCP database server; Azure published PostgreSQL skills and an MCP plugin for query/index tuning with assistants such as GitHub Copilot and Claude Code [4].
• Guardrails: read-only role, statement timeouts, no production credentials, row-level masking of personal data, SQL parser allow-list (no DDL/DML outside migration PRs).
• Choose B when: you want flexibility and have developers but no dedicated DBA.

Path C — Self-host (open source)

• Models: open-weight code models (Qwen3-Coder family, DeepSeek-Coder/V3 family, Codestral open variants, Granite code) served via vLLM or Ollama; a 30B-class quantised model fits one 24-32 GB GPU (HW2).
• Database tooling: PostgreSQL with pg_stat_statements [5], HypoPG (hypothetical indexes), Dexter (automatic indexer), pgMustard/explain.dalibo.com for plan visualisation, pgTAP for tests, Atlas, Liquibase, Flyway or Alembic for migrations, SQLFluff for linting.
• Effort: modest ops; the value lies in the verification harness, not the model.

Path D — Custom training

Rarely justified. Fine-tuning on your own schema conventions, naming rules and past query-rewrite pairs can help in large estates or for niche dialects (PL/SQL, T-SQL). Needs hundreds to thousands of reviewed examples. Learned optimisers and cardinality estimators remain mostly research or vendor-internal.

State of the art (2026)

• Plan-aware agents: models that call EXPLAIN (ANALYZE, BUFFERS), read the plan and iterate outperform one-shot rewriting; reasoning models are noticeably better at join and predicate logic than earlier generations.
• Text-to-SQL is strong on clean benchmarks but still weak on enterprise schemas: Spider 2.0 and BIRD remain far from saturated on real-world settings [1][2]. Check live leaderboards for current rankings.
• Vendor autonomy: managed databases increasingly create and drop indexes automatically from workload telemetry [3].
• Limits and failure modes: hallucinated columns, semantically different rewrites (NULL handling, duplicates, ordering), indexes that help one query and slow writes, advice tuned to tiny test data, dialect confusion, unsafe migrations (table rewrites, long locks), and plans that change with production statistics.

Shared resources & combinations

• Same stack (llm-code, agent-framework): CODE-001 IDE assistants, CODE-002 coding agents (open the migration PR), CODE-009 IaC for provisioning, CODE-005 for engine migrations.
• Adjacent: DATA-001 natural-language-to-SQL for business users (query generation without tuning), DATA-022 schema/EDI mapping, CODE-022 analytics engineering, CODE-023 performance optimisation, CODE-010/CODE-011 for observability signals.
• One self-hosted code model (HW2/HW3) serves all of these.

Effort, cost & KPIs

• Path A: days; mostly licence/usage costs of the platform.
• Path B: 2-6 weeks for an agent plus verification harness and CI integration; API cost is small versus DBA time.
• Path C: 1-3 months incl. GPU server and test-data pipeline.
• KPIs: p95/p99 latency of top-N queries, total DB CPU/IO, share of AI suggestions accepted after review, regressions caused by suggestions (target zero), migration failures and lock-time incidents, time from slow-query alert to fix.

People & change

Involve early: management (budget, rule that the agent never writes to production), the department lead or DBA as process owner, IT for the clone and accounts, the data owners of the source databases and IT security for credentials. The data protection officer joins when the clone could contain personal data; a DPIA is normally not needed for masked data, but an AVV (Art. 28 GDPR) is required with any API provider.

Works council: usually no co-determination for the tool itself, because it analyses database workload, not people. It depends where activity of individual developers is logged or evaluated (acceptance rates per person); then §87(1) no. 6 BetrVG applies, and §90(1) no. 3 requires timely information about AI-supported work processes. Public bodies: Personalrat.

AI literacy: support developers with a short training on verifying SQL suggestions (Art. 4 AI Act as amended by Regulation (EU) 2026/1744; GUIDE-006).

Communication and pilot: start with one volunteer team and a few known slow queries; show before/after benchmarks. The typical concern, "the AI will replace the DBA", is addressed by keeping the DBA as the approver of every migration. Humans stay in control: pull requests, review and staged rollout. See GUIDE-009 and GUIDE-008.

Risks, regulation & compliance

• EU AI Act: minimal risk; developer tooling falls under no Annex III category. AI literacy (Art. 4) applies. If the database later feeds a high-risk system (credit, HR), that system carries the obligations, not the tuning assistant [6].
• GDPR: schema and plan context rarely contain personal data, but query logs, sample rows and literals in slow-query logs often do. Mask or normalise literals, never send production rows to third-party APIs without a data processing agreement and a legal basis; prefer EU regions. Schema design should implement data minimisation, retention and erasure (Art. 5, 17, 25).
• Security: prompt injection through data or comments (e.g. a column comment instructing the agent), destructive SQL, leaked credentials. Mitigate with read-only roles, replicas, allow-lists and mandatory review.
• Operational: AI-suggested indexes affect write throughput and storage; always benchmark and stage.

Getting started

1. Enable pg_stat_statements (or your engine's equivalent such as Query Store) and list the top 10 queries by total time.
2. Create an anonymised replica or clone with realistic row counts; give the AI assistant a read-only role there.
3. Let an assistant explain the plan for one slow query, propose a rewrite, and verify result equivalence and timing yourself.
4. Add a CI check that lints migrations and flags lock-heavy DDL; require DBA or senior review for every schema change.
5. Track KPIs monthly and expand to schema design reviews for new features.

Sources

1. Spider 2.0: Evaluating Language Models on Real-World Enterprise Text-to-SQL Workflows — https://arxiv.org/abs/2411.07763
2. Spider2 benchmark repository (leaderboards) — https://github.com/xlang-ai/Spider2
3. Microsoft — Index tuning in Azure Database for PostgreSQL Flexible Server — https://learn.microsoft.com/azure/postgresql/flexible-server/concepts-index-tuning
4. Microsoft message centre summary — PostgreSQL skills and MCP plugin for Azure Database for PostgreSQL — https://mc.merill.net/message/AZ569664
5. PostgreSQL documentation — pg_stat_statements — https://www.postgresql.org/docs/current/pgstatstatements.html
6. Regulation (EU) 2024/1689 (AI Act) — https://eur-lex.europa.eu/eli/reg/2024/1689/oj
7. HypoPG — hypothetical indexes for PostgreSQL — https://github.com/HypoPG/hypopg