The search problem at 300 million profiles

By the end of this chapter you have a local lab running PostgreSQL 18, Elasticsearch 9.5.4, and Kibana 9.5.4 in Docker Compose, with one million synthetic user profiles in Postgres. You use it to find, with EXPLAIN rather than opinion, the point where B-tree and trigram indexes stop fitting a flexible user-search workload. You also leave with the rule the rest of the series depends on: PostgreSQL stays the source of truth, and Elasticsearch holds a rebuildable, read-optimised projection of it.

This series is for developers who already run Spring Boot services on PostgreSQL and are comfortable with Kotlin, SQL, and query plans. Spring and Kotlin are not explained anywhere in the series. Elasticsearch is explained from its data model up. This chapter contains no application code. Kotlin starts in chapter 09, once the index design is settled.

Versions and labels used throughout the series

The Java client is one minor version behind the server on purpose: it is the version Spring Boot 4.1.1 and Spring Data Elasticsearch 6.1 are built against, and the Java client documentation states that the client is forward compatible with greater or equal minor versions of the server. It does not expose features added in 9.5; nothing in this series needs them.

Budget about 4 GB of memory for Docker. Elasticsearch is capped at a 1 GB heap in this lab, which is enough for a million small documents and nowhere near a production size.

Configuration at this scale is never universal, so every claim in the series carries one of three labels:

• Principle: true of Elasticsearch or PostgreSQL in general, with a link to the primary source where it matters.
• Example assumption: a choice made for this example system. Change it when your context differs.
• Needs validation: a decision that only benchmarks, capacity planning, or production data can settle. The series gives the method, not the answer.

The workload: fifteen fields and seven query shapes

The system stores one row per user profile with these fields: userId, fullName, firstName, lastName, email, mobileNumber, city, state, country, pincode, dateOfBirth, gender, accountStatus, createdAt, and updatedAt. There is no free-text street address; “address-like” search in this series means city, state, and pincode.

Search traffic falls into seven shapes. They are the contract for everything that follows, and the lab exercises most of them.

Example assumption: 300 million rows (30 crore), a read-heavy search workload, and a continuous stream of profile updates from the rest of the platform. Query rate, latency targets, and update rate are left open on purpose; chapter 08 turns them into a sizing method, and chapter 18 lists them as questions you must answer before production.

Stage 1 — Start the lab

1. Create a working directory with this layout:

   1
   user-search-lab/
   2
   ├── .env
   3
   ├── compose.yaml
   4
   └── db/
   5
       └── init/
   6
           ├── 01-schema.sql
   7
           └── 02-seed.sql

2. Add the environment file. Compose reads it automatically, and every password in the lab comes from it.

   1
   # Local development only. Never commit real values.
   2
   STACK_VERSION=9.5.4
   3
   ELASTIC_PASSWORD=change-me-elastic
   4
   KIBANA_PASSWORD=change-me-kibana
   5
   POSTGRES_PASSWORD=change-me-postgres
   6
   ES_MEM_LIMIT=2g

