reindexer

Reindexer

Reindexer is an embeddable, in-memory, document-oriented database with a high-level Query builder interface.

Reindexer's goal is to provide fast search with complex queries. We at Restream weren't happy with Elasticsearch and created Reindexer as a more performant alternative.

The core is written in C++ and the application level API is in Go.

This document describes the Go connector and its API. For information about the Reindexer server and HTTP API, refer to the Reindexer documentation.

Table of contents:

• Features
  • Performance
  • Memory Consumption
  • Full text search
  • Vector indexes (ANN/KNN)
  • Hybrid search
  • Disk Storage
  • Replication
  • Sharding
• Usage
  • SQL compatible interface
• Installation
  • Installation for server mode
    • Official docker image
  • Installation for embedded mode
    • Prerequisites
    • Get Reindexer using go.mod
    • Get Reindexer using go.mod and replace
    • Get Reindexer for apps without go.mod (vendoring)
    • Get Reindexer using go.mod (vendoring)
• Advanced Usage
  • Index Types and Their Capabilities
    • NULL-values filtration
  • Nested Structs
  • Sort
    • Forced sort
  • Functions
    • flat_array_len(field_name)
    • now(unit)
  • Counting
  • Text pattern search with LIKE condition
  • Update queries
    • Updating a missing indexed field
    • Update queries with inner joins and subqueries
    • Update field with object
    • Remove field via update-query
    • Update array elements by indexes
    • Concatenate arrays
    • Remove array elements by values
  • Delete queries
  • Truncate queries
  • Transactions and batch update
    • Synchronous mode
    • Async batch mode
    • Transactions commit strategies
    • Implementation notes
  • Merge
    • Sorting
    • Deduplication
  • Join
    • Nested Join
    • Anti-join
    • Joinable interface
  • Subqueries (nested queries)
  • Complex Primary Keys and Composite Indexes
  • Aggregations
  • Search in array fields
    • Search in array fields with matching indexes
    • Search in array fields with matching indexes using grouping
      • Query Execution Examples
    • Grouped values extraction examples for more complex cases
  • Atomic on update functions
  • Expire Data from Namespace by Setting TTL
  • Direct JSON operations
    • Upsert data in JSON format
    • Get Query results in JSON format
  • Using object cache
    • DeepCopy interface
    • Get shared objects from object cache (USE WITH CAUTION)
    • Limit size of object cache
    • Geometry
• Events subscription
• Logging, debug, profiling and tracing
  • Turn on logger
  • Configure server logging via REST API
  • Slow actions logging
  • Debug queries
  • Custom allocators support
  • Profiling
    • Heap profiling
    • CPU profiling
    • Known profiling issues
  • Tracing
• Integration with other programming languages
  • Reindexer for python
  • Reindexer for java
    • Spring wrapper
  • 3rd party open source connectors
    • PHP
    • Rust
    • .NET
• Limitations and known issues
• Getting help
• References

Features

Key features:

