Database index review after launch: what to check in week two

Why launch week hides slow queries

Launch week rarely tells the truth about database performance. Traffic is uneven, the new feature has not settled into daily use, and many query patterns have not shown up yet. Index review works better after a short wait, when real behavior replaces guesses.

Staging data is one reason. Test databases are usually smaller, cleaner, and far less messy than production. They have fewer old records, fewer null values, fewer edge cases, and less history tied to each account. A query that feels instant on 50,000 tidy rows can drag once it hits millions of mixed records.

Real users are another reason. They do not follow the smooth path your team used in demos. They go back, refresh, open several tabs, sort a list three ways, then stack filters nobody thought to combine. That changes the SQL your app sends. Sometimes the same table suddenly gets hit with a new WHERE clause or ORDER BY that never got much attention before launch.

New filters and sorts are a common trap. One extra filter, one new sort column, or one search field can create a new query path. If the old index matched the old pattern, it may do almost nothing for the new one. The database then scans far more rows than expected.

Background jobs make this worse because they compete for the same tables. A release often adds more than user-facing screens. It also adds sync jobs, reports, notifications, cache refreshes, or analytics tasks. In staging, those jobs may run rarely or on tiny datasets. In production, they run beside user traffic and turn a mild slowdown into a visible problem.

A small example is enough. Imagine a new orders page that lets users filter by status and sort by updated_at. On launch day, only a few people use it, and it seems fine. Two weeks later, support uses it all day, finance exports from the same table, and a background job updates order statuses every few minutes. The table now has more reads, more writes, and a query pattern the original indexes did not match.

That is why slow queries often appear after release, not during it. The code did not suddenly get worse. Real usage finally exposed what the test environment could not.

What to review after two weeks

Two weeks gives you something staging never had: ordinary traffic. By then, strange filters, wider date ranges, and repeated background jobs start to pile up. That is the right time to review indexes against a normal workload, not launch-day spikes.

Start with slow query logs from regular weekdays. Pick days that reflect how people actually use the product. Tuesday and Wednesday usually tell a truer story than release day, a weekend, or the morning after a big announcement.

Then tie those slow queries back to the release. Look at the pages users open most, the jobs that now run on a schedule, and any reports or exports the feature introduced. New query paths often hide in routine actions, like opening a list view with one extra filter, not in the screen everyone demoed.

Sort by impact, not by surprise

Do not focus only on the single slowest query. That catches dramatic failures, but it misses the quieter problems that waste hours of database time every day. Sort queries by count, by total time, and by p95 time, then compare the results.

Each view shows a different problem. A query that runs 200,000 times at 40 ms can hurt more than one report that takes 8 seconds. A bad p95 often points to missing support for a common filter or sort.

Read paths on large tables usually deserve attention first. They are often safer to improve than write-heavy paths, and they affect more users. If a release added filtering on orders, invoices, events, or audit logs, check those queries early. Large tables punish full scans fast.

A familiar pattern shows up here. The feature works well in testing, but real users combine filters in ways nobody tried. They open a customer list, filter by status, sort by last activity, and move through page after page. One screen can turn into thousands of repeated reads against a large table.

If you only have time for a short review, start with four things: the top slow queries from weekday traffic, the most-used pages in the release, scheduled jobs that scan big tables, and reports with wide date ranges. That short pass usually finds the gaps worth fixing before users start complaining.

How to run the review

Two weeks after release, you finally have real behavior to inspect. People click in odd orders, filters combine in new ways, and one small feature can send far more reads to a table than anyone expected.

Start with the release itself, not the database. Open the changelog, sprint board, or release notes and list every user-facing change that could touch data. New search fields, sort options, status filters, dashboards, exports, background jobs, and admin screens all count.

Then work in a fixed order:

1. Map each shipped feature to the queries it triggers.
2. Pull production plans for the busiest queries using logs, database stats, or APM data.
3. Compare the plan you expected with the one the database chose.
4. Check whether the query shape changed after launch.
5. Change one thing at a time and measure again.

That order matters. If you jump straight to adding indexes, you can fix the wrong query or create overlap that slows writes. One careful change tells you more than three quick guesses.

