Data modeling

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

This guide helps you choose a layout for a VAST DataBase table before you create it. You can make a table sorted or partitioned, give it semi-sorted projections, allocate its row IDs yourself, or give it a vector index. Most of these choices can't be undone, and many can't be combined. So decide from the queries you expect to run, then create the table once.

These choices decide how the table's rows are laid out in the cluster's Element Store: which rows sit next to each other in the same column chunks. That's what lets a filter skip data, so it decides how much each query reads; see how tables are stored and read path and pruning.

What the Element Store means for your layout

A table in the Element Store: where it lives, how it's laid out, where it's stored.

• Rows are placed when they're written. In a regular table, rows share chunks in the order you insert them. A sorted table, a partitioned table or a projection changes which rows sit together, and that's what lets a filter skip data. Background work sorts a sorted table's new rows after the commit, so the newest rows aren't sorted yet (table structures).
• The layout is set when you create the table. Sorting and partitioning define where rows live in the store, which is why most of these choices can't be changed later.
• Every column is stored separately. A wide table costs little for queries that read a few of its columns, so you don't need to split tables to keep queries cheap (how tables are stored).
• Extra structures take capacity. A semi-sorted projection is a separate copy of the columns it holds, so it uses capacity of its own (projections).
• Snapshots and replication copy the store's data, with limits. Snapshots include the table. Sorted and partitioned tables can't be replicated, and projections aren't copied (snapshots and replication).

Most examples use the Python SDK; the last section creates the same structures with Trino, Spark and the VAST Query Engine. Run the code blocks in order in one Python session. Below each block is the output you should see.

Start from your queries

Each structure helps one kind of query, and each one costs you something. Use this table as a starting point. The sections below explain each choice.

Pick a structure from your main query pattern. The bar says when you can set it.

One pair is designed to work together: a table can be both partitioned and sorted, if you set both when you create it. Most other pairs are rejected. Limits has the full matrix, and table structures explains what each layout is.

Decisions you can't undo

Important to note: once made, these choices can't be undone. The details are in limits.

• Sorting can be added to an existing regular table, but once it's on, it stays. You can't change the sort columns or their order, and you can't update values in sort columns.
• Partitioning can only be set when the table is created, and it can't be changed or removed. Partition columns can't be dropped, and an update to a partition column is only allowed if the row stays in the same partition.
• Sorting a partitioned table is also creation-only. You can't add a sort key to an existing partitioned table.
• A vector index can only be set when the table is created, and it can't be removed. Its distance function can't be changed.
• User-defined row IDs: once one insert supplies row IDs, every later insert must supply them too.
• Exclusions last for the life of the table. For example, once a table is sorted you can never add a projection to it, and a sorted or partitioned table can never be replicated.

Set up

The examples read connection settings from the environment, as in the quickstart. They create a schema called guide_modeling and drop it at the end.

The describe() helper loads a table's metadata and prints its type, its sort columns and its partition keys. It reads the sort columns from TableMetadata, because Table.sorted_columns() prints a DeprecationWarning in SDK 2.1.0.

import os
import pyarrow as pa
import vastdb
from vastdb import TableRef
from vastdb.table_metadata import TableMetadata

session = vastdb.connect()
BUCKET = os.environ["VASTDB_BUCKET"]
SCHEMA = "guide_modeling"

columns = pa.schema([
    ("sensor_id", pa.int64()),
    ("region", pa.utf8()),
    ("ts", pa.timestamp("us")),
    ("value", pa.float64()),
])

def describe(table_name):
    """Print a table's type, sort columns and partition keys."""
    md = TableMetadata(TableRef(BUCKET, SCHEMA, table_name))
    with session.transaction() as tx:
        md.load(tx)
    print("type:", md.table_type.name)
    if md.table_type.name in ("Elysium", "SortedPartitions"):
        print("sort columns:", [f.name for f in md.sorted_columns])
    if md.partitioning is not None:
        keys = md.partitioning.spec.partition_keys
        print("partition keys:", [f"{k.transform}({k.column})" for k in keys])
        print("partition columns:", md.partitioning.post_transform_names)

