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 fast, but every write must update them too.
Also called: Index, Postgres index
Why do apps slow down as tables grow without an index?
Without an index, the database has to read every row to find the ones a query asks for. The PostgreSQL documentation describes exactly that: with no preparation, the system scans the entire table, row by row. That cost grows with the table, so a page that was instant at launch gets slower every week.
A prototype runs on a handful of rows, so every query is fast whether or not it has an index. Nobody changes the code; the data simply outgrows it, until pages time out. Our article on a Lovable app that is slow with real users walks through that symptom from the user's side.
Which columns should get a database index first?
Start with the columns your queries filter, join and sort on most, on the tables that grow. In a Supabase app the first candidates are the columns your row-level security policies check, because a policy is a filter the database adds to every query on that table.
- Columns your row-level security policies filter on, such as user_id or team_id.
- Foreign keys you join or filter by.
- Columns in frequent WHERE and ORDER BY clauses on large tables.
Supabase's guide adds a detail that catches people out: a column only counts as indexed when it is the first column of a btree index, so a composite primary key does not index its later columns. The PostgreSQL documentation puts the overall trade plainly: indexes make finding rows much faster, but they add overhead, so they should be used sensibly.
How do you add an index without blocking writes?
An index is one SQL statement, such as CREATE INDEX invoices_team_id_idx ON invoices (team_id), and it belongs in a schema migration like any other change. After that, PostgreSQL keeps it up to date on every write and uses it when it judges it faster than a full scan.
Building one on a large live table needs care. By default PostgreSQL allows reads while the index builds but blocks inserts, updates and deletes until it finishes. It can build an index while writes continue, with caveats its documentation lists. The documentation also notes you may need to run ANALYZE so the planner has current statistics, and that DROP INDEX removes an index nothing uses.
Can an index enforce a rule as well as speed up a query?
Yes. A unique index refuses a second row with the same values, whatever the application code does. That is a useful pattern for anything that must exist once, such as a booking, a payment or a reward, because a rule kept in application code alone is only as reliable as every path that touches the table.
Missing indexes on policy columns are one of the items our production readiness check asks about, alongside backups and access rules.
Common questions
Can too many indexes hurt?
Yes. Each index takes space and slows every insert and update a little, because the database must keep it current. Add them for queries you actually run, and remove ones nothing uses with DROP INDEX. Start with the columns your row-level security policies filter on and your foreign keys.
Does a primary key create an index automatically?
Yes. PostgreSQL automatically creates a unique index when a table gets a primary key or a unique constraint. Supabase's guide notes the limit: only the first column of that index counts as indexed, so a composite primary key does not cover its later columns for your policies' filters.
Does creating an index lock the table?
Partly. By default PostgreSQL lets reads continue while an index builds, but blocks inserts, updates and deletes until it is finished, which on a large busy table can mean failed writes. PostgreSQL can also build an index concurrently, with caveats, so plan big index builds rather than running them mid-day.
Go deeper
Scaling a Lovable app: what breaks firstScaling a Lovable app: why the database breaks first, how to fix indexes, connections and slow functions, and how to load-test before a traffic spike.
Related terms
Sources
- PostgreSQL documentation: Indexes
- PostgreSQL documentation: Indexes, Introduction
- PostgreSQL documentation: Unique Indexes
- Supabase documentation: Row Level Security, Add indexes
Sources checked October 24, 2026
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.