The total combined size of all rowstore tables is limited by the total available RAM on the leaf nodes in the cluster. It is important to reserve a reasonable amount of RAM, say 20%, for query execution. So the effective total rowstore table capacity will be reduced by this amount. See Memory Management and Identifying and Reducing Memory Usage for more details on configuring memory limits and troubleshooting out-of-memory conditions.

For applications that do many point lookups and small inserts, updates, and deletes, rowstore performs much better than columnstore. In general, since rowstores support a larger variety of workloads, they are a good starting point.

This section provides a conceptual overview of SingleStore DB’s rowstore and includes considerations for optimizing your database performance using the rowstore.

Creating a Rowstore Table

Rowstore is the default table storage format in SingleStore DB versions 7.1 and older (Columnstore is the default table storage format beginning in version 7.3). Typically you will specify a shard key and one or more indexes for a rowstore, although a shard key and indexes are optional. A primary key, which is enforced to be unique, is also supported.

Here is an example of a statement that creates a rowstore table:

CREATE TABLE products (
     ProductId INT,
     Color VARCHAR(10),
     Price INT,
     dt DATETIME,
     KEY (Price),
     SHARD KEY (ProductId)
);

We define a SHARD KEY to explicitly control the data distribution. We define it on ProductId since sharding on a high cardinality column or columns generally allows for a more even distribution and prevents skew. The KEY specified on Price causes an index to be created on the Price column.

It is also possible to randomly distribute data by either omitting the shard key, or defining an empty shard key SHARD KEY(), as long as no primary key is defined.

Rowstore keys

Rowstores with multiple indexes support very fast seeking to find one or a small number of rows via several different keys, or “access paths.” SingleStore DB rowstores are able to provide extremely fast lookup along multiple different access paths with low variance in response time from query to query.

The following statement creates a products table with keys on both Price and Color, and a unique (primary) key on ProductId.

CREATE TABLE products (
     ProductId INT,
     Color VARCHAR(10),
     Price INT,
     dt DATETIME,
     KEY (Price),
     KEY (Color),
     PRIMARY KEY(ProductId),
     SHARD KEY (ProductId)
);

Primary keys must contain all the columns in the shard key so they can be enforced efficiently by looking at data in only a single shard (partition). Creating a primary key without specifying a shard key automatically shards on the primary key.

You can also create a key by creating an index on a rowstore using the CREATE INDEX statement.

Rowstore persistence

Rowstore data is fully persistent and updates on rowstore tables are done in transactions. Persistence is implemented for rowstores using periodic snapshots of the in-memory data, and a write-ahead log, which are both stored on a file system to make them permanent. If a SingleStore node restarts, all its rowstore data will be recovered from the snapshots and log and the in-memory state for the rowstore will be rebuilt.

Porting applications to SingleStore DB rowstores

If you are porting an application from another database that uses a disk-based rowstore structure based on B-tree, ISAM or a similar access method, you should start by using a SingleStore DB rowstore with a key for each index on the original system. If the original system had a “heap” access method (unordered rowstore), you can start with a SingleStore DB rowstore with no key.

OLTP operations on large data sets

For OLTP operations on large tables, the total cost of provisioning enough RAM, and possibly enough servers, when using rowstores, can become a significant concern for some users. If this is the case, and the application doesn’t need the very fastest row lookup time, consider using columnstore tables with hash indexes on the most frequently-used lookup key columns instead. Or, for wide tables with many null values, the total cost of ownership (TCO) can be reduced by using rowstore tables with SPARSE data compression.

Related content

See The Story Behind SingleStore’s Skiplist Indexes for details about how standard rowstore table indexes are implemented in SingleStore DB.

Was this Article Helpful?

Thanks For Your Feedback!