with session.transaction() as tx:
    tx.bucket(BUCKET).create_schema(SCHEMA, fail_if_exists=False)
print("Schema ready:", SCHEMA)

Schema ready: guide_modeling

The type names come from the SDK's TableType. Elysium is the SDK's name for a sorted table, and SortedPartitions is a table that's both partitioned and sorted.

Regular tables

A regular table has no layout options, and the database allocates its row IDs.

Start with a regular table when you don't yet know the query pattern. It keeps the most options open: you can add sorting or semi-sorted projections later, though not both.

with session.transaction() as tx:
    tx.bucket(BUCKET).schema(SCHEMA).create_table("readings", columns, fail_if_exists=False)
describe("readings")

type: Regular

Sorted tables

A sorted table is sorted by up to four sort columns, in the background, so that queries for specific rows run faster. The table stays available for inserts and queries while it sorts, though background sorting can affect performance. (Sorted tables)

Choosing sort columns

• Pick the columns you filter on most. List them in the order you want the rows sorted.
• Use up to four columns. A table has one sort order, with at most four columns.
• Pick columns you never update. Values in sort columns can't be changed.
• Use date or timestamp types for time, not strings. Sorting uses only the first eight bytes of a sort column. A string timestamp such as "2026-10-05 12:00" is sorted only on "2026-10-", so it doesn't sort well.
• Don't use nested columns. Nested types can't be sort columns.
• Sort large tables. Tables are sorted in chunks of 512K rows, so smaller tables aren't effectively sorted. Sorting works best on tables with more than 1 million rows.
• Insert in large batches. Make inserts as large as possible, rather than many small ones. It improves both insert and query performance on sorted tables.

While a table is being sorted, its row count and size can grow and then shrink back. select count(*) stays correct throughout. (Sorted tables)

Creating a sorted table

Pass the sort columns as sorting_key, by name or by column index:

with session.transaction() as tx:
    tx.bucket(BUCKET).schema(SCHEMA).create_table(
        "readings_sorted", columns, sorting_key=["sensor_id", "ts"], fail_if_exists=False)
describe("readings_sorted")

type: Elysium
sort columns: ['sensor_id', 'ts']

You can also sort an existing regular table with add_sorting_key(). The table must not already have a sort key, a projection, user-defined row IDs or partitioning:

with session.transaction() as tx:
    tx.bucket(BUCKET).schema(SCHEMA).create_table("readings_sorted_later", columns, fail_if_exists=False)

with session.transaction() as tx:
    tx.bucket(BUCKET).schema(SCHEMA).table("readings_sorted_later").add_sorting_key(["sensor_id"])
describe("readings_sorted_later")

type: Elysium
sort columns: ['sensor_id']

Rows already in the table are sorted in the background. table.sorting_done() tells you whether the background sorter has caught up; the newest block of rows is sorted only once it's full (write path). In Trino, the equivalent is ALTER TABLE ... SET PROPERTIES sorted_by = ARRAY[...]; see Trino.

Partitioned tables

A partitioned table splits its rows into partitions by the values of up to four partition keys. Each key is a column, or a transform of a column such as the day of a timestamp. (Database partitioning)

Partitioning has these benefits:

• Partition pruning. A scan skips the partitions that are irrelevant to the query.
• Co-located joins and group-bys. Query engines can line their work up with partition boundaries. A group-by needs no shuffle, and nor does a join between two tables that share the partitioning scheme, when the join key matches the partition key.
• Inserts. Rows can be gathered in memory per partition and written in batches, which reduces write amplification from background reorganisation.
• Deletes. Deleting a whole partition is much faster than deleting rows one by one.