Keep the scope tight. Review the busiest new paths first, then the paths that got slower after the release. If a feature barely gets used, leave it alone unless it causes obvious pain.

Teams that run lean systems often treat this as a short weekly habit, not a rescue job. That approach fits the kind of AI-augmented operations Oleg Sotnikov talks about: watch real behavior, make measured changes, and avoid broad rewrites when a small fix will do.

When the review ends, save the before-and-after plans with a short note about what changed and why. That record makes the next post-launch pass much faster, and it helps stop the same mistake from coming back.

A simple example from a real release

A new orders screen can look harmless in staging. With 5,000 rows, filters feel instant, sorting looks cheap, and nobody sees a problem.

Two weeks after release, production can tell a very different story. In this case, the same screen sat on top of about 8 million orders, and users worked fast. They filtered by status, picked a date range, sorted by newest, and switched between teams.

That traffic pattern matters more than the screen design. The page did not run one giant report. It ran the same small query again and again, usually to fetch the latest orders for one team.

The query looked roughly like this:

SELECT id, status, owner_id, created_at
FROM orders
WHERE team_id = $1
  AND status = $2
  AND created_at >= $3
  AND created_at < $4
ORDER BY created_at DESC
LIMIT 50;

The database already had separate indexes on status and created_at. That sounds reasonable, but it did not match how people used the page. The database still had to sift through too many rows and sort them, and that cost showed up every time someone jumped from one team view to another.

The fix was one composite index that matched the busiest path:

CREATE INDEX idx_orders_team_status_created_at
ON orders (team_id, status, created_at DESC);

After that, the database could go straight to the newest matching rows for a team and status inside the date window. A wait that hovered around 1 to 2 seconds dropped to well under 100 milliseconds on the path people used most.

The owner filter still existed, but it was not the first thing worth indexing. Real traffic showed that team switching, status filtering, and newest-first sorting happened far more often than owner-specific searches. That is the part staging missed.

This is why index review works best after real usage starts. You are not guessing which filter might matter later. You are looking at the exact query paths people hit all day, then adding the index that fits that path instead of indexing every column on the screen.

Signs an index will help

You usually do not need a full query rewrite to spot an indexing problem. Week two often gives it away. Real users repeat the same actions, and those repeated patterns stress the same columns again and again.

One strong sign is repetition in the WHERE clause. If the app keeps asking for orders by customer_id, tasks by status, or events by created_at range all day, the database keeps searching the same path. Start there, because repeated filters often turn into repeated table scans.

Sorting is another giveaway. If a query filters rows first and then sorts them, the database may still do a lot of work after it finds the matches. You will feel this in feeds, admin tables, and report screens where users open the same view many times per hour. A well-placed index can support both the filter and the sort.

Joins deserve a close look too, especially on large foreign key columns. A feature can look fine in staging with a small sample, then slow down in production when one join touches millions of rows. If a new screen joins comments to posts, invoices to customers, or log entries to users, check whether the join column has the right index.

Traffic shape matters as well. If reads jump after release but writes stay about the same, adding an index is often a reasonable trade. You still need to test it, because indexes add write cost. But on a read-heavy path, the trade often makes sense.

A report that burns far more time than its row count suggests is another classic clue. If a dashboard returns 200 rows but takes 4 seconds, the problem is rarely the result size. The cost usually sits in how the database finds, joins, and sorts the data before it returns those 200 rows.

Check these patterns first:

• The same filter appears in slow query logs many times a day.
• The query spends time sorting after it filters.
• A join uses a large foreign key column.
• Read load rises, but write load does not.
• A small report takes much longer than its result size suggests.

If two or three of those show up together, test an index before you start rewriting application code.

Mistakes that make things worse

Index review goes off track when a team reacts to every slow query by adding another index. That feels safe, but it often turns a small issue into a bigger one. Extra indexes take space, slow writes, and make future tuning harder.

One common mistake is building an index for a report that runs once a month. If that report scans a lot of data but nobody waits on it during the day, the index may not earn its keep. A busy checkout flow, message sync, or order update path matters more than a rare export.

Another trap is adding overlapping indexes because each query looks a little different on paper. Suppose you already have an index on (account_id, created_at, status) and someone adds (account_id, created_at) a week later. In many cases, the longer index already covers the shorter query path. Now you pay the write cost twice for almost no gain.

