When working with a large dataset, one of the things I started noticing was how much search performance matters.

A search operation that seems perfectly fine with a small amount of data can become noticeably slower when the dataset grows to millions of records. During my third-year internship, I worked on a data-intensive application where search performance became an important challenge. The experience introduced me to practical database optimization and made me look beyond simply writing queries that worked.

Looking back at that experience, I wanted to revisit some of the concepts involved, understand them more deeply, and share what I learned along the way.

I didn't know everything about database optimization when I started working on it. However, investigating the performance issue helped me understand a few concepts that I had previously studied mostly in theory.

In this post, I want to share some of those learnings without going into the application's internal implementation.

1. The problem: searches becoming slow as data grows

The application needed to support searches across a dataset containing millions of records.

As the dataset grew, some searches became very slow. In certain cases, retrieving results could take around one to two minutes.

This made me think about a question:

If a database can store millions of records, what makes finding a particular record take so long?

The answer isn't always that the database is slow. The way data is stored, the indexes available, and the queries being executed can all affect performance.

This was where I started looking more closely at database indexing.

2. Understanding database indexes

Before working on this, I understood the basic idea of an index, but I hadn't fully appreciated its impact on a real application.

Think of a book with hundreds of pages. If you want to find a particular topic, you could read every page until you find it. Alternatively, you could use the index at the back of the book to locate the relevant pages more quickly.

A database index serves a similar purpose.

Without a suitable index, PostgreSQL may need to examine many rows to find the records matching a search condition. This is often called a sequential scan.

When an appropriate index exists, PostgreSQL may be able to locate matching records without examining every row.

Of course, indexes don't automatically make every query faster. Their usefulness depends on the query, the data, and the type of index. They also require storage and can add overhead to operations that modify data.

That distinction is something I want to keep in mind when thinking about database performance.

3. What changed in my project?

I worked on improving search performance through database indexing.

After the indexing-related improvements, search times went from around one to two minutes to a few seconds in the cases I was working on.

That was a significant practical improvement, and it made the concepts I had learned about databases feel much more relevant.

Later, I also worked with PostgreSQL GIN indexes. This introduced me to another important lesson: databases offer different index types because different kinds of queries have different needs.

GIN stands for Generalized Inverted Index. It can be useful when searching data structures such as arrays or JSONB values, and it can also support full-text search and certain text-search operations when configured appropriately.

However, a GIN index isn't a universal replacement for other indexes. Whether it helps depends on the data, the operators used in the query, and the index configuration.

I'm still building a clearer understanding of when to choose GIN, when a B-tree index is more suitable, and how to verify that an index is actually helping.

4. Why I want to understand more than just the fix

One thing I've realized is that making a change and understanding why it works are two different things.

For example, adding an index might improve a particular search, but I also want to understand:

  • How does PostgreSQL decide whether to use an index?
  • How can I tell whether a query is doing a sequential scan or an index scan?
  • How do I decide which columns or expressions should be indexed?
  • When is a GIN index useful, and when would a B-tree index be a better choice?
  • How can I measure an improvement rather than assume that a change helped?

These questions matter because database optimization shouldn't be about adding indexes everywhere. It should be about understanding the workload, identifying the bottleneck, and choosing an appropriate solution.

Tools such as PostgreSQL's EXPLAIN and EXPLAIN ANALYZE can help investigate query execution plans and measure query execution. I want to become more comfortable using them to explain why a query is slow and how a proposed change affects it.

5. A few things I'm taking away from this experience

My biggest takeaways so far are:

1. Performance changes as data grows.

A query that works well with a small dataset may behave very differently when the dataset reaches millions of records.

2. Indexes should be chosen based on the query.

Different index types serve different purposes. Understanding the query is an important part of choosing the right index.

3. Measuring matters.

It's useful to know that a search became faster, but understanding the execution plan can help explain why it became faster.

4. There's always more to learn.

Working on a real application gave me a reason to explore concepts beyond what I had studied in college. I'm still learning how to apply them correctly.

Conclusion

Working on search performance helped me connect database concepts with a practical engineering problem.

I learned more about indexing, saw how much it could matter for a large dataset, and later began exploring PostgreSQL GIN indexes.

I don't consider this topic mastered yet. My next step is to understand query execution plans better and learn how to evaluate indexing choices systematically.

I'd also be interested in learning how other developers approach database performance problems, particularly when an application needs to support flexible searches across large datasets.

Related Project

I’ve also documented the broader architecture and engineering decisions behind this project in a GitHub repository. It includes an architecture overview and notes on database indexing, search performance, and other backend design considerations.

Explore the Voter Data Platform — Engineering Overview on GitHub

The repository contains documentation only; the original production source code is not included.