In the Python SDK, a query on a partitioned table first finds the partitions that match the parts of your filter on partition columns, then queries each one. If the filter doesn't mention a partition column, every partition is scanned. (Python SDK reference)

Choosing partition keys

• Partition on what you filter on. Pruning only helps queries that filter on the partition columns.
• Partition on what you delete by. If you age out data a day or a month at a time, partition by day() or month() of the timestamp, so you can drop whole partitions.
• Pick the transform for the column. year, month, day and hour group dates and timestamps. bucket[N] hashes a column into N partitions, which tends to spread rows evenly. truncate[W] truncates a value to width W. identity uses the value as it is. The KB transform table lists the column types each one accepts.
• Pick columns you never update. A partition column can't be dropped, and an update to it is only allowed if the row stays in the same partition.
• Don't use nested columns. Lists, maps and structs can't be partition columns.
• Watch column names. The database adds an internal column for each partition column, named after the column with the transform as a suffix (ts_day in the example below). Don't give another column a name that clashes with it.

Creating a partitioned table

In the Python SDK, build a PartitionSpec from PartitionKeys that use PyIceberg transforms. This one partitions by day and region:

from pyiceberg.transforms import DayTransform, IdentityTransform
from vastdb import PartitionKey, PartitionSpec

by_day_and_region = PartitionSpec([
    PartitionKey("ts", DayTransform()),
    PartitionKey("region", IdentityTransform()),
])

with session.transaction() as tx:
    tx.bucket(BUCKET).schema(SCHEMA).create_table(
        "readings_by_day", columns, partition_spec=by_day_and_region, fail_if_exists=False)
describe("readings_by_day")

type: Partitioned
partition keys: ['day(ts)', 'identity(region)']
partition columns: ['ts_day', 'region']

Insert rows as usual. Each partition appears as a row in the table's partitions view, table.partitions():

from datetime import datetime

rows = pa.table({
    "sensor_id": [1, 2, 1, 3],
    "region": ["eu", "us", "eu", "eu"],
    "ts": [datetime(2026, 10, 1, 9), datetime(2026, 10, 1, 10),
           datetime(2026, 10, 2, 9), datetime(2026, 10, 2, 11)],
    "value": [20.5, 18.0, 21.0, 19.5],
}, schema=columns)

with session.transaction() as tx:
    table = tx.bucket(BUCKET).schema(SCHEMA).table("readings_by_day")
    table.insert(rows)

with session.transaction() as tx:
    table = tx.bucket(BUCKET).schema(SCHEMA).table("readings_by_day")
    parts = table.partitions().select(columns=["ts_day", "region"]).read_all()
    eu = table.select(columns=["sensor_id", "ts"], predicate=table["region"] == "eu").read_all()

print(parts.sort_by([("ts_day", "ascending"), ("region", "ascending")]))
print(eu.sort_by([("ts", "ascending"), ("sensor_id", "ascending")]))

pyarrow.Table
ts_day: date32[day]
region: string
----
ts_day: [[2026-10-01,2026-10-01,2026-10-02]]
region: [["eu","us","eu"]]
pyarrow.Table
sensor_id: int64
ts: timestamp[us]
----
sensor_id: [[1,1,3]]
ts: [[2026-10-01 09:00:00.000000,2026-10-02 09:00:00.000000,2026-10-02 11:00:00.000000]]

The query on region only reads the eu partitions. In Trino and Spark you declare partitions in CREATE TABLE; see Trino and Spark. The VAST Query Engine can't use partitioned tables in 5.5; see below.

To delete whole partitions, use table.delete_partitions(..., allow_non_acid=True). This isn't transactional. Changing partitions while other writes target the table can leave the data in an indeterminate state. See updates and deletes and the Python SDK reference.

On 5.5, create_table() accepted each of the identity, year, month, day, hour, bucket and truncate transforms (limits). To load large volumes into a partitioned table, see the buffered inserter.

Partitioned and sorted

