Universal Storage in SingleStoreDB is a single table type that supports both analytical and transactional workloads. It stores data in columnar format, optimized for seekability. There have been multiple enhancements introduced over the years to better handle real-time, hybrid transactional and analytics workloads including hash index, sub-segment access[1] and seekable encoding, etc.
But our journey doesn’t stop there. In the latest version of SingleStoreDB, 8.1, we are introducing a new feature called Column Group, which can be used to significantly improve transactional workload performance.
But first, let’s revisit columnstores. The idea of a columnstore is to store table data in a column-oriented format to achieve better compression efficiency, and support column-wise query execution. There are substantial benefits to using columnstores in analytics scenarios, but there are also limitations and drawbacks in certain cases — one of which is transactional operations involving a wide table.
Our seek performance for columnstores — with support for tens of columns — is actually quite good [3], and can return results in low, single-digit milliseconds. But the number of table columns doesn't stop there. In fact, we have customers that use over 300 columns in one table!
To access a particular row in super wide tables, the query engine needs to open and decode a potentially large number of columnstore blobs, as each column is stored independently within its own blob. The overhead of initializing and decoding the blob metadata is non trivial, especially when dealing with a large number of blobs. Also, since data is stored in columnar format, there are a significant amount of random seeks on disk to reassemble a row. In other words, it’s IO-expensive.
Column Group is a new type of index we introduced in SingleStoreDB 8.1 to speed up row retrieval, especially from wide tables (see the graph later in this blog).It creates an on-disk, row-oriented representation of the table data living side-by-side to the columns blobs. The query engine makes sure the data in Column Group is always in-sync and consistent with columnstore data. During query execution, the engine combines Column Group and columnstore to deliver an optimal execution plan.
Usage
Column Group can be defined when you create a table. I will use the following example to show how to define a Column Group, and how it can help speed up the query. Let’s say that we have an employee table definition like this:
CREATE TABLE emp
(
id INT PRIMARY KEY,
name VARCHAR(256),
dept VARCHAR(16),
salary FLOAT,
manager INT,
...
COLUMN GROUP cg_full (*)
);Assuming this table has many columns (the ‘...’ indicates there are many more column definitions followed) and is considered to be a wide table, we create a Column Group named ‘cg_full’ using this clause ‘COLUMN GROUP cg_full(*)’. The * here means the Column Group covers all the columns in the table. We currently only support Column Group over the entire table.
Note that Column Group can also be added to an existing table through ‘ALTER TABLE’ statement, such as:
ALTER TABLE emp ADD COLUMN GROUP cg_full (*);Similarly, if Column Group is no longer needed, user can drop it through ‘ALTER TABLE’ statement:
ALTER TABLE emp DROP COLUMN GROUP cg_full;Now consider the following simple query:
SELECT * FROM emp WHERE id = 56231;This is a single-point lookup using primary key id. The query engine will lookup the primary key index and locate the row through the index. Now since the result only contains one row, and we project all the columns, the query engine will retrieve the entire row from the Column Group using a single IO.
Here is another example:
SELECT * FROM emp WHERE dept='IT';This query uses a filter to list all employees in the IT department. During the execution, the query engine still uses the columnstore for filtering execution, as this only requires loading the ‘dept’ column and it’s the most efficient way for evaluating the predicate. After filtering (assuming IT is a small department) there are only a few rows passing the filter. In this case, the query engine uses the Column Group to read the full rows from the ‘emp’ table to maximize the IO efficiency.
Here’s another example regarding updates:
UPDATE emp SET dept='IT' WHERE id = 56231;This query updates the department name to ‘IT’ for an employee with ID 56231. Although the query only affects the IT column, the SingleStoreDB query engine still needs to read the entire row to implement Row Level Locking [2]. In this case, Column Group will be used to retrieve the row and load it into the in-memory segment. Similarly, Column Group can greatly improve delete query performance, since delete also requires reading and locking the entire row.
To summarize, a typical workflow is illustrated in the following diagram:
- User creates a columnstore table with Column Group
- When the in-memory segment is flushed to the disk (in addition to creating the columnstore blobs), we also create a Column Group blob (marked as green in the diagram)
- During query execution, columnstore hash indexes and columnstore blobs can be used for effective filtering
- The Column Group blob is used to materialize the rows post-filtering when only a small fraction of rows are retrieved.
As you can see, the query engine can dynamically choose different storage structures, combining hash indexes, columnstore and Column Group to deliver the most optimal query execution. This is only possible because we designed Column Group and columnstore blobs to live in the same columnstore segment — thus they can share the same row locators and indexes.
Test Results
To benchmark the Column Group performance, we designed a scenario that involves running high concurrency, single-key CRUD transactions on wide tables. We have three configurations for tables with 64, 128 and 256 columns respectively, measuring the Transactions Per Second (TPS) on a single leaf node with or without Column Group.
As you can see from below, Column Group increases the transaction throughput by 6x- 8x.
We also tested on the standard TPC-C benchmark, which measures the transactional performance of a database. The Column Group is also helpful in the test as most of the queries involve reading and updating the entire rows. We see 20% - 30% improvement in tpmC (Transactions per Minute), depending on the cluster configuration.
Conclusion
As we continue enhancing Universal Storage to efficiently support both transactional and analytical workloads, Column Group improves the transactional performance on wide tables. It addresses the limitations of columnstore, and provides a new option for the user to implement high-performance transactions.
Get started for free with SingleStoreDB.