Where teams usually slip

The worst damage often happens on tables that change all day. Every insert, update, or delete has to maintain each index on that table. Add two or three extra indexes to a busy events or orders table, and write latency can climb fast. Read queries might get 20 milliseconds faster while writes get slower across the app. That is a bad trade.

Staging also fools people. Test data is smaller, cleaner, and less skewed than production. Query plans that look fine in staging can fall apart once real users hit odd filters, long date ranges, or uneven tenant sizes. If production says a query is slow after release, trust production.

A small example makes this obvious. A team launches a new customer dashboard. One internal analytics report runs slowly, so they add three indexes in one afternoon. The report speeds up a little, but order writes start lagging because the same table handles live activity. They also cannot tell which index helped, because they changed everything at once.

That is why it helps to stay disciplined:

• Change one index at a time when you can.
• Measure the before-and-after result.
• Keep notes on query time, row counts, and write impact.
• Remove duplicate or unused indexes after the test.

If you change five indexes before you measure one result, you lose the trail. Then the team starts guessing, and index bloat grows quietly until the next release makes it hurt.

Quick checks before you ship a change

A new index can fix a real slowdown, but it can also add drag elsewhere. Every index takes space, makes inserts and updates do more work, and can change which plan the database picks for nearby queries.

Start with usage, not theory. If a query spiked on release day because one background job ran too often, you may not need to keep tuning it. Look at the last several days of traffic and check whether users still hit that path in normal flow.

Then compare the plan before and after the change. Do not stop at "the query got faster on my laptop." Run EXPLAIN or EXPLAIN ANALYZE on the same query shape, with realistic filters and sort order. Check whether the database uses the new index, how many rows it reads, and whether it avoids a sort or a large scan. If the plan barely changes, the index may not be worth keeping.

A few checks catch most bad bets:

• Measure p95 latency, not just average time.
• Watch CPU during busy periods, not a quiet test run.
• Check lock waits if the query touches hot rows.
• Estimate index size before adding it to a large table.
• Think about write cost if that table gets frequent inserts or updates.

That write cost matters more than teams expect. On a read-heavy reporting table, one more index may be cheap. On an orders or events table that changes all day, the same index can slow writes enough to affect the rest of the app. If you run a lean setup, extra CPU from a bad index shows up quickly.

Keep cleanup separate from the urgent fix. If the new index solves a live problem, ship it, watch the numbers for a few days, and schedule a later pass for dead or duplicate indexes. Mixing cleanup with a release-day fix makes review harder and raises the chance of removing something you still need.

The short version is simple: confirm the query still matters, compare real plans, check p95, CPU, and lock waits, and count the write cost before you merge.

What to do next

Treat this review as part of the release, not as cleanup you do only after complaints arrive. Real traffic changes query paths, and those changes often show up after a week or two, when more users touch the feature in different ways.

A simple habit works well: put a review on the release calendar for day 10 to day 14. That gives your team enough real usage to spot slow queries early, while there is still time to fix them before support issues pile up.

Keep the process light. Add a follow-up task when the feature ships, save a few real sample queries with their execution plans in the same ticket, ask product and support where users feel delay, and make one small decision after the review: add an index, rewrite a query, or leave it alone.

Saving sample queries matters more than many teams expect. A ticket that says "search feels slow" is hard to act on. A ticket with the query, the plan, and one note about when it slows down gives the team something concrete to compare next time.

Product and support usually hear the problem first. Users rarely say, "this query needs an index." They say the page hangs after a filter change, or a report takes too long at 9 a.m. Those details often point straight to the places where staging missed real behavior.

If you already do this kind of review, keep the notes close to the release record. Over time, patterns show up. Maybe every export feature needs a second pass. Maybe sorting by a newly added field causes trouble. Those patterns help more than one-off fixes.

If the same problems keep coming back, outside help can speed things up. Oleg Sotnikov at oleg.is works with startups and small teams as a Fractional CTO, helping them review query paths, cut infrastructure waste, and build better release habits.

The next release should already have this review scheduled before you ship it. Once that becomes normal, post-launch indexing stops feeling like firefighting and turns into routine maintenance.