A partitioned table can also be sorted, if you set both in create_table(). A column partitioned with the identity transform can't also be a sort column. A column partitioned with another transform can, so you can partition by day(ts) and sort by ts. (Database partitioning; limits)

with session.transaction() as tx:
    tx.bucket(BUCKET).schema(SCHEMA).create_table(
        "readings_by_day_sorted", columns,
        partition_spec=by_day_and_region,
        sorting_key=["sensor_id", "ts"],
        fail_if_exists=False)
describe("readings_by_day_sorted")

type: SortedPartitions
sort columns: ['sensor_id', 'ts']
partition keys: ['day(ts)', 'identity(region)']
partition columns: ['ts_day', 'region']

Known issue: importing into a table that's both sorted and partitioned takes considerably longer than importing into a table that's neither.

Semi-sorted projections

A semi-sorted projection is an extra copy of some of a table's columns, sorted by up to four of them. You choose the sorted columns, in order, and any number of other columns to carry along unsorted. It speeds up queries that use only the columns in the projection. (Managing table projections)

A table can have more than one projection, each sorted differently. That's the main reason to choose projections over a sorted table: a sorted table has one sort order, but several projections can serve filters on different columns. In the Python SDK, queries use projections automatically. (Python SDK reference)

Things to know:

• Only queries on projection columns benefit. Include every column the query reads or filters on.
• Projections can be added to an existing table, but not to a sorted or partitioned table, or to one with user-defined row IDs. Once a table has a projection, it can't be sorted.
• Projections aren't replicated. When a database is replicated, its semi-sorted projections aren't copied to the replica. (Overview of VAST replication)
• A projection limits cell size for the whole table. Once a table has a projection, every cell in every column is limited to about 126 KB, including metadata, not 5 MB. Larger inserts fail with SemiSortedIsNotSupportedWithLargeCells, and create_projection() fails on a table that already holds larger cells. See limits.

with session.transaction() as tx:
    table = tx.bucket(BUCKET).schema(SCHEMA).table("readings")
    table.create_projection("by_sensor", sorted_columns=["sensor_id"], unsorted_columns=["ts", "value"])
    table.create_projection("by_time", sorted_columns=["ts"], unsorted_columns=["sensor_id", "value"])

with session.transaction() as tx:
    table = tx.bucket(BUCKET).schema(SCHEMA).table("readings")
    for p in sorted(table.projections(), key=lambda p: p.name):
        print(p.name, p.columns().names)

by_sensor ['sensor_id', 'ts', 'value']
by_time ['ts', 'sensor_id', 'value']

To compare query speed with and without projections, set QueryConfig(use_semi_sorted_projections=False). To force a particular projection, set QueryConfig(semi_sorted_projection_name="by_time"). See QueryConfig.

Row IDs

Every row has a row ID. By default the database allocates them, and you use them to update or delete rows you've selected. See updates and deletes.

With user-defined row IDs you allocate them yourself. That helps when you want to insert into, or query, a specific range of rows. (User-defined row IDs)

• The table has a column named vastdb_rowid of type int64.
• Values can't be larger than 2^47.
• Once one insert supplies row IDs, every later insert has to supply them. You can't mix your own row IDs with ones the database allocates.
• You can mix queries that filter on vastdb_rowid with queries that don't.
• A table with user-defined row IDs can't be sorted or partitioned, and can't have a projection or a vector index (limits).

Choose user-defined row IDs only when you need to address rows by a number you control. If you only need a key to look rows up by, an ordinary column in a sorted table or a projection keeps more options open.

In the Python SDK, add a vastdb_rowid column and pass use_external_row_ids_allocation=True:

rowid_columns = pa.schema([
    ("vastdb_rowid", pa.int64()),
    ("sensor_id", pa.int64()),
    ("value", pa.float64()),
])

