SQL Reference

Hotdata's native SQL is HotSQL — standard SQL with extensions for analytics, time-series, and complex types. If you know Postgres, you already know most of it. You can also write queries in PostgreSQL, DuckDB, or Snowflake syntax (see SQL dialects).

SQL dialects

Hotdata accepts queries in four SQL dialects — HotSQL (the native dialect), PostgreSQL, DuckDB, and Snowflake. Set the dialect field on the query request to write in a non-HotSQL dialect, and Hotdata translates it to HotSQL before running — so queries written for another engine run without a rewrite. Write statements follow the same rules in every dialect (see Writing data with SQL), and an unrecognized dialect is rejected.

{
  "sql": "SELECT IFF(amount > 100, 'big', 'small') AS bucket FROM orders",
  "dialect": "snowflake"
}

SELECT syntax

The core query form is:

SELECT ... FROM ... [ WHERE ... ] [ GROUP BY ... ] [ HAVING ... ] [ ORDER BY ... ] [ LIMIT ... ]

WITH (CTEs)

Use WITH for common table expressions:

WITH top_customers AS (
  SELECT customer_id, sum(amount) AS total
  FROM orders
  GROUP BY customer_id
  ORDER BY total DESC
  LIMIT 10
)
SELECT * FROM top_customers;

FROM clause

Reference tables by schema and name. Tables live in your instant databases. Use fully qualified names when needed: catalog.schema.table or schema.table.

WHERE clause

Filter rows with conditions. Supports AND, OR, NOT, comparison operators (=, !=, <, >, <=, >=), IN, BETWEEN, LIKE, IS NULL, IS NOT NULL, and subqueries with EXISTS, IN, ANY, ALL.

JOIN clause

Join tables with INNER, LEFT, RIGHT, FULL, CROSS joins. Use ON for join conditions:

SELECT o.id, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.created_at >= '2025-01-01';

GROUP BY and HAVING

Group rows and filter groups:

SELECT department, count(*) AS headcount, avg(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING count(*) > 5
ORDER BY avg_salary DESC;

ORDER BY and LIMIT

Sort and limit results:

SELECT * FROM events
ORDER BY created_at DESC
LIMIT 100;

ORDER BY supports ASC, DESC, and multiple columns. LIMIT can optionally include OFFSET for pagination.

UNION

Combine result sets with UNION (distinct) or UNION ALL (keeps duplicates):

SELECT id, name FROM users
UNION ALL
SELECT id, name FROM archived_users;

Subqueries

Subqueries are supported in SELECT, FROM, WHERE, and HAVING:

SELECT *
FROM orders
WHERE customer_id IN (SELECT id FROM customers WHERE tier = 'premium');

Data types

Standard SQL types are supported:

BINARY, VARBINARY, and BLOB are not accepted as cast targets. For binary data use BYTEA, or arrow_cast(x, 'Binary') and decode(x, 'hex').

Operators

Comparison

=, !=, <>, <, >, <=, >=, IS NULL, IS NOT NULL, BETWEEN ... AND ..., LIKE, IN (...)

Logical

AND, OR, NOT

Numerical

+, -, *, /, % (modulo)

Bitwise

&, |, ^ (bitwise XOR), <<, >>

There is no bitwise-NOT operator: ~ is not supported, and there is no bit_not function to stand in for it.

String

|| for concatenation

Functions

HotSQL includes a comprehensive function library. Each category has a dedicated reference with signatures, arguments, and runnable examples:

• Math
• String — text, regular-expression, and hashing functions
• Date & time
• Conditional & utility
• Aggregate
• Window
• Array, struct & map
• JSON — path access with -> and ->>
• Spatial

Aggregates: FILTER and WITHIN GROUP

Aggregates accept a FILTER (WHERE ...) clause, and ordered-set aggregates (percentile_cont, approx_percentile_cont) accept WITHIN GROUP (ORDER BY ...):

SELECT
  sum(amount) FILTER (WHERE status = 'completed')       AS completed_revenue,
  percentile_cont(0.5) WITHIN GROUP (ORDER BY latency)  AS median_latency
FROM orders;

Window functions

Any aggregate or window function runs over a frame with OVER (PARTITION BY ... ORDER BY ...):

SELECT id, amount,
  sum(amount) OVER (ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total,
  rank()      OVER (ORDER BY amount DESC)                                          AS amount_rank
FROM transactions;

Expanding arrays

unnest(array) expands an array into rows:

SELECT id, unnest(tags) AS tag FROM articles;

Geospatial functions

Hotdata includes spatial functions that follow PostGIS-style ST_ naming, for querying geometry data. Geometries are planar (Cartesian)—there is no geography type, and coordinates are always interpreted as x = easting/longitude. Buffering and overlay operations (ST_Buffer, ST_Union, ST_Intersection, ST_Difference, ST_MakeValid, ...) and coordinate reprojection (ST_Transform) are available. For geodesic measurements in metres on lon/lat data, use the _Spheroid family (ST_Area_Spheroid, ST_Distance_Spheroid, ST_DWithin_Spheroid, ...) or ST_DistanceSphere.

Constructing geometries

Create points and geometries from coordinates or text:

-- Create a 2D point
SELECT ST_MakePoint(-122.4194, 37.7749) AS sf_point;

-- Create from Well-Known Text (WKT)
SELECT ST_GeomFromText('POINT(-122.4194 37.7749)') AS point;
SELECT ST_GeomFromText('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))') AS polygon;

-- Create from GeoHash
SELECT ST_PointFromGeoHash('9q8yy') AS point;

Geometry accessors

Extract properties and coordinates:

SELECT
  ST_X(location) AS lon,
  ST_Y(location) AS lat,
  ST_Area(boundary) AS area_sq_units,
  ST_Length(path) AS path_length,
  ST_GeometryType(geom) AS geom_type
FROM locations;

Spatial relationships

Test how geometries relate to each other:

-- Find stores within 10 km of a point (ST_DistanceSphere returns metres)
SELECT id, name
FROM stores
WHERE ST_DistanceSphere(
  ST_GeomFromText('POINT(-122.4194 37.7749)'),
  location
) < 10000;

-- Points contained in a polygon (e.g. delivery zones)
SELECT order_id, ST_AsText(destination)
FROM orders
WHERE ST_Within(destination, delivery_zone);

-- Intersecting geometries (e.g. overlapping regions)
SELECT a.id, b.id
FROM regions a
JOIN regions b ON ST_Intersects(a.geom, b.geom) AND a.id < b.id;

ST_Distance and ST_DWithin are planar—distances in the coordinates' own units (degrees for lon/lat data). For metres on lon/lat data use ST_DistanceSphere (great-circle) or ST_Distance_Spheroid / ST_DWithin_Spheroid (WGS84 geodesic).

Geometry processing

Compute derived geometries:

-- Convex hull of a geometry
SELECT id, ST_ConvexHull(geom) AS hull
FROM regions;

-- Simplify a polygon (Douglas-Peucker)
SELECT ST_Simplify(complex_geom, 0.001) AS simplified
FROM boundaries;

-- Centroid of a geometry
SELECT ST_Centroid(polygon) AS center FROM parcels;

-- Buffer a point by 1 unit
SELECT ST_Buffer(location, 1.0) AS service_area FROM stores;

-- Merge overlapping regions
SELECT ST_Union(a.geom, b.geom)
FROM regions a JOIN regions b ON ST_Intersects(a.geom, b.geom);

Coordinate reprojection

ST_Transform reprojects between coordinate reference systems. The CRS travels in the call (there is no per-geometry SRID); coordinates are always x = easting/longitude:

-- lon/lat (EPSG:4326) to Web Mercator metres (EPSG:3857)
SELECT ST_Transform(location, 'EPSG:4326', 'EPSG:3857') AS mercator
FROM stores;

Hotdata's spatial functions use PostGIS-style ST_ names; alternate spellings from other SQL dialects are accepted as aliases where they differ (e.g. ST_Distance_Sphere for ST_DistanceSphere). See the Spatial functions reference for the full set of 117 functions, and the PostGIS documentation for the semantics of individual functions.

Vector search

Hotdata provides vector similarity search with two approaches: semantic search on text columns using an embedding provider, and direct vector search on pre-computed embedding columns. The search table functions — vector_search for text and vector_search_vector for a query vector — always use the column's HNSW index. They do not read an IVF index.

Semantic search with vector_distance

vector_distance(column, 'search text') is the simplest way to perform semantic search. It takes a text column and a search string, automatically generates an embedding using the column's configured embedding provider, and computes the distance using the metric defined in the vector index.

SELECT id, title, vector_distance(description, 'machine learning frameworks') AS dist
FROM conn.public.articles
ORDER BY dist ASC
LIMIT 10;

Requirements:

• A vector index on the column with an embedding provider configured
• The column must be a text column (VARCHAR / TEXT)

Behind the scenes, vector_distance resolves the embedding provider from the vector index metadata, calls it to embed your search text, and rewrites the query to the appropriate distance function (e.g. cosine_distance) on the generated embedding column. You don't need to know which distance metric or embedding column is used—the index configuration handles it.

After that rewrite it is an ordinary distance function, so whether the index is used depends on the conditions under distance functions. Use vector_search for a semantic search that always uses the index.

Distance functions

For direct control over vector search—when you already have embedding vectors or want to specify the exact distance metric—use the distance functions directly on vector columns. All are lower-is-closer; use ORDER BY ... ASC:

l2_distance returns the squared Euclidean distance, matching the metric the index is built on. Ranking is unaffected, but a threshold is: WHERE l2_distance(a, b) < 0.5 is a radius of 0.707. For the unsquared distance use array_distance.

All three return Float32, so compare against a tolerance rather than an exact value.

These functions accept a vector column and a literal query vector (ARRAY[...]):

SELECT id, title, cosine_distance(embedding, ARRAY[0.1, -0.2, 0.5, ...]) AS dist
FROM documents
ORDER BY dist ASC
LIMIT 10;

These functions scan every row by default. Where a vector index exists, HNSW or IVF, the planner can rewrite ORDER BY distance_fn(col, ARRAY[...]) ASC LIMIT k into an index lookup. With either algorithm, these shapes are not rewritten and run as an exact scan without a warning:

• The query vector is not a literal ARRAY[...]: a vector held in a column, a parameter, a subquery, or a CTE is not rewritten. A constant vector written with a cast, such as ARRAY[1, 2, 3]::FLOAT[], is rewritten when the distance call is the ORDER BY key or a SELECT item with an alias that the ORDER BY names.
• The indexed vector column is in the output, by name or through SELECT *. Select the distance and the other columns you need instead.
• ORDER BY ... DESC. An index answers nearest-first, so only ascending order is a match.
• The distance function does not match the index's metric, for example cosine_distance against an l2 index.

Missing the rewrite makes a query slower, never wrong. Check with EXPLAIN when it matters.

For a guaranteed HNSW index lookup, use vector_search_vector, or vector_search for text.

The table functions read HNSW indexes only. To search a column with an IVF index, use the distance functions.

vector_search table function

vector_search is a table function for semantic search that returns full rows. Like vector_distance, it auto-embeds your search text using the configured embedding provider — but returns results as a table you can query directly.

vector_search('catalog.schema.table', 'column', 'search text', k)

Returns the table's columns plus a _distance column (Float32). Lower distance means more similar.

Two details of the returned shape are worth knowing before you write SELECT * against it:

• The result carries an internal row-addressing column (rowid) ahead of the table's own columns. It is an implementation detail of the index, not part of your table — list the columns you want explicitly rather than relying on SELECT *.
• The indexed embedding column is not among the columns you can select. The vector itself is not kept in the row-fetch path, so naming it — SELECT description_embedding FROM vector_search(...) — fails with an unknown-field error. Select the source text column instead.

Requirements: A vector index on the column with an embedding provider configured.

-- Semantic search returning full rows
SELECT id, title, _distance
FROM vector_search('mydb.public.articles', 'description', 'machine learning', 10)
ORDER BY _distance ASC;

-- With filtering and aggregation
SELECT category, COUNT(*) AS cnt, AVG(_distance) AS avg_dist
FROM vector_search('mydb.public.articles', 'description', 'data science', 50)
WHERE category IN ('ml', 'statistics')
GROUP BY category
ORDER BY avg_dist;

vector_search_vector table function

vector_search_vector is the direct ANN search table function — you provide the query vector yourself. It requires a vector index but no embedding provider.

vector_search_vector('catalog.schema.table', 'column', ARRAY[...], k)

Returns all table columns plus a _distance column (Float32).

Requirements: A vector index on the column.

SELECT id, title, _distance
FROM vector_search_vector('mydb.public.documents', 'embedding', ARRAY[0.1, -0.2, 0.5, ...], 10)
ORDER BY _distance ASC;

Choosing between vector search methods

The two table functions are the ones that guarantee an index scan, on an HNSW index only; the scalar functions compute a distance and reach the index only when the query meets every rewrite condition (see Distance functions). Prefer a table function on a large table, and a scalar function when you want a distance in the output, are working over a small table, or have no index at all.

WHERE clause filtering

This describes queries that run through an HNSW index — the two search table functions, and a distance-function query the planner rewrote. For IVF, see IVF indexes. Scalar WHERE conditions are absorbed and applied adaptively at execution time:

The 5% cutoff is a fixed engine default (not currently configurable per table).

SELECT id, title, cosine_distance(embedding, ARRAY[...]) AS dist
FROM documents
WHERE category = 'technical'
ORDER BY dist ASC
LIMIT 10;

Behavior and limitations

• expansion_search (ef_search): the beam width during HNSW traversal, a fixed engine default of 64 (higher would improve recall at the cost of speed).
• brute_force_selectivity_threshold: the selectivity cutoff below which brute-force is used instead of HNSW—a fixed engine default of 0.05 (5%).
• SELECT * with distance functions: when the table has generated embedding columns, SELECT * expands to include them, so the query falls back to brute force (see distance functions for the shapes that are never rewritten).
• Rewriting is an optimization, not a guarantee: a query that misses a rewrite condition is slow, not wrong. Use vector_search / vector_search_vector when the index lookup is what you need.
• Stacked filters: Only one Filter → TableScan layer is absorbed.

Full-text search

Hotdata provides BM25 full-text search via the bm25_search table function. It performs ranked text retrieval against a column with a BM25 index.

bm25_search

bm25_search('catalog.schema.table', 'column', 'query text' [, limit])

Returns all columns from the base table plus a score column (Float32) containing the BM25 relevance score. Higher scores indicate stronger relevance. Rows are not returned in score order—add an explicit ORDER BY score DESC to rank them.

Requirements: A BM25 index on the target column.

-- Search articles for "machine learning", return top 20 by relevance
SELECT id, title, body, score
FROM bm25_search('mydb.public.articles', 'body', 'machine learning', 20)
ORDER BY score DESC;

-- Combine full-text search with additional filters
SELECT id, title, score
FROM bm25_search('conn.public.docs', 'content', 'kubernetes deployment', 100)
WHERE category = 'infrastructure'
ORDER BY score DESC
LIMIT 10;

Note: bm25_search retrieves its top-limit matches first, then any WHERE filters are applied to that set. When combining with filters, pass a higher limit to bm25_search than the final number of rows you need so relevant rows aren't filtered out of a too-small candidate set.

How text is analyzed

A BM25 index stores tokens, and a document is findable only by a token it produced. Text is split on every non-alphanumeric character, lowercased, and any token over 40 bytes is dropped. Queries are analyzed the same way, so the two always agree.

Nothing else is applied: no stemming (run misses running), no accent folding (cafe misses café), and no camel-case splitting (mixed misses MixedCaseWord).

Chinese, Japanese, Korean and Thai are not usable today. They have no spaces or punctuation to split on, so a whole run of text becomes one token and only the exact full string matches. If you need full-text search for these scripts, insert word boundaries yourself before loading and index that column.

Managed table layout

Instant database tables can declare a storage layout—how their rows are partitioned and sorted on disk—when the table is created. Unlike an index, the layout is intrinsic to how the table's data is written. It is fixed at table creation (via the managed table API), cannot be changed afterward, and is not set from SQL.

• Partitioning — one or more partition keys, each a column plus a transform: identity, year, month, day, or hour. Transforms compose, so partitioning by year(ts) and month(ts) places each calendar month in its own partition. A WHERE filter on a partition column lets the planner skip whole partitions.
• Sort order — one or more sort keys (a column plus optional asc / desc and null ordering). Rows are written in this order, so range filters on the sort keys can prune row groups.

Because the layout is applied at write time, it also organizes data for key-based mutations (upsert, update, delete) on partitioned tables. This is complementary to sorted indexes: a sorted index is a secondary, pre-sorted copy of cached data, whereas the declared layout governs the table's primary storage.

Indexes

Hotdata supports three index types to accelerate different query patterns. Indexes are created via the API or CLI—not SQL. Indexes are built over instant-database tables, so load the data first (upload parquet or run an ingest).

CLI (instant-database tables): hotdata search create --type text|vector|sorted --from <catalog>.<schema>.<table> --column <col>

Indexes are stored in the catalog and used automatically when the query planner finds a beneficial match. Use GET .../indexes to list indexes and DELETE .../indexes/{index_name} to drop one.

Sorted indexes

Pre-sorted Parquet copies of cached data, ordered by the columns you specify. Accelerate range queries, ordering, and point lookups.

{
  "index_name": "idx_created_at",
  "columns": ["created_at"],
  "index_type": "sorted"
}

A sorted index is used for one thing: a WHERE filter on the index's leading sort column, where sorted Parquet lets the planner prune row groups. Range and equality filters both benefit.

-- Index on created_at: row-group pruning for the range filter
SELECT * FROM events
WHERE created_at >= '2025-01-01' AND created_at < '2025-02-01';

-- Index on created_at: also pruned, an equality filter is a degenerate range
SELECT * FROM events WHERE created_at = '2025-01-15';

An ORDER BY on its own does not select the index, so SELECT * FROM products ORDER BY sku LIMIT 100 still sorts.

Vector indexes

HNSW indexes for approximate nearest-neighbor search on vector columns, or IVF indexes with "algorithm": "ivf" (see IVF indexes). Require exactly one column.

On a pre-computed embedding column (float array):

{
  "index_name": "docs_embedding_idx",
  "columns": ["embedding"],
  "index_type": "vector",
  "metric": "cosine"
}

On a text column with auto-embedding: when the source column is text, Hotdata automatically generates embeddings using an embedding provider. This enables vector_distance() for semantic search.

If you omit embedding_provider_id, the system embedding provider is used automatically. If no system provider is configured, you must pass one explicitly. The metric also defaults to the provider's configured metric when not specified.

{
  "index_name": "docs_description_idx",
  "columns": ["description"],
  "index_type": "vector"
}

Or with explicit provider and metric:

{
  "index_name": "docs_description_idx",
  "columns": ["description"],
  "index_type": "vector",
  "metric": "cosine",
  "embedding_provider_id": "embp_abc123",
  "dimensions": 1536
}

The embedding provider generates a new column (default: {column}_embedding) containing the vector embeddings. Configure embedding providers via the Embedding Providers API.

An auto-embedding vector index must be the only index on its table. Creating one is rejected if the table already has any index, and once it exists no further index of any type can be added alongside it. Publishing an auto-embedding index rewrites the table's underlying storage, which would leave sibling indexes pointing at data that no longer matches. To combine full-text and vector search over the same content, keep them on separate tables.

This restriction applies only to auto-embedding indexes. A vector index over a pre-computed embedding column generates nothing, so it coexists with full-text and sorted indexes as normal.

CLI example:

hotdata search create --type vector --from mydb.public.documents \
  --column embedding --metric cosine

IVF indexes

Set "algorithm": "ivf" to build the index with IVF instead of HNSW. An hnsw index keeps the whole index in memory, so the memory a search needs grows with the table. An ivf index groups the vectors into clusters and reads only the clusters nearest the query, so the memory a search needs follows how much of the index it reads rather than the size of the table. Choose ivf for a table too large to index with hnsw on your instance. An ivf index keeps a copy of the table's rows beside its vectors, which is extra storage.

{
  "index_name": "docs_embedding_ivf",
  "columns": ["embedding"],
  "index_type": "vector",
  "algorithm": "ivf",
  "metric": "cosine",
  "async": true
}

hotdata search create --type vector --algorithm ivf --from mydb.public.documents \
  --column embedding --metric cosine --async

Every field is described under Create an index on a table. Queries are written the same way as for HNSW: the distance functions in an ORDER BY ... LIMIT k, with or without a WHERE, and the same shapes run as an exact scan. What differs with ivf:

• Vector columns, or text with an embedding provider. On a column that already holds vectors, ivf indexes it directly. On a text column with an embedding provider, hotdata generates the embeddings into a new column, as it does for hnsw, and builds the ivf index over that; search it with vector_distance and the query text. The provider's metric must be l2 or cosine.

• Metrics: l2 or cosine. dot is rejected.

• Options:

  • nlist sets the number of clusters, from 1 to 65536. When omitted, it is chosen from the table's size. An index has at most one cluster per indexed row, so a larger nlist is reduced to the row count; the index reports the number it used (nlist in hotdata search show and the index API).
  • probe_fraction sets the share of clusters a search starts by reading, greater than 0 and at most 1. When omitted, hotdata chooses it from measurements taken during the build. Reading more finds more of the true nearest neighbors and takes longer.
  • vector_precision accepts int8 (the default for ivf) or float32 only.

• Accuracy. With the default search width, an ivf search typically returns about 94% of the true 10 nearest neighbours (measured on about 900,000 1536-dimension OpenAI embeddings with cosine); individual queries vary, and a few find as few as half. Reading more clusters raises it: in that test 64 clusters gave about 96%, 128 about 97%, and every cluster about 99%, at higher latency. Measure on your own data, and use SET hotdata.ivf_probe_clusters or the index's probe_fraction to trade latency for accuracy.

• With int8, distances are approximate. The distance an ivf query returns, and the order of its rows, are computed from the index's compressed vectors. They differ from the exact distance by a small amount, so two rows at nearly the same distance can come back in swapped order. With float32, distances are computed from 32-bit copies of the vectors, which take four times the space of int8.

• The table functions do not use it. vector_search and vector_search_vector read hnsw indexes only. On a column with an ivf index they fail with an error suggesting the ORDER BY distance-function form to use instead.

• Filtered queries apply the WHERE during the search and keep reading clusters until they have enough matches, so they return k rows whenever at least k indexed rows match. Before the search, hotdata counts how many indexed rows pass the filter, once per table version. The query uses the index once that count has seen at least k matching rows. Otherwise it runs as an exact scan, which also returns rows whose vector is NULL. It also runs as an exact scan when the filter cannot be evaluated inside the index or the count does not finish in time. EXPLAIN shows which path was taken: no IvfSearchExec node means the exact scan.

• Filtered accuracy. Filtered searches are approximate, and the rarer the filter, the more a result can miss. To raise accuracy for a query, read more clusters with SET hotdata.ivf_probe_clusters (below), at higher latency.

• OFFSET after the LIMIT is not served by an ivf index: the query runs as an exact scan.

• Per-query search width: put SET hotdata.ivf_probe_clusters = N; in front of the query, in the same SQL text. It applies to that query only. N is a number of clusters, at least 1, and is capped at the index's cluster count:

  SET hotdata.ivf_probe_clusters = 64;
  SELECT id, cosine_distance(embedding, ARRAY[0.1, -0.2, 0.5, ...]) AS dist
  FROM mydb.public.documents
  ORDER BY dist
  LIMIT 10;
  

• Forks: an ivf index is not carried over to a forked database. Create it again on the fork.

A table can have only one vector index, whichever algorithm it uses. Creating a second one is rejected with "Only one vector index per table is currently supported".

Reading EXPLAIN. A query the index serves shows an IvfSearchExec node. If the node is missing, the query ran as an exact scan. The node shows:

• k: the number of rows the search returns.
• clusters=<start>/<total>: how many clusters the first round reads, out of the index's total.
• probe_fraction: the same starting width, as a share of the clusters.
• calibrated=<a>/<b> and target_met: these appear when the search uses the width measured during the build. a is the width for LIMIT 10 or less and b the width for LIMIT 100 or more. A limit between them starts at a width interpolated on a logarithmic scale of the limit, so LIMIT 50 sits about 70% of the way from a to b in that scale rather than halfway. target_met=false means the build found no width that reached hotdata's accuracy target for one of those limits, so it used the most accurate width it measured.
• probe_capped_by=budget: the first round was narrowed to keep latency predictable. A search that has not found enough rows still reads further.
• filter_fraction (filtered queries): the estimated share of indexed rows that match the WHERE.
• probe_widened_by=filter_fraction: the first round was widened because few rows match the filter.

EXPLAIN ANALYZE adds what the search actually did:

• clusters_probed: the clusters it read.
• rounds: how many times it widened, plus one.
• rows_scanned: the rows it read from those clusters; with a filter, the rows that matched.
• rows_matched: how many of those rows entered the nearest-k candidates as they were scored.

Keeping an ivf index current. Every load updates the index, in every mode (append, update, upsert, delete, replace). Loads place rows into the clusters computed when the index was built; the clusters are not rebalanced automatically. Rebuild the index when the table has grown far past its size at build time, or when new data is distributed differently from the data it was built on. Otherwise searches slow down, find fewer of the true nearest neighbors, or both. A rebuild means dropping the index and creating it again: with one vector index per table, a replacement cannot be built alongside the old one. Queries on the column run as an exact scan until the new index is ready.

BM25 indexes

Full-text search indexes using BM25 scoring. Required for bm25_search(). The index stores analyzed tokens — see how text is analyzed for what is and is not matchable.

{
  "index_name": "articles_body_idx",
  "columns": ["body"],
  "index_type": "bm25"
}

CLI example:

hotdata search create --type text --from mydb.public.articles \
  --column body

Point lookups

Queries that fetch a single row by equality (WHERE col = value with LIMIT 1) are classified as point lookups. Parquet bloom filter reads skip row groups that cannot contain the value, reducing I/O.

SELECT * FROM users WHERE id = 12345;

Index lifecycle

Every load into a table keeps its indexes current, in every load mode. If an index cannot be updated, drop it and create it again.

Writing data with SQL

The query endpoint runs a small set of write statements against the database's own tables. Each one runs once, inline, and answers with a one-row result holding a single rows_affected column.

CREATE TABLE events (id BIGINT, kind TEXT, at TIMESTAMP);
INSERT INTO events (id, kind, at) VALUES (1, 'signup', '2026-01-01T00:00:00');
INSERT INTO events SELECT id, 'import', now() FROM staging.rows;
DROP TABLE IF EXISTS staging.rows;

Rules that apply to every write:

• Who can write. The API key must hold both the read and write permissions. A saved query never runs a write, whoever executes it.
• Where you can write. Only the database's own default catalog. Attached catalogs are other databases' data and stay read-only.
• Each INSERT is a publish. It takes the table's write lock, appends a new generation, and maintains the table's indexes, exactly like an append load. Batch rows into one statement rather than inserting them one at a time; for anything of size, use the load endpoint.
• Append only. INSERT OVERWRITE, INSERT OR REPLACE, ON CONFLICT and RETURNING are refused. A table with an embedding index must be inserted into with an explicit column list that leaves the generated vector column out.
• Every column is nullable. NOT NULL, DEFAULT, PRIMARY KEY, UNIQUE and OR REPLACE on CREATE TABLE are refused rather than silently ignored. Table layout (partition_by, sorted_by, a key) is declared through the tables API, not in SQL.
• UPDATE and DELETE are not supported yet. They are refused by name. Use the load endpoint's keyed update, delete and upsert modes instead.
• Everything else that mutates is refused: ALTER, views, COPY, transaction control, and EXPLAIN or EXPLAIN ANALYZE of a write.

Information schema

Introspect the catalog by querying the information_schema views:

• information_schema.tables — tables in scope
• information_schema.columns — columns and their types
• information_schema.schemata — schemas
• information_schema.catalogs — catalogs

SELECT table_schema, table_name
FROM information_schema.tables
ORDER BY table_schema, table_name;

DESCRIBE table returns a single table's schema:

DESCRIBE conn.public.orders;

The information_schema.views, routines, and parameters views exist for tool compatibility but are empty. SHOW TABLES and SHOW COLUMNS both work and return rows; SHOW FUNCTIONS is accepted but currently returns no rows, so use information_schema or DESCRIBE to inspect a table. You can also list tables and schemas via the API or MCP.

EXPLAIN

Use EXPLAIN to inspect the query plan:

EXPLAIN SELECT * FROM orders WHERE created_at > '2025-01-01';

EXPLAIN ANALYZE runs the query and returns execution metrics (timing, rows, etc.). Because it executes the plan, a write wrapped in EXPLAIN or EXPLAIN ANALYZE is inspected and rejected rather than run.

Further reading

• Function references — every category, with signatures and runnable examples
• PostGIS documentation — semantics of the ST_ spatial functions (Hotdata implements a planar-geometry subset)
• HNSW — approximate nearest-neighbor search