ContextQMD
Back to Manticoresearch

UUID primary IDs implementation specification

doc/uuid-primary-ids-spec.md

28.5.018.0 KB
Original Source

UUID primary IDs implementation specification

Status: branch implementation and QA spec for GitHub issue #1429, synced with branch fix/uuid-primary-ids-1429.

Goal

Support UUID/GUID values as user-facing document IDs for RT/configless tables while keeping Manticore's internal DocID_t machinery numeric. This applies to both ordinary row-wise RT tables and RT tables with engine='columnar'. The design avoids widening DocID_t, adding a broad UUID attribute type, or adding native UUID/128-bit support to Columnar/MCL.

Design summary

The minimal implementation keeps two identities for UUID-ID tables:

  1. Internal ID: the existing numeric id / DocID_t (SPH_ATTR_BIGINT) used by RT chunks, docstore, kill lists, updates, deletes, replication, and other existing internals.
  2. Public ID: a hidden exact string attribute named @uuid_id, exposed to users as id on UUID-ID tables.

Public id operations are translated as follows:

  • incoming public id values are validated as strict UUID strings, normalized to lowercase, and stored in @uuid_id;
  • omitted id values generate a UUIDv8-style string derived from the unique internal numeric DocID_t surrogate;
  • explicit duplicate checks and REPLACE/UPDATE/DELETE resolution find the internal numeric docid through exact string lookup on @uuid_id; generated UUID inserts skip that lookup;
  • result formatting maps @uuid_id back to public id and hides the internal numeric surrogate.

This preserves exact UUID semantics without hashing UUIDs into 64-bit IDs.

UUID-ID correctness does not depend on query-time secondary indexes being enabled. The hidden @uuid_id string attribute is marked indexed and is explicitly eligible for the normal secondary-index and histogram planning paths so exact public id equality/IN filters can use @uuid_id:SecondaryIndex when secondary indexes are enabled. UUID lookup, duplicate resolution, REPLACE, UPDATE, and DELETE must still work with searchd.secondary_indexes = 0 by falling back to the ordinary exact string filter path.

Public behavior

DDL

sql
CREATE TABLE products_uuid ( id uuid, title text, price int );

Rules:

  • id uuid is valid only for RT/configless tables, including engine='columnar' RT tables.
  • Only the primary id column can use the uuid type.
  • Regular attributes cannot use uuid.
  • PQ/plain/indexer-created tables and shard tables are not supported for UUID IDs.
  • Existing tables cannot be converted to or from id uuid with ALTER TABLE.
  • SHOW CREATE TABLE renders the public schema as id uuid; the hidden storage attribute is not exposed as a normal column.

ID format

Accepted explicit IDs are quoted UUID strings in canonical 8-4-4-4-12 hexadecimal layout:

text
550e8400-e29b-41d4-a716-446655440000

Validation is strict enough to reject malformed or reserved UUID-like values:

  • exactly 36 bytes;
  • hyphens at positions 8/13/18/23;
  • hexadecimal digits elsewhere;
  • version nibble at position 14 must be 1 through 8;
  • RFC variant nibble at position 19 must be 8, 9, a, A, b, or B.

Uppercase hexadecimal input is accepted and normalized to lowercase. The nil UUID and version-0 / invalid-variant values are rejected by the same checks. Generated IDs use a UUIDv8-style layout that encodes the internal numeric DocID_t, making generated public UUID IDs unique under the same guarantees as internal numeric auto-IDs. Generated IDs are not random UUIDv4 values.

SQL DML

Supported:

  • INSERT INTO t (id, ...) VALUES ('uuid', ...)
  • INSERT INTO t (...) VALUES (...) with generated UUID id
  • REPLACE INTO t (id, ...) VALUES ('uuid', ...)
  • UPDATE t SET ... WHERE id='uuid'
  • DELETE FROM t WHERE id='uuid'
  • equality and IN filters on public id
  • ORDER BY id, GROUP BY id, COUNT(DISTINCT id), and facets over public id as string semantics
  • LAST_INSERT_ID() and @@session.last_insert_id return UUID strings for UUID-ID tables

Rejected/unsupported:

  • numeric values for UUID id
  • invalid UUID strings, including nil UUIDs, version-0 strings, and invalid RFC variants
  • direct use of @uuid_id in public select/filter/group/sort/update/insert paths
  • range filters (<, <=, >, >=) and arithmetic expressions on UUID id
  • treating 0 as an auto-ID marker; omit id instead

