sgr splits large tables into multiple objects. At commit time, it
chunks new tables in an image into fragments of 10000 rows by default. You can
change this by passing
sgr commit or changing the
SG_COMMIT_CHUNK_SIZE configuration flag/environment variable.
sgr chunks by primary key. You can also sort data inside a chunk
sgr commit. This lets
backend, use its own skip lists to not read parts of a chunk that aren't
interesting to a query, decreasing the amount of disk access.
When you commit a change to a table,
sgr represents that change as multiple
objects that contain row-level additions/deletions and can partially "overlap"
with old objects.
sgr appends these new objects to the end of the table's
You can override this behavior with
--snap, so that
sgr will repackage all
changed tables as brand new objects.
You can retain existing chunk boundaries when committing new data by passing
sgr commit. In this case,
sgr will split
delta-compressed changes according to the original table chunk boundaries. For
example, if there's a change to the first and the 20000th row of a table that
was originally committed with
--chunk-size=10000, this will create 2
fragments: one based on the first chunk and one on the second chunk of the
Optimal object sizes
There is a performance and space tradeoff regarding the amount of rows and the physical size of every object (chunk) and hence the amount of objects that a single table gets split up to.
- Every extra object in a table has multiple kinds of overhead:
- Fixed storage overhead in
sgr's metadata (
splitgraph_meta.objectstable), including the hashes and the index for that object.
- Overhead when scanning through the object index at layered query plan time
- (small) overhead of initializing a scan through the object by PostgreSQL
- Fixed overhead when checking out the table (more deltas have to be applied to the staging area)
- Latency overhead for downloading lots of small objects (each of which is a file) over few large ones
- Fixed storage overhead in
- Datasets with lots of small objects can take better advantage of the object
index: in layered querying,
sgrmight need to download or scan through a smaller fraction of the table to satisfy the query, since the table is more granular.
- Tables that share a lot of data with other tables have a better chance of reusing the same fragments when stored as smaller objects.
In practice, we have found that targeting object sizes of a few megabytes
delivers a good balance. It is not currently possible to get
automatically target a certain object size, but the required number of rows in
each object can be approximated by knowing the total image size and the amount
of rows in all of its tables.
An image that includes a lot of changes still holds all of its history, since it
contains all the intermediate objects together with deltas. This method allows
sgr to perform quicker commits (since a change is appended to the end of a
table's list of objects) at the expense of the image bloating up and having lots
of dead rows. This is sometimes undesirable. If you have multiple images in a
repository that show a dataset's history, you might want to optimize the latest
image in that repository to decrease its size and speed up querying. In
addition, you might want to be able to delete sensitive data from an image and
not have it in the history at all.
To rechunk an image, splitting every table in it into disjoint objects again, check it out and recommit it as a snapshot, adding necessary indexes. For example:
$ sgr checkout my/big_image:latest $ sgr commit my/big_image --snap --chunk-size 100000
To cleanup "loose" or "dangling" objects that are not linked to any tables, you
can use the
sgr cleanup command.