We put the index break-even at 0.67% of the table, and the second feature is where you find out

Three weeks for the second feature against an afternoon for the first, and the second diff was smaller. The reason is structural rather than a verdict on whichever model wrote the code.

An AI-built prototype has no seams, so the second feature is a global change rather than a local one. Three things are reliably absent once real traffic arrives: no index beyond the primary key, a secret in the repository or the client bundle, and a schema with no version history.

Why the second feature costs twice what the first one did

The second feature costs twice what the first one did because the prototype has no seams, so every change is a global change. A first feature is a straight line. One handler opens a connection, writes a row, reads a few back and returns JSON. Nothing on that line has to agree with anything else, and an agent gets it right quickly.

The second feature reads the same table from a second place. Shape is now a contract between two call sites, and there’s no copy of it in the repository. Rename a column and the edit is a search over strings rather than over types. No data-access module to change once. No schema file to migrate from. No test that fails when the two call sites disagree about what status is allowed to hold.

Typing was never the expensive part. Verification is. After the second feature you re-read every place the table is touched to see whether you broke the first one.

Three things an AI-built prototype is missing when real traffic arrives

Three things go missing together, and for one reason. No index on the ledger but its primary key, a live secret in the repository or the client bundle, and a schema with no version history. An agent builds what the prompt asks for. Nobody prompts for what only matters under load, under audit, or on the second deploy. Every article on this names the same three. Take the list as given — none of them prices the first one, which is the rest of this piece.

Ferrystone is a fiction — the library keeps it as a payments ledger so arithmetic about money has somewhere to live, and sizes it to whatever the article in hand needs, and writing payment code as if every request happens twice runs on the same invented system. Every parameter below was picked so a division comes out legible, and none was measured.

Ferrystone’s volume is 4,000 payments a month at £19.99 each, and each payment writes three ledger entries: debit, credit, fee. That count is chosen. So 4,000 × 3 = 12,000 rows a month, and over a chosen 24 months, 12,000 × 24 = 288,000 rows. Two hundred merchants share it, also chosen: 4,000 / 200 = 20 payments each per month.

The first feature reads that ledger by its primary key. The second reads it by merchant_id over a date range.

Does Postgres create an index for anything except the primary key?

No. PostgreSQL creates an index on its own for a primary key, a unique constraint or an exclusion constraint, and for nothing else. Section 11.6, Unique Indexes of the PostgreSQL 18 documentation says “PostgreSQL automatically creates a unique index when a unique constraint or primary key is defined for a table”. The third case is documented nowhere near the index chapter: the CREATE TABLE page says “Exclusion constraints are implemented using an index that has the same name as the constraint”.

Every other index is somebody’s decision, which is why an AI-built prototype ships with exactly one index per table. Whether the planner then uses what it has comes out of the cost constants in Section 19.7.2, Planner Cost Constants rather than out of your disk. One of them, random_page_cost, carries a default of 4.0, and the documented reason for that number is an assumption about cache rather than about hardware. Hold that thought. The derating run at the end turns on it.

The index break-even on a 116-byte row works out at 0.67% of the table

Break-even selectivity on a 116-byte row is 0.672%, about 1,935 rows out of Ferrystone’s 288,000. The term is ours: the fraction of a table above which PostgreSQL’s five cost constants, at their published defaults, make a sequential scan cheaper than an uncorrelated index scan. Uncorrelated is the load-bearing word. The model below charges a full random page for every matched row and gives the index no credit for cache, so 0.672% is a floor rather than a point estimate.

Five of the thirteen parameters in Section 19.7.2, Planner Cost Constants of the PostgreSQL 18 documentation, in the abstract cost unit where a sequential page fetch is 1.0.

The ladder starts at the row. A Ferrystone ledger entry is six bigint columns, two timestamptz, a uuid, two smallint and a char(3): 48 + 16 + 16 + 4 + 4 = 88 bytes of user data. Section 66.6.1, Table Row Layout says each row carries “a fixed-size header (occupying 23 bytes on most machines)”, which MAXALIGN rounds to 24 on a 64-bit build, and that a page’s line pointers each require “four bytes”. The stored tuple is 112 bytes and the pointer makes 116. Section 19.15, Preset Options gives block_size a default of “8192 bytes.” Call 8,100 usable; that is chosen. Then 8,100 / 116 = 69.8 and a page holds 69 rows, 288,000 / 69 = 4,173.9 pages, and 4,174 × 8 kB = 33,392 kB = 32.6 MB.

Now both sides. A sequential scan reads every page once and processes every row: 4,174 × 1.0 for pages plus 288,000 × (0.01 + 0.0025) = 3,600 for rows, so 4,174 + 3,600 = 7,774 cost units. One matched row through an uncorrelated index costs a random page fetch plus three CPU terms: 4.0 + 0.01 + 0.005 + 0.0025 = 4.0175. Divide one by the other. 7,774 / 4.0175 = 1,935 rows, and 1,935 / 288,000 = 0.672%. Above that fraction the planner reads the table; below it the index earns its place.

