SingleStore DB 7.3 Available

By Rick Negrin, product management team, SingleStore

SingleStore, Inc. has announced the availability of SingleStore DB 7.3 for download.

DB 7.3 is also available on SingleStore Managed Service, the company’s elastic cloud database, available on public cloud providers around the world.

The company is building the next database platform. SingleStore DB is a distributed operational database that delivers speed and scale. As the leading NewSQL platform for real-time analytics and augmented transaction processing ^[Ron19], it can handle all workloads in the cloud or in data center. This release provides improved ingestion and multi-way join efficiency to further strengthen SingleStore for mission-critical applications providing transaction processing, operational analytics, and more.

This blog post describes the major features of SingleStore DB 7.3 self-managed software and SingleStore Managed Service in some detail.

Why cloud world needs to simplify to accelerate
The proliferation of special-purpose datastores is complicating cloud infrastructure, inflating cloud bills, and adding unnecessary latency in copying and moving data from multiple operational stores to analytical stores. To tame the growing cloud complexity and data infrastructure sprawl, organizations should look to solutions like SingleStore that unify workloads across operational and analytical use cases without sacrificing the speed, scale, and flexibility which makes special-purpose datastores attractive. With fewer types of datastores and less data movement among them, data infrastructure is simplified and there are fewer skill sets to maintain allowing to focus on accelerating business.

Major features in SingleStore DB 7.3
Along with improvements across the product, SingleStore DB 7.3 provides notable features in the areas below:

Programmability
SingleStore supports the MPSQL language for programming internal extensions, including stored procedures and user-defined functions. The 7.3 release enhances this capability by providing additional flexibility for developers in these aspects:

• Query plans are smaller and thus use less memory than before.

• The new JSON_AGG function takes a rowset with an arbitrary set of relationships and returns a single JSON object.

• The new APPROX_PERCENTILE function does fast and accurate percentile calculations over large data sets and can be up to 10X faster than standard percentile functions like PERCENTILE_CONT.

• User-defined variables allow you to write statements that look like SELECT INTO @varname. User-defined variables have session scope, and you can use them across queries. Customers frequently asked for UDVs since they are convenient when building complex SQL scripts.

• Now you can EXPLAIN and PROFILE queries within stored procedures for easier query tuning.

Flexibility for developers
SingleStore DB now has DDL forwarding, which allows to send both DDL and DML statements to any aggregator. This simplifies things for developers so they don’t have to remember which endpoint to use based on the type of operation.

Cloud-native ingestion enhancements
This release has enhancements for data ingestion that are helpful for cloud deployments:

• SELECT … INTO GCS allows you to load data from SingleStore DB into a Google Cloud Store bucket. The syntax is similar to the existing SELECT … INTO S3 command.

• A new configuration option multipart_chunk_size_mb allows you to load up to 5TB of data per partition when running SELECT … INTO S3.

• SingleStore DB pipelines now integrate with Confluent schema registry, making it easy to add and remove fields from Avro schemas.

Multi-way join efficiency improvements
For queries that join more than 18 tables, the query optimizer generates plans more quickly and produces query plans that run faster. This enhancement is especially helpful for large enterprise customers.

Universal storage
SingleStore Universal Storage technology is a suite of features that the company is delivering over several releases. The aim of this technology is to support real-time analytics and online transaction processing (OLTP) on SingleStore, with performance and low TCO.

Universal Storage is an extension of firm’s columnstore technology that excels at analytics and which also improves OLTP, including support for indexes, unique keys, seeks, and fast, highly selective, nested-loop-style joins. Because the data is highly compressed and doesn’t all have to fit in RAM, Universal Storage gives excellent TCO.

In release, SingleStore DB extends Universal Storage with these features:

• INSERT … ON DUPLICATE KEY UPDATE allows to update an old row when trying to insert a unique key that already exists. This operation is commonly known as UPSERT.

• You can now set the default table type to columnstore by setting the default_table_type global variable appropriately.

• Simplified syntax for creating columnstore tables where you can write SORT KEY (…) instead of KEY (…) USING CLUSTERED COLUMNSTORE.

• Universal storage is now the default table for SingleStore Managed Service.

For a detailed explanation of what’s new in Universal Storage, read Eric Hanson’s blog post.

Greater operations visibility and resilience
Release 7.3 has improvements that make the product easier and more robust to operate and monitor:

• The new PROMOTE AGGREGATOR command lets switch the master aggregator of a cluster without stopping the old master aggregator. This helps keep a cluster online during maintenance.

• The SHOW REPLICATION STATUS command now has columns that give a clear picture of how far secondary partitions are from primary partitions. In particular, the ESTIMATED_CATCHUP_TIME_S column shows how long it will take the secondary partition to catch up to the primary one.

• A new management view LMV_BACKUP_STATUS permits to track the progress of a running backup along with the estimated time remaining.

• SingleStore DB lets create resource pools for specifying resource limits so that non-critical workloads do not overwhelm your cluster. You can now run the BACKUP and RESTORE commands in resource pools to manage how much CPU they use.

These are just a few of the new features you’ll get when you upgrade to SingleStore DB 7.3. For the full list of features, see the release notes at docs.singlestore.com.

Conclusion
The SingleStore DB 7.3 release delivers an advance in ability to support system-of-record applications on SingleStore, including improvements to Universal Storage (upserts, default table type); replication status monitoring; easier Avro schema migrations; faster large joins. 

^[Ron19] Adam Ronthal, There is Only One DBMS Market!,, Gartner, 2019.

Resources:
7.3 release notes      
On-demand seminar      
Video : What’s new in SingleStore 7.3