3. Add the Compose file.

   1
   name: user-search-lab
   2
   
   3
   services:
   4
     postgres:
   5
       image: postgres:18
   6
       environment:
   7
         POSTGRES_DB: profiles
   8
         POSTGRES_USER: profiles
   9
         POSTGRES_PASSWORD: ${POSTGRES_PASSWORD}
   10
       ports:
   11
         - "127.0.0.1:5432:5432"
   12
       volumes:
   13
         - pgdata:/var/lib/postgresql
   14
         - ./db/init:/docker-entrypoint-initdb.d:ro
   15
       healthcheck:
   16
         test: ["CMD-SHELL", "pg_isready -U profiles -d profiles"]
   17
         interval: 5s
   18
         retries: 30
   19
   
   20
     elasticsearch:
   21
       image: docker.elastic.co/elasticsearch/elasticsearch:${STACK_VERSION}
   22
       environment:
   23
         discovery.type: single-node
   24
         ELASTIC_PASSWORD: ${ELASTIC_PASSWORD}
   25
         xpack.security.enabled: "true"
   26
         # LOCAL-DEV SHORTCUT: plain HTTP on the REST port. Chapter 10 turns TLS on.
   27
         xpack.security.http.ssl.enabled: "false"
   28
         xpack.license.self_generated.type: basic
   29
         ES_JAVA_OPTS: -Xms1g -Xmx1g
   30
       mem_limit: ${ES_MEM_LIMIT}
   31
       ulimits:
   32
         memlock: { soft: -1, hard: -1 }
   33
       ports:
   34
         - "127.0.0.1:9200:9200"
   35
       volumes:
   36
         - esdata:/usr/share/elasticsearch/data
   37
       healthcheck:
   38
         test: ["CMD-SHELL", "curl -s -u elastic:${ELASTIC_PASSWORD} http://localhost:9200/_cluster/health | grep -Eq '\"status\":\"(green|yellow)\"'"]
   39
         interval: 10s
   40
         retries: 30
   41
   
   42
     # One-shot job: gives the built-in kibana_system user a password.
   43
     # Kibana should not connect as the elastic superuser.
   44
     kibana-setup:
   45
       image: docker.elastic.co/elasticsearch/elasticsearch:${STACK_VERSION}
   46
       depends_on:
   47
         elasticsearch: { condition: service_healthy }
   48
       restart: "no"
   49
       command: >
   50
         bash -c 'until curl -s -o /dev/null -w "%{http_code}" -u "elastic:${ELASTIC_PASSWORD}"
   51
         -X POST http://elasticsearch:9200/_security/user/kibana_system/_password
   52
         -H "Content-Type: application/json" -d "{\"password\":\"${KIBANA_PASSWORD}\"}" | grep -q 200;
   53
         do sleep 5; done; echo kibana_system password set'
   54
   
   55
     kibana:
   56
       image: docker.elastic.co/kibana/kibana:${STACK_VERSION}
   57
       depends_on:
   58
         kibana-setup: { condition: service_completed_successfully }
   59
       environment:
   60
         ELASTICSEARCH_HOSTS: http://elasticsearch:9200
   61
         ELASTICSEARCH_USERNAME: kibana_system
   62
         ELASTICSEARCH_PASSWORD: ${KIBANA_PASSWORD}
   63
       ports:
   64
         - "127.0.0.1:5601:5601"
   65
   
   66
   volumes:
   67
     pgdata:
   68
     esdata:

   Four lines in this file carry decisions worth understanding.

   discovery.type: single-node tells the node not to look for peers and to elect itself. It also keeps the node out of production mode, so the bootstrap checks, such as the vm.max_map_count check, are not enforced. That is convenient on a laptop; the same page recommends forcing the checks on with es.enforce.bootstrap.checks if a single node ever runs in production. A single node can never hold a replica of its own shards, so the cluster reports yellow as soon as an index has replicas. That is expected here and explained in chapter 02.

   xpack.security.enabled: "true" keeps authentication on even locally. Turning security off makes the lab slightly easier and makes every later chapter about API keys and roles impossible to try.

   xpack.security.http.ssl.enabled: "false" is the one deliberate shortcut: the REST API speaks plain HTTP so that curl and the Spring client work without a certificate. Chapter 10 turns TLS on and shows the client configuration for it.

   The kibana-setup service exists because Kibana should connect as the built-in kibana_system user, not as the elastic superuser. Elastic’s built-in users documentation describes kibana_system as the account Kibana uses to talk to Elasticsearch. The service waits for Elasticsearch, sets that user’s password through the change-password API, and exits.

   Security note — Every port binds to 127.0.0.1, so nothing in the lab is reachable from your network. Keep it that way: this Compose file has a known superuser password, plain-HTTP authentication, and no audit logging. None of those are acceptable outside a laptop. Chapter 16 covers the production equivalents, and Elastic’s Docker Compose guide shows a reference multi-node layout.

