SingleStore

SingleStore (formerly MemSQL) is a proprietary, cloud-native database designed for data-intensive applications. A distributed, relational, SQL database management system (RDBMS) that features ANSI SQL support, it is known for speed in data ingest, transaction processing, and query processing.

SingleStore primarily stores relational data, though it can also store JSON data, graph data, and time series data. It supports blended workloads, commonly referred to as HTAP workloads, as well as more traditional OLTP and OLAP use cases. For queries, it compiles Structured Query Language (SQL) into machine code. The SingleStore database engine can be run in various Linux environments, including on-premises installations, public and private cloud providers, in containers via a Kubernetes operator, or as a hosted service in the cloud known as SingleStore Managed Service.

History
On April 23, 2013, SingleStore launched its first generally available version of the database to the public as MemSQL. Early versions only supported row-oriented tables, and were highly optimized for cases where all data can fit within main memory. This design was based on the idea that the cost of RAM would continue to decrease exponentially over time, in a trend similar to Moore's law. This would eventually allow most use cases for database systems to store their data exclusively in memory.

Shortly after launch, MemSQL added general support for an on-disk column-based storage format to work alongside the in-memory rowstore. The decreases in cost of memory slowed over time, and the market for purely in-memory database systems largely failed to materialize, with increasing demand for disk-based OLAP workloads. Thus, over time, MemSQL's columnstore became a major focus and a crucial feature for customers.

On October 27, 2020, MemSQL rebranded to SingleStore to reflect a shift in focus away from exclusively in-memory workloads. The new name highlights the goal of achieving a universal storage format capable of supporting both transactional and analytical use cases.

In its current product release, v.7.5, SingleStore became the first and only database to combine separation of storage and compute plus system of record into a single platform. Headquartered in San Francisco, California, in June 2021 SingleStore opened an office in Raleigh, North Carolina. As part of the office opening, SingleStore launched Launch Pad, a center for innovation to incubate and prototype solutions. Its other offices include Sunnyvale, California, Seattle, Washington, and Lisbon, Portugal.

Funding
In January 2013, SingleStore announced it raised $5 million. Since then, the company has raised $318.1M from various investors including Khosla Ventures, Accel, Google Ventures, Dell Capital and HPE, among others.

Row and column table formats
SingleStore can store data in either row-oriented tables ("rowstores") or column-oriented tables ("columnstores"). The format used is determined by the user when creating the table.

Rowstore tables, as the name implies, store information in row format, which is the traditional data format used by RDBMS systems. Rowstores are optimized for singleton or small insert, update or delete queries and are most closely associated with OLTP (transactional) use cases. Data for rowstore tables is stored completely in-memory, making random reads fast, with snapshots and transaction logs persisted to disk.

Columnstores are optimized for complex SELECT queries, typically associated with OLAP (analytics) and data warehousing use cases. As an example, a large clinical data set for data analysis is best stored in columnar format, since queries run against it will typically be ad hoc queries where aggregates are computed over large numbers of similar data items. Data for columnstore tables is stored on-disk, supporting fast sequential reads and compression that typically reaches 5-10x.

Indexing
Rather than the traditional B-tree index, SingleStore rowstores use skiplists optimized for fast, lock-free processing in memory. Columnstores store data indexed in sorted segments, in order to maximize on-disk compression and achieve fast ordered scans. SingleStore also supports using hash indexes as secondary indexes to speed up certain queries.

Distributed architecture
A SingleStore database is distributed across many commodity machines. Data is stored in partitions on leaf nodes, and users connect to aggregator nodes. A single piece of software is installed for SingleStore aggregator and leaf nodes; administrators designate each machine’s role in the cluster during setup. An aggregator node is responsible for receiving SQL queries, breaking them up across leaf nodes, and aggregating results back to the client. A leaf node stores SingleStore data and processes queries from the aggregator(s). All communication between aggregators and leaf nodes is done over the network using SQL. SingleStore uses hash partitioning to distribute data uniformly across the number of leaf nodes.

Real-time streaming data ingestion
SingleStore Pipelines is an integration technology built-in which provides streaming data ingestion in parallel from distributed data sources. It provides live de-duplication as data is ingested, exactly once semantics from message brokers, and simplifies architectures by reducing or eliminating the need to ETL middleware. Transformation and ML integration can be done via SingleStore Pipeline Transforms by embedding a binary. SingleStore Pipelines connect to data sources such as Apache Kafka, Apache Spark, Amazon S3 buckets, Microsoft Azure Blob Storage Google Cloud Storage, HDFS, or files on disk and supports formats such as JSON, Parquet, Avro, and CSV. Because of the lock-free skip lists, queries can retrieve the data as soon as it lands, but are not blocked from continuing while data is ingested.

Bottomless storage
Bottomless storage separates storage and compute for SingleStore. Data files persist to S3 or comparable blob storage and NFS, asynchronously. The “blobs” are the compressed, encoded data structures that back the columnstore. High availability is maintained in the SingleStore cluster for the most recent data but long-term storage moves to blob storage. Blobs that are not queried are automatically deleted from SingleStore node’s local disk, allowing the cluster to hold more data than available disk, making the cluster’s storage “bottomless.” New replicas do not need to download all blob files to come online, creating and moving partitions. Bottomless acts as a “continuous backup” that obviates the need for traditional disaster recovery and backup cloud-operation procedures. It also supports larger petabyte-sized datasets for historical analytics.

Durability
Durability for the in-memory rowstore is implemented with a write-ahead log and snapshots, similar to checkpoints. With default settings, as soon as a transaction is acknowledged in memory, the database will asynchronously write the transaction to disk as fast as the disk allows.

The on-disk columnstore is actually fronted by an in-memory rowstore-like structure, indexed using a skiplist. This structure has the same durability guarantees as the SingleStore rowstore. Apart from that, the columnstore is durable, since its data is stored on disk.

Replication
A SingleStore cluster can be configured in "High Availability" (HA) mode, where every data partition is automatically created with master and slave versions on two separate leaf nodes. In HA mode, aggregators send transactions to the master partitions, which then send logs to the slave partitions. In the event of an unexpected master failure, the slave partitions take over as master partitions, in a fully online operation with no downtime.

Distribution formats
SingleStore can be downloaded for free and run on Linux for systems up to 4 leaf nodes of 32 gigs RAM each; an Enterprise license is required for larger deployments and for official SingleStore support. SingleStore clusters can be managed in containers using the SingleStore Kubernetes Operator. SingleStore is also available as a managed service named SingleStore Managed Service, available in various regions in Google Cloud and Amazon Web Services, with a Microsoft Azure implementation promised for the near future. The underlying engine and potential system performance are identical in all distribution formats.

SingleStore ships with a set of installation, management, and monitoring tools called SingleStore Tools. When installing SingleStore, Tools can be used to set up the distributed SingleStore database across machines. SingleStore also provides a browser-based query and management UI called SingleStore Studio, which provides query processing and database monitoring, and shows health and informational details about the running cluster.

Recognition
In December 2021, SingleStore was recognized in the Magic Quadrant for Cloud Database Management Systems published by Gartner for the first time. SingleStore was also included in Deloitte’s Technology Fast 500 North America, San Francisco Business Times Fast 100, Dresner Industry Excellence and Inc 5000 awards in 2020. The company is part of the Cloud Native Computing Foundation and Bytecode Alliance.