How to Build a Search System That Handles Thousands of Records

Question

Search functionality is one of the most important features in modern web applications. Whether you are building an e-commerce platform, a customer relationship management (CRM) system, an employee management dashboard, or a content management system, users expect to find the information they need quickly and accurately.

At first, building a search feature might seem simple. You can retrieve records from a database, filter them based on a keyword, and display the matching results. However, as the application grows and the number of records reaches thousands or even hundreds of thousands, inefficient search logic can create performance problems.

Users may experience slow response times, irrelevant results, excessive database queries, and frustrating pagination behavior. Developers must therefore design search systems that balance speed, accuracy, scalability, and maintainability.

In this article, we will explore how to build a search system capable of handling thousands of records using React, Python, FastAPI, and PostgreSQL. We will cover database design, query optimization, pagination, filtering, indexing, frontend integration, error handling, testing, and future scalability.

The goal is not simply to make search work. It is to build a reliable search feature that performs well as the application grows.

1. Understanding the Requirements of a Scalable Search System

Before writing code, it is important to understand what the search system needs to accomplish.

Imagine developing a CRM application that stores 50,000 customer records. Each customer has a name, email address, phone number, company, and account status.

A user might want to search for a customer by name, find a record using an email address, filter customers by status, or display only customers belonging to a particular company.

A well-designed search system should support these requirements:

• Keyword-based searching across selected fields.
• Filtering by status, category, date, or other attributes.
• Sorting by name, creation date, or relevance.
• Pagination to return manageable groups of results.
• Fast database queries through appropriate indexing.
• Input validation and safe query construction.
• Clear loading, empty, and error states in the frontend.
• Consistent results when users combine multiple filters.

Performance should be considered from the beginning. Returning thousands of records in one HTTP response is usually unnecessary, even if the database can retrieve them quickly.

Instead, the backend should return only the results required for the current page.

2. Choosing the Right Technology Stack

For this implementation, we will use React for the frontend, FastAPI for the backend, and PostgreSQL as the database.

Each technology serves a different purpose.

React provides the search interface, including input fields, filter controls, sorting options, and result tables.

FastAPI handles HTTP requests, validates query parameters, applies business rules, and communicates with the database.

PostgreSQL stores customer records and executes search queries. Its indexing capabilities and full-text search features make it suitable for many applications that need more than basic filtering.

This architecture separates responsibilities between the frontend, backend, and database.

A typical request follows this sequence:

1. The user enters a keyword in the React interface.
2. React sends a request to the FastAPI endpoint.
3. FastAPI validates the keyword, filters, and pagination parameters.
4. PostgreSQL executes an optimized query.
5. FastAPI returns the matching records and pagination metadata.
6. React renders the results.

This structure is easy to maintain and can be extended as the application becomes more complex.

For smaller projects, PostgreSQL may be sufficient for the entire search workload. Dedicated search platforms such as Elasticsearch or OpenSearch can be introduced later if requirements justify the additional infrastructure.

3. Designing the Database Schema

A scalable search feature begins with a sensible database schema.

For our CRM application, we can create a customers table containing the information required by the search interface.