JSON / Elasticsearch-compatible APIs

Supported UUID-aware paths include:

  • /json/insert, /json/replace, /json/update, /json/delete, /json/search
  • /json/bulk valid insert/replace/update/delete actions
  • ES-style _bulk index/create/update/delete actions with string _id
  • ES-style _search response IDs/docvalue fields for public id

ES-style _bulk classifies string IDs without resolving or locking the target table. Decimal integer strings remain numeric IDs, strings accepted by sphPrepareUuidDocid() become normalized UUID IDs, and all other strings retain the legacy numeric hash from GetDocID(). The same rule applies to metadata _id and source-body id.

This preserves numeric strings and arbitrary non-UUID string IDs on numeric-ID tables. The ES handler does not add target-schema validation for UUID-shaped strings. For index/create against a numeric-ID table, the existing numeric insert conversion applies, including acceptance of a leading decimal prefix when non-strict attribute conversion is enabled. update/delete continue to use string filters and follow the table's normal filter validation.

Client-side UUID validation errors should be reported as request validation errors, not daemon/internal errors. The intended status contract is HTTP 400 for invalid UUID strings, numeric IDs sent to UUID-ID tables, direct hidden @uuid_id access, and unsupported UUID range/arithmetic operations. The legacy @id query alias follows public id; on UUID-ID tables it resolves to the UUID string and does not expose the internal numeric DocID_t. Raw _source include/exclude patterns named @id or @uuid_id remain unmatched because source projection uses the public name id. Current branch QA shows the data-level behavior is reject/no-write, but a few HTTP status/envelope paths still need follow-up; see Known current follow-up gaps.

Code structure

Core helpers

src/indexsettings.cpp / src/indexsettings.h define the UUID-ID helpers:

  • sphGetUuidDocidName() returns @uuid_id;
  • sphHasUuidDocid(schema) detects UUID-ID tables by the linked numeric id flag and asserts the hidden string storage invariant in debug builds;
  • sphPrepareUuidDocid() validates strict UUID layout, version, and RFC variant, returns user-facing validation errors, and lowercases accepted UUID strings;
  • sphIsNormalizedUuidDocid() checks the normalized runtime invariant on a length-bearing string span.

src/searchd.cpp defines sphGenerateUuidDocid(DocID_t) and insert-time helpers for:

  • encoding the unique internal numeric DocID_t into a UUIDv8-style public string;
  • locating the hidden string attr ordinal in insert string storage;
  • resolving explicit UUID strings to existing numeric DocID_t values;
  • detecting duplicate UUIDs inside a single multi-row statement with sph::StringSet.

DDL and schema

src/ddl.y and src/searchdddl.cpp add a special id uuid grammar branch. The DDL parser:

  • validates that the type token is uuid;
  • validates that the column name is exactly id;
  • creates the normal internal numeric id attr;
  • adds hidden string attr @uuid_id and marks it indexed. This is an optional acceleration hook for exact string filters, not a correctness dependency on secondary_indexes=1.

src/attribute.cpp, src/indexcheck.cpp, and create-table validation treat @uuid_id as internal but allow it only when generated by the UUID-ID DDL path.

Insert/replace

src/searchd.cpp, src/searchdhttp.cpp, and src/searchdhttpcompat.cpp route public id values to @uuid_id for UUID-ID tables. They do not make SPH_ATTR_BIGINT accept UUID strings.

For insert/replace:

  1. map public id input to @uuid_id;
  2. validate and lowercase the UUID string;
  3. generate a UUID when id is omitted;
  4. resolve existing UUID on REPLACE and reuse existing internal docid when present;
  5. reject duplicates for INSERT and duplicate UUIDs staged inside the same statement/bulk request.

Lookup, filters, updates, deletes

UUID public id filters are handled as string filters against the hidden @uuid_id attribute where public query semantics need string behavior, or resolved to internal numeric docids before existing write/delete internals need DocID_t.

The implementation intentionally uses existing exact string/filter/query machinery for persisted/disk data and does not add a new persistent UUID→docid storage format. Hot committed RT RAM data additionally maintains a per-segment @uuid_id → internal DocID_t lookup map alongside the existing DocID_t → rowid map. REPLACE, duplicate checks, and transaction commit duplicate probes can use this RAM map when the served RT index has only RAM segments. If disk chunks are attached, or a segment-level duplicate/corrupt UUID entry makes the map ambiguous, the code falls back to the ordinary query/filter lookup path.

