# 7.3 Release Notes

> **📝 Note**: To deploy a SingleStore 7.3 cluster, refer to the [Deploy SingleStore Guide](https://docs.singlestore.com/db/v9.1/deploy.md).To make a backup of a database in this release or to restore a database backup to this release, follow [this](https://docs.singlestore.com/db/v9.1/manage-data/back-up-and-restore-data.md) guide.

## 7.3 Release Highlights

The SingleStore 7.3 release is focused on storage, query processing, and programmability. As Universal Storage evolves, useful features such as columnstore as a default and support for upserts into columnstore tables have been added. Other highlights include data definition language (DDL) forwarding from child to master aggregator, a new, safer command for promoting a child to master aggregator, support for large queries that join over 30 tables, and a variety of new engine variables.

## System of Record

* Added three information schema views: [mv\_aggregated\_replication\_status](https://docs.singlestore.com/db/v9.1/reference/information-schema-reference/management/mv-aggregated-replication-status.md), [mv\_replication\_status](https://docs.singlestore.com/db/v9.1/reference/information-schema-reference/replication-management/mv-replication-status.md), and [lmv\_replication\_status](https://docs.singlestore.com/db/v9.1/reference/information-schema-reference/replication-management/lmv-replication-status.md) to monitor the progress of a DR replication. Users can now view the aggregated replication status of each database (including partition-level details) and replication links between the primary and the secondary cluster to know if there is any lag in replication, and view statistics related to the lag.
* Added a new column, `type`, to the [Backup History Table](https://docs.singlestore.com/db/v9.1/manage-data/back-up-and-restore-data/backup-information-schema-tables.md). This column shows the type of backup and can be accessed by querying the `information_schema.MV_BACKUP_HISTORY` table.
* Tables that define a unique key using `UNENFORCED` now have an `INDEX_TYPE` of `NONE` in the information schema. The `INDEX_TYPE` was listed as `BTREE` in previous versions.

## Storage

* Implemented forwarding of data definition language (DDL) commands from child to master aggregator. Previously, these commands could only be run on a master aggregator. See [Cluster Management Commands](https://docs.singlestore.com/db/v9.1/reference/sql-reference/cluster-management-commands.md) for more information about how to enable this feature.
* Database-level DDL and clustering operations are now allowed to run in parallel across databases.
* Added new command [REBALANCE ALL DATABASES](https://docs.singlestore.com/db/v9.1/reference/sql-reference/cluster-management-commands/rebalance-all-databases.md), which rebalances the partitions on all databases in the cluster.
* Added the `FULL` option to [REBALANCE PARTITIONS](https://docs.singlestore.com/db/v9.1/reference/sql-reference/cluster-management-commands/rebalance-partitions.md) which takes effect when the number of partitions in the database is not divisible by the number of leaves. The extra partitions are placed on the leaves containing the fewest number of partitions.
* Added new command [PROMOTE AGGREGATOR … TO MASTER](https://docs.singlestore.com/db/v9.1/reference/sql-reference/cluster-management-commands/promote-aggregator-to-master.md) for all use cases that require promotion of a child aggregator to master, aside from permanent loss of the master aggregator.
* Added the information schema view [MV\_BACKUP\_STATUS](https://docs.singlestore.com/db/v9.1/reference/information-schema-reference/query-performance-workload-management-and-statistics/mv-backup-status.md) for monitoring backup progress.

## Universal Storage

* Added support for [INSERT … ON DUPLICATE KEY UPDATE](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-manipulation-language-dml/insert.md), [INSERT … IGNORE](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-manipulation-language-dml/insert.md), and [REPLACE](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-manipulation-language-dml/replace.md) on columnstore tables.
* Added the [columnstore as default feature](https://docs.singlestore.com/db/v9.1/create-a-database/creating-a-columnstore-table.md), which allows you to create a columnstore table using standard `CREATE TABLE` syntax.
* Added support for the `LOAD DATA ... [REPLACE | IGNORE | SKIP { ALL | CONSTRAINT | DUPLICATE KEY } ERRORS]` semantics for ingesting data into columnstore tables with unique keys. These semantics allow duplicate keys to be handled without returning an error to the client application. See example 10 in [LOAD DATA](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-manipulation-language-dml/load-data.md).
* Added support for [upserts](https://docs.singlestore.com/db/v9.1/query-data/advanced-query-topics/performing-upserts.md) on columnstore tables using Pipelines.

## Query Optimization

* Improved optimization of queries with large numbers of tables being joined. Join optimization is now significantly faster and adaptively handles very large join sizes to provide good execution plans while keeping query optimization time low. The engine variable `distributed_optimizer_max_join_size` is now deprecated and replaced by new variables `distributed_optimizer_unrestricted_search_threshold`, `distributed_optimizer_min_join_size_run_initial_heuristics`, and `singlebox_optimizer_cost_based_threshold` - see their descriptions in the [Sync Variables Lists](https://docs.singlestore.com/db/v9.1/reference/configuration-reference/engine-variables/list-of-engine-variables/#sync-variables-lists.md) for further information on configuring these variables.
* Added a new [engine variable](https://docs.singlestore.com/db/v9.1/reference/configuration-reference/engine-variables/list-of-engine-variables/#non-sync-variables-list.md)`profile_for_debug` which can be used to enable collection of additional data with `PROFILE` that can be displayed using `SHOW PROFILE JSON` and is useful for troubleshooting query optimizer issues. For more information, see [PROFILE](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-manipulation-language-dml/profile.md).
* Improved selectivity estimation by using sampling and histograms together, when both are available. This improvement only applies when `cardinality_estimation_level` is set to `7.3` or higher. By default, `cardinality_estimation_level` is set to `7.1`.
* Decreased query optimization cost of lookups of query plans from the on-disk plancache.

## Query Execution

* Decreased the in-memory size of query plans by up to 80%.
* Implemented optimizations for system information schema queries, resulting in significant performance increases for tables such as index\_statistics, column\_statistics, and columnar\_segments in particular.
* Added support for [EXPLAIN](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-manipulation-language-dml/explain.md) and [PROFILE](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-manipulation-language-dml/profile.md) queries in stored procedures.

## Usability and Programmability

* Added a new aggregate function [APPROX\_PERCENTILE](https://docs.singlestore.com/db/v9.1/reference/sql-reference/aggregate-functions/approx-percentile.md) that calculates the approximate percentile and is about 10 times faster than the [PERCENTILE\_DISC](https://docs.singlestore.com/db/v9.1/reference/sql-reference/window-functions/percentile-disc.md) and [PERCENTILE\_CONT](https://docs.singlestore.com/db/v9.1/reference/sql-reference/window-functions/percentile-cont-and-median.md) functions.
* The `USING` clause of query text is no longer captured as part of audit logging. It is now included in the output of [SHOW PROCESSLIST](https://docs.singlestore.com/db/v9.1/reference/sql-reference/show-commands/show-processlist.md) in a new column titled RPC Info.
* Added a new `JSON` function, [JSON\_AGG](https://docs.singlestore.com/db/v9.1/reference/sql-reference/json-functions/json-agg.md), that aggregates values as a `JSON` array.

  * Added a new variable, `json_agg_max_len`, which is the maximum string length [JSON\_AGG](https://docs.singlestore.com/db/v9.1/reference/sql-reference/json-functions/json-agg.md) can return in bytes.

    For more information, see the [Non-Sync Variables List](https://docs.singlestore.com/db/v9.1/reference/configuration-reference/engine-variables/list-of-engine-variables/#non-sync-variables-list-2.md).
* The `ALTER` permission is no longer required for `ANALYZE` (`SELECT` and either `ALTER` or `INSERT` are required).
* Added support for defining [User-Defined Variables](https://docs.singlestore.com/db/v9.1/reference/sql-reference/user-defined-variables.md), via `SELECT INTO @varname`.

## Ingest

* Added support for publishing data to Google Cloud Storage (GCS) via [SELECT … INTO GCS](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-manipulation-language-dml/select/#select-into-gcs.md).
* Added support for specifying the chunk size while uploading data to an Amazon S3 bucket via [SELECT … INTO S3](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-manipulation-language-dml/select/#select-into-s-3.md) to enable output of very large files.
* SingleStore Pipelines now supports new [Avro schema evolution](https://docs.singlestore.com/db/v9.1/load-data/load-data-from-files/load-data-from-avro-files/avro-schema-evolution-with-pipelines.md) capabilities. Hostname or IP address of the schema registry can be specified at the time of Pipeline creation; any changes to the schema can be easily viewed by the Pipelines. If fields are added to the Avro schema, the Pipelines can be modified without stopping and losing any offsets.
* Added new option, `max_retries_per_batch_partition`, for [Filesystem Pipeline Syntax](https://docs.singlestore.com/db/v9.1/reference/sql-reference/pipelines-commands/create-pipeline/#UUID-157f6a42-82bf-63ee-070c-4c4983c9f54c.md) and [ALTER PIPELINE](https://docs.singlestore.com/db/v9.1/reference/sql-reference/pipelines-commands/alter-pipeline.md). When set, this determines the number of retries that will be attempted for writing batch partition data to the destination table. Specifying fewer retries when there is a large amount of data to load can be useful for conserving resources; the reverse is true for increasing the number of retries for smaller tables where performance is less of a concern.
* Added a new option to the `CONFIG` clause of `CREATE PIPELINE` for Filesystem pipelines called `process_zero_byte_files`. Enabling this option ensures zero byte files are processed. Otherwise they are skipped by default.

## Small Fixes and Otherwise Uncategorized Items

* Improved the performance of columnstore batch deletes on certain column encodings.
* Reduced memory usage for columnstore updates and deletes.
* More concurrent [operations](https://docs.singlestore.com/db/v9.1/reference/sql-reference/operations-that-take-either-a-database-or-a-cluster-level-lock.md) are now allowed on the database level.
* Added `SORT KEY(...)` as an alias for `KEY(...) USING CLUSTERED COLUMNSTORE`.
* Added a new variable, `columnstore_row_value_table_lock_threshold`, that sets the threshold at which multiple inserts to a columnstore table with unique keys will switch from row value locking to table locking. For more information, see the [Sync Variables Lists](https://docs.singlestore.com/db/v9.1/reference/configuration-reference/engine-variables/list-of-engine-variables/#sync-variables-lists-2.md).
* Added two [engine variables](https://docs.singlestore.com/db/v9.1/reference/configuration-reference/engine-variables/list-of-engine-variables.md): `internal_columnstore_validate_blob_after_write` and `internal_columnstore_validate_blob_before_read` that control verification of the checksum of a blob immediately after it is created, and before reading it, respectively.
* Fixed a bug where some materialized common table expressions (CTEs) with cross database joins would not run because the table holding the CTE result set was assigned to the wrong database.
* Fixed an issue where `JSON_EXTRACT_STRING()` was not respecting `json_extract_string_collation` when extracting strings from columnstore tables.
* Fixed an issue that caused `RESTORE DATABASE FROM filesystem` to result in an unbalanced distribution of partitions among nodes in the restored database.
* Previously, running [ALTER VIEW](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-definition-language-ddl/alter-view.md) on a schema-bound view could cause recovery to fail to create a new schema-bound view referring to the altered view. This action has been disallowed.
* Changed how SingleStore looks for files when configuring SSL. SingleStore expects an absolute path (`/path/to/files`). If you specify a relative path (`./path/to/files`), SingleStore first looks for the path relative to the location of the `memsql.cnf` file. If that fails, SingleStore looks for the path relative to the current working directory. If neither of those paths work, the operation fails.
* `JSON_EXTRACT_BIGINT` now returns a SQL NULL value when the value of a key is JSON-NULL or otherwise undefined. Previously, this function returned 0 (not null) when using columnstore.
* Tables created with syntax such as `CREATE TABLE … AS SELECT` will no longer inherit `AUTO_INCREMENT behavior`. For example, if `CREATE TABLE table_1 AS SELECT * FROM table_2` is used to create `table_1` where `table_2` has an `AUTO_INCREMENT` column, it will be created as a non-auto-increment column in `table_1`.
* SingleStore now supports aliases for column names in a [PIVOT](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-manipulation-language-dml/pivot.md).
* `information_schema.MV_COLUMNSTORE_FILES` now includes files showing global secondary index disk usage.
* `information_schema.MV_BLOCKED_QUERIES` now shows queries on columnstore tables that are blocked.
* Added [connection link](https://docs.singlestore.com/db/v9.1/security/authentication/configuring-and-using-connection-links.md) feature that stores connection details (credentials and configurations) to supported data providers such as S3, Azure, GCS, HDFS, and Kafka. Permitted users can run commands such as `BACKUP`,`RESTORE`, `CREATE PIPELINE`, and `SELECT … INTO` without specifying the connection details.

## In this section

* [Maintenance Release Changelog](https://docs.singlestore.com/db/v9.1/release-notes/singlestore-memsql/7-3-release-notes/maintenance-release-changelog.md)

***

Modified at: July 29, 2024

Source: [/db/v9.1/release-notes/singlestore-memsql/7-3-release-notes/](https://docs.singlestore.com/db/v9.1/release-notes/singlestore-memsql/7-3-release-notes/)

(An index of the documentation is available at /llms.txt)