Looking for documents and index design for this usage case

OpenSearch
1

Versions (relevant - OpenSearch/Dashboard/Server OS/Browser): Latest

Describe the issue:

We have a freelance marketplace platform with two sources of truth stored in DynamoDB:

  • Freelancer Profile — live document, always up to date. Contains name, location, skills, work history, portfolio. 1 profile per freelancer.
  • Proposal — a freelancer can submit proposals to many projects. Contains proposal status, inbox status, submitted date, project id. 1 document per proposal.

The relationship is:

1 Freelancer Profile → many proposals

We use OpenSearch purely as a search and filter layer — we only need OpenSearch to return IDs, then we fetch the full data from DynamoDB

In application, company can post job and freelancer can apply to the job. The page can search, sort and filter.

Option 1 — Single index, 1 document per proposal (full denormalisation)
Merge all profile fields and proposal fields into one document per proposal. Purpose-built search index separate from DynamoDB source of truth.

Option 2 — Two indices + terms lookup
freelancer_profiles index for profile data. proposals index for proposal data. Use OpenSearch terms lookup (3.2+ query parameter) to dynamically cross-reference at query time.

Option 3 — Parent-child join
Single index using OpenSearch join field. Freelancer profile is the parent document, proposals are child documents on the same shard.

Option 4 — Nested documents
Single index where freelancer profile is the root document and proposals are nested objects inside the same document. 1 document per freelancer containing all their proposals as a nested array.

Looking for solid real world design that could share your thought or better design because I want to optimise for performance

Configuration:

Relevant Logs or Screenshots:

2

Hi,
What’re search use cases? eg
Do you really need to search for proposals, which Joe Doe was applied to?

Or you need to find all freelances who were ever applied to java coding proposals?
Are these queries web ui interactive? or a kind of a batch jobs?

3

Use case:

  • Web Page
    • Company user posted the job will access the job page showing all the freelancer proposal
    • In the page, user can search and filter.
  • Concern now on how to optimise the document design whether full denormalisation or other options
4

I think it’s worth to start with terms lookup. It should pull freelancers_id by the given proposal_id and apply it as a filter.

5

@kitkit I would agree with mkhl’s suggestion if you don’t need combined relevance scoring or sorting across freelancer and proposal fields in one pass.

You can do the following:

  1. Query the freelancer index for whatever freelancer-attribute filters are active (skills, location) → get back a list of matching freelancer_ids.
  2. Query the proposal index filtered by job_id + proposal-level filters (status, date) + a terms filter on freelancer_id using the list from step 1.

The example below demonstrates this:

1. Create the freelancers index

curl -sk -u admin:admin -X PUT "https://localhost:9200/freelancers" \
  -H 'Content-Type: application/json' -d '{
  "mappings": {
    "properties": {
      "freelancer_id": { "type": "keyword" },
      "name": { "type": "text" },
      "location": { "type": "keyword" },
      "skills": { "type": "keyword" }
    }
  }
}'

2. Create the proposals index

curl -sk -u admin:admin -X PUT "https://localhost:9200/proposals" \
  -H 'Content-Type: application/json' -d '{
  "mappings": {
    "properties": {
      "proposal_id": { "type": "keyword" },
      "freelancer_id": { "type": "keyword" },
      "job_id": { "type": "keyword" },
      "status": { "type": "keyword" },
      "submitted_at": { "type": "date" }
    }
  }
}'

3. Bulk-load sample freelancers

curl -sk -u admin:admin-X POST "https://localhost:9200/freelancers/_bulk" \
  -H 'Content-Type: application/json' -d '
{ "index": { "_id": "f1" } }
{ "freelancer_id": "f1", "name": "Alice", "location": "Berlin", "skills": ["python", "terraform"] }
{ "index": { "_id": "f2" } }
{ "freelancer_id": "f2", "name": "Bob", "location": "Remote", "skills": ["java", "aws"] }
{ "index": { "_id": "f3" } }
{ "freelancer_id": "f3", "name": "Carol", "location": "Berlin", "skills": ["python", "react"] }
'
4. Bulk-load sample proposals

curl -sk -u admin:admin -X POST "https://localhost:9200/proposals/_bulk" \
  -H 'Content-Type: application/json' -d '
{ "index": { "_id": "p1" } }
{ "proposal_id": "p1", "freelancer_id": "f1", "job_id": "job-1", "status": "submitted", "submitted_at": "2026-06-01" }
{ "index": { "_id": "p2" } }
{ "proposal_id": "p2", "freelancer_id": "f2", "job_id": "job-1", "status": "submitted", "submitted_at": "2026-06-05" }
{ "index": { "_id": "p3" } }
{ "proposal_id": "p3", "freelancer_id": "f3", "job_id": "job-1", "status": "withdrawn", "submitted_at": "2026-06-10" }
{ "index": { "_id": "p4" } }
{ "proposal_id": "p4", "freelancer_id": "f1", "job_id": "job-2", "status": "submitted", "submitted_at": "2026-06-12" }
'

5. Search freelancers by attribute filters

curl -sk -u admin:admin -X GET "https://localhost:9200/freelancers/_search" \
  -H 'Content-Type: application/json' -d '{
  "query": {
    "bool": {
      "filter": [
        { "term": { "skills": "python" } },
        { "term": { "location": "Berlin" } }
      ]
    }
  }
}'
Take the freelancer_id values from the hits (here: f1, f3) and plug them into step 2.

6. Search proposals for the job, filtered by status/date and the freelancer_id list from step 1

curl -sk -u admin:admin -X GET "https://localhost:9200/proposals/_search" \
  -H 'Content-Type: application/json' -d '{
  "query": {
    "bool": {
      "filter": [
        { "term": { "job_id": "job-1" } },
        { "term": { "status": "submitted" } },
        { "terms": { "freelancer_id": ["f1", "f3"] } }
      ]
    }
  }
}'

Result here is just p1, since Carol's proposal was withdrawn and Alice's other proposal belongs to a different job.
6

Hi @Anthony @MKH

Thank for your for the suggestion. I currently looking on option 1 and option 2. The rest too heavy for performance and complicate to maintain..

The issue is Option 2 terms lookup seems solve the problem but not all.

  1. If following the suggestion, query the profile and then query the proposal. and we got 10k proposal, we need append 10k freelancer_id, I’m wondering whether this could be risky if growing in certain way (10k is real scenario and not imagination issue)

  2. We also need to drop the sorting for profile that we provided before.

  3. Aggregation from denormalise document is easy for profile and proposal with filtering query on both profile and proposal and with terms lookup, we not able to do that easily because split into 2 different index filtering. Kind like the biggest problem to solve to use term look up for aggregation - need explore all the use cases whether could solve the issue,

  4. Term looks up solve the performance and storage issue vs the denormalise design solve the query issue but not on performance and storage.

Wondering anything else to consider and risk to trade off…