Designing the user-profile index

This chapter creates the index the rest of the series searches. By the end you have user-profile-v1 behind two aliases, user-profile-read and user-profile-write, with a strict mapping derived field by field from chapter 01’s query shapes, and the lab’s million profiles loaded into it and checked against PostgreSQL.

The chapter makes the decisions: which fields exist, what each one must support, and how the index is named and addressed. Chapter 06 explains the analysis machinery behind the text fields, and chapter 07 tunes autocomplete and name relevance. You need the lab from chapter 01 with chapter 04’s row_version column applied.

Why the index name has a version

What it is. The physical index is called user-profile-v1. Applications never use that name. They use two aliases, which are names that point at one or more indices: user-profile-read for searches and user-profile-write for the indexer.

Why it matters at 300 million profiles. Chapter 02 showed that primary shard count and existing field types cannot change in place. Sooner or later you need a new mapping or a different shard count, and that means building user-profile-v2 next to v1, filling it, and switching traffic. With aliases, the switch is one atomic _aliases request, and neither the Search API nor the indexer is redeployed. Without them, every application that hard-codes user-profile must change at the same moment, and there is no clean way back.

Two aliases instead of one is a deliberate choice. During a reindex (chapter 14), the indexer must write to the new index while searches still read the old one. Separate aliases let you move them at different times. They also give you separate security boundaries: chapter 16 grants the Search API read access to user-profile-read only.

Example. The index creation request in Stage 2 declares both aliases. is_write_index: true marks the index that receives writes when the write alias points to more than one index.

Common mistake. Creating the index as user-profile “for now”. An alias cannot have the same name as an existing index, so the first reindex turns into a rename under pressure.

Decide what every field must do

Before writing a mapping, list what each field has to support. Chapter 01’s query shapes Q1–Q7 decide it. Everything the mapping does follows from this table.

Three decisions in this table are worth stating explicitly.

Identifiers are keywords, even when they look like numbers. userId, mobileNumber, and pincode are never compared as numbers, summed, or searched by range. As keyword, they are matched exactly and support prefix queries, such as all pincodes starting 8000. Numeric types are optimised for range queries, which these fields never need.

dateOfBirth is stored, but not searchable. No query shape filters or sorts by date of birth. Mapped with "index": false and "doc_values": false, it is kept in _source for display and costs nothing in the inverted index or on disk for sorting. If a later requirement needs age filters, that is a mapping change and a reindex, a cost you should pay only when there is a real need. The stronger option, leaving it out of the projection altogether, is often right for personal data. Chapter 16 returns to data minimisation.

Nothing else exists. dynamic: strict rejects any document that carries a field not in this table. A field added upstream in PostgreSQL does not quietly start appearing in search results or growing the mapping. Someone has to decide to add it.

Stage 1 — Create the name synonyms set

The name analysers refer to a synonyms set stored in the cluster, so it must exist before the index. Synonyms let a search for Mohd find Mohammed. Chapter 07 explains how they work and how to change them without reindexing.

1
PUT _synonyms/profile-name-synonyms
2
{
3
  "synonyms_set": [
4
    { "id": "mohammed", "synonyms": "mohammed, mohammad, muhammad, mohd" },
5
    { "id": "lakshmi",  "synonyms": "lakshmi, laxmi" }
6
  ]
7
}

1
{"result":"created","reload_analyzers_details":{"_shards":{"total":0,"successful":0,"failed":0},"reload_details":[]}}

Stage 2 — Write the index definition

Keep the definition in a file under version control, next to the code that depends on it. Chapter 10’s Spring Boot service loads this same file, so the index in production is created from exactly what was reviewed.

Create es/user-profile-v1.json in the lab directory:

1
{
2
  "settings": {
3
    "index": {
4
      "number_of_shards": 1,
5
      "number_of_replicas": 0,
6
      "refresh_interval": "1s"
7
    },
8
    "analysis": {
9
      "filter": {
10
        "name_edge_ngram": {
11
          "type": "edge_ngram",
12
          "min_gram": 2,
13
          "max_gram": 15
14
        },
15
        "name_synonyms": {
16
          "type": "synonym_graph",
17
          "synonyms_set": "profile-name-synonyms",
18
          "updateable": true
19
        }
20
      },
21
      "analyzer": {
22
        "name_index": {
23
          "type": "custom",
24
          "tokenizer": "standard",
25
          "filter": ["lowercase", "asciifolding"]
26
        },
27
        "name_search": {
28
          "type": "custom",
29
          "tokenizer": "standard",
30
          "filter": ["lowercase", "asciifolding", "name_synonyms"]
31
        },
32
        "name_prefix_index": {
33
          "type": "custom",
34
          "tokenizer": "standard",
35
          "filter": ["lowercase", "asciifolding", "name_edge_ngram"]
36
        }
37
      },
38
      "normalizer": {
39
        "lowercase_ascii": {
40
          "type": "custom",
41
          "filter": ["lowercase", "asciifolding"]
42
        },
43
        "lowercase_only": {
44
          "type": "custom",
45
          "filter": ["lowercase"]
46
        }
47
      }
48
    }
49
  },
50
  "mappings": {
51
    "dynamic": "strict",
52
    "properties": {
53
      "userId":        { "type": "keyword" },
54
      "fullName": {
55
        "type": "text",
56
        "analyzer": "name_index",
57
        "search_analyzer": "name_search",
58
        "fields": {
59
          "prefix":  { "type": "text", "analyzer": "name_prefix_index", "search_analyzer": "name_index" },
60
          "keyword": { "type": "keyword", "normalizer": "lowercase_ascii", "ignore_above": 256 }
61
        }
62
      },
63
      "firstName":     { "type": "text", "analyzer": "name_index", "search_analyzer": "name_search" },
64
      "lastName":      { "type": "text", "analyzer": "name_index", "search_analyzer": "name_search" },
65
      "email":         { "type": "keyword", "normalizer": "lowercase_only" },
66
      "mobileNumber":  { "type": "keyword" },
67
      "city": {
68
        "type": "keyword",
69
        "normalizer": "lowercase_ascii",
70
        "fields": { "text": { "type": "text", "analyzer": "name_index" } }
71
      },
72
      "state": {
73
        "type": "keyword",
74
        "normalizer": "lowercase_ascii",
75
        "fields": { "text": { "type": "text", "analyzer": "name_index" } }
76
      },
77
      "country":       { "type": "keyword" },
78
      "pincode":       { "type": "keyword" },
79
      "dateOfBirth":   { "type": "date", "index": false, "doc_values": false },
80
      "gender":        { "type": "keyword" },
81
      "accountStatus": { "type": "keyword" },
82
      "createdAt":     { "type": "date" },
83
      "updatedAt":     { "type": "date" }
84
    }
85
  },
86
  "aliases": {
87
    "user-profile-read":  {},
88
    "user-profile-write": { "is_write_index": true }
89
  }
90
}

The decisions in this file, grouped by what they protect:

Settings are lab values, and say so. One primary and zero replicas fit a single-node laptop. They are not a recommendation for 300 million profiles; chapter 08 derives production values. refresh_interval is set explicitly to one second, so the search-idle behaviour from chapter 02 does not apply.

Three analysers for one name. name_index splits a name into words, lowercases them, and folds accents, so José is stored as jose. name_search does the same to the query and then expands synonyms. name_prefix_index also emits leading fragments of each word, pr, pra, pras, and so on, for autocomplete. Chapter 06 shows each one working with the _analyze API.

Synonyms apply at search time only. The name_synonyms filter is marked updateable, which Elasticsearch allows only in search analysers. The payoff is that changing the synonyms set takes effect without reindexing 300 million documents. Chapter 07 demonstrates it.

fullName is one value indexed three ways. A multi-field indexes the same source value into several sub-fields: fullName for word matching, fullName.prefix for autocomplete, and fullName.keyword for sorting and exact comparisons. The document still contains fullName once.

