Alpha

These pages are built from main, turborel 0.0.0. Anything may change from one build to the next: SQL, API, protocol and files alike.

Appendix G: Table Options (extension)

Each option of a table of the built-in storage engines, rowstore and columnstore, as INFORMATION_SCHEMA.TABLE_OPTIONS lists it. The options whose names begin COLUMNSTORE are the column store's alone; the others are both's. A table sets one by CREATE TABLE ... WITH ( ... ), and changes it by ALTER TABLE SET ( ... ); one set to DEFAULT takes its default again. Changed says how a change is made: in place, the rows as they are, or by a rebuild, the table made anew and its rows copied, as ALTER TABLE ... USING makes it.

Option Default Values Changed
COLUMNSTORE.BLOOM none AT MOST 8 OF THE TABLE'S COLUMNS, BY COMMAS in place
COLUMNSTORE.COMPRESSION AUTO ONE OF AUTO, NONE in place
COLUMNSTORE.FREEZE_DELAY 0 INTEGER FROM 0 TO 31622400 in place
COLUMNSTORE.FREEZE_ROWS 0 INTEGER FROM 0 TO 9223372036854775807 in place
COLUMNSTORE.GROUP_ROWS 65536 INTEGER FROM 2048 TO 65536 in place
COLUMNSTORE.LAYOUT COLUMNS ONE OF COLUMNS, PAX in place
COLUMNSTORE.ORDER none AT MOST 8 OF THE TABLE'S COLUMNS, BY COMMAS in place
COLUMNSTORE.REWRITE_FRACTION 0.2 NUMBER FROM 0 TO 1 in place
ENCODINGS none ENCODINGS, BY COMMAS in place
OUT_OF_LINE 0 INTEGER FROM 0 TO 1073741824 rebuild
STATISTICS.BUCKETS 100 INTEGER FROM 1 TO 10000 in place
STATISTICS.REFRESH_FRACTION 0.1 NUMBER FROM 0 TO 1000 in place
STATISTICS.REFRESH_ROWS 50 INTEGER FROM 0 TO 9223372036854775807 in place
STATISTICS.SAMPLE 10000 INTEGER FROM 1 TO 1000000 in place
TRANSACTIONS.HELD_BYTES 1048576 INTEGER FROM 4096 TO 1099511627776 in place
TRANSACTIONS.TRACKED_ROWS 65536 INTEGER FROM 1 TO 9223372036854775807 in place

Each in a sentence:

  • COLUMNSTORE.BLOOM: The columns, by commas, of which a table of the column store keeps a Bloom filter of each 1024 rows of each group it freezes, so an equality or IN on one skips the rows that cannot hold its values, groups frozen before written again so in the background; none if empty.
  • COLUMNSTORE.COMPRESSION: Whether a table of the column store compresses the chunks of the groups it freezes from then on: AUTO, each in the encoding that takes fewest bytes; NONE, its values as they are.
  • COLUMNSTORE.FREEZE_DELAY: The seconds a row of a table of the column store stays as it was written after its commit before it may be frozen, so rows changed soon after are not.
  • COLUMNSTORE.FREEZE_ROWS: The rows committed past the last group after which a table of the column store freezes them; 0 for COLUMNSTORE.GROUP_ROWS.
  • COLUMNSTORE.GROUP_ROWS: The rows of each group a table of the column store freezes; groups frozen before keep theirs.
  • COLUMNSTORE.LAYOUT: How a table of the column store lays out the groups it freezes: COLUMNS, each column's values on pages of their own, so a scan of a few columns reads only theirs; PAX, each page a range of rows, a chunk of each column on it, so a row read whole reads one page. Groups frozen before are written again so in the background.
  • COLUMNSTORE.ORDER: The columns, by commas, by which a table of the column store sorts the rows of each group it freezes, ascending, so a condition on the first skips more of them, groups frozen before sorted again in the background; none, in the order they were written.
  • COLUMNSTORE.REWRITE_FRACTION: The share of a frozen group's rows changed, deleted or added to, past which a table of the column store writes the group again with them; 0 for never.
  • ENCODINGS: The registered encodings, by commas, such as a compression or an encryption with its key (AES-256-GCM:KEY, ChaCha20-Poly1305:KEY), that the table's rows pass through as they are written, in its storage and the log; rows written before a change are read through their own, until the table is sealed again (turboreld ... reseal). See Encryption.
  • OUT_OF_LINE: Values of varying width longer than this, in bytes, are kept out of their row's record; 0 for a quarter of a page.
  • STATISTICS.BUCKETS: The buckets of each column's histogram.
  • STATISTICS.REFRESH_FRACTION: The share of the table's rows changed, and STATISTICS.REFRESH_ROWS more, after which its statistics are gathered again.
  • STATISTICS.REFRESH_ROWS: The changes past STATISTICS.REFRESH_FRACTION of the rows after which the statistics are gathered again.
  • STATISTICS.SAMPLE: The most values of each column the statistics sample, a character string by its first 64 characters, a binary string its first 64 octets, a geometry by its envelope.
  • TRANSACTIONS.HELD_BYTES: The bytes of a transaction's changes to the table held in memory, to undo them and check others' reads by; past them, changes are written to a spill area, and a committed one's rows kept as each column's least and greatest value.
  • TRANSACTIONS.TRACKED_ROWS: The rows of a transaction's reads, or writes, of the table tracked one by one to check its commit, and its spatial windows; past them, every row is counted, or the windows one rectangle holding them all, which may refuse a commit that need not be.