4. Add the table. user_id is a bigint identity, which later chapters rely on for keyset pagination during backfill. full_name is a stored generated column so that Postgres and Elasticsearch agree on its exact value.

   1
   CREATE TABLE user_profile (
   2
       user_id        bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
   3
       first_name     text        NOT NULL,
   4
       last_name      text        NOT NULL,
   5
       full_name      text GENERATED ALWAYS AS (first_name || ' ' || last_name) STORED,
   6
       email          text        NOT NULL UNIQUE,
   7
       mobile_number  text        NOT NULL UNIQUE,
   8
       city           text        NOT NULL,
   9
       state          text        NOT NULL,
   10
       country        text        NOT NULL DEFAULT 'IN',
   11
       pincode        text        NOT NULL,
   12
       date_of_birth  date,
   13
       gender         text        NOT NULL,
   14
       account_status text        NOT NULL,
   15
       created_at     timestamptz NOT NULL,
   16
       updated_at     timestamptz NOT NULL
   17
   );

5. Add the seed script. It generates one million profiles from fixed name and city lists. Nothing in it represents a real person, and it produces identical data on every run, so your query results match the ones in this chapter.

   1
   -- 1,000,000 synthetic profiles. Every value is generated; no real person is represented.
   2
   -- h1..h3 are independent pseudo-random buckets derived from the row number, so the
   3
   -- data is identical on every run without correlating name, city, and status.
   4
   INSERT INTO user_profile (first_name, last_name, email, mobile_number, city, state,
   5
                             pincode, date_of_birth, gender, account_status,
   6
                             created_at, updated_at)
   7
   SELECT fn,
   8
          ln,
   9
          lower(fn) || '.' || lower(ln) || '.' || g || '@example.com',
   10
          '9' || lpad(g::text, 9, '0'),
   11
          (ARRAY['Patna','Gaya','Mumbai','Pune','Bengaluru',
   12
                 'Chennai','Kolkata','Lucknow','Jaipur','Kochi'])[1 + h1 % 10],
   13
          (ARRAY['Bihar','Bihar','Maharashtra','Maharashtra','Karnataka',
   14
                 'Tamil Nadu','West Bengal','Uttar Pradesh','Rajasthan','Kerala'])[1 + h1 % 10],
   15
          (ARRAY['8000','8230','4000','4110','5600',
   16
                 '6000','7000','2260','3020','6820'])[1 + h1 % 10] || lpad((h1 % 100)::text, 2, '0'),
   17
          date '1960-01-01' + (h2 % 16000)::int,
   18
          CASE WHEN h3 % 97 = 0 THEN 'OTHER' WHEN h3 % 2 = 0 THEN 'FEMALE' ELSE 'MALE' END,
   19
          CASE h2 % 10 WHEN 7 THEN 'INACTIVE' WHEN 8 THEN 'SUSPENDED' WHEN 9 THEN 'DELETED'
   20
                       ELSE 'ACTIVE' END,
   21
          created,
   22
          created + (h3 % 570) * interval '1 day'
   23
   FROM (
   24
       SELECT g,
   25
              hashint4(g)::bigint          + 2147483648 AS h1,
   26
              hashint4(g # 1431655765)::bigint + 2147483648 AS h2,
   27
              hashint4(-g)::bigint         + 2147483648 AS h3,
   28
              (ARRAY['Prashant','Priya','Rahul','Anjali','Amit','Sneha','Vikram','Pooja',
   29
                     'Arjun','Kavya','Rohit','Neha','Suresh','Divya','Mohammed','Fatima',
   30
                     'Joseph','Mary','Gurpreet','Harpreet','José','Aditi','Karthik',
   31
                     'Lakshmi','Ravi','Meera','Sanjay','Ananya','Imran','Zoya'])[1 + g % 30] AS fn,
   32
              (ARRAY['Kumar','Sharma','Singh','Patel','Reddy','Iyer','Nair','Das','Gupta',
   33
                     'Mishra','Khan','Fernandes','Joshi','Yadav','Chatterjee','Menon',
   34
                     'Pillai','Verma','Rao','Jha'])[1 + (g / 30) % 20] AS ln,
   35
              timestamptz '2021-01-01' + (g % 1500) * interval '1 day' AS created
   36
       FROM generate_series(1, 1000000) AS g
   37
   ) AS s;
   38
   
   39
   CREATE INDEX user_profile_state_status_updated_idx
   40
       ON user_profile (state, account_status, updated_at DESC);
   41
   
   42
   ANALYZE user_profile;

   The hashint4 buckets matter more than they look. The first name comes from g % 30. Deriving the city from g with plain modulo arithmetic as well would silently correlate the two, and in a naive version every Prashant lives in Patna. Correlated synthetic data makes filters look far more or less selective than they will be in production, so check distributions before you trust any plan built on generated rows.

   The script creates one composite index, on (state, account_status, updated_at DESC). It exists to show what a well-fitted B-tree looks like in Stage 2. The primary key and the two UNIQUE constraints create the other three indexes.

6. Start the stack:

   1
   docker compose up -d

   The first start downloads about 1.5 GB of compressed images, and more once they are unpacked. After that, seeding Postgres takes about 20 seconds and the whole stack is ready in about a minute. Watch progress with docker compose ps -a --format 'table {{.Service}}\t{{.Status}}'. The -a flag keeps the finished setup job in the list. When everything is ready you should see something like:

   1
   SERVICE         STATUS
   2
   elasticsearch   Up 56 seconds (healthy)
   3
   kibana          Up 25 seconds
   4
   kibana-setup    Exited (0) 25 seconds ago
   5
   postgres        Up 56 seconds (healthy)

   kibana-setup shows Exited (0). That is success: it is a one-shot job.

Checkpoint: all three services answer

Load the passwords into your shell, then query each service. Reading them from .env keeps them out of your shell history.

1
set -a; source .env; set +a
2
curl -s -u "elastic:$ELASTIC_PASSWORD" localhost:9200

1
{
2
  "name" : "20482afb8e77",
3
  "cluster_name" : "docker-cluster",
4
  "cluster_uuid" : "FO7WnuACQkuBrrhHqrOsVA",
5
  "version" : {
6
    "number" : "9.5.4",
7
    "build_flavor" : "default",
8
    "build_type" : "docker",
9
    ...
10
    "lucene_version" : "10.5.1",
11
    "minimum_wire_compatibility_version" : "8.19.0",
12
    "minimum_index_compatibility_version" : "8.0.0"
13
  },
14
  "tagline" : "You Know, for Search"
15
}

The number field must read 9.5.4; name and cluster_uuid differ on your machine. The build fields are trimmed here. Now check Postgres:

1
docker compose exec postgres psql -U profiles -d profiles \
2
  -c "SELECT count(*), count(DISTINCT city) AS cities FROM user_profile;"

1
 count  | cities
2
---------+--------
3
 1000000 |     10
4
(1 row)

Finally, open http://localhost:5601, sign in as elastic with ELASTIC_PASSWORD, and open Dev Tools from the main menu. Chapters 02 to 08 send most requests from that console; curl works equally well.

If a service is missing, see the troubleshooting table at the end of this chapter.

Stage 2 — Queries PostgreSQL already serves well

Before looking for a problem, confirm what does not need solving. Open a psql session for the rest of this chapter, and refresh the planner statistics and visibility map first. Autovacuum does this on its own shortly after the seed; running it yourself means you don’t have to wait for it.

1
docker compose exec postgres psql -U profiles -d profiles

1
VACUUM ANALYZE user_profile;

The plans in this chapter use EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS OFF). That shows the plan shape and the real row counts, which are identical for this seed on any machine with default PostgreSQL settings. It leaves out timings and buffer counts, which are not identical. Where two plans cost almost the same, the planner’s choice can still differ between runs; Stage 3 shows one such case. PostgreSQL 18 includes buffer counts in EXPLAIN ANALYZE by default, hence BUFFERS OFF.

