Performance

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

Some of what decides how fast your queries and loads run is under your control: how you lay out tables, what you ask for in a query, how you batch writes, and how much parallelism the client uses. The rest is cluster configuration, which your platform team owns. This page covers both sides, so you know what to change yourself and what to ask for.

What you control and what the cluster controls

Area	Who controls it	Where to read more
Table structure: sorted, partitioned, semi-sorted projections	You, when you create the table	Data modeling
Which columns and rows a query asks for	You	Ask for less
Write batch sizes and load method	You	Write in large batches
Client parallelism: splits, endpoints, import workers	You	Spread work across endpoints
Which CNodes serve VAST DataBase (virtual IP pools)	Platform team	Cluster settings
QoS limits on views and users	Platform team	Cluster settings
Where Trino or Spark runs	Platform team and you	Running Trino or Spark on the cluster

Choose the table structure for your queries

Table structure has the biggest effect, and most of it can't be changed after you create the table. Data modeling explains how to choose. In short:

Sorted tables improve queries that filter on the sort columns (Sorted tables).
Partitioned tables let a query skip partitions that can't match, and deleting a whole partition is much faster than deleting rows (Database partitioning).
Semi-sorted projections are used by queries automatically. QueryConfig.use_semi_sorted_projections=False turns that off, for example to compare timings.

Why data order decides how much of a table a query reads is explained in read path and pruning.

Ask for less

Both of these are applied on the cluster, so less data travels to your client:

Columns. select(columns=[...]) reads only the columns you list. With columns=None you get every column. In Trino or Spark, avoid SELECT * for the same reason.
Filters. A predicate passed to select() is evaluated on the cluster. Write filters the cluster can evaluate; querying and pushdowns list what pushes down.

How the SDK uses your filter depends on the table:

On a partitioned table, the SDK first picks the partitions that can match, using only the parts of the filter on partition source columns, and then queries those partitions. A filter that doesn't mention a partition column scans every partition.
On a sorted table, the SDK asks the cluster how many rows the filter is likely to match, and uses that to decide how many splits to run.

limit_rows stops a query once that many rows have come back, which saves work when you only need a sample.

Set up an example table in a schema called ops_performance, using the environment variables from the quickstart:

import os
import pyarrow as pa
import vastdb
from vastdb.config import QueryConfig

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

columns = pa.schema([
    ("id", pa.int64()),
    ("sensor", pa.utf8()),
    ("reading", pa.float64()),
    ("note", pa.utf8()),
])
rows = pa.table({
    "id": list(range(10_000)),
    "sensor": [f"s{i % 10}" for i in range(10_000)],
    "reading": [float(i % 100) for i in range(10_000)],
    "note": ["x" * 100] * 10_000,
}, schema=columns)

with session.transaction() as tx:
    schema = tx.bucket(BUCKET).create_schema("ops_performance", fail_if_exists=False)
    table = schema.create_table("readings", columns, fail_if_exists=False)
    table.insert(rows)
    print("Inserted", rows.num_rows, "rows")

Inserted 10000 rows

Ask only for the two columns and the rows you need:

with session.transaction() as tx:
    table = tx.bucket(BUCKET).schema("ops_performance").table("readings")
    result = table.select(
        columns=["id", "reading"],
        predicate=(table["sensor"] == "s3") & (table["reading"] > 90),
    ).read_all()

print(result.schema.names)
print(result.num_rows, "rows")

['id', 'reading']
100 rows

Write in large batches

Each insert() call is at least one request, so many small inserts cost more than a few large ones. Insert into sorted tables in batches as large as possible: it improves both insert and query performance (see Sorted tables).

What the SDK does with one large batch (client behaviour in SDK 2.1.0):

A request can carry up to 5 MiB. If the whole batch fits, with a small safety margin, it goes in one request.
If it doesn't fit, the SDK inserts as many columns as fit, splitting the rows across requests if it must, to get row IDs. It then writes the remaining columns with update requests. So a very wide batch turns into several requests. Logging vastdb._by_column_inserter at DEBUG shows which path a batch took.
A single row that can't fit in a request raises TooWideRow.

For partitioned tables, inserts benefit when rows are grouped per partition before they're written (see Database partitioning). The SDK's buffered inserter does that grouping for you; BufferedInsertConfig sets the buffer size (10 MiB by default) and how many rows a partition collects before it's flushed (65,536 by default).

For files already in a VAST bucket, import them instead of reading them into the client and inserting. With import_files() the cluster reads the Parquet files itself, so the data never passes through your client. See ingest.

There's one known slow case in 5.5: importing into a table that is both sorted and partitioned "takes considerably longer" than importing into a table that's neither.

Spread work across endpoints

A VAST DataBase endpoint is a virtual IP (VIP) on a CNode. The SDK spreads queries and imports over several of them.

