Indexes Aren't Free: A Practical Guide to When (and How) to Add Them

It's 2am and the on-call engineer is staring at a dashboard. The support dashboard — the one that lists open tickets — has gone from loading instantly to timing out. Nothing was deployed today. The team didn't touch that query in months. Nobody did anything wrong, either: support_tickets has been growing steadily for three years, the way a table does at a company that's actually succeeding — a few hundred rows a day, never a single dramatic spike, until one ordinary Tuesday it crosses the line from "a scan is fine" to "a scan takes 40 seconds," and nobody was watching closely enough to notice exactly when.

The fix, it turns out, is one line:

CREATE INDEX idx_tickets_status ON support_tickets (status);

The query that took 40 seconds now takes 4 milliseconds. Same data, same hardware, same query. So what actually changed?

That question — and the judgment calls hiding behind it — is what this article is about: how do you know when to add an index, when not to, and how does the calculus change once your database is running on more than one machine?

A quick note on scope before diving in: this article sticks to relational databases. For single-node code examples and vendor-specific detail, that means PostgreSQL and MySQL — the two most common relational databases in production, and contrasting exactly two keeps the comparisons concrete instead of turning into a shallow survey of everyone's syntax. The underlying ideas (selectivity, composite indexes, the read/write/storage trade-off) apply broadly to relational databases in general. Once the table is sharded across a cluster, the vendor examples switch to CockroachDB and Google Spanner — both distributed relational databases, but a different pair, chosen because they're two concrete implementations of the same strongly-consistent approach to distributed indexing, which is what that section is actually illustrating.

Contents

• What an index actually buys you
• Meet the table
• A repeatable way to decide
• Applying it on a single node
  • Composite indexes and column order
  • Covering indexes
  • Partial indexes
  • Expression indexes
• The cost side, plainly
• When the table lives on more than one machine
• Before you add an index
• Closing

What an index actually buys you

Think of a database table without an index the way you'd think of a phone book with the pages torn out and shuffled: to find "Smith," you have no choice but to check every single page. An index restores the order. It's a separate, sorted structure that points back to where each row actually lives — a card catalog next to the shelves, not the shelves themselves.

Most relational databases build this sorted structure as a B-tree variant — in practice almost always a B+tree, where every actual data pointer lives in the leaf nodes and the leaves are linked together for fast range scans. Hash indexes exist too (fast for exact-match lookups, useless for ranges or sorting), but you rarely reach for one by hand — Postgres discourages them for most cases and MySQL/InnoDB mainly uses them internally for its adaptive hash index, not as something you create yourself. If you want the internal mechanics — page splits, node fanout, why B+trees specifically and not plain sorted arrays — Use The Index, Luke and the classic CMU database course materials cover it well. (Both Postgres and MySQL also support more specialized index types — GiST/GIN for full-text and JSONB, BRIN for huge sequentially-loaded tables, and so on — but B+tree covers the large majority of real-world cases and is what the rest of this article assumes.)

That's the whole shape you need to carry forward: a few levels of routing nodes get you to a leaf, and the leaf points at the actual row.

What matters for decision-making is simpler:

Without an index, the database has no choice but to read every row — a sequential scan. With one, it can jump almost directly to the rows that match — a seek. That gap barely matters at 500 rows. It's the entire difference between "instant" and "timed out" at 4 million.

But here's the sentence that should sit at the back of your mind for the rest of this article: an index is not a free performance upgrade. Every index you add has to be updated on every INSERT, UPDATE, and DELETE that touches its columns, and it takes up disk space. You're not choosing "fast" over "slow" — you're choosing to pay a cost on writes to get a discount on reads. Deciding whether that trade is worth it is the actual skill.

Meet the table

Every example from here on uses a single table, support_tickets:

CREATE TABLE support_tickets (
    id           BIGINT PRIMARY KEY,
    customer_id  BIGINT NOT NULL,
    status       TEXT NOT NULL,       -- 'open', 'pending', 'closed'
    priority     TEXT NOT NULL,       -- 'low', 'medium', 'high'
    assignee     TEXT,
    created_at   TIMESTAMP NOT NULL,
    closed_at    TIMESTAMP
);

A support desk is a good stand-in for a lot of real systems: most tickets end up closed and are rarely queried again, while a small, constantly-churning slice stays open and gets hit by dashboards, SLAs, and search constantly. That skew is exactly the kind of thing that makes index decisions interesting.