• Sortable indices
• Aggregation queries
• Indices on array fields
• Complex primary keys
• Composite indices
• Join operations
• Full-text search
• Up to 256 indexes (255 user's index + 1 internal index) for each namespace
• ORM-like query interface
• SQL queries

Performance

Performance has been our top priority from the start, and we think we managed to get it pretty good. Benchmarks show that Reindexer's performance is on par with a typical key-value database. On a single CPU core, we get:

• up to 500K queries/sec for queries SELECT * FROM items WHERE id='?'
• up to 50K queries/sec for queries SELECT * FROM items WHERE year > 2010 AND name = 'string' AND id IN (....)
• up to 20K queries/sec for queries SELECT * FROM items WHERE year > 2010 AND name = 'string' JOIN subitems ON ...

See benchmarking results and more details in benchmarking repo

Memory Consumption

Reindexer aims to consume as little memory as possible; most queries are processed without any memory allocation at all.

To achieve that, several optimizations are employed, both on the C++ and Go level:

• Documents and indices are stored in dense binary C++ structs, so they don't impose any load on Go's garbage collector.

• String duplicates are merged.

• Memory overhead is about 32 bytes per document + ≈4-16 bytes per each search index.

• There is an object cache on the Go level for deserialized documents produced after query execution. Future queries use pre-deserialized documents, which cuts repeated deserialization and allocation costs

• The Query interface uses sync.Pool for reusing internal structures and buffers. The combination of these technologies allows Reindexer to handle most queries without any allocations.

Full text search

Reindexer has internal full text search engine. Full text search usage documentation and examples are here

Vector indexes (ANN/KNN)

Reindexer has internal k-nearest neighbors search engine. k-nearest neighbors search usage documentation and examples are here. For selective post-filtered pages on HNSW, see streaming KNN (omit k and radius, use LIMIT/OFFSET on the query).

Hybrid search

Reindexer has internal hybrid full text and k-nearest neighbors search engine. Its usage documentation and examples are here

Disk Storage

Reindexer can store documents to and load documents from disk via LevelDB. Documents are written to the storage backend asynchronously by large batches automatically in background.

When a namespace is created, all its documents are stored into RAM, so the queries on these documents run entirely in in-memory mode.

Replication

Reindexer supports synchronous and asynchronous replication. Check replication documentation here

Sharding

Reindexer has some basic support for sharding. Check sharding documentation here

Usage

Here is complete example of basic Reindexer usage:

package main

// Import package
import (
	"fmt"
	"math/rand"

	"github.com/restream/reindexer/v5"
	// choose how the Reindexer binds to the app (in this case "builtin," which means link Reindexer as a static library)
	_ "github.com/restream/reindexer/v5/bindings/builtin"

	// OR use Reindexer as standalone server and connect to it via TCP or unix domain socket (if available).
	// _ "github.com/restream/reindexer/v5/bindings/cproto"

	// OR link Reindexer as static library with bundled server.
	// _ "github.com/restream/reindexer/v5/bindings/builtinserver"
	// "github.com/restream/reindexer/v5/bindings/builtinserver/config"

)

// Define struct with reindex tags. Fields must be exported - private fields cannot be written into Reindexer
type Item struct {
	ID       int64  `reindex:"id,,pk"`    // 'id' is primary key
	Name     string `reindex:"name"`      // add index by 'name' field
	Articles []int  `reindex:"articles"`  // add index by articles 'articles' array
	Year     int    `reindex:"year,tree"` // add sortable index by 'year' field
}

func main() {
	// Init a database instance and choose the binding (builtin)
	db, err := reindexer.NewReindex("builtin:///tmp/reindex/testdb")

	// OR - Init a database instance and choose the binding (connect to server via TCP sockets)
	// Database should be created explicitly via reindexer_tool or via WithCreateDBIfMissing option:
	// If server security mode is enabled, then username and password are mandatory
	// db, err := reindexer.NewReindex("cproto://user:[email protected]:6534/testdb", reindexer.WithCreateDBIfMissing())

	// OR - Init a database instance and choose the binding (connect to server via TCP sockets with TLS support
	// using cprotos-protocol and a package tls from the standard GO library)
	// Database should be created explicitly via reindexer_tool or via WithCreateDBIfMissing option:
	// If server security mode is enabled, then username and password are mandatory
	// It is assumed that reindexer RPC-server with TLS support is running at this address. 6535 is the default port for it.
	// tlsConfig := tls.Config{
	// 	/*required options*/
	// }
	// db, err := reindexer.NewReindex("cprotos://user:[email protected]:6535/testdb", reindexer.WithCreateDBIfMissing(), reindexer.WithTLSConfig(&tlsConfig))

	// OR - Init a database instance and choose the binding (connect to server via unix domain sockets)
	// Unix domain sockets are available on the unix systems only (socket file has to be explicitly set on the server's side with '--urpcaddr' option)
	// Database should be created explicitly via reindexer_tool or via WithCreateDBIfMissing option:
	// If server security mode is enabled, then username and password are mandatory
	// db, err := reindexer.NewReindex("ucproto://user:pass@/tmp/reindexer.socket:/testdb", reindexer.WithCreateDBIfMissing())

	// OR - Init a database instance and choose the binding (builtin, with bundled server)
	// serverConfig := config.DefaultServerConfig ()
	// If server security mode is enabled, then username and password are mandatory
	// db, err := reindexer.NewReindex("builtinserver://user:pass@testdb",reindexer.WithServerConfig(100*time.Second, serverConfig))

	// Check the error after rx instance init
	if err != nil {
		panic(err)
	}

	// Create new namespace with name 'items', which will store structs of type 'Item'
	db.OpenNamespace("items", reindexer.DefaultNamespaceOptions(), Item{})

	// Generate dataset
	for i := 0; i < 100000; i++ {
		err := db.Upsert("items", &Item{
			ID:       int64(i),
			Name:     "Vasya",
			Articles: []int{rand.Int() % 100, rand.Int() % 100},
			Year:     2000 + rand.Int()%50,
		})
		if err != nil {
			panic(err)
		}
	}

	// Query a single document
	elem, found := db.Query("items").
		Where("id", reindexer.EQ, 40).
		Get()

	if found {
		item := elem.(*Item)
		fmt.Println("Found document:", *item)
	}

	// Query multiple documents
	query := db.Query("items").
		Sort("year", false).                          // Sort results by 'year' field in ascending order
		WhereString("name", reindexer.EQ, "Vasya").   // 'name' must be 'Vasya'
		WhereInt("year", reindexer.GT, 2020).         // 'year' must be greater than 2020
		WhereInt("articles", reindexer.SET, 6, 1, 8). // 'articles' must contain one of [6,1,8]
		Limit(10).                                    // Return maximum 10 documents
		Offset(0).                                    // from 0 position
		ReqTotal()                                    // Calculate the total count of matching documents

	// Execute the query and return an iterator
	iterator := query.Exec()
	// Iterator must be closed
	defer iterator.Close()

	fmt.Println("Found", iterator.TotalCount(), "total documents, first", iterator.Count(), "documents:")

	// Iterate over results
	for iterator.Next() {
		// Get the next document and cast it to a pointer
		elem := iterator.Object().(*Item)
		fmt.Println(*elem)
	}
	// Check the error
	if err = iterator.Error(); err != nil {
		panic(err)
	}
}

There are also some basic samples for C++ and Go here

SQL compatible interface

As an alternative to the Query builder, Reindexer provides SQL-compatible query interface. Here is a sample of SQL interface usage:

    ...
	iterator := db.ExecSQL ("SELECT * FROM items WHERE name='Vasya' AND year > 2020 AND articles IN (6,1,8) ORDER BY year LIMIT 10")
    ...

Please note, that the Query Builder interface is preferred: it has more features and is faster than the SQL interface

String literals should be enclosed in single quotes.

Composite indexes should be enclosed in double quotes.

SELECT * FROM items WHERE "field1+field2" = 'Vasya'

If the field name does not start with alpha, '_' or '#' it must be enclosed in double quotes, examples:

UPDATE items DROP "123"

SELECT * FROM ns WHERE "123" = 'some_value'

SELECT * FROM ns WHERE "123abc" = 123

DELETE FROM ns WHERE "123abc123" = 111

Simple Joins may be done via default SQL syntax:

SELECT * FROM ns INNER JOIN ns2 ON ns2.id = ns.fk_id WHERE a > 0

Joins with condition on the left namespace must use subquery-like syntax:

SELECT * FROM ns WHERE a > 0 AND INNER JOIN (SELECT * FROM ns2 WHERE b > 10 AND c = 1) ON ns2.id = ns.fk_id

Subquery can also be a part of the WHERE-condition:

SELECT * FROM ns WHERE (SELECT * FROM ns2 WHERE id < 10 LIMIT 0) IS NOT NULL

SELECT * FROM ns WHERE id = (SELECT id FROM ns2 WHERE id < 10)

SELECT * FROM ns WHERE (SELECT COUNT(*) FROM ns2 WHERE id < 10) > 18

Installation

Reindexer can run in 3 different modes:

• embedded (builtin) Reindexer is embedded into application as static library, and does not require separate server process.
• embedded with server (builtinserver) Reindexer is embedded into the application as a static library and starts a server. In this mode, other clients can connect to the application via cproto, cprotos, ucproto, http, or https.
• standalone Reindexer runs as a standalone server; the application connects to Reindexer via network or Unix domain sockets.

Installation for server mode

In this mode Reindexer's Go-binding does not depend on reindexer's static library.

1. Install Reindexer Server
2. go get -a github.com/restream/reindexer/v5

Official docker image

The simplest way to get Reindexer server is to pull and run the Docker image from Docker Hub.

docker run -p9088:9088 -p6534:6534 -it reindexer/reindexer

Dockerfile

Installation for embedded mode

Prerequisites

Reindexer's core is written in C++20 and uses LevelDB as the storage backend, so CMake, a C++20 toolchain, and LevelDB must be installed before installing Reindexer. Also, OpenMP and BLAS/LAPACK libraries may be required for full vector index support.

To build Reindexer, g++ 10+, clang 15+, or mingw64 is required.

In embedded mode, Reindexer's Go binding depends on Reindexer's static libraries (core, server, and resource).

Get Reindexer using go.mod

This way is recommended and will fit for the most scenarios.

Go modules with go.mod do not allow to build C++ libraries in modules' directories. Go-binding will use pkg-config to detect libraries' directories.

Reindexer's libraries must be either installed from sources or from prebuilt package via package manager.

Then get the module:

go get -a github.com/restream/reindexer/v5

Get Reindexer using go.mod and replace

If you need modified Reindexer's sources, you can use replace like that.

1. Download and build reindexer:

# Clone reindexer via git. It's also possible to use 'go get -a github.com/restream/reindexer/v5', but its behavior may vary depending on Go's version
git clone https://github.com/restream/reindexer.git $GOPATH/src/reindexer
bash $GOPATH/src/reindexer/dependencies.sh
# Generate builtin binding
cd $GOPATH/src/reindexer
go generate ./bindings/builtin
# Optional (build builtin server binding)
go generate ./bindings/builtinserver

2. Add Reindexer's module to your application's go.mod and replace it with the local package:

# Go to your app's directory
cd /your/app/path
go get -a github.com/restream/reindexer/v5
go mod edit -replace github.com/restream/reindexer/v5=$GOPATH/src/reindexer

In this case, Go-binding will generate explicit libraries' and paths' list and will not use pkg-config.

Get Reindexer for apps without go.mod (vendoring)

If you're not using go.mod it's possible to get and build reindexer from sources this way:

export GO111MODULE=off # Disable go1.11 modules
# Go to your app's directory
cd /your/app/path
# Clone reindexer via git. It's also possible to use 'go get -a github.com/restream/reindexer', but its behavior may vary depending on Go's version
git clone https://github.com/restream/reindexer.git vendor/github.com/restream/reindexer/v5
# Generate builtin binding
go generate -x ./vendor/github.com/restream/reindexer/v5/bindings/builtin
# Optional (build builtin server binding)
go generate -x ./vendor/github.com/restream/reindexer/v5/bindings/builtinserver

Get Reindexer using go.mod (vendoring)

Go does not support proper vendoring for CGO code (https://github.com/golang/go/issues/26366), however, it's possible to use vend to copy Reindexer's sources into vendor-directory.

With vend you'll be able to call go generate -mod=vendor for builtin and builtinserver, placed in your vendor-directory.

It's also possible to simply copy Reindexer's sources into your project using git clone.

In these cases all the dependencies from Reindexer's go.mod must be installed manually with proper versions.

Advanced Usage

Index Types and Their Capabilities

Internally, structs are split into two parts:

• indexed fields, marked with reindex struct tag
• tuple of non-indexed fields

Queries are possible only on the indexed fields, marked with reindex tag. The reindex tag contains the index name, type, and additional options:

reindex:"<name>[[,<type>],<opts>]"

• name - index name.
• type - index type:
  • hash - fast select by EQ and SET match. Used by default. Allows slow and inefficient sorting by field.
  • tree - fast select by RANGE, GT, and LT matches. A bit slower for EQ and SET matches than hash index. Allows fast sorting results by field.
  • text - full-text search index. Details of full-text search are described here
  • - - column index. Can't perform fast select because it's implemented with a full-scan technique. Has the smallest memory overhead.
  • ttl - TTL index that works only with int64 fields. These indexes are quite convenient for representation of date fields (stored as UNIX timestamps) that expire after specified amount of seconds.
  • rtree - available only DWITHIN match. Acceptable only for [2]float64 (or reindexer.Point) field type. For details see geometry subsection.
• opts - additional index options:
  • pk - field is part of a primary key. Struct must have at least 1 field tagged with pk
  • composite – create composite index. The field type must be an empty struct: struct{}.
  • joined – field is a recipient for join. The field type must be []*SubitemType.
  • dense - reduce index size. For hash and tree it will save 8-16 bytes per unique key value. For - it will save 4-16 bytes per each element. Useful for indexes with high selectivity, but for tree and hash indexes with low selectivity can seriously decrease update performance. Also dense will slow down wide fullscan queries on - indexes, due to lack of CPU cache optimization. For the text index does not reduce index' size, but switches index' rebuild to the single-thread mode.
  • sparse - Row (document) contains a value of Sparse index only in case if it's set on purpose - there are no empty (or default) records of this type of indexes in the row (document). It allows to save RAM, but it will cost you performance - it works a bit slower than regular indexes.
  • collate_numeric - create string index that provides values order in numeric sequence. The field type must be a string.
  • collate_ascii - create a case-insensitive string index that works with ASCII. The field type must be a string.
  • collate_utf8 - create a case-insensitive string index that works with UTF-8. The field type must be a string.
  • collate_custom=<ORDER> - create custom order string index. The field type must be a string. <ORDER> is sequence of letters, which defines sort order.
  • linear, quadratic, greene or rstar - specify algorithm for construction of rtree index (by default rstar). For details see geometry subsection.
  • uuid - store this value as UUID. This is much more effective from the RAM/network consumption standpoint for UUIDs than strings. Only hash and - index types are supported for UUIDs. Can be used with any UUID variant, except variant 0

Fields with regular indexes are not nullable. Condition is NULL is supported only by sparse and array indexes.

NULL-values filtration

Conditions IS NULL/IS NOT NULL may have different behavior depending on target index type. In the tables below you can find results of IS NULL for different values/indexes/jsonpaths (IS NOT NULL will have the opposite value).

Shortcuts:

• + - document will be in the results;
• - - document will not be in the results;
• n - document is not valid.

Non-nested fields. Index has json_paths: ["f"] or json_paths: ["obj"] depending on context:

Documents	Non-indexed	Indexed array	Sparse	Sparse array
{}	+	+	+	+
{"f": null}	+	+	+	+
{"f": []}	+	+	+	+
{"f": [null]}	+	-	+	+
{"f": [1, null]}	+	-	+	+
{"f": 0}	-	-	-	-
{"f": [0]}	-	-	-	-
{"obj": {}}	-	n	n	n
{"obj": {"f": null}}	-	n	n	n
{"obj": {"f": null, "a": 0}}	-	n	n	n

Non-nested fields. Index has json_paths: ["f1", "f2"] or json_paths: ["obj1", "obj2"] depending on context:

Documents	Array with multiple jsonpaths
{}, {}	+
{"f1": 1}, {}	-
{"f1": null}, {"f2": null}	+
{"f1": 1}, {"f2": null}	-
{"f1": []}, {"f2": []}	+
{"f1": [1]}, {"f2": []}	-
{"f1": [null]}, {"f2": [null]}	-
{"f1": [1, null]}, {"f2": [1, null]}	-
{"f1": 0}, {"f2": 0}	-
{"f1": [0]}, {"f2": [0]}	-
{"obj1": {}}, {"obj2": {}}	n
{"obj1": {"f": null}}, {"obj2": {"f": null}}	n
{"obj1": {"f": null, "a": 0}}, {"obj2": {"f": null, "a": 0}}	n

Nested fields. Index has json_paths: ["obj.f"]:

Documents	Indexed array	Sparse	Sparse array
{"obj": {}}	+	+	+
{"obj": {"f": null}}	+	+	+
{"obj": {"f": null, "a": 0}}	+	+	+
{"obj": {"f": [null]}}	-	n	+
{"obj": {"f": [1, null]}}	-	n	+

Nested fields. Index has json_paths: ["obj1.f", "obj2.f"]:

Documents	Array with multiple jsonpaths
{"obj1": {}}, {"obj2": {}}	+
{"obj1": {"f": 1}}, {"obj2": {}}	-
{"obj1": {"f": null}}, {"obj2": {"f": null}}	+
{"obj1": {"f": 1}}, {"obj2": {"f": null}}	-
{"obj1": {"f": null, "a": 0}}, {"obj2": {"f": null, "a": 0}}	+
{"obj1": {"f": 1, "a": 0}}, {"obj2": {"f": null, "a": 0}}	-
{"obj1": {"f": [null]}}, {"obj2": {"f": [null]}}	-
{"obj1": {"f": [1, null]}}, {"obj2": {"f": [1, null]}}	-

Nested Structs

By default, Reindexer scans all nested structs and adds their fields to the namespace (as well as indexes specified). During index tags scanning, private (non-exported) fields, fields tagged with reindex:"-", and fields tagged with json:"-" will be skipped.

type Actor struct {
	Name string `reindex:"actor_name"`
	Age  int    `reindex:"age"`
}

type BaseItem struct {
	ID        int64  `reindex:"id,hash,pk"`
	UUIDValue string `reindex:"uuid_value,hash,uuid"`
}

type ComplexItem struct {
	BaseItem              // Index fields of BaseItem will be added to reindex
	Actor         []Actor // Index fields ("name" and "age") of Actor will be added to reindex as array-indexes
	Name          string  `reindex:"name"`      // Hash-index for "name"
	Year          int     `reindex:"year,tree"` // Tree-index for "year"
	Value         int     `reindex:"value,-"`   // Store(column)-index for "value"
	Metainfo      int     `json:"-"`            // Field "MetaInfo" will not be stored in reindexer
	Parent        *Item   `reindex:"-"`         // Index fields of "Parent" will NOT be added to reindex and all of the "Parent" exported content will be stored as non-indexed data
	ParentHidden  *Item   `json:"-"`            // Indexes and fields of "ParentHidden" will NOT be added to reindexer
	privateParent *Item                         // Indexes and fields of "ParentHidden" will NOT be added to reindexer (same as with `json:"-"`)
	AnotherActor  Actor   `reindex:"actor"`     // Index fields of "AnotherActor" will be added to reindexer with prefix "actor." (in this example two indexes will be created: "actor.actor_name" and "actor.age")
}

Sort

Reindexer can sort documents by fields (including nested and fields of the joined namespaces) or by expressions in ascending or descending order.

To sort by non-indexed fields, all the values must be convertible to each other, i.e. either have the same types or be one of the numeric types (bool, int, int64, or float).

Sort expressions can contain:

• field and index names (including nested fields and fields of the joined namespaces) of bool, int, int64, float, or string types. All the values must be convertible to numbers ignoring leading and trailing spaces;
• numbers;
• functions rank(), abs(), ST_Distance() and merged_query_id();
• parenthesis;
• arithmetic operations: +, - (unary and binary), * and /.

If a field name is followed by +, it must be separated by a space to distinguish a composite index name. Fields of the joined namespaces must be written like this: joined_namespace.field.

Abs() means absolute value of an argument.

Rank() means fulltext or KNN rank of match and is applicable only in fulltext and KNN queries.

ST_Distance() means distance between geometry points (see geometry subsection). The points could be columns in current or joined namespaces or fixed point in format ST_GeomFromText('point(1 -3)')

hash() or hash(seed) returns a hash (uint32) from the record id. If seed is not specified, it is generated randomly. This function is useful to achieve randomized sort order.

merged_query_id() returns the index of the current query in a MERGE. The main query and every joined query are 0. Each query added with MERGE is 1, 2, 3, ... in that order. A query without MERGE returns 0.

In SQL query sort expression must be quoted.

type Person struct {
	Name string `reindex:"name"`
	Age  int    `reindex:"age"`
}

type City struct {
	Id                 int               `reindex:"id"`
	NumberOfPopulation int               `reindex:"population"`
	Center             reindexer.Point `reindex:"center,rtree,linear"`
}

type Actor struct {
	ID          int               `reindex:"id"`
	PersonData  Person            `reindex:"person"`
	Price       int               `reindex:"price"`
	Description string            `reindex:"description,text"`
	BirthPlace  int               `reindex:"birth_place_id"`
	Location    reindexer.Point `reindex:"location,rtree,greene"`
}
....

query := db.Query("actors").Sort("id", true)           // Sort by field
....
query = db.Query("actors").Sort("person.age", true)   // Sort by nested field
....
// Sort by joined field
// Works for inner join only, when each item from left namespace has exactly one joined item from right namespace
query = db.Query("actors").
	InnerJoin(db.Query("cities")).On("birth_place_id", reindexer.EQ, "id").
	Sort("cities.population", true)
....
// Sort by expression:
query = db.Query("actors").Sort("person.age / -10 + price / 1000 * (id - 5)", true)
....
query = db.Query("actors").Where("description", reindexer.EQ, "ququ").
    Sort("rank() + id / 100", true)   // Sort with rank
....
// Sort by geometry distance
query = db.Query("actors").
    Join(db.Query("cities")).On("birth_place_id", reindexer.EQ, "id").
    SortStPointDistance(cities.center, reindexer.Point{1.0, -3.0}, true).
    SortStFieldDistance("location", "cities.center", true)
....
// Random sorting with stable seed:
query = db.Query("actors").Sort("hash(123123)", true)
....
// In SQL query:
iterator := db.ExecSQL ("SELECT * FROM actors ORDER BY person.name ASC")
....
iterator := db.ExecSQL ("SELECT * FROM actors WHERE description = 'ququ' ORDER BY 'rank() + id / 100' DESC")
....
iterator := db.ExecSQL ("SELECT * FROM actors ORDER BY 'ST_Distance(location, ST_GeomFromText(\'point(1 -3)\'))' ASC")
....
iterator := db.ExecSQL ("SELECT * FROM actors ORDER BY 'ST_Distance(location, cities.center)' ASC")
....
iterator := db.ExecSQL ("SELECT * FROM actors ORDER BY 'hash(123123)'")

It is also possible to set a custom sort order like this

type SortModeCustomItem struct {
	ID      int    `reindex:"id,,pk"`
	InsItem string `reindex:"item_custom,hash,collate_custom=a-zA-Z0-9"`
}

or like this

type SortModeCustomItem struct {
	ID      int    `reindex:"id,,pk"`
	InsItem string `reindex:"item_custom,hash,collate_custom=АаБбВвГгДдЕеЖжЗзИиКкЛлМмНнОоПпРрСсТтУуФфХхЦцЧчШшЩщЪъЫыЬьЭ-ЯAaBbCcDdEeFfGgHhIiJjKkLlMmNnOoPpQqRrSsTtUuVvWwXxYyZz0-9ЁёЙйэ-я"`
}

The very first character in this list has the highest priority, priority of the last character is the smallest one. It means that sorting algorithm will put items that start with the first character before others. If some characters are skipped their priorities would have their usual values (according to characters in the list).

Forced sort

It's possible to force sorting order by pulling some specific keys on the top of the selection results (works with ASC sort only):

query := db.Query("actors").Sort("id", false, 10, 15, 17) // Documents with IDs 10, 15, 17 will be on the top (if corresponding documents exist)

Sorting, tie-break and deduplication of merged queries are described in Merge.

Counting

Reindexer supports two versions of counting aggregation:

• count() - this aggregation counts all the documents that match the query's filters;
• count_cached() - this allows Reindexer to use cached count results. Generally this method is faster (especially for queries with low limit() values); however, it may return outdated results if the cache was not invalidated after some documents' changes.

Go example for count():

query := db.Query("items").
		WhereString("name", reindexer.EQ, "Vasya").
		Limit(10).
		ReqTotal()
it := query.MustExec()
it.TotalCount() // Get total count of items, matching condition "WhereString("name", reindexer.EQ, "Vasya")"

Go example for count_cached():

query := db.Query("items").
		WhereString("name", reindexer.EQ, "Vasya").
		Limit(10).
		CachedTotal()
it := query.MustExec()
it.TotalCount() // Get total count of items, matching condition "WhereString("name", reindexer.EQ, "Vasya"), using queries cache"

If you need to get total count field in JSON representation via Go-API, you have to set name for this field (otherwise it won't be serialized to JSON):

query := db.Query("items").
		WhereString("name", reindexer.EQ, "Vasya").
		Limit(10).
		ReqTotal("my_total_count_name")
json, err := query.ExecToJson().FetchAll()

Text pattern search with LIKE condition

For simple searching text pattern in string fields condition LIKE can be used. It looks for strings matching the pattern. In the pattern _ means any char and % means any sequence of chars.

Go example:

	query := db.Query("items").
		Where("field", reindexer.LIKE, "pattern")

SQL example:

SELECT * FROM items WHERE fields LIKE 'pattern'

'me_t' corresponds to 'meet', 'meat', 'melt' and so on '%tion' corresponds to 'tion', 'condition', 'creation' and so on

CAUTION: condition LIKE uses scan method. It can be used for debug purposes or within queries with another good selective conditions.

Generally for full text search with reasonable speed we recommend to use fulltext index.

Functions

Reindexer provides built-in functions that can be used within WHERE clauses to enable advanced filtering capabilities beyond simple field comparisons.

flat_array_len(field_name)

The flat_array_len function returns the length or cardinality of a specified field, making it particularly useful for filtering based on array sizes or field presence. The flat_array_len function can be used in both SELECT and UPDATE queries.

Behavior by Field Type:

• Array Fields: returns the number of elements in the array
• Scalar Fields (integers, strings, etc.): always returns 1
• Object Fields: always returns 1
• Nested Array Elements: returns the count of occurrences when the field is nested within arrays

Examples:

-- Find documents with exactly 3 tags
SELECT * FROM items WHERE flat_array_len(tags) = 3

-- Find documents with more than 5 comments
SELECT * FROM articles WHERE flat_array_len(comments) > 5

-- Find documents with at least one phone number
SELECT * FROM users WHERE flat_array_len(phone_numbers) >= 1

-- Filter objects nested in arrays (returns count of occurrences)
SELECT * FROM orders WHERE flat_array_len(items.product_id) = 2

-- Update field value with flat_array_len function
UPDATE items SET size = flat_array_len(comments) where id = 100;

// Find social media posts with between 10 and 50 comments
// and at least 3 attached media files
query := DB.Query("posts").
    WhereFlatArrayLen("comment", reindexer.GE, 10).
    WhereFlatArrayLen("comment", reindexer.LE, 50).
    WhereFlatArrayLen("media", reindexer.GE, 3)

// Execute query
it := query.Exec()

// Update field 'size' with flat_array_len function
res, err := DB.Query("items").Where("id", reindexer.EQ, 1).SetExpression("size", "flat_array_len(comments)").Update().FetchAll()

Notes:

• flat_array_len function operates efficiently on indexed fields
• Returns 0 if the specified field does not exist in a document
• Supports the following comparison operators: (=, >, >=, <, <=, Range, Set)
• Can be used in both SELECT and UPDATE queries

now(unit)

The now() function returns the current system timestamp, making it particularly useful for time-based filtering and data synchronization. This function can be used in both SELECT and UPDATE queries.

Arguments:

• sec - returns timestamp in seconds (default if no argument is provided)
• msec - returns timestamp in milliseconds
• usec - returns timestamp in microseconds
• nsec - returns timestamp in nanoseconds

Examples:

-- Find documents with exact current timestamp
SELECT * FROM logs WHERE timestamp = now(sec)

-- Filter documents with timestamps in the future (milliseconds)
SELECT * FROM schedules WHERE start_time > now(msec)

-- Update a document with current timestamp in nanoseconds
UPDATE items SET last_modified = now(nsec) WHERE id = 100

-- Set both creation time and modification time
UPDATE users SET created_at = now(sec), updated_at = now(sec) WHERE status = 'new'

// Find events that occurred in the past
query := DB.Query("events").
    WhereExpressions(reindexer.Field{Name: "timestamp"}, reindexer.LE, reindexer.Now{TimeUnit: reindexer.Sec})

// Update document with current microsecond timestamp
res, err := DB.Query("items").
    Where("id", reindexer.EQ, 42).
    SetExpression("updated_at", "now(usec)").
    Update().
    FetchAll()

// Insert document with serial ID and current timestamp
item := Item{Name: "test"}
DB.Insert("items", &item, "id=serial()", "created_at=now(msec)")

Notes:

• The returned timestamp represents seconds (or subunits) since the Unix epoch (January 1, 1970)
• Time resolution depends on the specified unit - use nsec for maximum precision
• All instances of Now() within a single query share the same value, which is computed at the start of the query execution
• Useful for implementing TTL (Time-To-Live) functionality and audit logging

Update queries

UPDATE queries are used to modify existing items of a namespace. There are several kinds of update queries: updating existing fields, adding new fields and dropping existing non-indexed fields.

General UPDATE SQL-Syntax:

UPDATE nsName
SET field1 = value1, field2 = value2, ..
WHERE condition;

It is also possible to use arithmetic expressions with +, -, /, * and brackets

UPDATE NS SET field1 = field2+field3-(field4+5)/2

including functions like now(), sec() and serial(). To use expressions from Golang code SetExpression() method needs to be called instead of Set().

To make an array-field empty

UPDATE NS SET arrayfield = [] WHERE id = 100

and set it to null

UPDATE NS SET field = NULL WHERE id > 100

In case of non-indexed fields, setting its value to a value of a different type will replace it completely; in case of indexed fields, it is only possible to convert it from adjacent type (integral types and bool), numeric strings (like "123456") to integral types and back. Setting indexed field to null resets it to a default value.

It is possible to add new fields to existing items

UPDATE NS SET newField = 'Brand new!' WHERE id > 100

and even add a new field by a complex nested path like this

UPDATE NS SET nested.nested2.nested3.nested4.newField = 'new nested field!' WHERE id > 100

will create the following nested objects: nested, nested2, nested3, nested4 and newField as a member of object nested4.

Example of using Update queries in golang code:

db.Query("items").Where("id", reindexer.EQ, 40).Set("field1", value1).Set("field2", value2).Update()

• there can be multiple .Set expressions - one for a field. Also, it is possible to combine several Set... expressions of different types in one query, like this: Set().SetExpression().SetObject()...

Updating a missing indexed field

UPDATE can set an indexed field that was absent from a document. The field is added at the index's JSON path, appears in the updated document, and can be found through the index. When the namespace uses persistent storage, the new value is preserved after a restart.

For example, suppose index idx has json_paths: ["field"] and a document contains {"id": 0, "data": "12345"}:

UPDATE ns SET field = 10 WHERE id = 0
SELECT * FROM ns WHERE idx = 10

The updated document is {"id": 0, "data": "12345", "field": 10}, and the SELECT query returns it. If the index name differs from its JSON path, the SET clause can use either name or JSON path.

For an index with multiple JSON paths, specify the path to update instead of the index name. For example, if idx has json_paths: ["field1", "field2"], use SET field1 = 10 or SET field2 = 10. Updating idx by name is ambiguous and returns an error.

Update queries with inner joins and subqueries

Inner join and subquery may also be used as WHERE conditions. Inner joins don't actually join items, but are applied as filtering conditions.

SQL syntax:

UPDATE ns SET val = 100 WHERE INNER JOIN (SELECT * FROM join_ns WHERE id > 100) ON ns.id = join_ns.id
UPDATE ns SET val = 100 WHERE field = (SELECT id FROM subq_ns WHERE val = 200)

Go syntax:

db.Query(items).
	OpenBracket()
		q.InnerJoin(DB.Query(joinItems).Where("id", reindexer.GT, 100)).
		On("ID", reindexer.EQ, "ID")
	q.CloseBracket().
	Set("val", 100).Update()

DB.Query(items).
	Where("field", reindexer.EQ,
		DB.Query(subq_ns).Select("id").Where("val", reindexer.EQ, 200)
	).
	Set("val", 100).Update()

Update field with object

Reindexer enables to update and add object fields. Object can be set by either a struct, a map or a byte array (that is a JSON version of object representation).

type ClientData struct {
    Name          string `reindex:"name" json:"name"`
	Age           int    `reindex:"age" json:"age"`
	Address       int    `reindex:"year" json:"year"`
	Occupation    string `reindex:"occupation" json:"occupation"`
	TaxYear       int    `reindex:tax_year json:"tax_year"`
	TaxConsultant string `reindex:tax_consultant json:"tax_consultant"`
}
type Client struct {
	ID      int         `reindex:"id" json:"id"`
	Data    ClientData  `reindex:"client_data" json:"client_data"`
	...
}
clientData := updateClientData(clientId)
db.Query("clients").Where("id", reindexer.EQ, 100).SetObject("client_data", clientData).Update()

In this case, Map in golang can only work with string as a key. map[string]interface{} is a perfect choice.

Updating an object field via SQL statement:

UPDATE clients SET client_data = {"Name":"John Doe","Age":40,"Address":"Fifth Avenue, Manhattan","Occupation":"Bank Manager","TaxYear":1999,"TaxConsultant":"Jane Smith"} WHERE id = 100;

Remove field via update-query

UPDATE DROP removes fields or array elements from the items matched by the query. It changes item data, but does not remove index definitions or alter the namespace schema.

Complete fields can be dropped if they are non-indexed or belong to sparse indexes. Dropping a non-sparse indexed field is not supported.

SQL syntax:

UPDATE nsName
DROP field1, field2, ..
WHERE condition;

Nested JSON paths and array indexes are supported:

-- Remove a field
UPDATE items DROP obsolete WHERE id = 100;

-- Remove a nested field
UPDATE items DROP metadata.legacy WHERE id = 100;

-- Remove the element at the zero-based index 2
UPDATE items DROP tags[2] WHERE id = 100;

-- Remove all existing array elements, leaving an empty array
UPDATE items DROP tags[*] WHERE id = 100;

Paths are resolved independently for each matched item and for each array branch. A path may legitimately exist only in some branches of a heterogeneous array. Branches where the path is missing are skipped without an error, while the operation is applied to every node that was found. An array index outside the array bounds is also treated as a missing target and produces no change.

For example, the following query removes the first tags element from the first and third branches and skips the second branch:

{
  "items": [
    {"tags": [1, 2]},
    {"name": "item"},
    {"tags": [3, 4]}
  ]
}

UPDATE nsName DROP items[*].tags[0];

DSL syntax:

{
  "namespace": "items",
  "type": "update",
  "drop_fields": [
    "obsolete",
    "metadata.legacy",
    "tags[2]"
  ]
}

Go API:

db.Query("items").Where("id", reindexer.EQ, 40).Drop("field1").Drop("field2").Update()

Update array elements by indexes

Reindexer update mechanism enables to modify array fields: to modify a certain item of an existing array or even to replace an entire field.

To update an item subscription operator syntax is used:

UPDATE NS SET array[*].prices[0] = 9999 WHERE id = 5

where * means all items.

To update entire array the following is used:

UPDATE NS SET prices = [999, 1999, 2999] WHERE id = 9

any non-indexed field can be easily converted to array using this syntax.

Reindexer also allows to update items of object arrays:

UPDATE NS SET extra.objects[0] = {"Id":0,"Description":"Updated!"} WHERE id = 9

also like this

db.Query("clients").Where("id", reindexer.EQ, 100).SetObject("extra.objects[0]", updatedValue).Update()

Reindexer supports heterogeneous arrays:

UPDATE NS SET info = ["hi", "bro", 111, 2.71] WHERE id = 9

	q := DB.Query(ns).Where("id", reindexer.EQ, 1).Set("array", []interface{}{"whatsup", 777, "bro"})
	res, err := q.Update().FetchAll()

Index array-fields support values that can be converted to an index type only. When saved, such values may change precision due to conversion.

UPDATE NS SET prices_idx = [11, '2', 3]

An array on the right-hand side replaces the entire value selected on the left-hand side; it does not append elements. This also works for indexed array fields and nested arrays. For example, given idx = [[1, 2], [3, 4]], each of the following statements produces the shown value of idx (run each statement independently):

UPDATE statement	Resulting idx
UPDATE NS SET idx = [[5, 6], [7, 8]] WHERE id = 9	[[5, 6], [7, 8]]
UPDATE NS SET idx[0] = [5, 6] WHERE id = 9	[[5, 6], [3, 4]]
UPDATE NS SET idx[*] = [5, 6] WHERE id = 9	[[5, 6], [5, 6]]
UPDATE NS SET idx[0][0] = [5, 6] WHERE id = 9	[[[5, 6], 2], [3, 4]]
UPDATE NS SET idx[0][*] = [5, 6] WHERE id = 9	[[[5, 6], [5, 6]], [3, 4]]
UPDATE NS SET idx[*][0] = [5, 6] WHERE id = 9	[[[5, 6], 2], [[5, 6], 4]]
UPDATE NS SET idx[*][*] = [5, 6] WHERE id = 9	[[[5, 6], [5, 6]], [[5, 6], [5, 6]]]

[*] selects every existing element at that level and assigns the right-hand value to each one. It does not create new elements. The selected element can itself be replaced with an array, even if it previously held a scalar. Indexed array fields still require every resulting scalar value to be convertible to the index type.

The same syntax applies to arrays of objects and objects containing arrays. Given nested = [{"values": [1]}, {"values": [2]}]:

UPDATE NS SET nested[*].values = [3, 4] WHERE id = 9

This produces nested = [{"values": [3, 4]}, {"values": [3, 4]}]. To replace an entire object in the array, use a path such as nested[0] and an object value:

UPDATE NS SET nested[0] = {"values": [5, 6]} WHERE id = 9

For a point field, an array of two coordinates replaces the whole point, for example UPDATE NS SET location = [10.5, 20.5] WHERE id = 9. A float vector field likewise accepts a complete vector with the dimension configured for its index, such as UPDATE NS SET vec_idx = [0.1, 0.2, 0.3] WHERE id = 9. If the vector index is configured as an array, use an array of vectors to replace the whole field, or select one vector to replace:

UPDATE NS SET vec_idx = [[0.1, 0.2, 0.3], [0.4, 0.5, 0.6]] WHERE id = 9
UPDATE NS SET vec_idx[0] = [0.7, 0.8, 0.9] WHERE id = 9

Vector coordinates cannot be updated through nested array indexes such as vec_idx[0][0]; replace the vector instead.

Concatenate arrays

To add items to an existing array the following syntax is supported:

UPDATE NS SET integer_array = integer_array || [5,6,7,8]

and

UPDATE NS SET integer_array = [1,2,3,4,5] || integer_array

The first one adds elements to the end of integer_array, the second one adds 5 items to the front of it. To make this code work in Golang SetExpression() should be used instead of Set().

Remove array elements by values

To remove items by value into an existing array the following syntax is supported:

UPDATE NS SET integer_array = array_remove(integer_array, [5,6,7,8])

and

UPDATE NS SET integer_array = array_remove_once(integer_array, [5,6,7,8,6])

The first one removes all occurrences of the listed values in integer_array, the second one deletes only the first occurrence found. To make this code work in Golang SetExpression() should be used instead of Set(). If you need to remove one value, you can use square brackets [5] or simple value 5.

UPDATE NS SET integer_array = array_remove(integer_array, [5])

update ns set integer_array = array_remove(integer_array, 5)

Remove command can be combined with array concatenate:

UPDATE NS SET integer_array = array_remove_once(integer_array, [5,6,7,8]) || [1,2,3]

also like this

db.Query("main_ns").SetExpression("integer_array", "array_remove(integer_array, [5,6,7,8]) || [1,2,3]").Update()

It is possible to remove the values of the second field from the values of the first field. And also add new values etc. Note: The first parameter in commands is expected to be an array/field-array, the second parameter can be an array/scalar/field-array/field-scalar. For values compatibility/convertibility required

UPDATE NS SET integer_array = [3] || array_remove(integer_array, integer_array2) || integer_array3 || array_remove_once(integer_array, [8,1]) || [2,4]

UPDATE NS SET integer_array = array_remove(integer_array, integer_array2) || array_remove(integer_array, integer_array3) || array_remove_once(integer_array, [33,777])

db.Query("main_ns").SetExpression("integer_array", "[3] || array_remove(integer_array, integer_array2) || integer_array3 || array_remove(integer_array, [8,1]) || [2,4]").Update()

Delete queries

Reindexer also supports delete queries. Those queries may contain conditions, inner joins, and subqueries. Inner joins don't actually join items, but are applied as filtering conditions.

SQL syntax for delete queries:

DELETE FROM ns WHERE field = 111
DELETE FROM ns WHERE id < 5 OR INNER JOIN ns_join ON ns.id = ns_join.id
DELETE FROM ns WHERE id < 5 OR INNER JOIN (SELECT * FROM ns_join WHERE age > 18) ON ns.id = ns_join.id
DELETE FROM ns WHERE id = (SELECT id FROM sub_ns WHERE age < 13)

Go syntax for delete queries:

// Delete query with INNER JOIN
DB.Query(ns).Where("id", reindexer.EQ, 1).
	OpenBracket().
		InnerJoin(
			DB.Query(join_ns).Where("DEVICE", reindexer.EQ, "android"),
			"some random name").
		On("PRICE_ID", reindexer.SET, "ID")
	CloseBracket().
	Delete()

// Delete query with subquery
DB.Query(ns).
	Where("ID", reindexer.EQ,
		DB.Query(sub_ns).Select("id").Where("age", reindexer.LT, 13)
	).
	Delete()

Truncate queries

Truncate is more effective alternative to delete-query for cases, when you need to remove all the items from namespace without filtering:

TRUNCATE ns

Go syntax:

DB.TruncateNamespace(ns)

Unlike queries like delete * from ns, truncate also deallocates dynamic memory associated with indexing structures and does not bloat WAL with serialized primary keys of deleted items.

Transactions and batch update

Reindexer supports transactions. Transactions perform atomic namespace updates. There are synchronous and async transactions available. To start transaction method db.BeginTx() is used. This method creates transaction object, which provides usual Update/Upsert/Insert/Delete interface for application. For RPC clients there is transactions count limitation - each connection can't have more than 1024 open transactions at the same time.

Synchronous mode

	// Create new transaction object
	tx, err := db.BeginTx("items");
	if err != nil {
		panic(err)
	}
	// Fill transaction object
	tx.Upsert(&Item{ID: 100})
	tx.Upsert(&Item{ID: 101})
	tx.Query().WhereInt("id", reindexer.EQ, 102).Set("Name", "Petya").Update()
	// Apply transaction
	if err := tx.Commit(); err != nil {
		panic(err)
	}

Async batch mode

For speed up insertion of bulk records async mode can be used.

	// Create new transaction object
	tx, err := db.BeginTx("items");
	if err != nil {
		panic(err)
	}
	// Prepare transaction object async.
	tx.UpsertAsync(&Item{ID: 100},func(err error) {})
	tx.UpsertAsync(&Item{ID: 100},func(err error) {})
	// Wait for async operations done, and apply transaction.
	if err := tx.Commit(); err != nil {
		panic(err)
	}

The second argument of UpsertAsync is completion function, which will be called after receiving server response. Also, if any error occurred during prepare process, then tx.Commit should return an error. So it is enough, to check error returned by tx.Commit - to be sure, that all data has been successfully committed or not.

Transactions commit strategies

Depending on amount of changes in transaction there are 2 possible Commit strategies:

• Locked atomic update. Reindexer locks namespace and applying all changes under common lock. This mode is used with small amounts of changes.
• Copy & atomic replace. In this mode Reindexer makes namespace's snapshot, applying all changes to this snapshot, and atomically replaces namespace without lock

The amount of data for selecting a Commit strategy can be selected in the namespace configuration. Check fields StartCopyPolicyTxSize, CopyPolicyMultiplier and TxSizeToAlwaysCopy in struct DBNamespacesConfig(describer.go)

If transaction size is less than TxSizeToAlwaysCopy, reindexer uses extra heuristic and trying to avoid namespace copying, if there were no selecting queries seen for this namespace. In some cases this heuristic may increase selects latency, so it may be disabled by setting REINDEXER_NOTXHEURISTIC env variable to any non-empty value.

Implementation notes

1. Transaction object is not thread safe and can't be used from different goroutines;
2. Transaction object holds Reindexer's resources, therefore application should explicitly call Rollback or Commit, otherwise resources will leak;
3. It is safe to call Rollback after Commit;
4. It is possible to call Query from transaction by call tx.Query("ns").Exec() ...;
5. Only serializable isolation is available, i.e. each transaction takes exclusive lock over the target namespace until all the steps of the transaction committed.

Merge

MERGE appends the rows of one or more queries to the main query. The queries may read different namespaces. Limit and Offset belong to the main query and are applied to the merged result; an inner query cannot set its own limit or offset. Joins inside a merged query are described in Join. Aggregations of the main query apply to every merged subquery; see Aggregations.

SELECT * FROM books WHERE year > 2000
MERGE (SELECT * FROM archived_books WHERE year > 1990)

it := db.Query("books").
	Where("year", reindexer.GT, 2000).
	Merge(db.Query("archived_books").Where("year", reindexer.GT, 1990)).
	Exec()

Without Sort() / ORDER BY, rows of the main query come first, then each merged query in the order it was written. Within one query the smaller row id comes first.

Sorting

Sort() / ORDER BY belongs to the main query. ORDER BY inside MERGE (...) is rejected, except for a hybrid reranker (Hybrid search). The sort is applied in every merged namespace and then to the concatenated rows. The same field name is resolved separately in each namespace. A field missing from one of the namespaces is checked with that query's strict mode.

When the sort keys are equal, the tie-break follows the direction of the first entry. ASC puts the smaller row id and the earlier merged query first. DESC puts the larger row id and the later merged query first.

A joined field (joined_namespace.field) is resolved in each merged query's