Exact lookup uses a unique index

Q1 is a point lookup. The UNIQUE constraint on email created a B-tree, and Postgres uses it:

1
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS OFF)
2
SELECT user_id, full_name, city
3
FROM user_profile
4
WHERE email = 'priya.sharma.31@example.com';

1
Index Scan using user_profile_email_key on user_profile (actual rows=1.00 loops=1)
2
  Index Cond: (email = 'priya.sharma.31@example.com'::text)
3
  Index Searches: 1

One index probe, one row. This stays a handful of page reads at 300 million rows, because B-tree depth grows logarithmically. Principle: exact lookups on identifiers are not a reason to adopt a search engine. If your whole workload looked like Q1, you would stop here.

A known filter-and-sort shape uses a composite index

Q4 plus Q5, in one fixed combination: active users in Bihar, most recently updated first.

1
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS OFF)
2
SELECT user_id, full_name, city, updated_at
3
FROM user_profile
4
WHERE state = 'Bihar' AND account_status = 'ACTIVE'
5
ORDER BY updated_at DESC
6
LIMIT 20;

1
Limit (actual rows=20.00 loops=1)
2
  ->  Index Scan using user_profile_state_status_updated_idx on user_profile (actual rows=20.00 loops=1)
3
        Index Cond: ((state = 'Bihar'::text) AND (account_status = 'ACTIVE'::text))
