Projections

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

In VAST DataBase, a projection is a semi-sorted projection: an extra copy of some of a table's columns, sorted by up to four of them. It's the way to get a second (or third) sort order on a table, for queries that filter on columns the table isn't sorted by. This page says what one is and when to use it, and points to the pages with the detail.

What a semi-sorted projection is

A copy of chosen columns. You pick up to four sorted columns, in order, and any number of other columns to carry along unsorted (table structures).
Sorted a block at a time. Each block of the table's rows is sorted separately, not the whole table at once. That's what "semi-sorted" means. Within each block, the min/max statistics can skip most chunks for a filter on the projection's sort columns (read path and pruning).
Used automatically. In the Python SDK, a query uses a matching projection without being asked. QueryConfig can turn that off or force a particular projection (Python SDK reference). On a test table of a few million rows, a query that matched the projection ran clearly faster than the same query with projections turned off.
Catches up after a load. A projection sorts the table's rows a block at a time, so after a load its stats.num_rows can stay below the table's for a while. Queries still return every matching row: rows the projection hasn't sorted yet are read from the table. Forcing a projection by name skips that, see below.

When to use one

Use projections on a regular table when different queries filter on different columns and each reads only a few columns. A sorted table has one sort order; a table can have several projections, each sorted differently, and you can add them to an existing table (data modeling).

A query uses a projection only if the projection holds every column it filters on and every column it returns, its filter is an AND of conditions that each name one column, it doesn't ask for row IDs, and its transaction hasn't written anything yet (read path and pruning). Asking for one column the projection doesn't hold makes the query read the table instead; it still returns the right rows. An OR of values on the projection's sort column, such as (_.customer == 1) | (_.customer == 2), still uses it.

Don't plan on projections when:

The table is sorted or partitioned, or uses user-defined row IDs: you can't add a projection to it. Once a table has a projection, it can't be sorted (limits).
The table must be replicated with its projections: projections aren't copied to the replica (table structures).
You want to manage them in the VAST Query Engine: it has no projection DDL. Use the Python SDK (SQL reference).

A projection costs storage of its own, since it's a copy of its columns.

A projection limits cell size for the whole table. The 5 MB cell size limit doesn't apply to semi-sorted projections; the limit before 5.5 was 126 KB, including metadata. Once a table has a projection, an insert with a larger cell in any column fails with BadRequest, code SemiSortedIsNotSupportedWithLargeCells, even if that column isn't in the projection. create_projection() fails the same way on a table that already holds larger cells. Dropping the projection lifts the limit. Don't add projections to tables that store documents, images or other large values.

Create and check one

These blocks run in order, in one Python session. They need your access key in AWS_ACCESS_KEY_ID and AWS_SECRET_ACCESS_KEY, the database endpoint in AWS_S3_ENDPOINT_URL, and your bucket in VASTDB_BUCKET, with the actions that create_projection() needs (setup prerequisites). The first block loads a regular table of 1,000,000 orders:

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

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

N = 1_000_000
orders = pa.table({
    "id": pa.array(range(N), pa.int64()),
    "customer": pa.array([(i * 7919) % 50_000 for i in range(N)], pa.int64()),
    "amount": pa.array([(i % 1000) / 10 for i in range(N)], pa.float64()),
    "note": pa.array([f"order {i}" for i in range(N)], pa.utf8()),
})

with session.transaction() as tx:
    schema = tx.bucket(BUCKET).create_schema(SCHEMA, fail_if_exists=False)
    old = schema.table("orders", fail_if_missing=False)
    if old:
        old.drop()
    schema.create_table("orders", orders.schema)

for start in range(0, N, 200_000):
    with session.transaction() as tx:
        tx.bucket(BUCKET).schema(SCHEMA).table("orders").insert(orders.slice(start, 200_000))
print("loaded", N, "rows")

loaded 1000000 rows

The rows are in id order, so customer values are scattered across the whole table. Add a projection sorted by customer that carries amount, then list the table's projections:

with session.transaction() as tx:
    table = tx.bucket(BUCKET).schema(SCHEMA).table("orders")
    table.create_projection("by_customer", sorted_columns=["customer"], unsorted_columns=["amount"])

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

by_customer ['customer', 'amount']

A query that filters on customer and returns only customer and amount can use the projection. Run it as is and with projections turned off. Both return the same rows; to see what the projection saves, time the two (profile a query):

def customer_orders(config=None, columns=("customer", "amount")):
    with session.transaction() as tx:
        table = tx.bucket(BUCKET).schema(SCHEMA).table("orders")
        return table.select(columns=list(columns), predicate=_.customer == 4242,
                            config=config).read_all()

automatic = customer_orders()
without = customer_orders(QueryConfig(use_semi_sorted_projections=False))
print(automatic.num_rows, without.num_rows)
print(automatic.sort_by("amount").equals(without.sort_by("amount")))

20 20
True

Don't force a projection by name for results you rely on. QueryConfig(semi_sorted_projection_name=...) reads only the rows the projection has already sorted. It leaves out the rest without an error. Right after create_projection() on this table, a forced query returned no rows, then 10 of the 20, and it stayed short until more rows arrived; the same query without forcing returned all 20 throughout. Use forcing only to compare timings, on a projection whose stats.num_rows matches the table's.

Forcing a projection that doesn't hold every column the query reads fails with NotFound. Without forcing, the same query reads the table:

try:
    customer_orders(QueryConfig(semi_sorted_projection_name="by_customer"),
                    columns=("customer", "amount", "note"))
except vastdb.errors.NotFound as e:
    print("forced:", e.code)
print("automatic:", customer_orders(columns=("customer", "amount", "note")).num_rows, "rows")

forced: TabularMismatchColumnName
automatic: 20 rows

Now that the table has a projection, a cell larger than the pre-5.5 limit is refused, here in note, a column the projection doesn't hold:

big = pa.table({"id": [N], "customer": [1], "amount": [0.0], "note": ["x" * 200_000]},
               schema=orders.schema)
try:
    with session.transaction() as tx:
        tx.bucket(BUCKET).schema(SCHEMA).table("orders").insert(big)
except vastdb.errors.BadRequest as e:
    print(e.code, "-", e.message)

SemiSortedIsNotSupportedWithLargeCells - Semi sorted projection is not supported with large cells

The SDK also logs the failed request and the rollback to stderr. Drop the schema when you're done:

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_projections

The methods and the QueryConfig fields are in the Python SDK reference. Data modeling has another example, with two projections on one table.

Projections

What a semi-sorted projection is

When to use one

Create and check one

See also