Normalisers make exact filters forgiving where users type. A normaliser is an analyser for keyword fields that produces a single token. city and state use lowercase_ascii, so a filter for patna or PATNA matches Patna. email uses lowercase_only, because email addresses are case-insensitive in practice, but folding accents in them would change the address. accountStatus and gender have no normaliser: their values come from the application’s enums, and an exact, case-sensitive match catches a client sending the wrong constant.

city and state are keywords with a text sub-field. Filtering needs the exact value, and the search box needs word matching and highlighting. The main field serves the more common use, filtering.

ignore_above: 256 on fullName.keyword. A value longer than 256 characters is not indexed in that sub-field, rather than producing an oversized term. Such a name is almost certainly bad data. See the ignore_above reference.

Stage 3 — Create the index and check the aliases

Create the index from the file. From a shell in the lab directory:

1
set -a; source .env; set +a
2
curl -s -u "elastic:$ELASTIC_PASSWORD" -X PUT "localhost:9200/user-profile-v1" \
3
  -H 'Content-Type: application/json' --data-binary @es/user-profile-v1.json

1
{"acknowledged":true,"shards_acknowledged":true,"index":"user-profile-v1"}

Then check where the aliases point:

1
GET _cat/aliases/user-profile-*?v&h=alias,index,is_write_index

1
alias              index           is_write_index
2
user-profile-read  user-profile-v1 -
3
user-profile-write user-profile-v1 true

Stage 4 — Load the lab’s profiles

The real ingestion path is the outbox indexer and the backfill job, both built in Kotlin in chapter 14. Until then, a short shell loader fills the index so chapters 06 to 13 have data to search. It exports every profile from PostgreSQL as bulk NDJSON, with row_version as the external version, and sends it in chunks.

Create es/export-profiles.sql:

1
-- One bulk action line and one document line per profile, as NDJSON.
2
-- The external version is the row version, so re-running the load is idempotent.
3
SELECT json_build_object(
4
           'index', json_build_object('_id', user_id::text,
5
                                      'version', row_version,
6
                                      'version_type', 'external'))::text
7
       || E'\n' ||
8
       json_build_object(
9
           'userId',        user_id::text,
10
           'fullName',      full_name,
11
           'firstName',     first_name,
12
           'lastName',      last_name,
13
           'email',         email,
14
           'mobileNumber',  mobile_number,
15
           'city',          city,
16
           'state',         state,
17
           'country',       country,
18
           'pincode',       pincode,
19
           'dateOfBirth',   date_of_birth,
20
           'gender',        gender,
21
           'accountStatus', account_status,
22
           'createdAt',     created_at,
23
           'updatedAt',     updated_at)::text
24
FROM user_profile
25
ORDER BY user_id;

Create es/load-v1.sh:

1
#!/usr/bin/env bash
2
# Throwaway loader for the lab. Chapter 14 replaces it with a Kotlin backfill job.
3
set -euo pipefail
4
set -a; source .env; set +a
5
ES="http://localhost:9200"
6
AUTH="elastic:${ELASTIC_PASSWORD}"
7
mkdir -p data
8
rm -f data/chunk-*
9

10
echo "Exporting profiles from PostgreSQL..."
11
docker compose exec -T postgres psql -U profiles -d profiles -At -f - \
12
  < es/export-profiles.sql > data/profiles.ndjson
13
split -l 20000 data/profiles.ndjson data/chunk-
14

15
echo "Disabling refresh for the load..."
16
curl -s -u "$AUTH" -X PUT "$ES/user-profile-write/_settings" \
17
  -H 'Content-Type: application/json' -d '{"index":{"refresh_interval":"-1"}}' > /dev/null
18

19
failed=0
20
for chunk in data/chunk-*; do
21
  response=$(curl -s -u "$AUTH" -X POST "$ES/user-profile-write/_bulk?filter_path=errors,items.*.status" \
22
    -H 'Content-Type: application/x-ndjson' --data-binary "@$chunk")
23
  if [[ "$response" == *'"errors":true'* ]]; then