4
        Index Searches: 1

There is no Sort node. The index already stores rows for (Bihar, ACTIVE) in updated_at DESC order, so Postgres reads the first 20 entries and stops. This plan does the same amount of work whether the table holds one million rows or three hundred million.

Principle: when the set of query shapes is small and known, composite B-tree indexes serve filtering and sorting without a scan or a sort step. Many “we need Elasticsearch” conversations end here, correctly.

Stage 3 — Where the indexes stop fitting

Now make the query look like what a support agent or an admin console actually sends.

Substring and infix name search scans the table

A user types kumar and expects the most recently updated matches first. They mean the surname, so a left-anchored prefix index on full_name cannot help.

1
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS OFF)
2
SELECT user_id, full_name, city, updated_at
3
FROM user_profile
4
WHERE full_name ILIKE '%kumar%'
5
ORDER BY updated_at DESC
6
LIMIT 20;

1
Limit (actual rows=20.00 loops=1)
2
  ->  Gather Merge (actual rows=20.00 loops=1)
3
        Workers Planned: 2
4
        Workers Launched: 2
5
        ->  Sort (actual rows=15.33 loops=3)
6
              Sort Key: updated_at DESC
7
              Sort Method: top-N heapsort  Memory: 27kB
8
              ...
9
              ->  Parallel Seq Scan on user_profile (actual rows=16669.67 loops=3)
10
                    Filter: (full_name ~~* '%kumar%'::text)
11
                    Rows Removed by Filter: 316664

Three processes read the whole table, about 333,000 rows each, and throw away 95% of what they read. The LIMIT 20 does not help: the result must be sorted by updated_at, so every match has to be found before the first 20 are known. The per-worker Sort Method lines are trimmed from this output and from the next plan. Depending on your CPU count and settings, the planner may choose a non-parallel plan, and the per-worker Sort rows and Heap Blocks figures shift slightly between runs. The totals are the same.

A trigram index fixes the scan

PostgreSQL has an answer for this: the pg_trgm extension indexes three-character sequences, and a GIN trigram index supports LIKE, ILIKE, and similarity operators.

