≫ Adding documents to a table

Adding documents to a real-time table

If you're looking for information on adding documents to a plain table, please refer to the section on adding data from external storages.

Adding documents in real-time is supported only for Real-Time and percolate tables. The corresponding SQL command, HTTP endpoint, or client functions insert new rows (documents) into a table with the provided field values. It's not necessary for a table to exist before adding documents to it. If the table doesn't exist, Manticore will attempt to create it automatically. For more information, see Auto schema.

You can insert a single or multiple documents with values for all fields of the table or just a portion of them. In this case, the other fields will be filled with their default values (0 for scalar types, an empty string for text types).

Expressions are not currently supported in INSERT, so values must be explicitly specified.

The ID field/value can be omitted, as RT and PQ tables support auto-id functionality. For numeric-ID tables, you can also use 0 as the id value to force automatic ID generation. Rows with duplicate IDs will not be overwritten by INSERT. Instead, you can use REPLACE for that purpose.

For tables created with id uuid, pass an explicit UUID as a quoted string or omit id to generate one automatically. Explicit values must match xxxxxxxx-xxxx-Vxxx-Wxxx-xxxxxxxxxxxx, where each x is a hexadecimal digit, V is the version (1 through 8), and W is the variant (8, 9, a, or b). Uppercase hexadecimal letters are accepted and normalized to lowercase. Unlike numeric IDs, 0 does not request automatic UUID generation.

When using the HTTP JSON protocol, you have two different request formats to choose from: a common Manticore format and an Elasticsearch-like format. Both formats are demonstrated in the examples below.

Additionally, when using the Manticore JSON request format, keep in mind that the doc node is required, and all the values should be provided within it.

‹›
  • SQL
  • JSON
  • Elasticsearch
  • PHP
  • Python
  • Python-asyncio
  • Javascript
  • Java
  • C#
📋

General syntax:

INSERT INTO <table name> [(column, ...)]
VALUES (value, ...)
[, (...)]
INSERT INTO products(title,price) VALUES ('Crossbody Bag with Tassel', 19.85);
INSERT INTO products(title) VALUES ('Crossbody Bag with Tassel');
INSERT INTO products VALUES (0,'Yellow bag', 4.95);
‹›
Response
Query OK, 1 rows affected (0.00 sec)
Query OK, 1 rows affected (0.00 sec)
Query OK, 1 rows affected (0.00 sec)

Adding documents to replicated tables

When working with replicated tables, you must use a special syntax to ensure that write operations are properly propagated to all nodes in the cluster.

For all write operations (INSERT, REPLACE, DELETE, TRUNCATE, UPDATE) on replicated tables, you must:

  • In SQL: Use the cluster_name:table_name format instead of just the table name
  • In JSON: Include the cluster property along with the table property

If you don't use the correct syntax, the operation will fail with an error.

‹›
  • SQL
  • JSON
  • PHP
  • Python
  • Javascript
  • Java
  • C#
  • Rust
📋
INSERT INTO posts:weekly_table(title,price) VALUES ('Crossbody Bag with Tassel', 19.85);
INSERT INTO posts:weekly_table VALUES (0,'Yellow bag', 4.95);
‹›
Response
Query OK, 1 rows affected (0.00 sec)
Query OK, 1 rows affected (0.00 sec)

Auto schema

NOTE: Auto schema requires Manticore Buddy. If it doesn't work, make sure Buddy is installed.

Manticore features an automatic table creation mechanism, which activates when a specified table in the insert or replace query doesn't yet exist. This mechanism is enabled by default. To disable it, set auto_schema = 0 in the Searchd section of your Manticore config file.

By default, all text values in the VALUES clause are considered to be of the text type, except for values representing valid email addresses, which are treated as the string type.