How the SDK finds endpoints. When you open a table, the cluster returns the VIPs of the pool that serves it, and the SDK uses those as its data endpoints. If the cluster returns none, the SDK logs a warning and uses only the endpoint you connected to. You can see what was discovered in get_stats().endpoints. To choose the endpoints yourself, set SessionConfig(data_endpoints=[...]) for the whole session, or data_endpoints in QueryConfig or ImportConfig for one call.

Queries. The SDK divides a query into splits, disjoint subsets of the table's rows, and runs them in parallel (read path):

Setting (`QueryConfig`)	Default	Effect
`num_splits`	Estimated as `num_rows // rows_per_split`, at least 1	How many splits, each its own stream of requests.
`rows_per_split`	4,000,000	Used for the estimate above.
`num_sub_splits`	4	Concurrent reads inside each request, on the cluster.
`data_endpoints`	The discovered endpoints	One client worker thread per entry. You can list an endpoint more than once to get more threads.
`queue_priority`	Unset	A non-negative integer. Requests with lower values are served before those with higher values.

So a small table runs as a single split by default. For a large table, more endpoints and more splits mean more parallel work, until the client or network is the bottleneck. Measure with your own data; there are no published figures for 5.5.

To process splits in separate workers yourself, for example one per process, use select_splits(). It returns one reader per split. It doesn't work on partitioned tables.

config = QueryConfig(num_splits=4)

with session.transaction() as tx:
    table = tx.bucket(BUCKET).schema("ops_performance").table("readings")
    readers = table.select_splits(columns=["id"], config=config)
    total = sum(reader.read_all().num_rows for reader in readers)

print(len(readers), "readers,", total, "rows")

4 readers, 10000 rows

To pin a query to particular endpoints, pass them in QueryConfig. The addresses come from your platform team:

config = QueryConfig(
    data_endpoints=["http://vip1.example.com", "http://vip2.example.com", "http://vip3.example.com"],
    num_splits=12,
)
with session.transaction() as tx:
    table = tx.bucket(BUCKET).schema("ops_performance").table("readings")
    result = table.select(columns=["id"], config=config).read_all()

Trino splits. The VAST connector for Trino also divides each scan into splits, set by num_of_splits in the catalog or the vast.num_of_splits session property (Trino). It uses fewer splits than that for a small table only when it knows the table is small, from row statistics that ANALYZE collects:

A large table that had never been analyzed used the full num_of_splits for every scan. After ANALYZE, the same scan used fewer splits, and a table of a few rows used one.
A selective filter on an analyzed table, a filter on a sorted table's sort columns, or a filter on a partitioned table's partition column also used fewer splits.
The vast.query_data_rows_per_split session property made no visible difference to the split count.

In the same tests, one split made a full scan two to three times slower than eight, while 8, 64 and 256 splits were within run-to-run noise. Those are single-endpoint loopback results on one Trino worker; measure on your own cluster.

ANALYZE vast."<database>/<schema>".<table> stores the statistics as an object in the table's database (bucket), so they survive a Trino restart. Re-run it after large loads. SHOW STATS FOR shows what Trino knows: before ANALYZE, only the row count.

Imports. import_files() runs ImportConfig.import_concurrency workers. The default is twice the number of CNodes behind the endpoints. Each request carries up to 10 files, and workers take endpoints round-robin across CNodes. The SDK hands out whole files, so each file is imported by one worker. A load of a few very large files can't use more workers than it has files.

Throttling. If the cluster is busy, it can answer with Slowdown (HTTP 503). The SDK retries those automatically with backoff. See errors and Retries.

Clean up

with session.transaction() as tx:
    schema = tx.bucket(BUCKET).schema("ops_performance")
    schema.table("readings").drop()
    schema.drop()
    print("Dropped ops_performance")

Dropped ops_performance

Cluster settings to ask about

These are set by your platform team. If your client side looks fine and things are still slow, ask about them.

A virtual IP pool dedicated to VAST DataBase. Use one, containing only the CNodes that serve database work, so that database workloads and other cluster services don't affect each other's performance (see Configuring the VAST cluster for database access). Ask how many CNodes and VIPs the pool has: the SDK spreads work over those VIPs.

A Query Engine pool. The VAST Query Engine needs its own virtual IP pool with the Query Engine role (VAST Query Engine). A Query Engine pool can't be modified after creation; it has to be deleted and recreated.

QoS policies. Platform teams can cap bandwidth and IOPS per view, and bandwidth, IOPS and the number of S3 connections per user (Creating a QoS policy). If a limit applies to your user or database view, your throughput stops at that limit however you tune the client.

Admin task: send to your platform team. See Configuring the VAST cluster for database access and Creating a QoS policy.

Running Trino or Spark on the cluster

Trino and Spark can run as managed applications on CNodes of the VAST cluster, sharing those CNodes' compute with VAST storage (Managed applications on clusters). Setting this up is part of VAST DataEngine, which has its own documentation. For what changes for you as a user, see Trino and Spark.

Admin task: send to your platform team. See Managed applications on clusters.