Second route, and it never computes 1,935. The scan spends 7,774 units across 288,000 rows, so a scanned row costs 7,774 / 288,000 = 0.026993 units against 4.0175 for a matched row. One match is worth 4.0175 / 0.026993 = 148.8 scanned rows, and the index pays while matches stay under 1 / 148.8 = 0.672% of the table.

The row count never entered that, and it can’t. Write f = (P + 0.0125N) / (4.0175N), substitute P = N / 69, and N cancels: f = (1/69 + 0.0125) / 4.0175 = 0.672%. Double the history to 576,000 rows and the long way round agrees: 8,348 pages, 8,348 + 7,200 = 15,548, 15,548 / 4.0175 = 3,870, and 3,870 / 576,000 = 0.672%. Change the row width and it does move. At 60 bytes a page holds 8,100 / 60 = 135 rows, and f = (1/135 + 0.0125) / 4.0175 = 0.496%. Row width sets this number and table size does not.

Ferrystone’s second feature at both extremes, in PostgreSQL’s abstract cost unit, where a row is one ledger entry matched by a single merchant’s filter.

Both rows are chosen: a median merchant at 10 payments a month, a largest at 10% of volume or 400 a month, so 10 × 3 × 24 = 720 rows and 400 × 3 × 24 = 28,800. Then 720 × 4.0175 = 2,892.6 and 7,774 / 2,892.6 = 2.69, while 28,800 × 4.0175 = 115,704 and 115,704 / 7,774 = 14.9. The obvious estimate says add the index and watch the query fly. The division gives 2.69x for the typical merchant — and a refusal for the biggest.

And the index does nothing at all for Ferrystone’s largest merchant, while still costing something on every INSERT.

I had this threshold filed in my head at roughly a tenth of a per cent for years, and the CPU terms turn out to be most of the difference. Two documented terms are missing from the index side, and they pull against each other. The model charges nothing for descending the btree, which flatters the index and puts 0.672% above where it belongs. It also ignores effective_cache_size, which the same section says “is factored into estimates of the cost of using an index” and gives a default of 4 gigabytes. That one pulls harder, and the other way. I don’t know what correlation reads on your ledger either. Table 53.29 says an index scan on a column near −1 or +1 “will be estimated to be cheaper than when it is near zero”, and an append-only ledger sits near +1 on created_at and near zero on merchant_id. A full random page per matched row is honest for a merchant filter and pessimistic for a date range. Read EXPLAIN before betting an afternoon on 0.672%.

Run this ten-minute test on your own prototype

Three checks, five commands, each with an answer that counts as a failure. The first asks what indexes exist, how often the database went to the table anyway, and how many rows it read when it did. PostgreSQL’s Examining Index Usage page, Section 11.12, runs two paragraphs of prose before it gets anywhere, then opens its list of tips with four words — “Always run ANALYZE first.”

SELECT s.schemaname, s.relname,
       s.seq_scan, s.seq_tup_read, s.idx_scan, s.n_live_tup,
       count(i.indexrelid) FILTER (WHERE NOT i.indisprimary) AS extra_indexes
FROM pg_stat_user_tables s
LEFT JOIN pg_index i ON i.indrelid = s.relid
GROUP BY s.schemaname, s.relname, s.seq_scan, s.seq_tup_read, s.idx_scan, s.n_live_tup
ORDER BY s.seq_scan DESC;

Table 27.29 of the monitoring statistics documentation gives seq_scan as the “Number of sequential scans initiated on this table”. Zero non-primary-key indexes, and seq_scan in the thousands. That is the first feature’s unpaid bill. Counters reset when somebody calls pg_stat_reset(), so a comfortable number might just mean a recent reset.

The second check is three greps. The first two don’t look for the same thing.