1
CREATE EXTENSION IF NOT EXISTS pg_trgm;
2
CREATE INDEX user_profile_full_name_trgm_idx
3
    ON user_profile USING gin (full_name gin_trgm_ops);

Rerun the same EXPLAIN for %kumar%:

1
Limit (actual rows=20.00 loops=1)
2
  ->  Gather Merge (actual rows=20.00 loops=1)
3
        Workers Planned: 2
4
        Workers Launched: 2
5
        ->  Sort (actual rows=15.67 loops=3)
6
              Sort Key: updated_at DESC
7
              Sort Method: top-N heapsort  Memory: 27kB
8
              ...
9
              ->  Parallel Bitmap Heap Scan on user_profile (actual rows=16669.67 loops=3)
10
                    Recheck Cond: (full_name ~~* '%kumar%'::text)
11
                    Heap Blocks: exact=887
12
                    ...
13
                    ->  Bitmap Index Scan on user_profile_full_name_trgm_idx (actual rows=50009.00 loops=1)
14
                          Index Cond: (full_name ~~* '%kumar%'::text)
15
                          Index Searches: 1

The sequential scan is gone: the trigram index finds the 50,009 candidate rows directly. They still have to be fetched and sorted, because “Kumar” matches 5% of the table and the index knows nothing about updated_at. For a rarer string such as %zoya fern%, the same plan touches only 1,667 rows. That works, and it is worth being clear about it: for a moderate dataset with a single free-text field, pg_trgm or PostgreSQL full-text search is often enough. Check the price, though:

1
SELECT pg_size_pretty(pg_relation_size('user_profile'))                     AS table_size,
2
       pg_size_pretty(pg_relation_size('user_profile_full_name_trgm_idx')) AS trgm_index;

1
 table_size | trgm_index
2
------------+------------
3
 164 MB     | 19 MB
4
(1 row)

One trigram index on one column adds 19 MB to a 164 MB table. Run SELECT pg_size_pretty(pg_indexes_size('user_profile')); and you will see that the five indexes on this table now take 163 MB together, about as much as the data itself. Every one of them is updated on every write.

Relevance ordering and optional filters combine badly

Now combine Q3, Q4, and Q5 the way a real search box does: a name that should match approximately, two filters, and best match first.

1
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS OFF)
2
SELECT user_id, full_name, city, similarity(full_name, 'prashant kumar') AS score
3
FROM user_profile
4
WHERE full_name % 'prashant kumar'
5
  AND state = 'Bihar'
6
  AND gender = 'MALE'
7
ORDER BY score DESC
8
LIMIT 20;

1
Limit (actual rows=20.00 loops=1)
2
  ->  Sort (actual rows=20.00 loops=1)
3
        Sort Key: (similarity(full_name, 'prashant kumar'::text)) DESC
4
        Sort Method: top-N heapsort  Memory: 27kB
5
        ->  Bitmap Heap Scan on user_profile (actual rows=4755.00 loops=1)
6
              Recheck Cond: ((full_name % 'prashant kumar'::text) AND (state = 'Bihar'::text))
7
              Rows Removed by Index Recheck: 6672
8
              Filter: (gender = 'MALE'::text)
9
              Rows Removed by Filter: 4951
10
              Heap Blocks: exact=7845
11
              ->  BitmapAnd (actual rows=0.00 loops=1)
12
                    ->  Bitmap Index Scan on user_profile_full_name_trgm_idx (actual rows=81676.00 loops=1)
13
                          Index Cond: (full_name % 'prashant kumar'::text)
14
                          Index Searches: 1
15
                    ->  Bitmap Index Scan on user_profile_state_status_updated_idx (actual rows=199755.00 loops=1)
16
                          Index Cond: (state = 'Bihar'::text)
17
                          Index Searches: 1

