Wide or narrow tables

SynSQL serves two kinds of datasets with the same SQL and the same commands. They are stored differently, and they do not support exactly the same operations. This page tells you which kind you have, and what it changes. Every command page of this documentation says so when the two kinds behave differently.

Narrow tables: billions of rows

A narrow table has up to 1500 columns. Its rows are cut into partitions by the value of the hash key. Each partition is a table stored whole on a data broker, so a row is read in one place. The Gaia DR3 catalog is a narrow table: gaia_source has about 1.8 billion rows and 152 columns, in 600 partitions.

A narrow table supports joins (up to 16 tables), order by and limit, adding a column after deployment, and it can be deployed close to another table so that joins stay inside one partition.

Wide tables: millions of columns

A wide table has more than 1500 columns, or was created with create table T wide;. Its columns are cut into chunks of about 1500 columns. Each chunk is its own table on the data brokers, and the hash key columns are repeated in every chunk. Reading a row means reading its chunks and assembling them. A multi-omics table is wide: multi_omics has 585,011 columns in 391 chunks.

A wide table does not support:

• joins: a wide table cannot be one of the tables of a join;
• group by and order by;
• adding columns once the table is deployed (declare all columns before deploy table);
• deploy table T close to ....

A select on a wide table must select all the columns of the hash key. Filtering with or on columns that live in distant chunks forces the server to assemble every candidate row, which is expensive: measure before you rely on it. Inserting one row costs more than for a narrow table because every chunk receives its part.

How to know

describe single_cell;

The line Wide says no for a narrow table, and yes - 14 chunks (for example) for a wide one.

Which one do I need?

• Your data is a catalog: many rows, a number of attributes that you can list on a page. Use a narrow table. This is the normal case, and the only one that supports joins.
• Your data is a matrix: one row per patient, per cell or per sample, and thousands to hundreds of thousands of measures per row. Use a wide table.
• You do not choose the engine by name. SynSQL decides when you deploy: more than 1500 columns makes the table wide. Use create table T wide; to say it from the start.

Both kinds live in the same cluster, under the same accounts and the same privileges.