Make the database do the minimum: six rules I give my AI

I build CRMs and back-office systems for small Italian companies, and I write most of the code with an AI assistant. That works well for a lot of things. It works badly for one thing in particular: the database.

My AI writes queries that are correct. They return the right rows, the tests pass, the page loads. With a hundred rows in the table. After a year of real use, the same page can take seconds, and the database plan you're paying for is suddenly too small.

The reason is simple. In my setups the database is the small, expensive resource, and the application server is the big, cheap one. Every query, every megabyte of database memory, has to justify itself. The AI doesn't know that. It writes the shortest code that works, and the shortest code usually asks the database to do more than it should.

One system I worked on had a sync loop that re-read calendars every minute "to see if anything changed". Nobody noticed it in development. In production, that one loop made most of all the requests the database received.

So I wrote the rules down. Here are six of them, the ones that make sense without any context.

1. No reads inside a loop

What the AI writes, if you let it:

const orders = await db.query('select id, customer_id from orders where day = $1', [day]); for (const order of orders) { order.customer = await db.query('select name from customers where id = $1', [order.customer_id]); }

One query for the list, then one more for each row. This is the classic N+1 problem, and Markus Winand explains it well on use-the-index-luke.com, in the section on nested loops: it's usually an ORM doing it silently, but an AI does it just as happily by hand. With 10 orders it's invisible. With 2,000 it's 2,001 round trips.

The rule: read each piece of data once per request, as a set. Independent reads start together, not one after the other.

const orders = await db.query('select id, customer_id from orders where day = $1', [day]); const customerIds = [...new Set(orders.map((o) => o.customer_id))]; const customers = await db.query('select id, name from customers where id = any($1)', [customerIds]);

Two queries, whatever the number of orders.

2. Short, targeted reads

What the AI writes:

const all = await db.query('select * from contacts'); const open = all.filter((c) => c.status === 'open').slice(0, 50);

It reads every column of every row, ships them to Node, and throws most of them away. And on every page open it adds a select count(*) of the whole table, because the UI shows "1,234 contacts".

The rule: only the columns you need (never select * on a wide table), filtered by columns that have an index, with a limit. Never "read everything and filter later". No exact counts of a big table on every page load.

const open = await db.query( `select id, name, updated_at from contacts where status = $1 order by updated_at desc limit $2`, [ContactStatus.Open, config.contactsPageSize], );

Plus an index on (status, updated_at). Winand's SQL Performance Explained is the book that taught me why the index has to match both the filter and the order, and why a limit only helps when the database can stop early.

3. Dashboards read a saved summary

Ask an AI for "a dashboard with the number of open deals per stage" and you'll get one of two things. Either a big group by over the live tables on every page view, or, if you said "make it fast", a trigger that updates a counter on every insert and delete.

The first one gets slower every month. The second one is worse: every write now pays for the dashboard, the counter drifts the first time a bulk import skips the trigger, and nobody can explain why the number is wrong.

The rule: a dashboard reads a small summary table with the numbers already computed. A background job rebuilds it on a slow schedule (the interval lives in configuration), and there's an "Update now" button next to "Last updated". The rebuild:

runs one at a time: it takes a database lock without waiting, and if the lock is taken it answers "already running";
replaces, never adds: it recomputes from the real rows, so a missed run can't leave a wrong total behind;
is checked every night against a real count, with an alert if they differ.

And no triggers for counters or summaries.

type RefreshOutcome = 'refreshed' | 'already-running'; async function refreshDealSummary(db: Db): Promise<RefreshOutcome> { return db.transaction(async (tx) => { const [{ locked }] = await tx.query( 'select pg_try_advisory_xact_lock($1) as locked', [LockKey.DealSummary]); if (!locked) return 'already-running'; await tx.query('delete from deal_summary'); await tx.query(`insert into deal_summary (stage, open_deals, computed_at) select stage, count(*), now() from deals where closed_at is null group by stage`); return 'refreshed'; }); }

pg_try_advisory_xact_lock returns immediately instead of waiting, and the lock goes away with the transaction. Two people pressing "Update now" at the same moment get one rebuild and one "already running".

