Composite Keys or OpenSearch: Which Multi-Attribute Search Does Your Application Need?

A contract screen always asks for active contracts in one department expiring in a selected year. A records investigator combines changing metadata filters with phrases from document content. Both screens need search, but they place different demands on the index.

Start with the questions the application must answer. Configured composite keys optimize repeated combinations of structured attributes. OpenSearch is useful when discovery requires broader combinations, document text, relevance, or aggregations. Prove the query shape, result order, and operational behavior before choosing the backend.

Work from the question to the index

1. 1 · Inventory

   Write the actual queries

   Record filters, operators, sorting, result fields, and expected volume.

2. 2 · Match

   Check index coverage

   Compare a configured metadata access pattern with search-engine mappings.

3. 3 · Prove

   Test the whole result set

   Exercise pagination, updates, access restrictions, and recovery.

Use composite keys for repeated metadata combinations

A composite key indexes a defined combination of attribute values. FormKiQ's site or classification schema can declare combinations such as department, status, and expiryYear. Metadata consistency matters: missing values or inconsistent department names undermine the intended query.

Match the configured key order and supported operators. For a covering composite search, equality conditions precede the final field's supported prefix or range condition. A three-field definition does not automatically provide every two-field lookup or an arbitrary sort order. Design and test each required access pattern.

As of FormKiQ v1.19.2, POST /search supports multi-attribute AND searches without a composite key covering every filter. Configured composite keys can still improve efficiency for frequently used combinations. Test response times and pagination with realistic data, and confirm the capabilities of older deployments before using the same queries.

Compare the requirement, not just the number of filters

OpenSearch's Query DSL supports full-text and exact-term queries with different semantics. Map identifiers and exact categories appropriately; a text analyzer can change matching behavior. Supported fuzzy or prefix queries still need workload testing, especially with broad filters and large result sets.

Work through the contract-screen decision

Suppose the operational screen always supplies department = Legal, status = active, and expiryYear = 2027. The following is an illustrative schema fragment, not a complete schema or search request:

{
  "compositeKeys": [
    { "attributeKeys": ["department", "status", "expiryYear"] }
  ]
}

1. Prove the routine screen

   Configure the combination, populate consistent values, and reindex older documents where required. Compare known matching document IDs across every continuation page. Test both a small department and the busiest department.

2. Evaluate the changed question

   The investigator now removes the department filter and searches contract text for an indemnity phrase while asking for counts by counterparty. The original composite key does not establish support for that query. Evaluate the installed full-text backend and OpenSearch aggregation path against the new requirements.

3. Keep both paths explicit

   The application can retain the structured operational query and add discovery separately. Define each path's pagination, sorting, freshness, and error behavior. Avoid silently switching backends when that changes what a result means.

Measure freshness and recover the index

Treat OpenSearch as a derived index of the authoritative document records. Extraction, OCR, synchronization, and index refresh can each affect when new content becomes discoverable. OpenSearch documents become searchable after index refresh; refreshing the index cannot repair content that never reached it.

DynamoDB-backed search also needs an explicit consistency model. AWS documents that global secondary index reads are eventually consistent. Do not promise immediate visibility just because a search avoids OpenSearch. Show an uploaded document by its returned identity where appropriate and explain a pending discovery state.

Budget for index storage, processing, monitoring, and rebuild work. Reconcile missing updates and deletions; test recovery after schema or mapping changes. Retrieve and authorize current state before sensitive actions, and enforce access restrictions on hits, snippets, and aggregates so search does not expose restricted information.

Verify the choice before release

• List each query's fields, operators, sort order, expected result size, and latency goal.
• Confirm endpoint behavior in the installed release and selected search modules.
• Test queries with and without covering composite keys, including skewed data and missing attributes.
• Follow continuation tokens to completion; distinguish a page from the full result set.
• Reindex older records and reconcile known matches after configuration changes.
• Exercise delayed indexing, deletes, denied access, and index recovery.

Choose the path that answers the application's question

Use configured composite keys where repeated metadata combinations justify a deliberate index. Evaluate OpenSearch when the task needs broader discovery and search-engine features. Keep the choice grounded in observed query behavior, result completeness, and the team's ability to operate and recover the deployment.