Databases for AI Builders: Why Your AI-Generated App Gets Slow and Loses Data

Quick answer

• AI assistants write queries that are correct for small data and wrong for large data. The two causes are missing indexes and the N+1 query pattern.
• A database with 50 rows in development hides every performance problem your app will have at 500,000 rows.
• Writes that must succeed or fail together need a transaction; writes that arrive twice need idempotency.
• Connection pooling is why your app works with one user and exhausts the database with fifty.
• Migrations, backups, and a plan for slow queries are the three things assistants almost never generate unprompted.
• Engine, key, and column-type choices are the expensive ones to reverse. Decide those before the first migration, not after.
• Every concept below links to a full guide. Read the ones matching your current symptom.

The database gap in AI-generated apps

An AI coding assistant writes database code the way it writes everything else: it produces something that works against the data it can imagine. With no rows, a SELECT * with no index is instant. With no second request touching the same row, a missing transaction never fails. With one developer, an unpooled connection never hits a limit.

Those assumptions are all correct in development and all false in production, which is why database problems feel like they appear suddenly. The app didn’t change — the data did, and the data was the missing constraint the whole time.

The guides below cover the database work an assistant skips, in roughly the order it bites: engine and schema decisions, schema changes, query performance, concurrency, connections, consistency, and the backups nobody asks for until they need them.

Choosing and designing: The decisions you only get to make once

Which engine you run, how your tables are keyed, and what type each column is — those are the choices that get expensive after real data exists. Every other topic in this hub is an edit; this one is a design. Read these three before your first migration rather than after your first data loss.

• How to Choose a Database for Your App: A Decision Guide — Workload, consistency, data shape, scale, and ops burden, with a comparison table and the cases where each option is wrong.
• What Are the Different Types of Databases (and What Is Each One For)? — Relational, document, key-value, wide-column, graph, time-series, search, and vector, family by family.
• How to Design a Database Schema (That You Won’t Have to Rewrite) — Queries first, then keys, relationships, types, constraints, indexes, and a migration order that survives deploy day.

Schema and migrations: Changing structure without losing data

Your schema will change, and the code that changes it runs against production data that must survive. Assistants generate schema definitions readily and migration plans rarely, and when they do, the plan often drops or rewrites a column in a single step — which is fine on an empty table and unrecoverable on a populated one.

• What Are Database Migrations (and Why Do They Break Deploys)? — Versioned schema changes, and why they fail in the middle of a deploy.
• What Is an ORM (and When Should You Use Raw SQL)? — The abstraction that decides how your schema gets expressed.
• How to Set Up Automated Database Backups — The safety net that decides whether a bad migration is an incident or a footnote.

Queries and indexes: Why the app is fast and then isn’t

This is where most “the AI’s app got slow” incidents live. A query with no index scans every row; an ORM that loads a list and then one related record per item turns a single request into hundreds of round trips. Both are invisible on small data, both are common in generated code, and both have fixes that are cheaper than rewriting the feature.

• What Is Database Indexing (and Why Is My Query Slow)? — The single biggest lever on read performance.
• How to Add an Index to a Slow SQL Query — Reading the query plan and adding the right index.
• What Is the N+1 Query Problem? — The ORM performance killer generated code produces constantly.
• How to Debug AI-Generated Database Queries That Return Wrong Results — Missing joins, wrong filters, and silent type coercion.

Transactions and concurrency: When two requests touch the same row

Databases are shared, and requests overlap. Two operations that each look correct in isolation can interleave into a wrong result — a balance read by both requests before either writes, a stock count decremented twice, a duplicate record created by a retry. Assistants write the happy path; the guarantees around it are yours to add.

• What Is a Database Transaction (ACID)? — Atomicity, consistency, isolation, durability, without the textbook padding.
• What Is a Race Condition? — The interleaving failure, with a concrete example.
• What Is Idempotency (and Why Does It Matter for APIs)? — Why the same request arriving twice must not charge twice.

Connections: Why you run out of them

Every database connection is a server-side resource. Generated code often opens one per request, which works until concurrency rises and the database refuses new clients. Connection pooling — a fixed set of connections shared by all requests — is the fix, and “too many connections” is the error that tells you it’s missing.

• What Is a Connection Pool? — How pooling works and where to configure it.
• How to Fix ‘Too Many Connections’ in Postgres — Diagnosing the exhaustion, including the pooled-proxy route.
• What Is Graceful Shutdown? — Releasing connections and finishing in-flight queries when the app stops.

Consistency and distribution: Why reads return stale data

Once you add a read replica, a cache, or a multi-region setup, “read what you just wrote” stops being free. Eventual consistency isn’t a bug — it’s the tradeoff that makes those systems fast — but generated code rarely accounts for it, so users see their own update vanish for a moment.

