Migrating from Elasticsearch

If you are coming from Elasticsearch or OpenSearch, most search features map onto SereneDB's inverted index and plain SQL. This page maps the concepts side by side; each Elasticsearch feature links to its reference. The biggest shift is that search and analytics are both just SQL — you filter with @@ and aggregate with GROUP BY in the same query, against the same database that holds your relational data.

Key differences

Aspect	Elasticsearch	SereneDB
Query language	Query DSL (JSON)	SQL — @@, ORDER BY, GROUP BY
Data model	Documents in indices	Rows in tables, with an inverted index beside the columnar data
Schema & types	Dynamic field mappings, JSON types	Typed SQL columns; per-column operator classes pick how each is indexed
Search types	Separate field types — text, dense_vector, geo_*	One inverted index spans full-text, vector and geospatial
Analyzer config	Analyzers, tokenizers, token filters	Text search dictionaries — templates + pipeline
Relevance	BM25 by default	BM25 by default, plus other scorers
Aggregations + hits	One request, two result trees	One SQL query — filter + GROUP BY / window
Transactions	None across documents	Full ACID
Joins	Limited (nested / parent-child)	Native SQL joins
Deployment	Distributed JVM cluster	Single binary, PostgreSQL wire protocol

Migration tips

• Model each Elasticsearch index as a table plus an inverted index; map field mappings to operator classes.
• Replace analyzer definitions with text search dictionaries.
• Replace the Query DSL with WHERE … @@ … for matching and ORDER BY <scorer> for relevance; see Full-Text Search and Ranking.

Query capabilities

Elasticsearch	SereneDB	Notes
match	✅	col @@ 'terms' (Full-Text Search)
match_phrase / proximity	✅	ts_phrase, ## operator
prefix / wildcard / regexp	✅	ts_starts_with, ts_like, ts_regexp
fuzzy	✅	ts_levenshtein; plus ts_ngram n-gram similarity (no ES equivalent)
bool	✅	&& || !!, ts_compound
term / terms	✅	verbatim columns; ts_any / ts_all
range	✅	ts_between, ts_lt/le/gt/ge
query_string	✅	to_tsquery, websearch_to_tsquery
more_like_this	⚠️	No direct function; use the minhash scalar function or vector similarity

Function mapping

The detailed mapping from each Elasticsearch query to the specific SereneDB function — the left column links to the Elasticsearch reference, the right to the SereneDB function reference:

Elasticsearch query	SereneDB function
match	a bare string, or ts_tokenize
match (operator: and)	plainto_tsquery
match_phrase	ts_phrase / phraseto_tsquery
match_phrase (slop)	ts_phrase with slop := N or ::slop(N)
prefix	ts_starts_with
wildcard	ts_like
regexp	ts_regexp
fuzzy	ts_levenshtein
(no ES equivalent)	ts_ngram — n-gram similarity
term / terms	a verbatim token, ts_any / ts_all
terms_set	ts_any with min_match
range	ts_between, ts_lt / ts_le / ts_gt / ts_ge
exists	Plain SQL IS NOT NULL / IS NULL — the index claims both on indexed columns.
bool	ts_compound, or && / || / !!
query_string	to_tsquery
simple_query_string	websearch_to_tsquery
boost (^)	^ operator
_score (BM25)	BM25 and other scorers
highlighting	ts_highlight, ts_offsets
kNN	<-> / <=> / <#> + ORDER BY … LIMIT
geo queries	ST_Intersects, ST_Contains, ST_Distance_*
analyzer test	ts_lexize

Text analysis

Elasticsearch	SereneDB	Notes
Tokenizers	✅	split_text, generate_ngrams, split_text_csv, split_text_icu, … templates
Token filters (lowercase / stemming / stopwords)	✅	split_text template options + stem_words / remove_stopwords templates
Accent folding	✅	accent = false
n-gram / edge n-gram	✅	generate_ngrams (mode = 'only_prefix' for edge n-grams), generate_sparse_ngrams
Shingles	✅	generate_shingles wraps another template and emits word n-grams
Synonyms	✅	expand_solr_synonyms, expand_wordnet_synonyms
Custom analyzers	✅	compose templates with pipeline
Separate search analyzer	✅	Symmetric by default; override per query with ts_tokenize(text, 'dict') or 'text'::tokenize('dict')

Scoring and relevance

