Choosing a Table Storage Type
On this page
The most important consideration when creating a table is its data layout.
The following table enumerates the strengths and intended uses of the columnstore and rowstore.
Flash, SSD, or Disk-based Columnstore/Universal Storage |
In-Memory Rowstore |
---|---|
Analytical workloads |
Operational/transactional workloads |
Fast inserts over a small or large number of rows |
Fast inserts and updates over a small or large number of rows |
Fast aggregations and table scans via sub-segment access |
Random seek performance |
Updates/deletes are rare |
Updates/deletes are frequent |
Compression |
|
Conceptual Differences Between Columnstore and Rowstore
Column-oriented stores or columnstores
(columnstore is also referred to as Universal Storage) treat each column as a unit.
In addition, columnstore tables utilize a feature called sub-segment access with allows quick retrieval of a row once its position is known via hash indexing.
Columnstores are usually batch-loaded for optimal performance, although small-batch and streaming insert to columnstores can also perform well.
In general, columnstore tables are effective when your workload matches the following descriptions as much as possible:
-
A large number of rows are scanned sequentially (i.
e. millions of rows or >5% of the table) -
Aggregation happens over only a few columns (e.
g. <10 columns) -
Small updates and deletes are rare; most affect large batches of rows
A simplified example of how columnstore data is physically stored is shown here:
Columnstore
ProductId |
---|
1 |
2 |
3 |
4 |
Color |
---|
Red x 2 |
Black |
White |
x 2
indicates that the value is repeated twice.
Price |
---|
10 |
20 x 3 |
x 3
indicates that the value is repeated three times.
Row-oriented stores or rowstores
are the most common type of data stores used by relational databases.
With rowstore, data is stored in lock-free indexes for great performance at high concurrency.
Finally, when rowstore tables are created with SPARSE compression, the tables are allowed to store more data in the same amount of RAM.BIGINT
, INT
, MEDIUMINT
, SMALLINT
, TINYINT
, DATE
, DATETIME
, TIME
, TIMESTAMP
, and VARCHAR
.
Logical table and rowstore representation
ProductId |
Color |
Price |
---|---|---|
1 |
Red |
10 |
2 |
Red |
20 |
3 |
Black |
20 |
4 |
White |
20 |
Related Topics
-
Training: SingleStoreDB Storage
Check Your Understanding
Q: Do columnstore tables use memory?
A: Absolutely – SingleStoreDB uses the operating system disk buffer cache to cache segment files in memory.
Q: Do rowstore tables ever write to disk?
A: Yes – data in a rowstore is also written to the transaction log on disk so that it can be recovered when SingleStoreDB is restarted.
Q: Are rowstore tables always faster than columnstore tables?
A: No, columnstore tables are faster on some workloads – if the workload is batch inserts and sequential reads (e.
Q: What if I need small high-concurrency updates and my data doesn’t fit in memory?
A: You can also use columnstore tables while setting hash index(es) on columns you regularly filter on.SPARSE
compression.
Related Topics
-
Training: SingleStoreDB Storage
Last modified: February 22, 2023