9. 0 Release Notes
On this page
Note
-
To deploy a SingleStore 9.
0 cluster, refer to the Deploy SingleStore Guide. -
To upgrade a self-managed install to this release, follow this guide.
-
To make a backup of a database in this release or to restore a database backup to this release, follow this guide.
-
New deployments of SingleStore 9.
0 require a 64-bit version of RHEL/AlmaLinux 7 or later, or Debian 8 or later, with kernel 3. 10 or later and glibc2.17 or later. Refer to the System Requirements and Recommendations for additional information.
Release Highlights
Feature: Sequences - The addition of a SEQUENCE modifier for AUTO_ ensures that a unique sequence of numbers is generated, while greatly reducing the difference between the generated numbers.SEQUENCE modifier allows you to set the column type for AUTO_ columns to other integer types and specify custom starting values.
Feature: Automatic Query Reoptimization with Feedback - This feature, also referred to as Feedback Reoptimization (FR), uses statistics from previous query runs to improve query plans and performance.
Feature: Multi-value Index for JSON - The new multi-value hash index enables 100x faster analytics performance on JSON arrays.JSON_ queries.
Enhancement: Enhanced Query History - The updated Query History feature provides users with a powerful tool for monitoring and optimizing SQL query performance.
Preview Feature: Distributed Plancache (DPC) - The Distributed Plancache adds a third layer to the plancache, supplementing the existing in-memory plancache and on-disk persistent plancache (PPC).
-
Enabled synchronous Distributed Plancache lookup during query execution.
-
Distributed Plancache pool size adjustment fails gracefully.
-
Prevented the drop of Distributed Plancache files on regular Persistent Plancache GC.
Changes in Default Behavior
Collation Change
The default collation for new (9.utf8mb4_ from utf8mb4_ to improve default performance of string comparisons, which can substantially improve query speed.utf8mb4_ collation is not case sensitive and provides performance advantages when case sensitivity is not required.
-
For existing clusters that are upgraded to 9.
0+, the collation is not changed. -
For new (9.
0+) clusters, the default collation will be utf8mb4_.bin -
The default collation for the
utf8andutf8mb4charsets has been changed.If a charset, e. g. utf8mb4is used in new (9.0+) clusters without an explicit collation, the default collation will be utf8mb4_instead ofbin utf8mb4_.general_ ci -
Refer to Specifying Character Set and Collation for Clusters for additional information.
Enhancements
Full-text Search
-
Added full-text pushdown optimization that works with any filter in the
WHEREclause. -
Added a new metadata table to track full-text search indexes eligible for merging.
-
Added support for custom stopword lists for full-text search version 2.
-
Added new nori (Korean) analyzer customizations for full-text search version 2.
Iceberg
-
Implemented support for automatically generated column mappings (for primitive data types and structures) as part of Iceberg schema inference.
-
Added support for file filtering based on the WHERE clause in the pipeline declaration.
Refer to Iceberg Ingest for more information.
JSON
-
Introduced Multi-Value Hash Index for JSON columns.
This index optimizes key-path and array-value lookups in JSON columns. -
Extended a rewrite to allow merging derived tables with JSON functions.
-
Added a new JSON_
BUILD_ ARRAY function. -
Added support for JSON arrays in pipelines loading JSON files.
Each JSON record in the array is loaded as a separate row. -
Enabled the use of the
MATCH_tokenizer option with underscore in multi-value index settings.ANY -
Added charset validation for JSON type values in external functions.
-
Added support for the JSON data type in external functions.
Observability
-
Improved performance of profiled query plans by introducing a new profile codegen mode.
-
Included aggregator activity statistics in Query_
completion event traces. -
Added the
auditlog_engine variable that enables audit logging for only the root user.root_ only -
Added
activity_field in activity structure to track the resource consumption by pipelines.tracking_ id -
Updated the default values of
enable_andauto_ profile auto_engine variable toprofile_ type ONandSMART, respectively. -
Added the
SHOW BOTTOMLESS_command that returns observability metrics for the distributed plancache.PLANCACHE STATUS -
Added estimates for query memory usage based on static row count and row size estimates to improve Workload Manager (WM) memory management.
-
Added an internal allocator
Alloc_which tracks certain per-connection allocations that were previously tracked using the standard allocator.connection_ context -
Added an option to run queries that failed asynchronous compilation, allowing users to introspect the compilation results.
-
Added logging for LRU compiled unit eviction.
Pipelines
-
Added support for the
PIPELINE <pipeline_clause to the SHOW PROFILE command which allows you to view profile information for the specified pipeline.name> -
Added support for
SHOW PROFILE PIPELINEon Helios. -
Added the
CREATE_andUSER ALTER_fields in theUSER PIPELINESinformation schema view.For more information, refer to PIPELINES. -
Added a
HEADER DETECTIONON/OFFclause to control inference from CSV files.For more information, refer to Schema and Pipeline Inference. -
Added support for inferring Parquet files with the
INFER PIPELINEcommand.For more information, refer to Schema and Pipeline Inference. -
Added support for inferring Iceberg schemas automatically on pipeline creation with the
INFER PIPELINEcommand.For more information, refer to Schema and Pipeline Inference. -
Added
SHOW CREATE LINKcommand that allows you to view theCREATEstatement for the specified link. -
Added support for
TIME(0),TIME(6),DATETIME(0), andDATETIME(6)types for theCASTcommand for use with computed columns and theSETclause for use with pipelines andLOAD DATA. -
Added support to infer CSV files with a single column when the file contains no field terminators in any record.
-
Implemented exponential backoff sleep between batch retries.
-
Added support for metadata garbage collection for Azure pipelines.
Query Execution
-
Added Join Memory Reduction Optimization.
This optimization is a new query optimization feature designed to help prevent memory overflow (spilling) during hash joins on large datasets. When enabled, it automatically rewrites qualifying joins into smaller, manageable pieces—reducing memory pressure and the risk of out-of-memory (OOM) errors. This feature is especially useful when joining large tables on high-cardinality columns, where traditional hash joins could exceed memory limits. Added engine variables cardinality_andthreshold_ for_ join_ split_ rewrite target_to control the behavior of this optimization.group_ size_ for_ join_ split_ rewrite Refer to Join Memory Reduction Optimization for details. -
Added segment metadata that allows the engine to determine if columns are fully enclosed in (or pass) a filter and allows the engine to do segment elimination on queries with an
IS NULLfilter. -
Added the
CHARACTER SET <charset>andCOLLATE <collation>clauses to theALTER TABLEcommand that allows converting the character set and collation of a table, respectively. -
Added support for parameterized
IN-lists in BSON multi-value index optimization enabling improved performance for queries with same-type constant literals and single-argument built-in expressions.Previously limited to hex literals. -
Disabled the pushdown of
INSERT SELECTqueries to leaf nodes for shard key affecting columns. -
Enabled expression pushdown from join into hash builds on materialized CTEs.
-
Enabled the rewriting of correlated subselects that depend on more than one outer table with correlation conditions other than
=andAND. -
Extended predicate pushdowns to identify more cases.
-
Extended rewrite for
EXISTSsubselects to handle arbitrarily nestedEXISTSsubselects underANDorOR. -
Added the ability to ignore join selectivity estimates of zero.
Security
-
Enabled mutual TLS authentication between the client and server.
-
Added support for manual password activation and expiration to the
ALTER USERcommand. -
Added the
password_engine variable.expiration_ mode When set to LIMITED_, users are allowed to log in even after password expiration and run password update commands, such asACCESS ALTER USERorSET PASSWORD. -
Internal roles and groups cannot be deleted anymore.
-
Passwords are now masked in extractor subprocess logs.
-
A warning is now raised for every query if the user's password is expiring soon.
Vector Search
-
Added the
VECTOR_information schema view which shows the amount of memory and disk used by each vector index.INDEX -
Added a fallback mechanism so that when the engine detects that an indexed vector search followed by filters yields fewer than
LIMITrows, the engine falls back to a full table scan to ensure sufficient rows are produced.The fallback mechanism can be disabled by setting the engine variable vector_to FALSE.index_ fallback_ non_ index_ scan -
Extended the
VECTOR_aggregate function to support theSUM VECTORdata type. -
Added a vector index cache to limit the amount of memory used by vector indexes.
Refer to Vector Indexing for details.
General Updates
-
Added events to indicate the start and end of asynchronous upgrades.
-
Added the
ENSURE_clause to theONLINE_ NODES REBALANCE ALL DATABASESandREBALANCE PARTITIONScommands which allows rebalancing to run only if all the leaf nodes are online. -
Allowed background merger to run while
OPTIMIZE TABLEis running. -
For deployments with small cache size, changed the default blob cache size to 75% of the disk.
Refer to Setting the Blob Cache for details. -
Connected the distributed plancache to the plancache manager.
-
Disabled unnecessary collection of block statistics.
-
All distributed plancache operations are now asynchronous.
-
Added support for reusing autostats file on recovery.
-
Added support for spilling if input data has NULL rows.
-
Aggregators now periodically synchronize query plans across the deployment.
-
DROP RESOURCE POOLcommand cannot delete the current resource pool anymore. -
The engine now waits until asynchronous upgrade steps complete before taking a snapshot.
-
Added support for NULL type in Wasm UDF return types and parameters.
-
Added support for expressions, builtin functions, and user-defined variables in addition to hex literals in Multi-Value index for BSON.
Refer to Multi-Value Hash Index (BSON) for more information. -
Extended redundant
GROUP BYdetection to outer join predicates. -
Users with database visibility can now query the
*_information schema views in the respective database.BOTTOMLESS_ * CLUSTERpermissions are no longer enforced for querying these views. -
Columns with extended types now use the correct default table collation.
-
Enabled correlating fields in subselects to reference outer selects more than one level up.
-
Enabled skipping of non-alterable tables from asynchronous DDL queries.
-
Updated to use a consistent snapshot while performing a shallow table copy.
-
Disabled segment elimination for
INclauses for which the left-hand expression is not a table column. -
Improved the error message returned when invalid key is specified in the CONFIG/CREDENTIALS JSON.
-
Improved the error message for parameter count mismatch during query parsing.
-
Users with
SHOW METADATAprivilege can now access theLOAD_information schema view.DATA_ ERRORS -
Added support for delimited batch sets in external functions.
-
Added support for the
LIMITclause in prepared statements. -
Computed column definitions now support the
SPLITfunction. -
The
DROPandALTER TABLEcommands no longer have to wait for the plan garbage collector. -
Added support for updated Standard and Enterprise licenses.
-
Improved the garbage collection behavior for plancache.
-
Introduced support for placeholders for partition ID and timestamp in the
SELECT INTO .command.. . filename -
Added Lucene logs to the cluster report.
-
Added statistics related to AWS S3 lookup latency.
-
Added knob to disable cardinality estimates on JSON/BSON columns during analyze.
Engine Variables
This update adds the following new engine variables:
-
auditlog_: Enables audit logging for only the root user.root_ only -
disk_: Disables disk plan garbage collection on startup.plan_ gc_ pause_ seconds_ on_ startup This variable prevents hot plans from being unintentionally disk garbage collected. -
disk_: Specifies the number of seconds to cache detached table modules before eviction from disk.plan_ gc_ pause_ minutes_ for_ detached_ tables -
enable_: Enables reporting of garbage collection events.gc_ events Gates the MV_information schema view that provides visibility into garbage collection passes.GC_ EVENTS -
info_: Allows non-privileged users to view the rows inschema_ show_ null_ dbs *_information schema views where associated database name is NULL.BOTTOMLESS_ * -
managed_: Specifies the maximum number of HTTP connections per node for managed external functions, such as Python User Defined Function (UDF)s.functions_ max_ connections -
optimizer_: Controls if the cardinality of JSON columns is collected by autostats indisable_ analyze_ cardinality_ on_ json PERIODICmode. -
optimizer_: Controls the maximum estimated table row count to be used as the per column cardinality when no column-level statistics exists.max_ table_ row_ count_ as_ default_ cardinality -
password_: When set toexpiration_ mode LIMITED_, users are allowed to log in even after password expiration and run password update commands, such asACCESS ALTER USERorSET PASSWORD. -
regexp_: Controls the behavior of error handling for regexp operations.error_ handling_ level -
scheduler_: Specifies the threshold for triggering the verbose logging of scheduler thread timing.slow_ loop_ seconds -
ssl_: Support for client certification.ca_ for_ client_ cert -
suppress_: Suppresses all DML query warnings.dml_ warnings -
vector_: Controls a fallback mechanism so that when the engine detects that an indexed vector search followed by filters yields fewer thanindex_ fallback_ non_ index_ scan LIMITrows, the engine falls back to a full table scan to ensure sufficient rows are produced. -
Distributed Plan Cache: Added the following engine variables to control this feature:
enable_,distributed_ plancache distributed_,plancache_ worker_ threads distributed_,plancache_ max_ download_ plans distributed_,plancache_ agg_ sync_ s enable_,periodic_ distributed_ plancache_ agg_ sync enable_.synchronous_ dpc_ lookup -
Join Memory Reduction Optimization : Added
cardinality_andthreshold_ for_ join_ split_ rewrite target_to control the behavior of this optimization.group_ size_ for_ join_ split_ rewrite -
Replication: Note: Only use the following under the guidance of, or as directed by, SingleStore Support.
-
repl_: Limits the number of page descriptors in the page map that a 'ReplLog' can use before stalling new transactions.page_ desc_ limit_ before_ stall -
repl_: Limits the number of IO buffers that a 'ReplLog' can use before stalling new transactions.io_ buffer_ limit [dr_ ] -
repl_: Replication tries to set both send and receive buffer sizes to reduce waits on TCP ACK packets.network_ buffer_ size Can be set for both DR and non-DR replicas. [dr_ ] -
repl_: When sending logs or snapshots to a replica, limit the chunk size to this valuemax_ chunk_ size
-
Information Schema
This update adds the following information schema views and columns:
-
MV_.ACTIVE_ METADATA_ VERSIONS Provides visibility into active metadata transaction read versions. -
MV_andCOLUMNSTORE_ ACTIVE_ MERGE_ PLANS LMV_.COLUMNSTORE_ ACTIVE_ MERGE_ PLANS Show internal details on currently running merge plans. -
MV_.GC_ EVENTS Provides visibility into garbage collection passes. This view is gated behind the preview feature and engine variable enable_.gc_ events -
MV_andTABLE_ COUNTERS LMV_.TABLE_ COUNTERS Show cumulative per-table metrics, i. e. , the amount of work performed by the background merger. -
VECTOR_.INDEX Shows the amount of memory and cache used by each vector index. -
Added the
UPLOAD_column toBLOB_ LEAD_ PAGES information_.schema. MV_ BOTTOMLESS_ SUMMARY Ensures that uploading log chunks to the remote storage does not keep falling behind. -
Added more metrics to
information_andschema. MV_ CLOUD_ PER_ COMPUTE_ REMOTE_ STATS information_.schema. MV_ CLOUD_ PER_ STORAGE_ REMOTE_ STATS Tracks retention log chunks and snapshots. -
Added the
ATTACH DATABASE,DETACH DATABASE,CREATE DATABASE, andDROP DATABASEcolumns toinformation_.schema. MV_ EVENTS -
Added the
CREATE_andUSER ALTER_fields toUSER information_.schema. PIPELINES -
source_is now included as part of thetype config_injson information_.schema. pipelines
Bug Fixes
-
Fixed a case where long running background work was blocking in-memory garbage collection of database metadata.
-
Fixed a memory allocation bug during query compilation.
-
Limited the number of retries on plan compilation errors; this fix limits resource usage.
-
Fixed a deadlock in result table creation during reshuffle with the always-false filter.
-
Fixed a crash when a
BM25function in a subselect referenced a table in the outer select. -
Fixed a crash during replay when a table uses an FTS index with JSON options.
-
Fixed a potential replay failure on a table that uses full-text search version 2.
-
Fixed how
JSON_builtin function escapes characters.PRETTY -
Fixed a bug that caused
JSON_join queries to return wrong results where the quotes on strings were removed incorrectly.TO_ ARRAY -
Fixed using delimited batch sets with JSON format.
-
Fixed performance regression for queries involving JSON string extraction (e.
g. JSON_andEXTRACT_ STRING JSON_) in situations where there are a large number of JSON values.ARRAY_ CONTAINS_ STRING -
Fixed incorrect aggregation of leaf query stats in Query_
completion tracing. -
Fixed an issue that caused an error if column names had trailing spaces in
CREATE PIPELINEstatements. -
Fixed an issue where monitoring pipelines would sometimes fail to produce data for large source clusters.
-
Fixed a wrong result in
ORDER BY <sort key> LIMITquery that skipped rows with NULL values in the sort key. -
Added support for the
IF NOT EXISTSclause to theCREATE VIEWcommand. -
Added support for table-level collations.
-
Improved optimization speed of parameterized
IN-lists by limiting traversal depth. -
OPTIMIZE TABLE .is now more responsive to. . INDEX KILL QUERYstatements. -
Enabled using the estimated table row count as default column cardinality for better join estimation.
-
Added support for PSQL ARRAY type arguments to the
TO_built-in function.JSON -
Removed the
CASToperator if a redundant cast is performed. -
Improved error handling for regular expressions: errors are now properly raised instead of returning SQL NULL.
To avoid breaking existing apps the regexp_engine variable was added.error_ handling_ level This variable controls the behavior of error handling for regexp operations. -
Fixed an issue where concurrent DDL and
PROMOTE AGGREGATOR TO MASTERstatements were not forwarded to the Master Aggregator. -
Fixed the projection DDL output in the debug profile.
-
Fixed an error named
ER_onBAD_ TABLE_ ERROR UPDATEorDELETEqueries with a specific shape. -
Fixed an issue in CTE query rewrites caused when a CTE referenced a recursive CTE.
-
Fixed the predicate pushdown logic for materialized common table expressions.
-
Fixed query performance issue caused by heuristic estimate selectivity being overwritten.
-
Fixed row count estimation for union subquery containing Materialized Common Table Expression (MCTE)s.
-
Resolved an issue where the regular expression engine would return SQL NULL when hitting the memory limit during execution.
It now raises an appropriate error instead. -
Fixed resource pool and workload management CPU limiting.
-
Fixed a locking bug related to table module eviction during recovery.
-
Fixed a deadlock in a rare scenario related to low memory conditions.
-
Fixed a rare race condition which caused a node to get stuck in offline mode after recovery.
-
Fixed an issue that led to undefined behavior if a node failed concurrently with the computation of the replication distribution tree.
-
Fixed a race condition that caused autostats file leak while reprovisioning.
-
Fixed incorrect autostats in a rare failover case during asynchronous replication.
-
Fixed a race condition in autostats between table refresh and table deletion.
-
Fixed a race condition that could cause shutdown to hang.
-
Fixed a rare crash in synchronized variables initialization.
-
Fixed a bug in the deadlock avoidance algorithm that could lead to deadlocks.
-
Fixed a bug that caused nodes to crash when
ALTER TABLEqueries ignored foreign keys. -
Fixed a rare deadlock related to temporary tables.
-
Fixed a crash in
SHOW PROCESSLISTin a rare race condition scenario. -
Fixed a bug in CTE rewrites.
-
Fixed a leak in a rare scenario.
-
Fixed a bug in parser.
-
Fixed a crash in a shallow copy of a table with a pending ALTER operation.
-
Fixed an issue where IN-list factorization did not work with newline characters around the IN-list.
-
Fixed an issue where periodic autostats was disabled when it should not have been.
-
Fixed a crash in lockfree hashtable when the engine is under high memory pressure.
Reports an "out of memory" error instead. -
Fixed a bug in lateral join.
-
Updated third party libraries to fix security vulnerabilities.
-
Increased the _
bt timeout from 60 seconds to 180 seconds. -
Implemented shallow copy of a table with a dropped index, the shallow copy now includes the in-memory segment.
-
Fixed incorrect aggregation of leaf query stats in Query_
completion tracing. -
Fixed an issue where certain commands (e.
g. DROP TABLE) could hang due to aborted queries caused by stuck collocated services triggered by external functions. -
Fixed a bug in the detection of shard key matches in the window function partition by list.
-
Fixed an error in backup subprocess and enhanced subprocess tracing to use structured logging.
Release Log
2025-10-15 Version 9. 0. 10
Enhancements
-
Added collection of enhanced query stats from kill query Recursive Common Table Expressions (RCTE) writer statistics and sorted iterator error cases.
-
Added Unix timestamp versions of
start_andtime event_fields in event trace samples exposed by monitoring exporter.time -
Added option to merge credentials with external secret storage.
-
Made Resource Governance aware of cgroup limits in Kubernetes clusters.
The minimum of host cores and cgroup cores is now used as the CPU limit for governing resource pools. -
Modified ingestion to skip
NULLrecords while usingSKIP ALL ERRORSandSKIP PARSER ERRORSin kafka pipelines. -
Added a new
BLOB_column forCOMPUTE_ ID COLUMNAR_andSEGMENTS MV_information schema views.COLUMSTORE_ FILES -
Added a new column
MEMORY_toUSAGE_ BYTES LMV_andRESOURCE_ POOL_ STATUS MV_information schema views.RESOURCE_ POOL_ STATUS
Last modified: October 23, 2025