Read the plan from the bottom up. The trigram index returns 81,676 candidates for the name. The composite index, used here only for its leading state column, returns 199,755 rows in Bihar. BitmapAnd intersects the two bitmaps. (It reports rows=0.00 because bitmap nodes do not count rows.) The heap scan then visits 7,845 pages. SELECT relpages FROM pg_class WHERE relname = 'user_profile'; returns 20,941, so that is 37% of the table. It discards rows that fail the recheck, because trigram matching in a GIN index is approximate, and rows with the wrong gender. The remaining 4,755 rows are scored and sorted to produce 20.

Run the same EXPLAIN several times, with a VACUUM ANALYZE user_profile; in between, and on some runs you get a different plan. It uses only the trigram index, applies both state and gender as a filter, and reports Heap Blocks: exact=20941: every page of the table. ANALYZE builds its statistics from a random sample, and the two plans cost almost the same, so small changes in the sample flip the choice. Both plans find the same 4,755 candidates. That is the real problem with ad-hoc filter combinations on PostgreSQL: the cost of a search depends on a planner decision that nobody designed for this combination.

The ranking is also weaker than it looks. Similarity is shared character trigrams, not meaning:

1
SELECT DISTINCT full_name, round(similarity(full_name, 'prashant kumar')::numeric, 2) AS score
2
FROM user_profile
3
WHERE full_name % 'prashant kumar'
4
ORDER BY score DESC
5
LIMIT 4;

1
   full_name    | score
2
----------------+-------
3
 Prashant Kumar |  1.00
4
 Prashant Khan  |  0.56
5
 Prashant Das   |  0.47
6
 Prashant Jha   |  0.47

“Prashant Khan” outranks “Prashant Das” only because trigram overlap rewards the shared leading k and the shorter surname. It is not a better answer. And every exact “Prashant Kumar” in Bihar scores 1.00, so the order among them is arbitrary unless you add another sort key.

The combinatorics are the deeper problem. Q4 allows any subset of five filters: that is 2⁵ = 32 filter combinations, each of which can be paired with a name condition or not, and sorted by recency or by relevance. You cannot build a composite B-tree for each combination, and the planner can only combine separate indexes through bitmap scans, which give up the “already sorted, stop after 20” behaviour Stage 2 relied on.

Relevance itself is also limited. PostgreSQL’s full-text ranking functions are useful, but the ranking documentation is explicit that they “do not use any global information” and that ranking must consult each matching document. There is no notion that Prashant is rarer, and therefore more informative, than Kumar across the whole table. Scoring that uses corpus-wide term statistics is exactly what a search engine’s inverted index maintains.

What changes at 300 million rows

Everything in Stage 3 ran on one million rows. The mechanisms do not change at 300 million, but their costs do, and several of them land on the transactional database.

• Index size and write cost land on the OLTP primary. Every search index on user_profile is maintained synchronously on every insert and update. A trigram GIN on names, plus indexes for each popular filter combination, is write amplification paid by the service that owns the data. In the lab, indexes already equal the table in size. A naive linear extrapolation of the lab’s 164 MB table and 163 MB of indexes to 300 million rows gives roughly 48 GB of each. That is an estimate from short synthetic names, not a measurement; real names are longer and more varied, so the trigram index would likely be larger.
• Read scaling means copying everything. A Postgres read replica holds the full table and every index. Adding search capacity means adding whole copies of the database. Elasticsearch splits an index into shards spread across nodes, runs one query on all shards in parallel, and scales reads by adding replica shards. Principle, described in chapter 02.
• Search traffic and transactional traffic compete. Long bitmap scans and sorts compete for buffer cache and I/O with the writes that keep the business running, unless you move search to replicas, which returns you to the previous point.
• Search needs change faster than schemas should. Adding accent folding, a new autocomplete strategy, or a new synonym list is a text-analysis change. In Postgres it means new indexes or expression changes on the production table. In Elasticsearch it means building a new index next to the old one and switching an alias, without touching Postgres. Chapter 14 does exactly that.