If you attempt to INSERT/REPLACE multiple rows with different, incompatible value types for the same field, auto table creation will be canceled, and an error message will be returned. However, if the different value types are compatible, the resulting field type will be the one that accommodates all the values. Some automatic data type conversions that may occur include:

  • mva -> mva64
  • uint -> bigint -> float (this may cause some precision loss)
  • string -> text

The auto schema mechanism does not support creating tables with vector fields (fields of type float_vector) used for KNN (K-Nearest Neighbors) similarity search. To use vector fields in your table, you must explicitly create the table with a schema that defines these fields. If you need to store vector data in a regular table without KNN search capability, you can store it as a JSON array using the standard JSON syntax, for example: INSERT INTO table_name (vector_field) VALUES ('[1.0, 2.0, 3.0]').

Also, the following formats of dates will be recognized and converted to timestamps while all other date formats will be treated as strings:

  • %Y-%m-%dT%H:%M:%E*S%Z
  • %Y-%m-%d'T'%H:%M:%S%Z
  • %Y-%m-%dT%H:%M:%E*S
  • %Y-%m-%dT%H:%M:%s
  • %Y-%m-%dT%H:%M
  • %Y-%m-%dT%H

Keep in mind that the /bulk HTTP endpoint does not support automatic table creation (auto schema). Only the /_bulk (Elasticsearch-like) endpoint and the SQL interface support this feature.

‹›
  • SQL
  • JSON
📋
MySQL [(none)]> drop table if exists t; insert into t(i,f,t,s,j,b,m,mb) values(123,1.2,'text here','test@mail.com','{"a": 123}',1099511627776,(1,2),(1099511627776,1099511627777)); desc t; select * from t;
‹›
Response
--------------
drop table if exists t
--------------
Query OK, 0 rows affected (0.42 sec)
--------------
insert into t(i,f,t,j,b,m,mb) values(123,1.2,'text here','{"a": 123}',1099511627776,(1,2),(1099511627776,1099511627777))
--------------
Query OK, 1 row affected (0.00 sec)
--------------
desc t
--------------
+-------+--------+----------------+
| Field | Type   | Properties     |
+-------+--------+----------------+
| id    | bigint |                |
| t     | text   | indexed stored |
| s     | string |                |
| j     | json   |                |
| i     | uint   |                |
| b     | bigint |                |
| f     | float  |                |
| m     | mva    |                |
| mb    | mva64  |                |
+-------+--------+----------------+
8 rows in set (0.00 sec)
--------------
select * from t
--------------
+---------------------+------+---------------+----------+------+-----------------------------+-----------+---------------+------------+
| id                  | i    | b             | f        | m    | mb                          | t         | s             | j          |
+---------------------+------+---------------+----------+------+-----------------------------+-----------+---------------+------------+
| 5045949922868723723 |  123 | 1099511627776 | 1.200000 | 1,2  | 1099511627776,1099511627777 | text here | test@mail.com | {"a": 123} |
+---------------------+------+---------------+----------+------+-----------------------------+-----------+---------------+------------+
1 row in set (0.00 sec)

Auto ID

Manticore provides automatic ID generation for documents inserted or replaced into a real-time or Percolate table. The generator produces a unique numeric value with the guarantees below, but it should not be considered an auto-incrementing sequence.

The generated ID value is guaranteed to be unique under the following conditions:

  • The server_id value of the current server is in the range of 0 to 127 and is unique among nodes in the cluster, or it uses the default value generated from the MAC address as a seed
  • The system time does not change for the Manticore node between server restarts
  • The average auto-ID generation rate between two server starts stays below about 16 million IDs per second

The auto ID generator creates a 64-bit integer with the following layout:

  • Bits 0 to 23 are a counter that is incremented on every call to the auto ID generator
  • Bits 24 to 55 store the server start time in seconds, encoded as (unix_timestamp_at_start - 2019-05-01 00:00:00 UTC)
  • Bits 56 to 62 store the server_id (the value is masked to the range 0..127)

