Typical Problems When Working with Indexes

Even the most high-tech car will stall if you pour lemonade in the gas tank instead of fuel. Same goes for indexes in PostgreSQL. They’re super powerful, but you gotta use them wisely. Let’s look at some classic problems folks run into with indexes.

Problem 1: Over-indexing

Let’s quickly recap a topic from a couple lectures ago. When you have too many indexes on a single table, PostgreSQL has to keep every one of them up to date. This directly impacts insert, update, and delete operations. Every index needs to be updated and kept in sync!

Say we have a students table:

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(255) UNIQUE,
    age INTEGER,
    grade INTEGER
);

And you decide to create indexes on every column “just in case”:

CREATE INDEX idx_students_name ON students(name);
CREATE INDEX idx_students_age ON students(age);
CREATE INDEX idx_students_grade ON students(grade);

Now imagine you’re inserting 10,000 new records. PostgreSQL has to not only write the data to the table, but also update all three indexes. If there’s a lot of data, write speed drops and your system’s performance takes a hit.

How do you avoid this? Before creating an index, ask yourself two questions:

1. How often does this column get used in filtering (WHERE), sorting (ORDER BY), or grouping (GROUP BY)?
2. Will the query actually use this index, or will it still do a full table scan?

If your answer to both is “rarely” or “never,” you probably don’t need that index.

Problem 2: Indexing the Wrong Columns

Creating an index on low-cardinality data is like trying to pour tea into a cup with the lid glued shut: it’s basically useless. If a column only has 2-3 unique values, PostgreSQL will probably just do a full table scan instead of using the index.

Let’s say we have a courses table:

CREATE TABLE courses (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255),
    level VARCHAR(10) -- Can only be 'Beginner', 'Intermediate', or 'Advanced'
);

And you decide to create an index on the level column:

CREATE INDEX idx_courses_level ON courses(level);

But the query:

SELECT * FROM courses WHERE level = 'Beginner';

might not use the index, because PostgreSQL will figure it’s easier to just scan the whole table than look up the index. This is especially true for small tables and low-cardinality data.

So, indexes make sense on columns with high cardinality (meaning lots of unique values). For low-cardinality data, you’re better off using other optimization tricks, like table partitioning.

Problem 3: Stale Indexes

Sometimes people create indexes and then forget to drop them, even if they’re not used anymore. It’s like files piling up on your desktop: at first there’s just a couple, then suddenly you’re wasting time scanning through a sea of icons looking for the right one... Sound familiar?

Say we created an index for some old feature, then changed our query logic and added a new index. The old one isn’t needed anymore, but it’s still there, taking up space and slowing down writes.

To avoid this, regularly check and analyze your indexes. PostgreSQL gives you a handy metric:

SELECT
    relname AS table_name,
    indexrelname AS index_name,
    idx_scan AS total_scans
FROM
    pg_stat_user_indexes
WHERE
    idx_scan = 0;

Here, idx_scan shows how many queries have used each index. If it’s 0, the index isn’t being used and you can drop it:

DROP INDEX idx_courses_level;

Problem 4: Indexes on Frequently Updated Columns

If you have an index on a column that gets updated a lot, PostgreSQL has to rebuild that index every time the value changes. That can seriously slow things down.

Imagine a table with order data:

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    status VARCHAR(20), -- Might change several times (like "new", "in progress", "completed")
    total NUMERIC(10, 2)
);

You create an index on the status column to speed up filtering by status:

CREATE INDEX idx_orders_status ON orders(status);

But if the status gets updated dozens of times for each record, the index starts to hurt performance.

To avoid this, don’t create indexes on columns that change a lot. If you really need an index, consider using a partial index:

CREATE INDEX idx_orders_status_partial
ON orders(status) 
WHERE status = 'in progress';

This way, the index only gets updated for records with that specific value.

Problem 5: UNIQUE Constraints on Unnecessary Columns

Unique indexes (UNIQUE) are automatically created to enforce data uniqueness. But if uniqueness isn’t strictly needed, these indexes just add extra load.

Say we created a table for logs:

CREATE TABLE logs (
    id SERIAL PRIMARY KEY,
    message TEXT,
    created_at TIMESTAMP UNIQUE
);

If thousands of records are added every second, enforcing uniqueness on created_at creates a huge load.

To keep things running smooth, only use UNIQUE constraints where you really need them. In our example, if uniqueness on created_at isn’t required, swap it for a regular index:

CREATE INDEX idx_logs_created_at ON logs(created_at);

Problem 6: Misusing Multi-Column Indexes

Multi-column indexes are great if your queries filter or sort by several columns at once. But you have to create them the right way, or they’ll just sit there unused.

Say we have this index:

CREATE INDEX idx_students_name_grade ON students(name, grade);

This index is used if your query filters or sorts by both columns:

SELECT * FROM students WHERE name = 'Alice' AND grade = 90;

But this query:

SELECT * FROM students WHERE grade = 90;

won’t use the index, since name comes first.

To avoid this, only create multi-column indexes in the order they’re most often used in your queries. If you need to filter by just one column, create a separate index for it.

Pro Tips

Monitor your index usage. PostgreSQL has a system view called pg_stat_user_indexes where you can see which indexes are being used and which aren’t.

Optimize your queries along with your indexes. Bad queries will still be bad, even with indexes.

Don’t forget to clean up. Stale indexes just take up space and slow down writes.

That’s it, folks! Indexes are a powerful tool, but remember: with great power comes great responsibility. Use indexes wisely, and your database will run like a SpaceX rocket!