24
    # 409 means Elasticsearch already holds this version or a newer one: not a failure.
25
    bad=$(grep -o '"status":[0-9]*' <<< "$response" | grep -Evc '"status":(200|201|409)' || true)
26
    if (( bad > 0 )); then
27
      echo "$chunk: $bad failed items"
28
      failed=$((failed + bad))
29
    fi
30
  fi
31
done
32

33
echo "Restoring refresh and refreshing..."
34
curl -s -u "$AUTH" -X PUT "$ES/user-profile-write/_settings" \
35
  -H 'Content-Type: application/json' -d '{"index":{"refresh_interval":"1s"}}' > /dev/null
36
curl -s -u "$AUTH" -X POST "$ES/user-profile-write/_refresh" > /dev/null
37
echo "Done. Failed items: $failed"

The script applies three rules from earlier chapters. It writes through the user-profile-write alias, never the index name. It turns off refresh for the duration of the load and restores it at the end, as the indexing-speed guidance cited in chapter 02 recommends. And it classifies bulk items with chapter 03’s table, so a 409 is not a failure.

Run it:

1
chmod +x es/load-v1.sh
2
./es/load-v1.sh

1
Exporting profiles from PostgreSQL...
2
Disabling refresh for the load...
3
Restoring refresh and refreshing...
4
Done. Failed items: 0

In the lab this took about 45 seconds. Run it a second time: every item comes back 409, because every document already has that version, and the script still reports Failed items: 0. That is the idempotency chapter 04 designed for.

Security note — data/profiles.ndjson is a full export of the profile table, about 470 MB for the lab. For synthetic data that is harmless. For real data, an export like this is exactly the kind of file that leaks: keep it out of version control, and never produce one from production outside a controlled process.

Checkpoint: the index matches PostgreSQL

1
GET user-profile-read/_count?filter_path=count

1
{"count":1000000}

1
docker compose exec postgres psql -U profiles -d profiles -At -c "SELECT count(*) FROM user_profile;"

1
1000000

The counts match: chapter 04 deleted one profile and inserted one. Check those two changes arrived:

1
GET user-profile-read/_doc/42?filter_path=_version,_source.city,_source.accountStatus

1
{"_version":4,"_source":{"city":"Gaya","accountStatus":"SUSPENDED"}}

GET user-profile-read/_doc/43 returns "found":false. The Elasticsearch _version equals PostgreSQL’s row_version, which is what makes later writes from the indexer safe.

1
GET _cat/indices/user-profile-v1?v&h=index,docs.count,docs.deleted,store.size

1
index           docs.count docs.deleted store.size
2
user-profile-v1    1000000            0    157.6mb

Chapter 08 uses that store size, measured on your own data, as the starting point for capacity planning.

Stage 5 — Check the matrix behaves as designed

A mapping is a set of promises. Test the important ones now, while they are cheap to fix.

Email lookup ignores case. The normaliser is applied to the query value too:

1
GET user-profile-read/_search?filter_path=hits.total,hits.hits._source
2
{
3
  "query":   { "term": { "email": "PRIYA.KUMAR.1@EXAMPLE.COM" } },
4
  "_source": ["userId", "fullName", "email"]
5
}

1
{"hits":{"total":{"value":1,"relation":"eq"},
2
 "hits":[{"_source":{"userId":"1","fullName":"Priya Kumar","email":"priya.kumar.1@example.com"}}]}}

City filters ignore case; status filters do not.

1
GET user-profile-read/_count?filter_path=count
2
{ "query": { "bool": { "filter": [
3
  { "term": { "city": "patna" } },
4
  { "term": { "accountStatus": "ACTIVE" } }
5
] } } }

1
{"count":70214}

That is exactly SELECT count(*) FROM user_profile WHERE city = 'Patna' AND account_status = 'ACTIVE' in PostgreSQL. The same query with "accountStatus": "active" returns 0, as intended.

Unknown fields are rejected.

1
PUT user-profile-write/_doc/999
2
{ "userId": "999", "fullName": "Test", "nickname": "x" }