A repeatable way to decide

Rather than a list of index types to memorize, it helps to have one process you can run any time a query is slow — on a single Postgres box or across a twelve-node cluster. We'll use this same five-step path twice in this article: once for a single database, once for a distributed one.

1. Symptom — something is slow, or a query is about to become slow as the table grows.
2. Diagnose — confirm why, don't guess. EXPLAIN ANALYZE and a look at the access pattern.
3. Candidates — what are the realistic options (a plain index, a composite index, a partial index, or possibly no index at all)?
4. Decide — weigh selectivity, read/write ratio, table size, and how often the query actually runs.
5. Verify — re-run the diagnosis and confirm the fix actually did what you expected.

---

Applying it on a single node

1. Symptom

The open-tickets dashboard is slow.

2. Diagnose

Both PostgreSQL and MySQL support EXPLAIN ANALYZE, though their output formats differ. In Postgres:

EXPLAIN ANALYZE
SELECT * FROM support_tickets WHERE status = 'open';

Seq Scan on support_tickets  (cost=0.00..84821.00 rows=41302 width=96)
                              (actual time=0.02..612.31 rows=40118 loops=1)
  Filter: (status = 'open'::text)
  Rows Removed by Filter: 3959882
Planning Time: 0.11 ms
Execution Time: 613.02 ms

Seq Scan and Rows Removed by Filter: 3959882 tell the story: the planner read every one of the ~4 million rows to find the ~40,000 that matter. MySQL's EXPLAIN (or EXPLAIN ANALYZE since MySQL 8.0.18) shows the equivalent as type: ALL with a high rows estimate — same underlying problem, different vocabulary.

3. Candidates

A single-column index on status is the obvious first move:

CREATE INDEX idx_tickets_status ON support_tickets (status);

That alone gets us from a sequential scan to an index scan. But two things are worth noticing before stopping there.

Composite indexes and column order

If the dashboard actually filters on status = 'open' AND priority = 'high', a composite index does more work than two separate single-column ones:

CREATE INDEX idx_tickets_status_priority ON support_tickets (status, priority);

Column order isn't cosmetic — it determines what the index can actually help with. Postgres and MySQL both apply a leftmost-prefix rule: the index is usable for queries that filter on status alone, or on status and priority together, but not for a query that filters on priority alone, because the index isn't sorted by priority first.

Covering indexes

If a query only needs columns that are already in the index, the database can skip touching the actual table row entirely — an index-only scan. Understanding when that's possible requires one concept that's been implicit until now: clustered vs. secondary indexes.

Clustered vs. secondary, briefly: a clustered index physically determines how a table's rows are stored on disk — the table itself is sorted in that index's order, so a table can have at most one (rows can only be sorted one way at a time). A secondary (non-clustered) index is everything else: a separate structure that points back to wherever the row actually lives, rather than storing the row itself.

The implication that matters here: a lookup via the clustered index is cheap by default, because the index leaf is the row — nothing more to fetch. A lookup via any secondary index normally costs one extra hop to go get the actual row afterward, unless the query only needs columns already sitting in that secondary index's own leaf entries — which is exactly the "covering index" case below.

This is where Postgres and MySQL genuinely diverge under the hood, and it's a good example of theory not quite mapping to implementation:

• In MySQL/InnoDB, the primary key is the clustered index — not optional, not something you configure, InnoDB always stores the table in primary-key order. Every secondary index stores the primary key as its pointer back to the row. Adding a primary key isn't free; it dictates physical row layout.
• In PostgreSQL, tables are heap-organized — there's no clustered index by default. Every index, including the primary key's, is a secondary structure pointing at a heap location (ctid). (Postgres does have a one-off CLUSTER command that physically reorders a table's rows to match a chosen index, but it's a manual, point-in-time operation — new writes aren't kept in that order automatically the way InnoDB maintains it continuously.) Postgres compensates with index-only scans when a "visibility map" confirms the row hasn't been touched since the last vacuum, but the underlying model is different enough that advice tuned for one doesn't always transfer cleanly to the other.

Practical gotcha: an index-only scan only kicks in if the visibility map is actually up to date. On a table with heavy write churn, if autovacuum falls behind, Postgres quietly falls back to a regular index scan (touching the heap after all) even though the query looks perfectly coverable on paper. If you've built a covering index and EXPLAIN still shows Index Scan instead of Index Only Scan, autovacuum lag is worth checking before you assume the index is wrong.

Partial indexes

So far every index we've built still indexes every row, even the ~99% of tickets sitting closed that the dashboard never looks at. That's wasted space and wasted write overhead. A partial index restricts the index to only the rows that matter — you're still paying the same currency, write cost for read speed, but only for the rows that were ever going to be worth it:

-- PostgreSQL: native support
CREATE INDEX idx_tickets_open ON support_tickets (status)
WHERE status = 'open';

This is a genuinely good fit whenever a table has a persistent, skewed split between "the rows everyone queries" and "the rows nobody looks at again." A few concrete cases beyond our dashboard:

• Soft deletes — index WHERE deleted_at IS NULL so deleted rows never bloat the index.
• Conditional uniqueness — enforce "only one active subscription per customer" without blocking multiple cancelled ones:

  CREATE UNIQUE INDEX idx_one_active_sub ON subscriptions (customer_id)
  WHERE status = 'active';

• Sparse flags — a priority = 'high' index that only covers the handful of urgent tickets, instead of every row.

Here's where the PostgreSQL/MySQL gap really shows up: MySQL has no native partial index support. The common workaround is a generated column plus an index on it:

-- MySQL workaround: generated column + index
ALTER TABLE support_tickets
  ADD COLUMN is_open BOOLEAN GENERATED ALWAYS AS (status = 'open') STORED;
CREATE INDEX idx_tickets_is_open ON support_tickets (is_open);

It gets you a similar query-plan benefit, but not the same storage win — the generated-column index still has one entry per row, true or false; it isn't restricted to only the matching subset the way Postgres's WHERE-qualified index is. If your goal is specifically to shrink the index (not just speed the query), that difference matters. Concretely, on five sample rows:

id	status	is_open
1	open	true
2	closed	false
3	closed	false
4	open	true
5	pending	false

idx_tickets_is_open ends up with an entry for all five rows — true, true, false, false, false — because every row gets a value either way. Postgres's WHERE status = 'open' index, by contrast, has exactly two entries: rows 1 and 4. Rows 2, 3, and 5 aren't false in that index — they simply don't appear in it at all. That's the actual mechanism behind "smaller index": Postgres excludes non-matching rows from the structure entirely; MySQL's workaround narrows what the planner has to consider, not what's physically stored.

The same generated-column pattern extends to the other two use cases mentioned above, with one wrinkle for uniqueness:

-- Soft deletes: same shape as is_open
ALTER TABLE support_tickets
  ADD COLUMN is_not_deleted BOOLEAN GENERATED ALWAYS AS (deleted_at IS NULL) STORED;
CREATE INDEX idx_tickets_not_deleted ON support_tickets (is_not_deleted);

-- Sparse flags: same shape again
ALTER TABLE support_tickets
  ADD COLUMN is_high_priority BOOLEAN GENERATED ALWAYS AS (priority = 'high') STORED;
CREATE INDEX idx_tickets_high_priority ON support_tickets (is_high_priority);

-- Conditional uniqueness needs a different trick: a boolean can't enforce
-- uniqueness "only among active rows" on its own, so lean on the fact that
-- unique indexes allow multiple NULLs without conflict
ALTER TABLE subscriptions
  ADD COLUMN active_customer_id BIGINT GENERATED ALWAYS AS (
    CASE WHEN status = 'active' THEN customer_id ELSE NULL END
  ) STORED;
CREATE UNIQUE INDEX idx_one_active_sub ON subscriptions (active_customer_id);

Cancelled subscriptions all compute NULL here, and MySQL's unique constraint doesn't treat NULLs as duplicates of each other, so any number of cancelled rows per customer is fine — but two active rows for the same customer both compute the same non-null value and collide, which is exactly the rule being enforced. The same storage caveat still applies, though: InnoDB stores an index entry for every row here too, NULL included — it's the same size as a full index regardless of how few subscriptions are actually active, where Postgres's version would contain only the active ones.

Every example so far has filtered on a text column (status), but that's incidental, not a restriction. A partial index's WHERE clause isn't tied to specific data types — it just needs an expression that evaluates to a boolean, using whatever operators exist for the column's type. That covers more or less anything Postgres can compare or index at all:

• Timestamps — WHERE created_at > '2024-01-01', handy for "recent rows only" without indexing years of history
• Numerics — WHERE total > 1000
• Booleans
• UUIDs
• Arrays — WHERE tags @> ARRAY['urgent']
• JSONB — WHERE metadata @> '{"escalated": true}'

Caveat worth knowing before reaching for it: the expression has to be IMMUTABLE — a fixed date literal works, but WHERE created_at > now() - interval '90 days' doesn't, because now() isn't immutable and Postgres needs the predicate to mean the same thing every time it's evaluated.

None of that changes the MySQL side of the story — there's still no CREATE INDEX ... WHERE syntax regardless of data type, so the same generated-column workaround (and the same "query speed, not storage" caveat) applies whether the underlying comparison is on a string, a timestamp, or a JSON field. One MySQL feature worth not confusing with a partial index, since it's easy to: prefix indexes (INDEX (email(20))), which index only the first N characters of a string column. That solves a different problem — indexing less of each value — not the partial-index goal of indexing fewer rows.

Expression indexes

These are the natural sibling of partial indexes, and follow the same PG/MySQL split. If you frequently query WHERE LOWER(assignee) = 'maria', Postgres can index the expression directly:

-- PostgreSQL: native expression index
CREATE INDEX idx_tickets_assignee_lower ON support_tickets (LOWER(assignee));

MySQL requires the same generated-column pattern shown above rather than indexing an expression directly (functional key parts exist since MySQL 8.0.13, with similar mechanics but different syntax — worth checking your version's docs before assuming parity).

4. Decide

For our dashboard: status is highly skewed (a small open fraction against a large closed majority) and queried constantly, so a partial index is a clear win on Postgres — smaller than a full index on disk, not just faster to scan, since it avoids maintaining entries for millions of rows nobody queries by status again. That specific storage win is Postgres-only, per the comparison above; on MySQL the same generated-column index would still get the query-speed benefit but not the size reduction. In cost terms, this is the cheapest version of the trade: you're paying write cost for read speed, but (on Postgres) only for the sliver of rows that actually get queried by status, instead of all four million. If write volume on support_tickets were extremely high and the dashboard were rarely used, the answer might flip toward "leave it as a sequential scan" instead.

5. Verify

Re-run EXPLAIN ANALYZE and confirm Index Scan (or Index Only Scan) replaces Seq Scan, and that the row estimate roughly matches reality.

Planner gotcha: if you create an index and the planner still chooses a sequential scan, that isn't necessarily a bug — the planner might be right. It happens when table statistics are stale (especially right after a bulk load), when the table is small enough that a scan is genuinely cheaper than the overhead of an index lookup, or when the particular value you're filtering on isn't actually selective for that column. Before reaching for a query hint to force the index, it's worth checking what the database's own statistics say.

In Postgres, pg_stats is populated by ANALYZE (run automatically by autovacuum, or manually) and shows per-value selectivity, not just a column-wide summary:

SELECT attname, n_distinct, most_common_vals, most_common_freqs, correlation
FROM pg_stats
WHERE tablename = 'support_tickets';

 attname |    n_distinct    |      most_common_vals      |     most_common_freqs      | correlation
---------+-------------------+-----------------------------+-----------------------------+-------------
 status  |                 3 | {closed,open,pending}      | {0.95,0.03,0.02}           |        0.12

most_common_freqs is the part that matters here: closed is 95% of the table (a sequential scan is genuinely the right plan for that value), open is only 3% (an index scan should win). If a query filtering on 'open' is still getting a sequential scan, this tells you whether the planner's belief about selectivity is even close to reality — and if ANALYZE hasn't run recently, it can be stale. correlation is worth a glance too: it measures how well physical row order matches the column's logical order, from -1 to 1. Even a selective value won't make an index scan cheap if correlation is near 0 (as it is here), because matching rows are scattered across random pages rather than clustered together — the seek is fast, but the row fetches that follow aren't.

MySQL's equivalent is coarser. SHOW INDEX FROM support_tickets reports Cardinality — an estimate of distinct values for the whole column, not a per-value breakdown:

Table            | Key_name            | Column_name | Cardinality
------------------+----------------------+-------------+-------------
support_tickets  | idx_tickets_status  | status      |           3

Divide row count by cardinality (4,000,000 / 3 ≈ 1.33M matches per value, on average) and that column looks unselective overall — there's no MySQL equivalent of Postgres's most_common_freqs to show that 'open' specifically is far rarer than the average. That's a real difference, not just cosmetic: it's part of why MySQL sometimes needs more help (via query restructuring or hints) to reach a plan Postgres would pick on its own, even against identical data. ANALYZE TABLE support_tickets; forces MySQL to resample cardinality if it's gone stale.

---

The cost side, plainly

Every index adds:

• Write overhead — inserts, updates, and deletes on indexed columns now also update the index structure. A table with six indexes pays that cost six times per write.
• Storage — indexes are not free to store; a handful of indexes can easily outweigh the table's own data size.
• Maintenance — Postgres needs VACUUM to reclaim space from dead tuples (including in indexes); MySQL/InnoDB has its own housekeeping via OPTIMIZE TABLE and background purge threads. Neither is "set and forget" at scale. Indexes accumulate bloat over time as rows are updated and deleted, and it's worth knowing what that actually looks like rather than just that it happens.

In Postgres, pg_stat_user_indexes shows size alongside usage, which surfaces two different problems at once:

  SELECT indexrelname, pg_size_pretty(pg_relation_size(indexrelid)) AS index_size, idx_scan
  FROM pg_stat_user_indexes WHERE relname = 'support_tickets';

        indexrelname       | index_size | idx_scan
  --------------------------+------------+----------
   idx_tickets_status      | 210 MB     |    40211
   idx_tickets_open        | 8 MB       |    40211
   idx_tickets_assignee    | 95 MB      |        0

idx_tickets_assignee at idx_scan = 0 is pure write and storage cost for zero read benefit — a drop candidate, not a bloat problem. The size gap between idx_tickets_status (210 MB) and idx_tickets_open (8 MB) on the same underlying data is the partial-index storage argument from earlier, made concrete.

To check whether an index's size is legitimate or wasted, the pgstattuple extension's pgstatindex() gives a direct number: avg_leaf_density is how full each leaf page actually is. A healthy index sits around 85–90%; a reading like 54.2 means leaf pages are less than half full and the index is taking up roughly double the space it needs, which is the signal that routine vacuum isn't keeping up and a targeted rebuild is warranted:

  REINDEX INDEX CONCURRENTLY idx_tickets_status;

(CONCURRENTLY avoids the lock that would otherwise block writes during the rebuild.)

MySQL doesn't expose an equivalent per-index density figure, but SHOW TABLE STATUS LIKE 'support_tickets' surfaces the same underlying signal at the table level via Data_free — bytes InnoDB could reclaim from deleted/updated rows but hasn't yet. A Data_free that's grown to a large fraction of Data_length is the MySQL-side trigger for OPTIMIZE TABLE support_tickets; — worth knowing that on InnoDB this rebuilds the entire table and all its indexes via ALTER TABLE ... FORCE, not just the one index that's bloated, so it's a comparable fix but a coarser-grained one than Postgres's REINDEX INDEX.

What over-indexing actually looks like: a write-heavy events-style table with a dozen indexes on it means every single INSERT has to update a dozen separate B+trees before it's considered done. Reads on that table are fast. Writes are a slow-motion disaster, and it's rarely obvious from a single slow-query log — it shows up as generally elevated write latency across the whole table, which is much easier to blame on "the database" than on the indexes sitting on top of it. The fix is almost always deleting indexes nobody's queries actually use, not adding more.

The concept that ties this together is selectivity: the fraction of rows a given predicate actually matches. The lower that fraction, the more selective the predicate — and the more an index has to offer, because it lets the database skip a proportionally larger share of the table. WHERE status = 'open' on our ticket table is highly selective (it matches maybe 1% of rows), which is exactly why the partial index works so well. A predicate like WHERE is_deleted = false, on the other hand, often matches close to half the table — not selective at all, so an index barely narrows anything down, and you've paid the write cost for a plan that ends up looking almost like a sequential scan anyway. Indexing a column whose common queries aren't selective just adds write cost without meaningfully cutting read cost.

Rules of thumb:

• Good candidate: high selectivity, large table, read-heavy, frequently filtered/sorted/joined on.
• Skip it: low selectivity (few distinct values spread evenly), small table (a scan is already fast), write-heavy hot path, rarely queried.
• Skewed distribution, small "interesting" subset: reach for a partial index before a full one.

---

When the table lives on more than one machine

Everything above assumes queries are answered by one database on one box. Shard the table across a cluster, and the same five-step path still applies — but step 3, "candidates," gets a new dimension: where the index lives matters as much as what it covers.

1. Symptom

support_tickets is now sharded across three nodes by customer_id (a natural choice — most queries are "show me this customer's tickets"). A query filtering on customer_id is still fast. A query filtering on status = 'open' across all customers has gotten dramatically slower since the sharding rollout.

2. Diagnose

Does the query filter on the shard key, or not?

• WHERE customer_id = 501 → the router can send the query to exactly one shard.
• WHERE status = 'open' → status isn't the shard key, so the coordinator has no way to know which shard holds matching rows. It has to ask all of them and merge the results — a scatter-gather query.

3. Candidates

• Local (per-shard) index — an index on status that exists independently on each shard. Cheap to maintain (a write only touches its own shard's index), but doesn't solve the scatter-gather problem: a status-filtered query still has to visit every shard, just faster once it gets there.
• Global secondary index — a single logical index on status that spans the whole cluster and can be queried without touching every shard. This solves scatter-gather, but someone has to keep it consistent with data living on other nodes — and that "someone" pays a real cost.
• Denormalize / duplicate data — sometimes cheaper than either: maintain a separate, purpose-built table (e.g., a materialized "open tickets" table) if the access pattern is narrow and well known.

4. Decide

The deciding factor is usually: how much write latency (or staleness) are you willing to accept to make this particular read fast? A globally consistent secondary index generally means every write has to coordinate across nodes before it's considered committed — that's expensive but predictable. An asynchronously-maintained global index is cheaper to write but can return stale results for a short window. There's no universally correct answer; it depends on whether the query in question needs to be exactly right or just close to right. Same currency as before — write cost paid for read speed — just exchanged at a worse rate, because "write cost" now means cross-node coordination instead of updating a local B+tree.

How this actually gets implemented — CockroachDB and Spanner. It's worth being honest that the textbook trade-off doesn't always look the way vendors build it. Both CockroachDB and Google Spanner take the more expensive, more consistent route by default: their global secondary indexes are kept strongly consistent with the base table, using the same distributed transaction machinery that guarantees consistency for regular writes — not an eventually-consistent background sync (which is the model some other systems, like DynamoDB's global secondary indexes, use instead). In practice, that means:

• A write to an indexed column in CockroachDB or Spanner is a distributed transaction that updates both the row and its index entries together, so reads never see a stale index. In practice, a plain UPDATE support_tickets SET status = 'closed' WHERE id = 123 — a query that would be a single local write on Postgres or MySQL — now has to coordinate across whatever nodes hold the row's data and the index's data before it's considered committed. The exact consensus mechanics differ by vendor, but the shape of the cost is the same: cross-node coordination on a write that used to be purely local.
• Neither forces you to choose between "index works" and "index is occasionally wrong" — but that consistency doesn't come from a clever trick; it comes from paying the coordination cost on every write. The theoretical trade-off (sync cost vs. staleness) doesn't disappear just because the vendor supports global indexes natively — it's just been moved from your application code into the database engine.

Contrast that with a local-index-only approach (common in simpler sharded Postgres/MySQL setups, e.g. via extensions like Citus): writes stay fast and fully local, but any query not aligned to the shard key pays the scatter-gather cost at read time, every time.

5. Verify

Same principle as the single-node case — re-run the query plan (most distributed SQL databases expose their own EXPLAIN variant) and confirm the query is hitting a single shard, or a targeted index range, rather than fanning out to the whole cluster.

---

Before you add an index

A condensed version of everything above:

Symptom	Likely fix
Slow lookup on an equality filter (WHERE status = 'open')	Single-column or partial index
Query filters on two+ columns together	Composite index, most selective/most-filtered column first
Query only needs indexed columns	Covering index / index-only scan
Skewed data — a small, frequently-queried subset	Partial index
Filtering on a computed value (LOWER(email))	Expression/functional index
Fast on one shard, slow across the cluster	Check whether the query aligns with the shard key; consider a global secondary index if not
Table is small, or the column has low selectivity	Don't index — the write cost isn't worth it

---

Closing

The 2am dashboard problem wasn't really a mystery — it was a table that outgrew the assumption "a scan is fine" without anyone deciding, on purpose, whether that assumption still held. That's the pattern worth taking away: an index isn't a default you reach for, it's a specific answer to a specific cost question — what are you willing to pay, and in what currency: read speed, write speed, or storage — and that question doesn't go away when you shard the table. It just gets a second, harder version: how much are you willing to pay to make that answer true everywhere at once?