Elasticsearch	SereneDB	Notes
BM25	✅	BM25(idx.tableoid) (Ranking)
Other scorers	✅	TFIDF, lm_jm, lm_dirichlet, dfi, more
boosting / boost	✅	^ operator (Relevance Tuning)
function_score	✅	compose the scorer in SQL arithmetic, e.g. BM25(...) * 2
Top-K acceleration	✅	optimize_top_k (WAND)
Reciprocal Rank Fusion	✅	Hybrid Search + RRF

Highlighting

Elasticsearch	SereneDB	Notes
Snippets / fragments	✅	ts_highlight
Custom tags	✅	StartSel / StopSel
Match offsets	✅	ts_offsets

Suggesters

Elasticsearch suggesters read a field's terms to complete and correct what a user types. SereneDB serves the same from the inverted index dictionary with the ts_dict_* aggregates, so there is no separate suggester structure to build or keep in sync. You query the dictionary directly and rank however you like.

Elasticsearch	SereneDB	Notes
completion suggester	✅	prefix match over the dictionary, ranked by document frequency (Autocomplete)
term suggester	✅	ts_levenshtein over the dictionary, ranked by ts_dict_score (Spell Correction)
phrase suggester	⚠️	correct each term with ts_levenshtein; no whole-phrase language model (Spell Correction)
context suggester	✅	autocomplete with a WHERE filter on another indexed column

Vector and geospatial

Elasticsearch	SereneDB	Notes
Dense vector / kNN (IVF)	✅	Vector Search
Geo queries	✅	Geospatial Search (ST_*)

Aggregations

This is where SereneDB's SQL model shines: every Elasticsearch aggregation maps to a SQL construct, run in the same query as the search filter. Elasticsearch's three families map as follows.

Bucket aggregations

Bucket aggregations group documents into buckets — SereneDB's GROUP BY clause:

Elasticsearch	SereneDB
terms / multi_terms	GROUP BY col (one or more columns)
histogram	GROUP BY width_bucket(col, …)
date_histogram	GROUP BY date_trunc('month', col)
range / date_range / ip_range	GROUP BY CASE …
filter	WHERE …
filters	count(*) with a FILTER clause per branch
missing	WHERE col IS NULL
rare_terms	GROUP BY col with HAVING count(*) <= n
nested	UNNEST / LATERAL

Metric aggregations

Metric aggregations compute a value over each bucket — SereneDB's aggregate functions:

Elasticsearch	SereneDB
avg / sum / min / max / value_count	avg / sum / min / max / count
stats / extended_stats	those together (+ stddev / variance)
cardinality	approx_count_distinct(col) (or count(DISTINCT col))
percentiles	quantile_cont(col, p)
percentile_ranks	count(*) FILTER (WHERE col <= v) / count(*)
weighted_avg	sum(w*x) / sum(w)
top_hits	DISTINCT ON / windowed row_number()

Pipeline aggregations

Pipeline aggregations post-process the output of other aggregations — SereneDB's window functions:

Elasticsearch	SereneDB
cumulative_sum	sum(x) OVER (ORDER BY …)
derivative / serial_diff	x - lag(x) OVER (…)
moving_fn	avg(x) OVER (… ROWS BETWEEN …)
bucket_script	arithmetic over aggregated columns
bucket_selector	HAVING
bucket_sort	ORDER BY … LIMIT

Aggregates run over the inverted index itself — GROUP BY and aggregate functions over indexed (and INCLUDEd) columns are answered without materializing the base table. Add columns you frequently aggregate to the index. On a keyword column a terms aggregation goes further and reads the term dictionary directly, so facet counts cost a dictionary walk rather than a scan; see Faceted Search. The query below filters with @@ and buckets the matches by category in one statement — the Elasticsearch equivalent of a terms aggregation inside a query:

Query

SELECT category, count(*) AS nFROM catalog_docs_idxWHERE body @@ 'galaxy'GROUP BY categoryORDER BY category;

Result

 category | n----------+--- guide    | 1 sci-fi   | 2

Index management and operations

Elasticsearch	SereneDB	Notes
Create / delete index	✅	CREATE INDEX … USING inverted / DROP INDEX
Reindex	✅	DROP INDEX + CREATE INDEX
Refresh	✅	VACUUM (REFRESH_TABLE) (Maintenance)
Force merge	✅	VACUUM (COMPACT_TABLE)
Aliases	✅	Use a view
Pagination (from/size, search_after)	✅	LIMIT / OFFSET, keyset pagination

See also

• Inverted Index · Text Analysis · Full-Text Search · Ranking
• Vector Search · Hybrid Search · Geospatial Search
• Search cookbook