1
strict_dynamic_mapping_exception: [1:46] mapping set to strict, dynamic introduction of [nickname] within [_doc] is not allowed

Text fields refuse to aggregate. Try to count profiles by fullName:

1
GET user-profile-read/_search
2
{ "size": 0, "aggs": { "names": { "terms": { "field": "fullName" } } } }

1
illegal_argument_exception: Fielddata is disabled on [fullName] in [user-profile-v1]. Text fields are not
2
optimised for operations that require per-document field data like aggregations and sorting, so these
3
operations are disabled by default. Please use a keyword field instead. ...

That is the protection working. Use fullName.keyword for this. Chapter 06 explains why turning on fielddata instead is dangerous.

A returned-only field does not always fail loudly. A term query on dateOfBirth fails:

1
query_shard_exception: failed to create query: Cannot search on field [dateOfBirth] since it is not indexed nor has doc values.

But a range query on the same field returns zero hits and no error:

1
GET user-profile-read/_search?size=0
2
{ "query": { "range": { "dateOfBirth": { "gte": "1990-01-01" } } } }

1
{"took":0,"timed_out":false,"_shards":{"total":1,"successful":1,"skipped":0,"failed":0},
2
 "hits":{"total":{"value":0,"relation":"eq"},"max_score":null,"hits":[]}}

A caller who builds a date-of-birth filter against this index gets an empty result, not an error. The protection against that is the Search API’s request contract (chapter 10), which exposes only the filters the matrix allows.

When index templates help, and why this series does not use one

An index template applies settings, mappings, and aliases automatically to any new index whose name matches a pattern. Composable templates are built from reusable component templates. You could put this chapter’s settings and mappings into a component template, and have an index template apply it to every user-profile-v* index:

1
PUT _component_template/user-profile-schema
2
{ "template": { "settings": { ... }, "mappings": { ... } } }
3

4
PUT _index_template/user-profile
5
{ "index_patterns": ["user-profile-v*"], "priority": 100, "composed_of": ["user-profile-schema"] }

The simulate API shows what a future index would receive:

1
POST _index_template/_simulate_index/user-profile-v2?filter_path=template.settings.index.number_of_shards,template.mappings.dynamic

1
{"template":{"settings":{"index":{"number_of_shards":"1"}},"mappings":{"dynamic":"strict"}}}

Trade-off. Templates earn their place when indices are created automatically and often, as with time-based data streams for logs. A search projection gets a new index rarely, deliberately, and usually because the mapping changed. A file per version, user-profile-v1.json and later user-profile-v2.json, makes each version’s definition reviewable and diffable. A template, by contrast, is shared state in the cluster that silently applies to the next matching index, including one created by a typo. This series uses files. If you tried the template requests, delete both before continuing:

1
DELETE _index_template/user-profile
2
DELETE _component_template/user-profile-schema

Common mistakes with index design

• Designing the mapping from the table instead of from the queries. Mirroring every PostgreSQL column produces a large index full of fields nobody queries, and puts more personal data at risk.
• Mapping identifiers as numbers. Long IDs and phone numbers as long lose leading zeros and prefix queries, and gain nothing.
• One alias for reading and writing. It works until the first reindex, when the indexer and the search traffic need to point at different indices.
• Leaving dynamic at its default. The first unexpected field from upstream changes the mapping permanently, as chapter 02 showed.

What you built, and what comes next

You have user-profile-v1 holding one million profiles, reachable only through user-profile-read and user-profile-write, with a strict mapping in which every field exists because a query shape needs it. You checked the count against PostgreSQL and tested each promise of the capability matrix.

The settings are for a laptop. Shard and replica counts for 300 million profiles are chapter 08’s subject, and they need measurements this chapter cannot provide.

Chapter 06 opens up the text fields: how analysers, tokenisers, token filters, character filters, and normalisers turn José Fernandes into searchable terms, and why fielddata and uncontrolled mappings are the two fastest ways to exhaust a cluster’s memory.