Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

The Synapse tables feature has been in beta for a few months and has been growing in popularity.  I thought it would be a good time to capture some of design decision decisions we made and document the current state of the feature.

Table Features

The table's tables feature allows Synapse users to store their tabular data in a structured "table".  Each table has a schema that defines the columns' data type types and namenames

Column Types

  • Integer
  • String  - Up to 1000 characters per cell.
  • Double
  • Date
  • Boolean
  • Entity - A link to any Synapse entity.
  • Link - Generic link to any URL.
  • File - A reference to a FileHandle and functions much like a BLOB.

...

Most table operation are "Asynchronous" meaning the client starts a job and is issued a JobId that is then used to fetch but the status and the result of the Job.  The current supported operation are:

  • SQL-like Query - A SQL-like query language is supported to query against a single table (joins across tables is are not supported).  SQL includes , pagination, sorting, grouping, basic aggregation functions, and query operations (equals, greater than, less than, not equals, null, not null, in, and like).
  • CSV/TSV upload - The upload operation can be used to both create and update rows of a table from a single CSV file.  Currently, tables with 100M+ rows have been created with CSV uploads.
  • CSV/TST TSV download - SQL-like query results can be fetched as a CSV file that a client can download from Synapse.
  • Partial Row Set - A partial row-set allows a client to change a sub-set of the cells of a row.  This feature is used by the Portal to create/update rows using the UI.
  • Row Set - A change set the includes all cells of a row for both insert and update.
  • Fetch Rows by Version - Fetch specific version versions of all rows specified in a RowReferenceSet.

...

Each operation that creates, updates or deletes a row in a table results in a new version of that row being appended to the table.  This means the tables feature is an append-only database that maintains all the state of all rows.  While any version of a row can be fetched , from its rowId and rowVersionNumber, only the current versions of all rows are reflected in the SQL-like queries.

Concurrency

The tables feature is intended to be used concurrently by many users at a time. Consistency is maintained by processing updates on a single table serially and blocking queries while the updates are being processed.  This is enforced with a very simple two lock system:

  • Non-Exclusive Lock (Read Lock). - When a worker receives a query request, it first attempts to acquire a non-exclusive lock on the table.  A non-exclusive lock can be issue immediately if there are zero to many non-exclusive locks already on the table.  However, if an exclusive lock has been issued for that table then the non-exclusive lock acquisition fails immediately (non-blocking) and the worker returns the query request to the queue to be attempted later.
  • Exclusive Lock (Write Lock) - When a worker receives a create or update request to a table, it first attempts to acquire an exclusive lock on the table.  If another exclusive lock is already issued to the table then the lock acquisition fails immediately and the update request is returned to the queue to be processed later.  If there are any non-exclusive locks outstanding, then an exclusive precursor lock is placed on the table.  This precursor lock will block the acquisition of all new locks on the table but will not interrupt the existing non-exclusive locks.  At this point the worker is blocked while waiting for the non-exclusive locks to release (or time out).  Once all outstanding non-exclusive locks are release released (or time out), an exclusive lock is issued to the worker and update proceeds.

Updates are process applied to a table in blocks of 25K rows.  Each block will be issued a version number which also becomes the new version number of every row in that block.  If all blocks are processed without failure then the metadata of each block is committed to the table's history in a single transaction making them a permanent part of the table's history.  Any failure in any block results in a rollback of all blocks from that update.  This makes each update to a table atomic regardless of the size of the update.  Raw block data is stored in S3, while the metadata about the blocks is store in the main Synapse MySQL database.

...