This layout ensures that generated IDs are unique among cluster nodes and that data inserted into different cluster nodes does not create collisions. This is particularly important when working with replicated tables, as it guarantees that auto-generated IDs are unique across all nodes in the replication cluster.

Important: the 24-bit counter is not a hard limit on the total number of documents you can insert during one server run. You can insert more than 16,777,216 documents after startup; the IDs will still keep increasing and remain unique for that running process. The ~16 million IDs per second rule matters for uniqueness across restarts: after a restart, the time-based part must advance far enough so that newly generated IDs do not overlap with IDs created before the restart.

As a result, the first ID from the generator used for auto ID is NOT 1 but a larger number. Additionally, the document stream inserted into a table might have non-sequential ID values if inserts into other tables occur between calls, as the ID generator is singular in the server and shared between all its tables.

For numeric-ID tables, this integer is the public document ID. For UUID-ID tables, Manticore encodes it as a canonical UUIDv8 string; clients see only the UUID.

‹›
  • SQL
  • JSON
  • PHP
  • Python
  • Python-asyncio
  • Javascript
  • Java
  • C#
  • Rust
📋
INSERT INTO products(title,price) VALUES ('Crossbody Bag with Tassel', 19.85);
INSERT INTO products VALUES (0,'Yello bag', 4.95);
select * from products;
‹›
Response
+---------------------+-----------+---------------------------+
| id                  | price     | title                     |
+---------------------+-----------+---------------------------+
| 1657860156022587404 | 19.850000 | Crossbody Bag with Tassel |
| 1657860156022587405 |  4.950000 | Yello bag                 |
+---------------------+-----------+---------------------------+

UUID_SHORT multi-ID generation

CALL UUID_SHORT(N)

The CALL UUID_SHORT(N) statement allows for generating N unique 64-bit IDs in a single call without inserting any documents. It is particularly useful when you need to pre-generate IDs in Manticore for use in other systems or storage solutions. For example, you can generate auto-IDs in Manticore and then use them in another database, application, or workflow, ensuring consistent and unique identifiers across different environments.

‹›
  • Example
Example
📋
CALL UUID_SHORT(3)
‹›
Response
+---------------------+
| uuid_short()        |
+---------------------+
| 1227930988733973183 |
| 1227930988733973184 |
| 1227930988733973185 |
+---------------------+

Bulk adding documents

You can insert not just a single document into a real-time table, but as many as you'd like. It's perfectly fine to insert batches of tens of thousands of documents into a real-time table. However, it's important to keep the following points in mind:

  • The larger the batch, the higher the latency of each insert operation
  • The larger the batch, the higher the indexation speed you can expect
  • You might want to increase the max_packet_size value to allow for larger batches
  • Normally, each batch insert operation is considered a single transaction with atomicity guarantee, so you will either have all the new documents in the table at once or, in case of failure, none of them will be added. See more details about an empty line or switching to another table in the "JSON" example.

Note that the /bulk HTTP endpoint does not support automatic creation of tables (auto schema). Only the /_bulk (Elasticsearch-like) endpoint and the SQL interface support this feature. The /_bulk (Elasticsearch-like) HTTP endpoint allows the table name to include the cluster name in the format cluster_name:table_name.

/_bulk endpoint accepts document IDs in the same format as Elasticsearch, and you can also include the id within the document itself:

{ "index": { "table": "products", "_id": "1" } }
{ "title": "Crossbody Bag with Tassel", "price": 19.85 }

or

{ "index": { "table": "products" } }
{ "title": "Crossbody Bag with Tassel", "price": 19.85, "id": "1" }

For an RT table declared with id uuid, /bulk reads UUIDs from id. /_bulk reads them from metadata _id or the document id. Both endpoints can omit the ID to generate a UUID automatically.

Chunked transfer in /bulk