• What Is Eventual Consistency (and Why Do My Reads Return Stale Data)? — The guarantee you actually get from replicated reads.
• Eventual Consistency vs Strong Consistency: What’s the Difference (and When Does It Matter)? — The decision, with the cases where each is required.

Caching: Making reads cheap without serving wrong data

Caching is the standard next step when a query is slow and an index isn’t enough — and the standard way apps start returning data that’s quietly outdated. The questions are what key the value is stored under, how it gets invalidated, and what happens when the cache is empty or unreachable.

• What Is Caching (and the Most Common Ways to Get It Wrong)? — Cache keys, invalidation, and the failure modes.
• How to Add Redis Caching to Your App — The implementation, including the cache-aside pattern.
• What Is Memoization (and When Does It Actually Help)? — Caching inside a process, and why it doesn’t survive a restart.

Durability: Backups, retention, and knowing what you’d lose

Retention is a product decision disguised as an infrastructure setting. The relevant question isn’t “do we have backups” but “what is the largest amount of data we could lose, and how long would recovery take.” Assistants don’t raise it, because it isn’t part of the feature being built.

• How to Set Up Automated Database Backups — Scheduling backups and, more importantly, testing restores.
• What Is a Database Transaction (ACID)? — The durability guarantee inside the database, which is not the same as having a backup.
• How to Monitor Your Cron Jobs — Because a backup job that silently stopped looks identical to one that works.

Common mistakes: The twelve that keep showing up

Most database problems in generated apps reduce to a short list of recurring defects — a query with no index, a loop that queries per row, money in a float, a naive timestamp, a uniqueness rule living only in application code. They’re worth reading as a set, because they share a cause and a detection method.

• The 12 Database Mistakes That Show Up in AI-Generated Apps — Each defect with the failure it causes, why generated code produces it, and the fix.

Where this bites vibecoders

The prompt was “add a search endpoint.” The assistant returns a working endpoint: it fetches matching records, then loops over them to load the author of each one, then filters in application code because it wasn’t sure how the ORM expresses a LIKE. All of that is fine against the twelve posts in your development database. In production, that endpoint runs one query per result, scans a table with no index on the searched column, and holds a connection from the pool for the whole time. Nothing in the diff looks wrong, the tests pass, and the first symptom is a page that takes nine seconds — which is also the first moment you learn that the development dataset was hiding the entire problem. Reviewing generated database code means asking “how does this behave at a million rows and fifty concurrent users,” not just “does it return the right answer.”

Checklist

• Test every new query against a realistically sized dataset, not your development seed data.
• Look at the query plan before trusting a query that will run often.
• Batch related loads instead of fetching one record per item in a loop.
• Wrap multi-step writes that must not half-succeed in a transaction.
• Make retryable write endpoints idempotent before you add retries.
• Use a connection pool, and set its size deliberately.
• Review every migration for destructive steps before it runs against production.
• Verify a backup by restoring it, not by confirming the job ran.
• Decide how stale a read is allowed to be before adding a replica or cache.

FAQ

Why is my app fast in development but slow in production?

Almost always row count. Development databases have tens of rows, so a full table scan takes a millisecond and a missing index goes unnoticed. The same query against production data reads millions of rows or fetches related records one at a time, and the response time goes from instant to unusable — with no code change in between.

Do I need to know SQL if my AI writes the queries?

You need to be able to read it. You can’t diagnose a slow endpoint, spot a missing filter, or tell whether a migration is destructive without understanding what the query asks the database to do. Reading SQL — joins, where clauses, and what an index changes — is the minimum, and it’s a few hours of learning rather than a course.

When should I add an index?

Add one when a specific query is slow and you can point at the column it filters or sorts on. Indexes cost write time and storage, so adding them everywhere is its own problem. The reliable signal is evidence: the query plan shows a sequential scan over a large table, or an endpoint slows down as the table grows.

Should I use an ORM or raw SQL?

Use the ORM for ordinary reads and writes, because it handles parameterization, which prevents SQL injection. Drop to raw SQL for reporting, bulk operations, and anything where the generated query is clearly inefficient. An ORM is a convenience layer over SQL — knowing SQL is what tells you when the ORM’s output is wrong.

Related topics

• What Is Database Indexing (and Why Is My Query Slow)?
• How to Add an Index to a Slow SQL Query
• What Is the N+1 Query Problem?
• What Is a Database Transaction (ACID)?
• What Are Database Migrations (and Why Do They Break Deploys)?
• What Is an ORM (and When Should You Use Raw SQL)?
• What Is a Connection Pool?
• What Is Caching (and the Most Common Ways to Get It Wrong)?
• Software Engineering for AI Builders

Sources

• PostgreSQL Documentation
• Use The Index, Luke — A Guide to Database Performance
• MySQL Reference Manual — Optimization
• Redis Documentation — Caching patterns