The column store

A table of the column store, CREATE TABLE ... USING columnstore, keeps the rows written to it as a table of the row store does, each row's values together, so writing and fetching a row by its key cost what they cost there. Once a group's worth of them, COLUMNSTORE.GROUP_ROWS, is committed and no transaction may still see them otherwise, they are frozen: written again as a group of each column's values together, and the least and greatest of each page of a column's values kept. The database freezes them in the background, soon after, so no transaction waits for it; queries read the table meanwhile as before, the rows not yet frozen from where they were written. A query that reads a few columns of many rows then reads only those columns' pages, and a condition on a column skips, 1,024 rows at a time, the values it cannot match. Each page of a column's values is compressed, unless COLUMNSTORE.COMPRESSION is NONE: a value the same throughout kept once, numbers kept as their differences from the least, a number repeated over rows in a row kept once for them all, an approximate number written as a decimal of few digits kept as those digits, character strings alike in their parts, as addresses, names or paths are, kept as codes of those parts, or few distinct values kept once each, whichever takes fewest bytes, so a page holds more of them. Strings so coded take far fewer pages than plain, and are read as fast. Approximate numbers so kept are read back exactly as written, in fewer pages than plain. The more of a table's rows are frozen, and the fewer of its columns a query reads, the faster it reads them.

A frozen row may be updated or deleted as any other; it is then kept as rows written are, its frozen values no longer read, until a fifth of its group's rows are so (COLUMNSTORE.REWRITE_FRACTION): the group is then written again with them, the deleted rows gone from it. Fetching one frozen row by its key reads a page of each of its columns, so it costs more than in a table of the row store. COLUMNSTORE.FREEZE_DELAY keeps rows that are changed soon after they are written from being frozen while they still are.

COLUMNSTORE.ORDER sorts the rows of each group as it is frozen, or written again, by the columns it names, ascending, nulls as ORDER BY puts them: each page of the first column's values then holds a narrow range of them, so a condition on that column skips most pages. A condition on a column the rows were in the order of before then skips fewer, and fetching a row by its key costs a little more. Once the option is set or changed, groups frozen before are written again in the background, sorted as it says, a few at a time, until every group is; queries read them as before meanwhile, in either order. A query ordered by the first columns the option names, each ascending, ORDER BY S or ORDER BY S, G of an option naming S and G, reads the rows in that order as it scans them, merging the groups', and needs no sort; so does one by them each descending, ORDER BY S DESC or ORDER BY S DESC, G DESC, reading them in that order backward. One mixing the two, ORDER BY S DESC, G, is sorted. Of FETCH FIRST, such a query sorts no more than the first rows it asks for of the rows not yet frozen and of each group frozen before the option was set. A column the option names is not dropped until it names it no more; each is named as an identifier is, a regular one in any case, a delimited one in double quotes.

COLUMNSTORE.BLOOM keeps, of each 1,024 frozen rows of each column it names, a Bloom filter: about 10 bits for each row, from which a query learns that a value is surely not among those rows, or may be. A condition column = value, or column IN (...), of a column so named then reads only the rows that may hold one of its values, though they lie scattered through the table, where its least and greatest values skip none: of many frozen rows of distinct values in no order, finding one value reads only the few that may hold it, where without the filters it reads them all. A value not among the rows is taken as perhaps among them about once in 120 times, and those rows are then read and judged as any are. The filters take about 1.25 bytes for each row, a sixth as many again as a column of numbers of 8 bytes plain, so name only the columns a query looks up by equality; a column of few values, each among most 1,024 rows, gains nothing. Rows not yet frozen are read as before. Once the option is set or changed, groups frozen before are written again in the background, as for COLUMNSTORE.ORDER, and it names columns as that option does.

COLUMNSTORE.LAYOUT says how a frozen group is laid out on its pages. COLUMNS, the default, puts each column's values on pages of their own: a query reads only the pages of the columns it names, which suits scans of a few columns of many rows. PAX puts on each page a range of the group's rows, as many as fit, the values of each column of them apart on it: a query that reads a row whole, as by its key, reads one page where COLUMNS reads one for each column, at some cost to a scan of a few columns, which then reads every column's values. Each column's values are kept in the same encodings either way, and a query gives the same rows. A row too long for a page with the rest is laid out as COLUMNS lays it out. Once the option is changed, groups frozen before are written again in the background, as for COLUMNSTORE.ORDER.

Of each frozen group the database keeps in memory only a summary: the range of rows it holds, and each column's least and greatest values and nulls, so a query passes over a group whose summary says no row of it can match without reading more of it. The rest of what describes a group, of each 1,024 rows of each column, is read as a query needs it, and up to 16 MiB of it kept for the queries after, of MEMORY.BUDGET_BYTES: INFORMATION_SCHEMA.MEMORY_RESERVATIONS lists it as the headers of frozen row groups, a MiB at a time. What was read longest ago is given up first, and read again when it is needed.

What a table holds, and what a query of it gives, is the same in either store; only how fast each query runs differs. ALTER TABLE ... USING moves a table to the other, its rows copied; the setting TABLES.ENGINE gives tables created without USING theirs.

Plug-in engines declare options of their own, listed alike.