CREATE TABLE customers (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    company VARCHAR(150),
    status VARCHAR(30) NOT NULL DEFAULT 'active',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

This schema includes a unique identifier, customer name, email address, company name, status, and creation timestamp.

The primary key allows each record to be identified efficiently. The unique email constraint prevents duplicate email addresses and creates an index that supports email lookups.

However, not every searchable field needs its own index. Indexes consume storage and increase the cost of inserts, updates, and deletes. We should create indexes based on actual query patterns.

For example, if users frequently filter customers by status and sort them by creation date, a composite index may be useful:

CREATE INDEX idx_customers_status_created
ON customers (status, created_at DESC, id DESC);

This index can help PostgreSQL retrieve records in the desired order when the query filters by status. The id column also provides a stable tie-breaker for pagination.

Database design should reflect how users search rather than simply indexing every column.

4. Building the Search API with FastAPI

The backend API is responsible for receiving search requests and returning structured results.

We can create an endpoint such as:

GET /api/customers?q=alex&page=1&page_size=20

The q parameter represents the search keyword. The page parameter specifies the current page, while page_size determines how many records to return.

A response might look like this:

{
  "items": [
    {
      "id": 12,
      "name": "Alex Morgan",
      "email": "*Emails are not allowed*",
      "company": "Northstar",
      "status": "active"
    }
  ],
  "page": 1,
  "page_size": 20,
  "total": 1
}

The items property contains the matching records. The remaining fields help the frontend display pagination controls and communicate the number of matches.

For the implementation, SQLAlchemy can manage database interactions while Pydantic models validate API responses.

First, define a response model:

from datetime import datetime
from pydantic import BaseModel, ConfigDict


class CustomerOut(BaseModel):
    id: int
    name: str
    email: str
    company: str | None
    status: str
    created_at: datetime

    model_config = ConfigDict(from_attributes=True)


class CustomerSearchResponse(BaseModel):
    items: list[CustomerOut]
    page: int
    page_size: int
    total: int

Next, implement the search endpoint using an asynchronous SQLAlchemy session:

from fastapi import FastAPI, Depends, Query
from sqlalchemy import select, func, or_
from sqlalchemy.ext.asyncio import AsyncSession

app = FastAPI()


@app.get(
    "/api/customers",
    response_model=CustomerSearchResponse
)
async def search_customers(
    q: str = Query(default="", max_length=100),
    status: str | None = Query(default=None),
    page: int = Query(default=1, ge=1),
    page_size: int = Query(default=20, ge=1, le=100),
    session: AsyncSession = Depends(get_session),
):
    filters = []

    keyword = q.strip()

    if keyword:
        pattern = f"%{keyword}%"

        filters.append(
            or_(
                Customer.name.ilike(pattern),
                Customer.email.ilike(pattern),
                Customer.company.ilike(pattern),
            )
        )

    if status:
        filters.append(Customer.status == status)

    count_query = (
        select(func.count())
        .select_from(Customer)
        .where(*filters)
    )

    total = await session.scalar(count_query)

    data_query = (
        select(Customer)
        .where(*filters)
        .order_by(
            Customer.created_at.desc(),
            Customer.id.desc()
        )
        .offset((page - 1) * page_size)
        .limit(page_size)
    )

    result = await session.scalars(data_query)
    items = result.all()

    return {
        "items": items,
        "page": page,
        "page_size": page_size,
        "total": total or 0,
    }

This example assumes that Customer is a mapped SQLAlchemy model and get_session is a properly configured asynchronous session dependency.

The query is constructed using SQLAlchemy expressions rather than concatenating user input into raw SQL. This helps prevent SQL injection through the search parameters.

The example also limits the page size to 100 records. Such limits protect the API from unnecessarily large responses.

In a production application, we should additionally validate the permitted status values, configure database connection pooling, handle expected database errors, and enforce authorization where customer data is restricted.

5. Understanding Partial-Match Search

One of the most common search requirements is partial matching.

Suppose the database contains these customer names:

• Alexander Johnson
• Alexandra Smith
• Alex Morgan
• Benjamin Carter

If a user enters Alex, the search system should return the relevant records containing that text.

In PostgreSQL, the ILIKE operator supports case-insensitive pattern matching:

SELECT id, name, email
FROM customers
WHERE name ILIKE '%alex%';

The percent signs indicate that other characters may appear before or after the keyword.

This is useful for flexible search, but there is an important performance consideration: a normal B-tree index generally cannot efficiently support a leading-wildcard pattern such as %alex%.

With thousands of records, a sequential scan may still be acceptable. With much larger tables or more complex queries, the database may need a better indexing strategy.

We should also distinguish between different types of search:

Exact matching finds a specific value, such as an email address.

Prefix matching finds values beginning with the keyword, such as names starting with Alex.

Substring matching finds text appearing anywhere within a field.

Full-text search finds documents based on words, linguistic processing, and relevance.

Choosing the appropriate search method improves both performance and user experience.

6. Optimizing Search with Database Indexes

Indexes are among the most important tools for improving database performance.

Without a suitable index, PostgreSQL may need to inspect many rows to find matching records. As a table grows, this can increase query execution time.

For exact email searches, the unique constraint already creates an appropriate index.

For status-based filtering, an index can reduce the amount of data PostgreSQL needs to examine.

For case-insensitive substring searches, PostgreSQL's pg_trgm extension can support efficient trigram-based indexing.

For example:

CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE INDEX idx_customers_name_trgm
ON customers
USING GIN (name gin_trgm_ops);

CREATE INDEX idx_customers_email_trgm
ON customers
USING GIN (email gin_trgm_ops);

CREATE INDEX idx_customers_company_trgm
ON customers
USING GIN (company gin_trgm_ops);

Trigram indexing breaks text into groups of three characters and can accelerate many substring and similarity searches.

These indexes can be useful for queries involving ILIKE '%keyword%', although effectiveness depends on the query and the search term. Very short search terms may not benefit as much.

Indexes also have trade-offs. Every additional index requires disk space and adds work to data modifications. Therefore, index selection should be based on real usage patterns and query plans.

Use PostgreSQL's EXPLAIN ANALYZE to inspect actual execution behavior:

EXPLAIN ANALYZE
SELECT id, name, email
FROM customers
WHERE name ILIKE '%alex%'
ORDER BY created_at DESC, id DESC
LIMIT 20;

This helps developers identify sequential scans, expensive sorting operations, row estimates, and other performance bottlenecks.

Rather than adding indexes blindly, measure the query before and after each change.

7. Implementing Pagination Correctly

Pagination prevents the backend from returning every matching record in a single response.

Imagine that the CRM system contains 20,000 customers. Displaying all of them in one table would consume unnecessary memory, increase network transfer time, and make the interface difficult to use.

With page-based pagination, the application can retrieve 20 records at a time.

The SQL query uses LIMIT and OFFSET:

SELECT id, name, email
FROM customers
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 40;

This query retrieves up to 20 records after skipping the first 40. It corresponds to the third page when the page size is 20.

Offset pagination is simple and appropriate for many administrative dashboards. However, large offsets can become expensive because the database may still need to walk through rows that will not be returned.

Another issue is data consistency. If records are inserted or deleted while someone moves between pages, users may see duplicated or missing entries.

For deeper result sets, cursor-based pagination is often a better option.

A cursor can use the last record's timestamp and ID to fetch the next group:

SELECT id, name, email, created_at
FROM customers
WHERE
    created_at < :last_created_at
    OR (
        created_at = :last_created_at
        AND id < :last_id
    )
ORDER BY created_at DESC, id DESC
LIMIT 20;

This example assumes descending ordering and a cursor containing the last record's timestamp and ID.

In production, the cursor should be validated and encoded appropriately. The complete query must also account for any active search filters.

Offset pagination works well when users need numbered pages and total counts. Cursor pagination is useful for large result sets, infinite scrolling, and activity feeds.

8. Building the Search Interface with React

The frontend should make searching simple and predictable.

A typical interface includes a search input, optional filters, a results table, pagination controls, and loading or error messages.

React state can track the current keyword, result data, loading status, and errors.

Here is a simplified example:

import { useEffect, useState } from "react";

export default function CustomerSearch() {
  const [query, setQuery] = useState("");
  const [customers, setCustomers] = useState([]);
  const [loading, setLoading] = useState(false);
  const [error, setError] = useState("");

  useEffect(() => {
    const controller = new AbortController();

    async function search() {
      setLoading(true);
      setError("");

      try {
        const params = new URLSearchParams({
          q: query,
          page: "1",
          page_size: "20",
        });

        const response = await fetch(
          `/api/customers?${params.toString()}`,
          { signal: controller.signal }
        );

        if (!response.ok) {
          throw new Error("Unable to load customers.");
        }

        const data = await response.json();
        setCustomers(data.items);
      } catch (err) {
        if (err.name !== "AbortError") {
          setError(err.message);
        }
      } finally {
        if (!controller.signal.aborted) {
          setLoading(false);
        }
      }
    }

    search();

    return () => controller.abort();
  }, [query]);

  return (
    <section>
      <label htmlFor="customer-search">
        Search customers
      </label>

      <input
        id="customer-search"
        type="search"
        value={query}
        onChange={(event) => setQuery(event.target.value)}
        placeholder="Search by name, email, or company"
      />

      {loading && <p>Searching...</p>}
      {error && <p role="alert">{error}</p>}

      {!loading && !error && customers.length === 0 && (
        <p>No matching customers found.</p>
      )}

      <ul>
        {customers.map((customer) => (
          <li key={customer.id}>
            {customer.name} — {customer.email}
          </li>
        ))}
      </ul>
    </section>
  );
}

This component requests the first page of results whenever the search keyword changes. It also cancels outdated requests when a newer search begins or the component unmounts.

That cancellation helps prevent unnecessary work and avoids displaying results from requests that are no longer relevant.

The example assumes the API is available at the same origin. If the frontend and backend use different origins, the application must configure its API URL and appropriate CORS policies.

For a full application, we should also implement pagination controls, status filters, sorting, and a consistent way to handle authentication.

9. Using Debouncing to Reduce API Requests

A common frontend performance problem occurs when the application sends a request after every keystroke.

Suppose a user types Alexander. Without debouncing, the frontend may send separate requests for A, Al, Ale, Alex, and every subsequent character.

Some of these requests may become unnecessary before they finish.

Debouncing waits until the user stops typing for a short period before executing the search.

A reusable React hook can implement this behavior:

import { useEffect, useState } from "react";

export function useDebouncedValue(value, delay = 300) {
  const [debouncedValue, setDebouncedValue] = useState(value);

  useEffect(() => {
    const timeoutId = setTimeout(() => {
      setDebouncedValue(value);
    }, delay);

    return () => clearTimeout(timeoutId);
  }, [value, delay]);

  return debouncedValue;
}

The search component can then use the debounced keyword instead of the raw input value:

const debouncedQuery = useDebouncedValue(query, 300);

Use debouncedQuery in the API-request effect's dependency array and in the request parameters.

This small change can significantly reduce the number of requests generated during typing.

A delay of approximately 250–400 milliseconds is a reasonable starting point, but the ideal value depends on the application.

Debouncing should not replace backend optimization. It reduces request frequency, while indexes, query planning, and pagination reduce the cost of each request.

10. Adding Filters and Sorting

Real-world search systems rarely depend on keywords alone.

A CRM user might want to display only active customers, search for a company name, and sort the results by the newest creation date.

The backend should allow these parameters to be combined.

For example:

GET /api/customers?q=alex&status=active&sort=created_at&direction=desc

Each filter should be validated independently and then combined with the search conditions.

Sorting deserves special attention. Never insert arbitrary user-provided SQL column names or sort directions directly into a query.

Instead, map supported parameters to known SQLAlchemy expressions:

sort_columns = {
    "name": Customer.name,
    "created_at": Customer.created_at,
    "company": Customer.company,
}

sort_column = sort_columns.get(sort, Customer.created_at)

if direction == "asc":
    ordering = sort_column.asc()
else:
    ordering = sort_column.desc()

data_query = data_query.order_by(
    ordering,
    Customer.id.desc()
)

Here, sort and direction should be validated API parameters, with sort restricted to the keys in the mapping and direction restricted to asc or desc.

The unique ID tie-breaker helps create deterministic ordering when multiple records have the same primary sort value.

When users change a filter or sort order, the frontend should reset the current page to the first page. Otherwise, the selected page might no longer contain any results.

11. Improving Search Relevance

Finding matching records is only one part of a useful search experience. The order of results matters too.

Suppose someone searches for *Emails are not allowed*. A record with that exact email should generally appear before a customer whose company description happens to contain the same keyword.

One simple strategy is to prioritize exact matches, then prefix matches, followed by broader substring matches.

PostgreSQL can express this with a ranking condition:

SELECT id, name, email
FROM customers
WHERE
    name ILIKE '%alex%'
    OR email ILIKE '%alex%'
    OR company ILIKE '%alex%'
ORDER BY
    CASE
        WHEN email = '*Emails are not allowed*' THEN 0
        WHEN name ILIKE 'alex%' THEN 1
        WHEN email ILIKE 'alex%' THEN 2
        ELSE 3
    END,
    created_at DESC,
    id DESC
LIMIT 20;

This is a simplified illustration. In an actual application, the ranking rules should be built using parameterized query expressions, and the exact-match condition should be based on the original search input.

For longer text documents, PostgreSQL full-text search offers additional capabilities.

A basic full-text query might look like this:

SELECT id, title
FROM documents
WHERE search_vector @@ websearch_to_tsquery('english', :query)
ORDER BY ts_rank(search_vector, websearch_to_tsquery('english', :query))
DESC
LIMIT 20;

Here, search_vector represents a stored or generated tsvector column that has been configured and indexed appropriately.

Full-text search supports linguistic processing and relevance ranking, making it useful for knowledge bases, article directories, and documentation portals.

However, full-text search and substring search solve different problems. If users need to find a partial email address or match characters in the middle of a product name, trigram indexing may be more appropriate.

12. Caching Frequently Requested Results

Caching can reduce repeated database work when many users search for the same information.

For example, a dashboard may repeatedly request the same active customer list or a frequently used category of products.

A cache such as Redis can store selected search responses for a short period.

A cache key might include the normalized keyword, filters, sorting options, page number, and page size:

customers:q=alex:status=active:sort=created_at:page=1:size=20

Every parameter that can change the result must be represented in the key. Otherwise, users might receive incorrect results from a cache entry created for a different search.

Caching also introduces invalidation challenges. If a customer's status changes, a previously cached result may become outdated.

Possible strategies include short expiration periods, targeted invalidation, and versioned cache keys.

Caching should usually be introduced after measuring the system. For thousands of records, a well-indexed PostgreSQL database may already be fast enough. An unnecessary cache adds complexity, infrastructure costs, and another failure mode.

Applications handling private or permission-sensitive data must also ensure that cache entries cannot leak results between users with different access rights.

13. Handling Errors and Empty Results

A professional search experience should handle more than successful requests.

Users may search for a keyword that has no matches, experience a network failure, or submit an invalid filter.

The API should return predictable response formats and appropriate HTTP status codes.

For example:

• 200 OK for a successful search, even when no records match.
• 422 Unprocessable Entity for invalid FastAPI query parameters.
• 401 Unauthorized when authentication is required but missing or invalid.
• 403 Forbidden when an authenticated user lacks permission.
• 429 Too Many Requests when a rate limit is exceeded.

Database outages and unexpected server errors should be logged for investigation, while the client receives a safe and understandable message.

The frontend should distinguish between an empty result set and a failed request.

A message such as "No customers match your search" is appropriate when the API returns an empty list. "Unable to load customers. Please try again" is more suitable when a request fails.

Clear feedback helps users understand what happened and reduces unnecessary repeated actions.

14. Security Considerations

Search endpoints accept user input, so they require the same security attention as other API endpoints.

First, use parameterized queries or a trusted query builder such as SQLAlchemy. Avoid building SQL statements through string concatenation.

Second, validate query lengths, pagination limits, filter values, and sort options. A maximum keyword length can help prevent unusually large inputs from consuming excessive resources.

Third, apply authentication and authorization wherever the records are not public. A user should only be able to search records they are allowed to access.

For example, a multi-tenant CRM should scope its database query to the authenticated user's organization. Hiding unauthorized results only in React is not sufficient.

Fourth, consider rate limiting. Automated clients can generate large numbers of expensive requests, even when each individual query is valid.

Finally, do not log sensitive search terms or personal information unnecessarily. Logs should provide enough diagnostic information for troubleshooting while respecting privacy and data-retention requirements.

Security and performance often reinforce each other. Restricting page sizes, validating parameters, and limiting request rates helps protect the system from accidental misuse as well as malicious traffic.

15. Testing the Search System

A search feature should be tested with realistic data and a variety of query patterns.

Unit tests can verify input validation, filter construction, sorting rules, and pagination calculations.

Integration tests should run against a test database to confirm that the SQL queries return the expected records.

Important test cases include:

• Searching for a complete customer name.
• Searching with different letter capitalization.
• Searching for a partial email address.
• Searching with leading or trailing whitespace.
• Applying a keyword and status filter together.
• Requesting an empty result set.
• Rejecting a page number below one.
• Rejecting an excessive page size.
• Testing keywords containing SQL-like input.
• Confirming stable ordering when records share the same timestamp.
• Verifying that one organization cannot access another organization's records.

Performance tests should use datasets that resemble actual production data. A test with 100 rows will not reveal the same bottlenecks as a test with 50,000 records.

Measure latency at different levels, including database execution time, API response time, and the full user-perceived search experience.

Useful metrics include median response time, p95 latency, request volume, error rate, database connection usage, and cache hit rate when caching is enabled.

The objective is not merely to achieve a fast result once. It is to maintain predictable performance under normal and peak workloads.

16. Common Mistakes to Avoid

Several implementation mistakes can make a search feature difficult to scale.

Loading every record into React: Fetching thousands of rows and filtering them in the browser increases transfer time and memory usage. Perform filtering and pagination on the server when the dataset is substantial.

Indexing every column: Unnecessary indexes consume storage and slow down writes. Index the fields and combinations that match real query patterns.

Ignoring query plans: A query that appears efficient in development may behave differently with production data. Use EXPLAIN ANALYZE and representative datasets.

Sending requests for every keystroke: Use debouncing and cancellation to avoid unnecessary requests.

Using unstable sorting: Ordering by a non-unique column can produce inconsistent page boundaries. Add a unique tie-breaker.

Using huge offsets for deep pagination: Consider cursor pagination when navigating large result sets.

Adding caching too early: Measure database performance first. Caching is valuable when it addresses a demonstrated bottleneck.

Ignoring access control: Every query must enforce the correct data-access rules on the server.

Returning unbounded results: Always enforce sensible page-size limits and consider response-size constraints.

Avoiding these mistakes creates a stronger foundation for future improvements.

17. Scaling Beyond Thousands of Records

A PostgreSQL-based search system can handle many workloads involving thousands or even millions of records when queries, indexes, and hardware are appropriate. However, the correct architecture depends on search complexity, update frequency, concurrency, and latency requirements.

As the application grows, several improvements may become useful.

Connection pooling: Reuse database connections efficiently rather than opening a new connection for every request.

Query optimization: Remove unnecessary joins, select only required columns, and optimize the most expensive filters.

Read replicas: For suitable workloads, replicas can distribute read traffic, although replication lag and consistency requirements must be considered.

Dedicated search engines: Elasticsearch or OpenSearch can support advanced text analysis, fuzzy matching, complex relevance ranking, and large-scale search workloads. They also introduce operational overhead and synchronization requirements.

Asynchronous indexing: If a dedicated search engine is used, database changes must be propagated reliably. Queues, change-data capture, or an outbox pattern can help prevent search indexes from becoming inconsistent with the source database.

Monitoring and alerting: Track slow queries, database saturation, API latency, and error rates so that performance problems are detected before they affect many users.

These improvements should be introduced according to actual requirements rather than assumptions about future scale.

For many business applications, a carefully indexed PostgreSQL database remains the simplest and most maintainable solution.

18. A Practical Development Workflow

When implementing a search system, following a structured workflow helps reduce unnecessary complexity.

Start by identifying the fields users need to search, the filters they require, and the expected number of records.

Design the database schema and create a small but realistic dataset. Build a basic API that supports keyword search, validation, and pagination.

Next, integrate the API with React. Add debouncing, loading indicators, empty states, and error handling.

Once the basic functionality works, examine the database query plan and add appropriate indexes. Test the combined behavior of keyword search, filtering, sorting, and pagination.

Finally, measure performance under realistic conditions. Add caching, cursor pagination, or a dedicated search engine only when the evidence shows that they are needed.

This workflow keeps the initial implementation focused while leaving room for future improvements.

Conclusion

Building a search system that handles thousands of records is not simply a matter of adding a search box to a web application. It requires thoughtful database design, efficient queries, controlled pagination, a responsive frontend, and reliable error handling.

React provides an intuitive search interface, FastAPI offers a structured backend for validating requests and applying business logic, and PostgreSQL provides powerful querying and indexing capabilities.

By using appropriate indexes, debouncing requests, validating filters, implementing stable pagination, and measuring real query performance, developers can create a search experience that remains responsive as data grows.

The most important lesson is to optimize based on evidence. Start with a simple, well-structured implementation, measure how it performs, identify bottlenecks, and introduce more advanced tools only when they solve a real problem.

A scalable search system is not necessarily the one with the most technologies. It is the one that delivers accurate results efficiently, protects user data, and remains easy to maintain as the application evolves.