You’ve probably bumped into indexes before. Today, let’s really dig into them so you get all the ins and outs of how they work. Indexes are awesome—they can make your queries hundreds or even thousands of times faster. You’re gonna love them. But what exactly are indexes?
Imagine you’re looking for a specific book in a massive library. If there’s no order to the search, it’d take forever. To speed things up, books are usually sorted alphabetically or by category. An index in a database works pretty much the same way.
Formally, an index is a special extra data structure created to quickly search for rows in a table. It cuts down the amount of data you have to look through to find what you need.
In a way, indexes are like the table of contents in an encyclopedia, if you think of the book itself as a table.
Instead of reading the whole encyclopedia from start to finish, you use the table of contents to jump right to the page you want.
Imagine the employees table has 100,000 employees. If you’re searching for an employee with the last name "Min", without an index you’d have to check every single row (which takes forever). With an index, the search is way faster because Postgres knows where to look.
How do indexes work?
To get how indexes speed up queries, you gotta know what’s happening behind the scenes. An index is built on one or more columns of a table and organizes the data in a way that’s easy to search.
Indexes in PostgreSQL usually use a B-Tree (Balanced Tree) data structure. Here’s how it works:
- Index is created: the data from the columns you’re indexing gets organized into a tree.
- During search: PostgreSQL uses the index to quickly find the rows you want, instead of scanning the whole table.
- Tree structure: the tree is ordered, so you can jump from one record to another super fast (binary search algorithm).
Example: let’s say you have a table with 1 million rows. Without an index, you’d have to check a million records. With an index, PostgreSQL can cut that down to just a few dozen.
When should you use indexes?
Indexes massively speed up read operations, like searching, filtering, and sorting. But there’s a trade-off: they slow down write operations (like INSERT, UPDATE, DELETE), because the index has to be updated whenever the data changes.
Scenarios where indexes are a must
Frequent search queries: When you’re often looking up rows by specific values, like:
SELECT * FROM employees WHERE last_name = 'Min';
Filtering data: When you use conditions in WHERE or HAVING:
SELECT * FROM employees WHERE salary > 50000;
Sorting data: When you use ORDER BY:
SELECT * FROM employees ORDER BY hire_date DESC;
Joining tables (JOIN): If you often join tables based on specific columns.
*When *NOT to use indexes
- If the table is super small (like, 10-50 rows), an index won’t really make things faster.
- If the column you’re indexing is rarely used in queries.
- If the column has very few unique values (like a
genderfield that only has 2-3 options).
Example: creating an index on a status field with just two possible values—"active" and "inactive"—doesn’t make sense.
How indexes affect performance
As you’ve probably noticed, indexes speed up read queries, but can slow down writes. That’s because when you add, update, or delete rows, PostgreSQL has to update the index info too.
Balancing indexes and performance
- For tables that change a lot, be careful with how many indexes you add.
- For tables where most of the action is reading data, indexes are a must.
Why indexes matter in real life
In practice, indexes are used for:
- Optimizing web apps: speeding up page loads by making SQL queries run faster.
- Working with big tables: without indexes, queries on tables with millions of rows can take minutes.
- Scalability: indexes let you handle bigger and bigger amounts of data efficiently.
For example, an online store with a database of products and orders just can’t live without indexes—otherwise, customers would be waiting for pages to load for ages.
GO TO FULL VERSION