with session.transaction() as tx:
    table = tx.bucket(BUCKET).schema(SCHEMA).create_table(
        "readings_rowid", rowid_columns, use_external_row_ids_allocation=True, fail_if_exists=False)
    table.insert(pa.table({
        "vastdb_rowid": [1000, 1001, 1002, 2000, 2001],
        "sensor_id": [1, 1, 1, 2, 2],
        "value": [20.5, 21.0, 21.5, 18.0, 18.5],
    }, schema=rowid_columns))

with session.transaction() as tx:
    table = tx.bucket(BUCKET).schema(SCHEMA).table("readings_rowid")
    print("user-defined row IDs:", table.stats.is_external_rowid_alloc)
    first_range = table.select(predicate=table["vastdb_rowid"].between(1000, 1999)).read_all()

print(first_range.sort_by("vastdb_rowid"))

user-defined row IDs: True
pyarrow.Table
vastdb_rowid: int64
sensor_id: int64
value: double
----
vastdb_rowid: [[1000,1001,1002]]
sensor_id: [[1,1,1]]
value: [[20.5,21,21.5]]

An insert without row IDs into this table is rejected by the cluster, with the same TabularRowAllocMethodConflict code you get when you combine user-defined row IDs with a structure that doesn't allow them (limits):

from vastdb import errors

try:
    with session.transaction() as tx:
        table = tx.bucket(BUCKET).schema(SCHEMA).table("readings_rowid")
        table.insert(pa.table({"sensor_id": [3], "value": [17.0]}))
    print("inserted")
except errors.HttpError as e:
    print(type(e).__name__, e.status, e.code)

BadRequest 400 TabularRowAllocMethodConflict

A table created without use_external_row_ids_allocation=True can also end up with user-defined row IDs: if its first insert includes a vastdb_rowid column, the table reports is_external_rowid_alloc as True from then on (SDK 2.1.0 tests, test_external_row_id). So treat the first insert of a vastdb_rowid column as a permanent decision.

Vector-indexed tables

A vector index lets you search a vector column for nearest neighbours. You set it when you create the table, on one vector column, with a distance metric. It can't be added later or removed, and its distance function can't be changed. You can't add or drop columns in an indexed table either. A vector-indexed table can't be sorted or partitioned, or use user-defined row IDs. See Vector database index.

from vastdb import VectorIndexSpec

schema.create_table("items", columns_with_vector,
                    vector_index=VectorIndexSpec(column="embedding", distance_metric="l2sq"))

For the SDK parameters, see the Python SDK reference. For how vector search works and how to tune it, see the VAST Vector Search.

Wide or normalized tables

VAST DataBase stores each column separately within groups of rows, and a query reads only the columns it asks for (how tables are stored). So a wide table costs little for queries that touch a few of its columns, and adding a column later doesn't rewrite existing rows. Updates also write only the columns they change.

Two facts bear on this choice. Queries read only the columns you ask for, because column selection is pushed down to the cluster (pushdowns). And if you join large tables in Trino or Spark, partitioning both the same way on the join key lets each worker join its own partitions without moving data between nodes (Database partitioning).

Create tables with Trino, Spark or the VAST Query Engine

The structures are properties of the table, not of the client that created it. A table that Trino partitions and sorts is the same kind of table as one the SDK creates, and every client can read it. The examples below use the database (bucket) vastdb; use yours instead. Each one checks the result with the describe() helper from Set up.

Trino

Set the structure as table properties in CREATE TABLE. partitioning takes the partition keys, with transforms written as day(ts), and sorted_by takes the sort columns in order. This table is partitioned by day and region, and sorted, like readings_by_day_sorted above:

CREATE TABLE vast."vastdb/guide_modeling".trino_by_day_sorted (
    sensor_id bigint,
    region varchar,
    ts timestamp(6),
    value double
)
WITH (
    partitioning = ARRAY['day(ts)', 'region'],
    sorted_by = ARRAY['sensor_id', 'ts']
);

describe("trino_by_day_sorted")

