What is a database index?
The reason an app that was instant with 100 rows takes seconds with 100,000. Here is the fix, and its cost.
In short
A database index is a separate lookup structure, kept by the database, that lets it find the rows matching a condition without reading the whole table, much like the index at the back of a book. Indexes make reads on large tables fast, but every write must update them too, so they are added where queries need them.
Also called: Index, Postgres index
Why it matters when your prototype goes to production
A prototype runs on a handful of rows, so every query is fast whether or not it has an index. With real users, the tables grow and a query that reads every row gets slower each week, until pages time out. Nobody changed the code; the data simply outgrew it.
Row-level security makes indexes matter more. A policy such as "rows where the owner is the current user" is a filter the database applies to every query on that table, so the column it filters on should be indexed. The PostgreSQL documentation puts the trade plainly: indexes make finding rows much faster, but they add overhead, so they should be used sensibly.
Where to add one
- Columns your row-level security policies filter on.
- Foreign keys you join or filter by.
- Columns in frequent WHERE and ORDER BY clauses on large tables.
Common questions
Can too many indexes hurt?
Yes. Each index takes space and slows every insert and update a little. Add them for queries you actually run, and remove ones nothing uses.
Related terms
Sources
More on this: Production architecture & security · All glossary terms
Built something in Lovable you want people to rely on?
We are the engineers who take it the rest of the way — secured, tested, released and supported.