When secondary indexes are enabled and built for disk chunks, public UUID equality/IN filters are rewritten before iterator planning so the hidden @uuid_id storage attribute can be selected by the secondary-index planner. The hidden backing index is an implementation detail: direct public @uuid_id access remains rejected, and user-facing index listings such as SHOW TABLE ... INDEXES do not expose @uuid_id. Runtime diagnostics may still show @uuid_id:SecondaryIndex in SHOW META because that is the actual internal iterator used for public id='uuid' filters.

When secondary indexes are disabled globally (searchd.secondary_indexes = 0), the same public UUID operations still resolve through the ordinary string filter path; they may be slower, but they are semantically supported.

Result rendering

Changed result-formatting paths map @uuid_id back to public id:

  • SQL column names and values;
  • JSON search hits and _source/docvalue handling;
  • ES-compatible responses;
  • joins, facets, sorting/grouping, and aggregate keys involving public id.

The internal numeric id remains hidden on UUID-ID tables.

Distributed and replication

Distributed agent result paths send the hidden UUID string when the master needs public id output.

Shard tables reject id uuid in this branch. UUID shard routing and write buffering need a separate design because shard routing must know the final placement key before the target RT table can resolve the hidden numeric DocID_t.

Replication tests cover create/insert/update/replace/delete behavior through a cluster table.

Columnar/MCL

Columnar RT tables are supported for UUID primary IDs. UUID public IDs use the existing hidden string attribute path (@uuid_id) and the normal internal numeric DocID_t surrogate, so the minimal implementation does not require Columnar/MCL storage-format changes. This avoids adding a native UUID or 128-bit type to MCL.

The supported columnar contract is the same public UUID-ID contract as row-wise RT tables: explicit UUID inserts, generated UUID inserts where applicable, equality/IN lookup by public id, result rendering as UUID strings, and delete/update/replace paths that first resolve the public UUID to the internal numeric docid. Operations that are generally limited by columnar storage rules, such as updating a columnar stored string attribute, remain governed by those existing columnar limitations; the UUID primary-id layer does not add a new MCL UUID type.

Performance and memory considerations

  • Internal DocID_t stays 64-bit, so existing row, kill-list, and docstore memory layouts are unchanged.
  • Each UUID-ID row stores one additional exact string (@uuid_id).
  • Per-statement duplicate detection uses sph::StringSet, avoiding O(N²) linear duplicate checks for large multi-row inserts/bulk batches.
  • The branch does not add a persistent or table-wide UUID→docid storage format. It does add a committed-RT-RAM per-segment @uuid_id → internal DocID_t map, built with the existing RAM segment rowid map and populated from either row-wise string blobs or columnar string iterators. This map accelerates hot REPLACE/duplicate/existence checks without widening DocID_t or adding an MCL UUID type.
  • Explicit UUID inserts in transactions and /json/bulk defer committed-data duplicate detection to commit and then check the accumulated UUID list in one batch. Transaction-local UUID state is hash-backed, avoiding O(N²) vector scans inside large explicit UUID batches.
  • ID-based operations that start from a public UUID string (SELECT ... WHERE id='uuid', UPDATE, REPLACE, and DELETE) are expected to be slower than the same operations on numeric IDs because they must resolve @uuid_id back to the internal numeric DocID_t. With secondary indexes enabled and rebuilt for disk chunks, exact public UUID equality/IN filters should use the hidden @uuid_id secondary index and avoid table-size-dependent full scans. Local 1k/10k/50k lookup measurements showed UUID miss median slope improving from 4.09x before the hidden-index eligibility fix to 1.41x after it; at 50k rows, UUID miss median improved from 1.812 ms to 0.642 ms.
  • Local repeated explicit-UUID /json/bulk benchmarks over 30 × 1000-row commits showed the RAM map removing the committed-row-count slope for hot RT RAM data: final fast path median was 1.1007s / 27.3k docs/s for 30k rows, versus 16.0630s / 1.87k docs/s with only the two RAM UUID lookup call sites temporarily disabled. In the side-by-side Manticore-vs-Elasticsearch harness, explicit UUID bulk insert reached about 23.6k docs/s for Manticore versus about 84.1k docs/s for Elasticsearch, narrowing the old after-SI gap from 22.24x to 3.57x in the main run. Disk/flushed chunks still use the query/SI-capable fallback path unless they get an equivalent committed UUID lookup path later.
  • Shard-table UUID insert/replace performance is out of scope for this branch because id uuid is rejected for type='shard' tables.

