Write path

Applies to VAST Cluster 5.5. Examples use the VAST DataBase Python SDK 2.1.0.

This page explains what happens to rows after you insert, update, delete or import them, and why some loading patterns behave better than others. For how-to guidance, see ingest and updates and deletes.

Writes, deletes, sorted-table inserts and column drops each finish in the background after you commit.

Inserts

When you insert rows, the cluster gives them new row IDs and writes each column's values for those rows into the cluster's write buffer, fast storage-class memory (SCM) in the cluster's storage enclosures, tagged with your transaction. Nothing is visible to other transactions until you commit; see transactions.

After the commit, background work moves the data from the write buffer to the cluster's flash (NVMe SSDs), into its long-term place in the table's layout. You don't have to wait for this: committed rows can be queried straight away.

Small writes and background compaction

Each insert or update request writes its own small chunk for each column it touches. Many small requests leave many small pieces for the same rows, and queries have to read all of them. A background job later rewrites fragmented ranges into fewer, larger chunks. It works on older ranges first and leaves recently changed ones alone.

So insert in batches that are as large as you reasonably can. The same applies to sorted tables; see sorted tables. The Python SDK splits batches that are too big for one request for you; see ingest.

Imports

import_files() doesn't send data through your client. The cluster opens each Parquet file in its bucket, gives the whole file's rows new row IDs at once, and writes the file column by column. An import therefore produces long runs of consecutive rows and large column chunks, which is why it's the better path for bulk loads.

Two details follow from how the cluster tracks imports:

• Re-importing an unchanged file does nothing. The cluster recognises a file by the stored object itself. Through the Python SDK, the second import is skipped without an error. See ingest.
• key_names imports rely on row order. With ImportConfig(key_names=[...]), the cluster finds the table row that matches the file's first row, then writes the file's rows into the following rows in order. The file's rows must be in the same order as the table's, with no gaps; a mismatch fails the import. The KB says the keys must be "ordered in the same order"; that the first row is the anchor is from the server.

Updates

An update writes new values for only the columns you change, at the rows' existing positions. The row keeps its row ID. Older versions stay readable by transactions and snapshots that started earlier, and are cleaned up later in the background. Update only the columns that actually change.

If the table has semi-sorted projections, the same update request updates them too.

Deletes

A delete doesn't remove data. It marks each row as deleted (a tombstone). Later, a background job removes the deleted rows' data and frees space once no running transaction can still see those rows. So:

• deleting rows doesn't free space at commit,
• the table's row count in get_stats() goes down only after that background cleanup.

Because a delete writes only its marker, it doesn't conflict with another transaction updating other columns of the same row. Both commit and the row ends up deleted; see transactions.

Adding and dropping columns

A table's list of columns is stored like a small table of its own, and adding or dropping a column changes that list. Like any other write, the change is part of your transaction: other transactions see it only after you commit, and a rollback undoes it.

Adding a column doesn't rewrite any data. Rows that were already in the table read null for the new column; see when incoming data has new columns.

Dropping a column works like a delete. The column is marked deleted in the column list, so it disappears for transactions that start after you commit. A transaction that began before the drop still sees the column and can still read it, as snapshot isolation says. Its data isn't removed at commit: a background job removes it later and frees the space, once no running transaction can still see the column. So, like deleting rows, dropping a large column doesn't give space back straight away.

Some columns can't be dropped:

• a sort column of a sorted table,
• vastdb_rowid, once the table has user-defined row IDs,
• any column of a vector-indexed table: you can't add or drop columns there at all (table structures).

The first two are refused with BadRequest (400), code TabularUndroppableColumn; the vector-indexed case with BadRequest (400), code InvalidArgument. See errors.

Partitioned tables

When you insert into a partitioned table, the cluster works out each row's partition, groups the request's rows by partition, and writes each group into that partition's own storage. The partition column values are kept once per partition, not in every row. Partitioned tables do best when inserts are collected per partition and written in batches (database partitioning). A batch spread thinly over many partitions writes many tiny pieces.

Row IDs on partitioned (and sorted) tables also record where the row is stored, which is why $row_id is a 128-bit decimal there instead of an integer.

An update can't move a row to another partition. Writing back a partition column's existing value is accepted; a different value is rejected.

Deleting partitions. delete_partitions() drops each partition's storage in one step, in its own internal transaction that commits at once. That's why it isn't part of your transaction and a rollback can't undo it (database partitioning: "non-ACIDic"). Empty partitions are cleaned up later in the background.

Sorted tables

Rows inserted into a sorted table land unsorted in an area for recent rows. A background sorter takes them in blocks of 512K rows, sorts each block into a sorted run, and leaves a forwarding pointer from each row's old position to its new one. A background merger then merges overlapping runs into larger ones. Each move rewrites the rows. That's the extra work sorting costs, and why the row count can briefly grow while sorting runs (see sorted tables).

The newest block isn't sorted until it's full. A block is sorted only once later inserts have moved past it. So a sorted table with fewer than 512K rows isn't sorted at all, and in a larger table the most recent rows wait until their block fills. On a table that's both sorted and partitioned, each partition is sorted separately, so a partition with fewer than 512K rows stays unsorted.

What sorting_done() means. It's True when the background sorter has nothing left to do. The newest, unfinished block doesn't count, so a small table reports done even though none of its rows are sorted. sorting_score() rises as sorting and merging catch up.

Updates and deletes follow the row. Because of the forwarding pointers, a $row_id you read before a sort or merge still works afterwards. Writing back a sort column's existing value is accepted; a different value is rejected, because sort columns can't be updated. Updates don't re-sort anything.

Semi-sorted projections pick up new rows the same way: a block is added to a projection once the block is complete.

Table statistics

The row count and size from get_stats(), and the estimates in a partitioned table's partitions() view, are kept up to date by background work. They aren't part of your transaction. So for a while they can:

• miss rows that were just committed,
• include rows from transactions that are still open,
• still count deleted rows that haven't been cleaned up.

On sorted and partitioned tables, the totals are rolled up from the table's parts and can lag for minutes. To check a load, count the rows with a query instead.

What this means for your loads

See also

• How tables are stored
• Transactions
• Ingest and updates and deletes