type: SortedPartitions
sort columns: ['sensor_id', 'ts']
partition keys: ['day(ts)', 'identity(region)']
partition columns: ['ts_day', 'region']

SHOW CREATE TABLE lists the properties back. To sort an existing regular table, use ALTER TABLE ... SET PROPERTIES sorted_by = ARRAY[...]; see Trino.

Spark

Declare partitions with PARTITIONED BY, using Spark's transform names, such as day(ts). Where your platform team has set up the connector, getOrCreate() picks up its settings; to try it locally, see Try it from a local PySpark session:

from pyspark.sql import SparkSession

spark = SparkSession.builder.getOrCreate()
spark.sql(f"""
CREATE TABLE ndb.`{BUCKET}`.{SCHEMA}.spark_by_day (
    sensor_id bigint,
    region string,
    ts timestamp,
    value double
)
PARTITIONED BY (day(ts), region)
""").collect()
describe("spark_by_day")

type: Partitioned
partition keys: ['day(ts)', 'identity(region)']
partition columns: ['ts_day', 'region']

This guide doesn't show sorting a table with Spark. Sort the table with the SDK or Trino.

VAST Query Engine

Make a sorted table with a SORT KEY constraint in CREATE TABLE. Connect with the ADBC driver; QE_ENDPOINT is the Query Engine's endpoint from your platform team:

import adbc_driver_manager
import adbc_driver_manager.dbapi as dbapi
import adbc_driver_vastdb

conn = dbapi.connect(
    driver=adbc_driver_vastdb.get_driver_path(),
    db_kwargs={
        "vast.db.endpoint": os.environ["QE_ENDPOINT"],
        "vast.db.access_key": os.environ["AWS_ACCESS_KEY_ID"],
        "vast.db.secret_key": os.environ["AWS_SECRET_ACCESS_KEY"],
    },
    autocommit=True,
)
with conn.cursor() as cur:
    cur.execute(f'''CREATE TABLE "{BUCKET}/{SCHEMA}".qe_sorted (
        sensor_id BIGINT, region VARCHAR, ts TIMESTAMP, value DOUBLE,
        CONSTRAINT by_sensor SORT KEY (sensor_id, ts))''')
describe("qe_sorted")

type: Elysium
sort columns: ['sensor_id', 'ts']

In VAST Cluster 5.5 the Query Engine can't create a partitioned table, and it rejects queries, inserts, updates and deletes on one (SQL reference). Querying the partitioned table from earlier fails with InternalError. The message says the table is partitioned and can't be scanned directly:

with conn.cursor() as cur:
    try:
        cur.execute(f'SELECT count(*) FROM "{BUCKET}/{SCHEMA}".readings_by_day')
    except adbc_driver_manager.DatabaseError as e:
        print("Rejected:", type(e).__name__)
conn.close()

Rejected: InternalError

If your application uses the Query Engine, don't partition the tables it reads. Sort them instead.

Before you create the table

• Which columns do most queries filter on? That decides the sort key, the partition keys or the projections.
• Do you delete data by time period? Partition by that period.
• Will you ever update the candidate sort or partition columns? If so, choose different ones.
• Does the table need to be replicated? Sorted and partitioned tables can't be, and projections aren't copied. Ask your platform team whether the table's database is on a replicated path. Admin task: send to your platform team. See Overview of VAST replication.
• Is the table big enough to benefit from sorting? Sorting works best above 1 million rows.
• Check the combination you want in the combinability matrix.

Clean up

Drop the tables, then the schema:

with session.transaction() as tx:
    schema = tx.bucket(BUCKET).schema(SCHEMA)
    for t in schema.tables():
        t.drop()
    schema.drop()
print("Dropped", SCHEMA)

Dropped guide_modeling

See also

• Table structures: what each layout is, and what it buys and costs.
• Limits: sizes, counts and the combinability matrix.
• Querying: writing filters that push down.
• Ingest: loading data, including into partitioned tables.
• Python SDK reference: create_table() parameters.