grep -rEo '[A-Za-z_]*(KEY|SECRET|TOKEN|PASSWORD)[A-Za-z_]*' dist/assets/*.js | sort -u
grep -rEoh 'sk_live_[A-Za-z0-9]{8,}|AKIA[0-9A-Z]{16}|eyJ[A-Za-z0-9_-]{10,}\.' dist/assets/*.js
git log --all -p -S 'SECRET' -- . | head -40

A name grep catches a bundle that hands the whole environment object to the browser. A value grep catches one where the name was replaced by its value at build time, which is what Vite does by default. Neither sees a secret that only ever lived in an ignored file. But the test proves presence, never absence.

The third asks whether the database knows what version its schema is.

SELECT to_regclass('public.alembic_version'),
       to_regclass('public._prisma_migrations'),
       to_regclass('public.schema_migrations');

Three NULLs mean nothing does. The Alembic name is a documented default and configurable, so a NULL there is suggestive, not conclusive.

A secret in the repository and a secret in the client bundle are different problems

A secret in the repository is a revocation problem and a secret in the client bundle is an architecture problem — rewriting git history fixes neither. The OWASP Secrets Management Cheat Sheet sets the order in Section 9.2, Remediation: Revocation, Rotation, Deletion and Logging. Step 1 says exposed keys “should undergo immediate revocation”. Step 3 settles the argument teams have instead: secrets in code “could have commit history for the exposure squashed”, but “this may introduce other problems as it rewrites git history”, so the rewrite is optional and last. Section 2.7.2 declines to name a rotation interval, and puts a secret’s lifetime anywhere from minutes to years, so I won’t hand you ninety days either.

The bundle half is a different failure, and one prefix decides it. Vite’s Env Variables and Modes guide says “Variables prefixed with VITE_ will be exposed in client-side source code after Vite bundling”, and its own example is the prototype’s mistake: VITE_SOME_KEY=123 reaches the browser, DB_PASSWORD=foobar does not. Those values are “bundled into your source code at build time”, and the same page says they are “statically replaced at build time to make tree-shaking effective”. Static replacement is why the name grep above comes back empty on plenty of bundles that leak. And envPrefix is configurable too, so one grep is not an audit.

How to add migrations to a database that was never versioned

Autogenerate one revision describing the schema as it stands, stamp it, and never run it against the live database. Alembic’s commands reference describes stamp() as “‘stamp’ the revision table with the given revision; don’t run any migrations”, and ensure_version() as “Create the alembic version table if it doesn’t exist already.” Two calls, and the baseline exists.

The table name is itself a default, documented where nobody looks. The runtime API reference for EnvironmentContext.configure() gives version_table as “The name of the Alembic version table. The default is ‘alembic_version’.” A parameter list far down an API page is not the tutorial, which is why the test above treats a missing table as a hint rather than a verdict.

Then the first real migration. Well below the fold on PostgreSQL’s ALTER TABLE page, under the #SQL-ALTERTABLE-NOTES anchor, the third paragraph reads:

Adding a column with a volatile DEFAULT (e.g., clock_timestamp()), a stored generated column, an identity column, or a column with a domain data type that has constraints will cause the entire table and its indexes to be rewritten.

The paragraph above it gives the safe case: a non-volatile DEFAULT is “evaluated at the time of the statement and the result stored in the table’s metadata”. So the difference is which default you typed. One migration finishes instantly; the other rewrites all 4,174 pages under an ACCESS EXCLUSIVE lock.

We would not rebuild an AI-built prototype from scratch

A rebuild re-earns nothing the prototype already proved and restarts the same clock, so we don’t quote one for a prototype with live users. I would turn the job down if a from-scratch rewrite were what was being bought. The honest case is real: you get the seams in one pass, and a working prototype is a specification you can run. The cost is the second-feature tax paid from zero across every feature rather than one. A rewrite wins where there are no users and no data worth keeping. Ferrystone has both.

Repair runs in a fixed order, and the order is the argument. The seam goes in first, at the data-access boundary: one module owns the ledger table, every call site routes through it, no SQL anywhere else. The same argument one scale up is why monolith splits fail at the database. Second the schema baseline: every later repair is a migration with nowhere to live until the version table exists. Third the secrets — revoke, rotate, then decide about history. Index last.

Index last because of a flag that cannot sit where you need it. CONCURRENTLY skips the write lock a standard build takes, and PostgreSQL’s CREATE INDEX page says “Another difference is that a regular CREATE INDEX command can be performed within a transaction block, but CREATE INDEX CONCURRENTLY cannot.” Alembic documents transaction_per_migration as “if True, nest each migration script in a transaction rather than the full series of migrations to run”, and it ships False, so the whole series runs inside one transaction. Type BEGIN; CREATE INDEX CONCURRENTLY ...; yourself and Postgres refuses the statement. The repair that most needs live traffic is the one the tooling will not perform.

One number decides whether this is worth doing, so derate it. Section 19.7.2 gives a reason for the 4.0 that is about cache rather than disks: random reads “are assumed to be in cache”, and lowering the constant “might be appropriate” where the database is smaller than total server memory. The planner assumes as much already. Its effective_cache_size default is 4 gigabytes against a 32.6 MB ledger: 4 GB is 4,096 MB, so 4,096 / 32.6 = 125.6 times over. Set random_page_cost to 1.0 and a matched row costs 1.0175, which puts break-even at 7,774 / 1.0175 = 7,640 rows, or 2.653% — a move of 7,640 / 1,935 = 3.95x. The decision does not flip. 720 rows is still below the line, and 28,800 is still 3.8x above it. Work of that shape is an architecture review on our software development page, not a build.