SingleStore 7.5 release announcement

• Added support for multi-column hash keys (which can be PRIMARY or UNIQUE) on columnstore tables.

• Added support for creating multi-column hash indexes on columnstore tables, using CREATE INDEX.

• Added support for upserts, using ON DUPLICATE KEY UPDATE or IGNORE on columnstore tables with multi-column hash keys.

Storage

• Added the unlimited storage feature, which allows you to store databases in a remote object store, with remote objects cached locally on the cluster to enhance performance. This is known as separation of storage and compute. Currently, the Amazon S3 remote object store and Ceph remote object store on Amazon S3 are supported.

• Added support for the utf8mb4 character set, which includes encoding for emojis and additional supplementary character sets. The character_set_server and collation_server engine variables specify the server character set and collation. Although these are session variables, it is strongly recommended to set these variables globally via SQL commands or set them at server startup using SingleStore Tools.

• Added "straight-to-disk" support for columnstore tables with a unique index using INSERT … IGNORE, REPLACE, and INSERT … ON DUPLICATE KEY UPDATE. Straight-to-disk is when inserts and updates on columnstores bypass the rowstore (memory-backed) segment and go straight to disk.

• The preserve_original_colstore_json engine variable now defaults to ON.

System of Record

• Added point-in-time recovery (PITR), which allows you to restore a bottomless database to a specified point-in-time.

• Added lock-free backup functionality, allowing backups to start without pausing concurrent online updates. Also, added the Implementation column to the information_schema.mv_backup_status table. Implementation can have either of the following values: Original or Lockfree.

Usability and Programmability

• Added a new function, JSON_BUILD_OBJECT, that converts a comma-separated argument list to a JSON object.

• Added a new function, JSON_PRETTY, that outputs the given JSON object or JSON array in an indented and legible format.

• Added two new built-in SQL commands for creating identifiers: SYS_GUID and UUID.

• The data_conversion_compatibility_level engine variable can now be set to 7.5 for stricter data type conversions. This new data_conversion_compatibility_level setting additionally flags invalid string-to-number conversion in INSERT statements.

• Introduced a beta version of HTTP APIs that can be used to execute SQL statements over an HTTP connection.

  The HTTP APIs enable you to develop custom applications and build seamless integrations with applications. The HTTP API endpoints can be easily enabled or disabled using engine variables. For more information, see HTTP API Documentation.

Query Execution

• Added support for hash group spilling, which enables GROUP BY queries with result sets larger than available RAM to finish successfully.

• UPDATE and DELETE queries containing a join (without using LIMIT) now only lock rows that pass the join.

• Added support for executing Data Manipulation Language (DML) queries for reference tables on child aggregator nodes in addition to the master aggregator node. A new enable_dml_query_forwarding engine variable enables DML queries for reference tables to be forwarded from child aggregator nodes to the master aggregator node.

• Added the SHOW REPRODUCTION command, which collects troubleshooting data for query optimizer and code generation issues for queries that fail in the compilation phase. The troubleshooting information can be displayed on the terminal or written to a file, which can then be sent to the SingleStore Technical Support team for further analysis.

• Added support for reading SELECT query results in parallel, enabling faster transmission of large results sets to parallel client applications.

• Added the compile_lite and compile_firstsettings to the interpreter_mode engine variable.

• Added the default_columnstore_table_lock_threshold engine variable. This is the threshold for the number of rows that are locked before a table lock is acquired when updating or deleting rows in a columnstore table.

• Updates and deletes can now be run as a distributed transaction. This feature is enabled when the engine variable disable_update_delete_distributed_transactions is set to OFF (the default). When this variable is set to OFF, the engine variables deadlock_avoidance_strategy, lock_wait_timeout, and wait_die_retry_on_die_sleep_factor control the locking behavior of the updates and deletes.

Ingest

• Added support for IMDSv2 in S3 pipelines.

• Added support for ingesting Kafka keys using pipelines.

Security

• Added database version information to MV_BACKUP_HISTORY via the SERVER_VERSION column.

• Added replay_stuck_on_low_blob_cache_space to the output of SHOW DATABASE STATUS. It indicates whether the replay is stalling because of low blob cache space.

• The RPC info column has been removed from the output of the SHOW PROCESSLIST command.

• Added the MV_CACHED_BLOBS and MV_BLOB_CACHE_EVENTS information schema tables.

• The local join optimizer now uses beam search to enable finding better query plans. The new engine variable, optimizer_beam_width, controls the beam search width. The default value is 10. Setting this to 1 reverts to the previous behavior (prior to v7.5). Higher values (maximum is 1024) will make the query optimization time slower.

• For commands that require CONFIG and CREDENTIALS, you can specify them in either order (CONFIG followed by CREDENTIALS or CREDENTIALS followed by CONFIG).

• Added success as a JSON key that is populated in the DETAILS field of the information_schema.MV_EVENTS table for rebalance events. The value of the success key indicates whether a rebalance succeeded or failed.

• The SHOW REBALANCE STATUS, SHOW REBALANCE ALL DATABASES STATUS, EXPLAIN REBALANCE PARTITIONS, and EXPLAIN REBALANCE ALL DATABASES commands now output Estimated_Time_Remaining_Ms, which provides a time estimate for completing a rebalance.

• Extended UNION support to a wider range of cross-database queries.

• Added the ability to run multiple concurrent ALTER commands on different tables in the same database.

• Added the multi_statement_xact_idle_timeout engine variable, which is the time, in seconds, that is allowed for a multi-statement transaction to remain idle while holding locks.