Limitations / non-goals

  • No native SPH_ATTR_UUID type.
  • No widening of DocID_t or internal row IDs.
  • No UUID IDs for plain/indexer-created tables, PQ tables, or shard tables.
  • No ALTER TABLE conversion to/from UUID IDs.
  • No numeric/range/arithmetic semantics for UUID id.
  • No direct public access to @uuid_id.
  • Generated UUIDs are for document identifiers, not cryptographic secrets.

Known current follow-up gaps

Exploratory QA on this branch confirmed the following remaining UUID-related gaps. These are status/envelope classification issues unless noted otherwise: the branch rejects the bad request and does not intentionally write data, but the HTTP status or error wrapper does not yet match the desired client-validation contract.

  1. POST /sql?mode=raw with a UUID id range predicate, for example WHERE id > 'aaaaaaaa-aaaa-4aaa-8aaa-aaaaaaaa0001', currently returns HTTP 500 with uuid id range filters are not supported; it should be HTTP 400.
  2. POST /json/search with a UUID id range predicate currently returns HTTP 500 with the same validation error; it should be HTTP 400.
  3. POST /json/update with an invalid UUID string in top-level id currently returns HTTP 409/action-request-validation; it should be HTTP 400 parse/request validation.
  4. POST /json/insert with a numeric top-level id into a UUID-ID table currently returns HTTP 409/action-request-validation; it should be HTTP 400 because UUID IDs must be strings.
  5. POST /json/replace with an invalid UUID string in top-level id currently returns HTTP 409/action-request-validation; it should be HTTP 400.
  6. POST /json/bulk with an invalid UUID action id currently returns top-level HTTP 500 and per-item 409; it should not be a server error. Prefer either top-level HTTP 400 or the endpoint's established bulk-envelope behavior with per-item 400.
  7. ES-compatible POST /_bulk keeps the pre-UUID handler's grouped transactions and error-envelope behavior. Standardizing independent per-item commits, conflict responses, or top-level versus item status codes is separate main-branch work and is not part of UUID-ID support.

The likely minimal fix area for these gaps is HTTP error classification/envelope handling around searchdhttp.cpp and the JSON/ES bulk adapters, not the UUID storage model.

Test coverage in this branch

New tests:

  • test/test_517/test.xml — RT UUID document IDs across SQL, JSON, ES-compatible bulk/search, generated IDs, validation errors, duplicate handling, REPLACE, UPDATE, DELETE, flush-to-disk, columnar RT, joins, distributed agent path, facets/aggs, and numeric-ID regression checks.
  • test/test_518/test.xml — replicated RT UUID document IDs through cluster create/join/insert/update/replace/delete convergence.
  • test/test_519/test.xml — focused UUID-ID regression with searchd.secondary_indexes = 0, covering row-wise UUID mutation/lookup paths, columnar RT lookup before/after FLUSH RAMCHUNK, columnar delete by UUID, and JSON lookup by public UUID id.

Focused verification should include:

bash
# from /Users/sn/manticore_uuid/build
cmake --build . --target searchd indexer indextool

# from /Users/sn/manticore_uuid/test
php ubertest.php gen -s /Users/sn/manticore_uuid/build/src/searchd -i /Users/sn/manticore_uuid/build/src/indexer test_517
php ubertest.php t   -s /Users/sn/manticore_uuid/build/src/searchd -i /Users/sn/manticore_uuid/build/src/indexer test_517
php ubertest.php gen -s /Users/sn/manticore_uuid/build/src/searchd -i /Users/sn/manticore_uuid/build/src/indexer test_518
php ubertest.php t   -s /Users/sn/manticore_uuid/build/src/searchd -i /Users/sn/manticore_uuid/build/src/indexer test_518
php ubertest.php gen -s /Users/sn/manticore_uuid/build/src/searchd -i /Users/sn/manticore_uuid/build/src/indexer test_519
php ubertest.php t   -s /Users/sn/manticore_uuid/build/src/searchd -i /Users/sn/manticore_uuid/build/src/indexer test_519

If the local build/test harness uses a different build directory, run the equivalent existing configured target and the same test directories.