The /bulk (Manticore mode) endpoint supports Chunked transfer encoding. You can use it to transmit large batches. It:

  • reduces peak RAM usage, lowering the risk of OOM
  • decreases response time
  • allows you to bypass max_packet_size and transfer batches much larger than the maximum allowed value of max_packet_size (128MB), for example, 1GB at a time.

Bulk import

For faster large batch loads into a local real-time table, Manticore Search can write INSERT rows directly to a disk chunk and publish that chunk when the transaction commits. This avoids building the batch in a RAM chunk first. Rows remain invisible until the chunk is published, and a failed operation or ROLLBACK leaves the table unchanged.

It supports both row-wise and columnar tables, including full-text fields, numeric attributes, strings, JSON, MVA/MVA64, and float vectors with KNN indexes.

Table names are case-insensitive here. For example, SET bulk_import=Products and bulk_import=products both select the table stored as products; tables whose names differ only by letter case cannot be distinguished.

SQL

Enable the mode for the current SQL session, run one or more INSERT statements against the bound table, and commit:

SET bulk_import=products;
INSERT INTO products(id,title,price) VALUES
  (101,'Crossbody Bag with Tassel',19.85),
  (102,'Microfiber Sheet Set',19.99);
INSERT INTO products(id,title,price) VALUES
  (103,'Pet Hair Remover Glove',7.99);
COMMIT;
SET bulk_import=0;

Run SET bulk_import=<table> before BEGIN and before any uncommitted write on that connection. It reserves the selected table for that session: searches remain available, while another bulk import or an ordinary write to the same table is rejected until the reservation is released. BEGIN and START TRANSACTION are allowed but not required and have no effect in this mode.

COMMIT publishes the rows collected since the previous COMMIT or ROLLBACK as one disk chunk. ROLLBACK discards those rows. In both cases, bulk import remains enabled so you can start another batch. Run SET bulk_import=0 to discard any uncommitted rows, disable bulk import, and release this connection's reservation. Writes to the table resume after the reservation is released. Closing the connection does the same. Autocommit cannot be changed while bulk import is enabled.

HTTP /bulk

Add bulk_import=<table> to a Manticore /bulk request. The request body remains standard newline-delimited JSON (NDJSON), and each operation must be insert or create for the bound table:

printf '%s\n' \
  '{"insert":{"table":"products","id":101,"doc":{"title":"Crossbody Bag with Tassel","price":19.85}}}' \
  '{"insert":{"table":"products","id":102,"doc":{"title":"Microfiber Sheet Set","price":19.99}}}' |
curl -sS -X POST \
  -H 'Content-Type: application/x-ndjson' \
  --data-binary @- \
  'http://localhost:9308/bulk?bulk_import=products'

In a request body, an empty line ends and publishes the current batch; the end of the request publishes the final batch. Manticore sends the HTTP response after processing all batches in that request. If a later batch fails, batches published earlier in the same request remain searchable. To publish the entire request atomically as one disk chunk, do not include empty lines. The response contains one aggregate bulk result for each published batch rather than one result per document.

For a typical import, send the complete NDJSON body in one request as shown above. Close the HTTP connection when the import is complete to release the table for other writes.

The endpoint supports chunked transfer encoding, so it can process bodies larger than max_packet_size without buffering the whole request.

Elasticsearch /_bulk

The Elasticsearch-compatible /_bulk endpoint does not support direct-to-disk bulk_import; use SQL or Manticore /bulk.

Duplicate document IDs

Within one direct-to-disk batch, the first row for a numeric document ID remains visible and later rows with the same ID are logically removed. For SQL, a batch consists of the rows staged before COMMIT or ROLLBACK. For Manticore /bulk, each group published at an empty line, table change, or request EOF is a separate batch.

When Manticore publishes a disk chunk to the target table, a row in that chunk replaces any existing row with the same ID. This also applies to IDs published by an earlier HTTP batch. As a result, retrying a previously published batch replaces its rows, while duplicates within the retried batch still keep the first row. In a Manticore /bulk request, the create operation, for example:

{"create":{"table":"products",...}}

behaves like insert; it does not fail merely because the ID already exists.

Staging files and cleanup

Manticore removes the current staging directory after a normal COMMIT, ROLLBACK, mode disable, or session close. A daemon or host crash can leave an abandoned staging directory behind. A later direct-to-disk load creates a new uniquely named directory and does not reuse or attach files left by the crashed load.

Use the following query to inspect all direct-to-disk staging entries for a table:

SELECT file, normalized, size
FROM products.@files
OPTION format='bulk_import';

The result recursively lists files and directories below the table's direct-to-disk staging root. Directories and other non-regular entries have a reported size of 0.

After confirming that no direct-to-disk load is active for the table, remove its entire staging root with PURGE BULK_IMPORT:

PURGE BULK_IMPORT FROM TABLE products;

PURGE requires an existing local real-time table that is not in a replication cluster. It removes only direct-to-disk staging state and does not change the table schema or indexed rows. It succeeds as a no-op when the staging root is absent. In configless mode, DROP TABLE also removes the table's direct-to-disk staging root.

Current limitations

  • The target must be one existing, unfrozen local real-time table that is not a replication-cluster member. Distributed, sharded, replicated, percolate, and plain tables are not supported.
  • SQL supports only INSERT. Manticore /bulk accepts insert and create; index, replace, update, and delete are rejected.
  • Every row must provide an explicit numeric, non-zero document ID. Auto-generated and UUID document IDs are not supported.
  • Static builds are not supported.
  • The platform-specific executable (indexer on Linux, indexer.exe on Windows) must be in the same directory as the running searchd executable. Manticore Search resolves only that sibling path and does not search PATH. Use the executable from the same installation as searchd to ensure compatibility. If it is absent, unreadable, or cannot be started, only bulk import fails; normal startup and regular insertion remain available.

The assisted loader uses the csvpipe source. Its command defaults to /bin/cat on Linux and - on Windows, where - reads from the indexer's standard input. Set INDEXER_RT_BULK_CSV_PIPE_COMMAND in the searchd environment to override only this command. The override does not select another source or feed; the command must work with the platform's input feed and pass CSV rows to the indexer. An unsupported command causes the assisted load to fail.

‹›
  • SQL
  • JSON
  • Elasticsearch
  • PHP
  • Python
  • Python-asyncio
  • Javascript
  • Java
  • C#
  • Rust
📋

For bulk insert, simply provide more documents in brackets after VALUES(). The syntax is:

INSERT INTO <table name>[(column1, column2, ...)] VALUES(value1[, value2 , ...]), (...)

The optional column name list allows you to explicitly specify values for some of the columns present in the table. All other columns will be filled with their default values (0 for scalar types, empty string for string types).

For example:

INSERT INTO products(title,price) VALUES ('Crossbody Bag with Tassel', 19.85), ('microfiber sheet set', 19.99), ('Pet Hair Remover Glove', 7.99);
‹›
Response
Query OK, 3 rows affected (0.01 sec)

Expressions are currently not supported in INSERT, and values should be explicitly specified.

Inserting multi-value attributes (MVA) values

Multi-value attributes (MVA) are inserted as arrays of numbers.

‹›
  • SQL
  • JSON
  • Elasticsearch
  • PHP
  • Python
  • Python-asyncio
  • Javascript
  • Java
  • C#
  • Rust
📋
INSERT INTO products(title, sizes) VALUES('shoes', (40,41,42,43));

Inserting JSON

JSON value can be inserted as an escaped string (via SQL or JSON) or as a JSON object (via the JSON interface).

‹›
  • SQL
  • JSON
  • Elasticsearch
  • PHP
  • Python
  • Python-asyncio
  • Javascript
  • Java
  • C#
  • Rust
📋
INSERT INTO products VALUES (1, 'shoes', '{"size": 41, "color": "red"}');
Data creation and modification Adding rules to a percolate table