4. External calls after the response

What the AI writes for "save the contact form and email me":

await db.transaction(async (tx) => { await tx.query('insert into messages (...) values (...)', [...]); await emailProvider.send(notificationFor(message)); // waits on the network });

It looks tidy: everything in one transaction. But the transaction stays open, holding a connection and its locks, for as long as the email provider takes to answer. On a slow day that's seconds, and connections are exactly what a small database has the fewest of.

The rule: never keep a transaction open while you wait for an external service. Commit first, answer the user, then do the outside work (after the response, or through a queue).

const message = await saveMessage(db, form); // short transaction, committed scheduleAfterResponse(() => notifyOwner(message)); return { status: 'received' };

This is how my own site handles its contact form: the message is saved, the visitor gets the answer, and the notification goes out after the response. If the notification fails, that has its own path (I wrote about uncertain deliveries in the first article of this series), but it never holds the database hostage.

5. No state in the process

This one is not about queries, but it ends in the same place: a system that breaks when it grows.

My site runs headless Chrome to read web pages, fill in forms and print PDFs. Each Chrome is heavy, and the server is shared with other systems. So there's a brake: a small semaphore that lets at most 2 Chrome instances run at the same time, puts the next requests in a first-come-first-served queue, and fails cleanly if a request waits too long. The limit and the wait come from configuration.

It works. But here's the honest part: the semaphore lives in the memory of one process. With one copy of the server, the limit is 2. Start a second copy behind a load balancer and each copy has its own semaphore. The limit is now 4, and nobody changed a line.

This is the stateless processes rule from Adam Wiggins' The Twelve-Factor App, factor VI: the memory of a process is fine for a brief cache, not for anything the next request depends on. Counters, limits towards providers, queues, tokens: they belong in the database or another shared place.

I've seen the expensive version of this mistake. Another system I worked on kept its rate limit towards a phone service in memory. When it went to several copies, the limit multiplied with them, and the provider started saying "too many requests".

So my site's project file says, in plain words, that it runs as one copy only, and that the Chrome brake and a few other in-memory counters must be rewritten before a second copy exists. I'd rather have a known, written limitation than an invisible one. If you can't fix it today, write down where it breaks.

6. Measure first

Every rule above can be overdone. An AI that has read them will happily add indexes nobody uses and summary tables for pages nobody opens.

Donald Knuth's famous line, "premature optimization is the root of all evil", comes from his 1974 paper Structured Programming with go to Statements. People quote only that half. In the paper, the next sentence says we should not pass up our opportunities in the critical 3%, and then he says that programmers' intuitive guesses about which code is critical fail, and that measurement tools are what tell you. The point is not "don't optimize". It's "measure, then optimize what the measurement shows".

On Postgres, pg_stat_statements counts time and rows for every query shape, and a slow-query log catches the rest. My site's database has both turned on. The rule I give my AI: every performance fix comes with the number before and after, and the command that produced it. Martin Kleppmann, in the first chapter of Designing Data-Intensive Applications, makes the same point from the other side: you can't talk about scaling until you've described your load and measured your performance.

The check that blocks

As always with an AI, a rule in a document is a rule it will forget the next time it writes a new page. So the rule has to become a test that goes red.

The test I want on every important action counts the database queries it makes, and fails if the number grows with the data:

it('lists the day orders with a constant number of queries', async () => { const db = countingDb(testDb); // wraps the client, counts every query await seedOrders(testDb, { count: 1 }); await listDayOrders(db, today); const withOne = db.queriesMade(); db.resetCount(); await seedOrders(testDb, { count: 50 }); await listDayOrders(db, today); expect(db.queriesMade()).toBe(withOne); // 50 orders must not mean 50 more queries });

There's no magic budget in it: the test only says that more rows must not mean more queries. When the AI brings back a read inside a loop, this goes red, and it has to explain itself.

I'll be honest: my rules file asks for this test on every action, but not every project of mine has it yet. It's the next check I'm adding, because it's the one that catches the mistake an AI makes most often.