None of this means Postgres cannot do it. It means the cost of doing it in Postgres grows with the number of query shapes, not only with the number of rows.

Trade-off — Elasticsearch is not free either. You take on a second datastore with its own capacity planning, upgrades, security model, and on-call knowledge; a synchronisation pipeline that must survive failures; and search results that are seconds behind the source. Chapters 04, 08, and 16 are the bill.

When PostgreSQL is still the right answer

Principle: introduce a search engine for a defined workload, not for “search”. If you cannot write down the query shapes, the fields, and the acceptable staleness, you are not ready to design the index, because index design in Elasticsearch is derived from exactly those three things.

The Q1–Q7 table earlier in this chapter is that definition for this series. Everything outside it — reports, joins across tables, exports for finance — stays in PostgreSQL or goes to a warehouse.

Production note — The most common way a search projection goes wrong is scope creep: “while we are at it, index the addresses, the order history, and the KYC documents.” Every extra field costs heap, disk, mapping complexity, and privacy exposure. Chapter 05 builds the field capability matrix that keeps the index to what the query shapes need.

Source of truth versus search projection

The architecture rule for the whole series:

PostgreSQL is the system of record. Elasticsearch holds a denormalised, read-optimised projection derived from it, and nothing else.

The diagram shows writes going only to PostgreSQL. A synchronisation pipeline turns committed changes into index and delete operations on Elasticsearch. The Search API reads only from Elasticsearch. A dotted edge from Elasticsearch back to PostgreSQL marks that the whole projection can be rebuilt from the source at any time. Chapter 04 replaces the “sync pipeline” box with a transactional outbox or change data capture, and explains why.

That rule has concrete consequences:

1. No write goes only to Elasticsearch. If a value exists in the index and not in Postgres, it is a bug.
2. The projection is disposable. You must be able to delete every index and rebuild it from Postgres. If you cannot, you have two sources of truth. Chapter 14’s backfill job is the proof.
3. Search results are a lead, not a decision. Before acting on a profile, such as blocking an account or showing an email address to an agent, read it from Postgres and apply authorisation there. The projection can be seconds stale, and it is not the place for access control (chapter 16).
4. Deletes are data too. An erased user must disappear from the projection, from older index versions kept for rollback, and eventually from snapshots.
5. The projection has its own shape. It stores what search needs, in the form search needs: a fullName analysed several ways, a mobileNumber as an exact keyword, perhaps no dateOfBirth at all. It does not mirror the table column for column.

Common mistake: reading a document from Elasticsearch, changing a field, and writing it back to PostgreSQL. That turns a stale copy into the source of a write, and it is how projection lag becomes data loss.

When the lab does not start

Warning — docker compose down -v deletes the lab’s Postgres and Elasticsearch volumes permanently. Run it only inside user-search-lab/, where the data is generated and disposable.

What you built, and what comes next

You have a running PostgreSQL, Elasticsearch, and Kibana lab, one million deterministic synthetic profiles, and plan-level evidence for two claims. Exact lookups and known filter-and-sort combinations are well served by B-tree indexes. Free combination of approximate name matching, optional filters, and relevance ordering is where PostgreSQL’s cost grows with every new query shape.

What this chapter did not do is measure anything at 300 million rows. The index-size extrapolation is a rough estimate, and the lab’s timings mean nothing for production hardware. Chapter 08 replaces both with a capacity-planning method.

Next, chapter 02 builds the Elasticsearch mental model you need before designing an index: clusters, shards, segments, refresh, and the exact places where “an index is like a table” stops being true. It uses the lab you just started.

Production note — Keep the pg_trgm index from Stage 3 only if you intend to use it. On a production table it is a real write cost. In this lab, drop it with DROP INDEX user_profile_full_name_trgm_idx; if you want Stage 3 to be repeatable.