The OpenSearch console provides four methods for you to define an application schema. OpenSearch Industry Algorithm Edition supports multi-table schemas, letting you model complex data relationships across a primary table and up to 10 secondary tables. During indexing, OpenSearch performs a LEFT JOIN on these tables to produce a wide table, which the search index is built on.
How it works
At index time, OpenSearch runs a LEFT JOIN across the primary table and all associated secondary tables. The result is a wide table whose row count matches the primary table — one search document per primary record. Secondary table fields with no matching record are filled with default values: 0 for INT fields and null for STRING fields.
The index is built from this wide table and serves all search queries.
Association rules
Follow these rules when designing a multi-table schema:
One primary table per application. Only one table can be the primary table.
N:1 or 1:1 record ratio only. Each primary table record maps to at most one secondary table record. A single primary record cannot map to multiple secondary records (1:N is not supported).
Key-based association. A secondary table joins the primary table through its primary key, which must match a foreign key in the primary table. The primary table's foreign key can only reference the primary key of a secondary table.
Same field type required. Fields can only be mapped across tables if they share the same type. For example, an INT primary key in a secondary table can only map to an INT field in the primary table.
Up to two association levels for standard applications; exclusive applications support up to three levels.
Up to 10 secondary tables per application; exclusive applications can exceed this limit.
Supported association patterns
| Pattern | Association | Supported |
|---|---|---|
| Primary + 1 secondary | Primary → Secondary | Yes |
| Primary + 2 secondaries in chain | Primary → B → C | Yes |
| Primary + multiple secondaries (fan-out) | Primary → B, Primary → C, Primary → D (≤2 levels, ≤10 tables) | Yes |
| Primary + 3-level chain | Primary → B → C → D (>2 levels) | No, except exclusive applications (up to 3 levels) |
| Cyclical association | Primary → B, B → Primary | No |
| Primary + >10 secondaries | More than 10 secondary tables | No, except exclusive applications |
Limits and operational notes
Update delay when N is large
When the primary-to-secondary record ratio is N:1 and N is large, updating a single secondary record triggers N updates to the primary table index. This can cause update delays for both tables. Keep N at 10 or below to minimize this risk.
Default values for unmatched fields
After the LEFT JOIN, any secondary table field with no matching primary record is filled with a default value in the wide table:
| Field type | Default value |
|---|---|
| INT | 0 |
| STRING | null |