Creating a local table

There are 2 different approaches to deal with tables in Manticore:

Online schema management (RT mode)

Real-time mode requires no table definition in the configuration file, but presence of data_dir directive in searchd section is mandatory. Index files are stored inside the data_dir.

Replication is available only in this mode.

In this mode you can use SQL commands like CREATE TABLE, ALTER TABLE and DROP TABLE to create and change table schema and drop it. This mode is especially useful for real-time and percolate tables.

Table names are case insensitive in the RT mode.

Defining table schema in config (Plain mode)

In this mode you can specify table schema in config which will be read on Manticore start and if the table doesn’t exist yet it will be created. This mode is especially useful for plain tables that are built upon indexing data from an external storage.

Dropping tables is only possible by removing them from the configuration file or by removing the path setting and sending a HUP signal to the server or restarting it.

Table names are case sensitive in this mode.

All table types are supported in this mode.

Table types and modes

Real-time table

Real-time table is the main type of tables in Manticore. It allows adding, updating and deleting documents with immediate availability of the changes. Real-time table settings can be defined in a configuration file or online via CREATE/UPDATE/DELETE/ALTER commands.

Real-time table internally consists of one or multiple plain tables called chunks. There can be:

• multiple disk chunks. They are stored on disk with the same structure as any plain table
• single ram chunk. Stored in memory and used as an accumulator of changes

RAM chunk size is controlled by rt_mem_limit. Once the limit is exceeded the RAM chunk is flushed to disk in a form of a disk chunk. When there are too many disk chunks they can be merged into one for better performance using command OPTIMIZE.

• SQL
• JSON
• PHP
• Python
• Javascript
• Java
• CONFIG

SQL JSON PHP Python Javascript Java CONFIG

CREATE TABLE products(title text, price float) morphology='stem_en';

POST /cli -d "CREATE TABLE products(title text, price float)  morphology='stem_en'"

$index = new \Manticoresearch\Index($client);
$index->setName('products');
$index->create([
    'title'=>['type'=>'text'],
    'price'=>['type'=>'float'],
]);

utilsApi.sql('CREATE TABLE forum(title text, price float)')

res = await utilsApi.sql('CREATE TABLE forum(title text, price float)');

utilsApi.sql("CREATE TABLE forum(title text, price float)");

table products {
  type = rt
  path = tbl
  rt_field = title
  rt_attr_uint = price
  stored_fields = title
}

Response

Query OK, 0 rows affected (0.00 sec)

{
"total":0,
"error":"",
"warning":""
}

Creating a real-time table via JSON over HTTP:

👍 What you can do with a real-time table:

• Add documents
• Update attributes and full-text fields
• Delete documents
• Truncate table
• Change schema online with help of the command ALTER
• Define table in a configuration file
• Use UUID for automatic ID provisioning

⛔ What you cannot do with a real-time table:

• Index data with help of indexer
• Link it with sources for easy indexing from external storages
• Update it’s killlist_target, it’s just not needed as the real-time table takes controls of it automatically

Real-time table files structure

Plain table

Plain table is a basic element for non-percolate searching. It can be specified only in a configuration file in the Plain mode). It’s not supported in the RT mode). It’s normally used together with a source to process data from an external storage and afterwards can be attached to a real-time table.

👍 What you can do with a plain table:

• build it from an external storage with help of source and indexer
• do an in-place update of an integer, float, string and MVA attribute
• update it’s killlist_target

⛔ What you cannot do with a plain table:

• insert more data into a table after it’s built
• delete data from it
• create/delete/alter a plain table online (you need to define it in a configuration file)
• use UUID for automatic ID generation. When you fetch data from an external storage it must include a unique identifier for each document

Except numeric attributes (including MVA)), the rest of the data in a plain table is immutable. If you need to update/add new records you need to rebuild the table. While table is being rebuilt, existing table is still available for serving requests. When a new version of the table is ready, a process called rotation is performed which puts the new version online and discards the old one.

• Plain table example

Plain table example

A plain table can be only defined in a configuration file. It’s not supported by command CREATE TABLE

source source {
  type             = mysql
  sql_host         = localhost
  sql_user         = myuser
  sql_pass         = mypass
  sql_db           = mydb
  sql_query        = SELECT id, title, description, category_id  from mytable
  sql_attr_uint    = category_id
  sql_field_string = title
 }
table tbl {
  type   = plain
  source = source
  path   = /path/to/table
 }

Plain table building performance

Speed of plain indexing depends on several factors:

• how fast the source can be providing the data
• tokenization settings
• your hardware (CPU, amount of RAM, disk performance)

Plain table building scenarios

Rebuild fully when needed

In the simplest usage scenario, we would use a single plain table which we just fully rebuild from time to time. It works fine for smaller data sets and if you are ready that:

• the table will be not as fresh as data in the source
• indexing duration grows with the data, the more data you have in the source the longer it will take to build the table

Main+delta

If you have a bigger data set and still want to use a plain table rather than Real-Time what you can do is:

• make another smaller table for incremental indexing
• combine the both using a distributed table

What it can give is you can rebuild the bigger table seldom (say once per week), save the position of the freshest indexed document and after that use the smaller table to process anything new or updated from your source. Since you will only need to fetch the updates from your storage you can do it much more frequently (say once per minute or even each few seconds).

But after a while the smaller indexing duration will become too high and that will be the moment when you need to rebuild the bigger table and empty the smaller one.

This is called main+delta schema and you can learn more about it in this interactive course.

When you build a smaller “delta” table it can get documents that are already in the “main” table. To let Manticore know that documents from the current table should take precedence there’s a mechanism called kill list and corresponding directive killlist_target.

More information on this topic can be found here.

Plain table files structure