Your database contains 100 records.
Everything feels fast.
Then it reaches one million.
A query that used to take milliseconds starts taking seconds.
The application code hasn't changed.
The data has.
The Database Has More Work to Do
Imagine searching users by email.
Without an appropriate index, the database may need to inspect many rows to find the matching record.
With an index, it can find the relevant data much more efficiently.
Think of an Index Like a Book Index
Imagine a 1,000-page book.
You want to find every page mentioning “PostgreSQL.”
You could read every page.
Or you could use the index.
A database index provides a similar shortcut.
What Should You Index?
Common candidates include columns frequently used in:
WHERE JOIN ORDER BY Unique lookups
For example:
CREATE INDEX users_email_idx ON users(email);
But don't create indexes everywhere.
Indexes Have a Cost
Indexes consume storage.
They also need to be maintained when data changes.
So adding dozens of indexes isn't automatically a performance improvement.
Measure First
Use tools such as:
EXPLAIN ANALYZE
to understand how PostgreSQL is executing a query.
Hostwares' database documentation specifically recommends indexes and EXPLAIN ANALYZE as part of PostgreSQL performance work.
The Real Lesson
A slow database query isn't always a hosting problem.
Sometimes the database simply needs a better way to find the data.
Before upgrading the server, check whether the query knows where to look.