--- title: "Projections" slug: "dev-database-guides-projections" updated: 2026-10-30T18:19:11Z published: 2026-10-30T18:19:11Z canonical: "docs.vastdata.com/dev-database-guides-projections" --- > ## Documentation Index > Fetch the complete documentation index at: https://docs.vastdata.com/llms.txt > Use this file to discover all available pages before exploring further. # 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](/develop/docs/dev-database-concepts-architecture-table-structures#semi-sorted-projections)). - **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](/develop/docs/dev-database-concepts-architecture-read-path-and-pruning#semi-sorted-projections)). - **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](/develop/docs/dev-database-reference-sdk#semi-sorted-projections)). 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](/develop/docs/dev-database-guides-projections#create-and-check-one). ## 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](/develop/docs/dev-database-guides-data-modeling#semi-sorted-projections)). 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](/develop/docs/dev-database-concepts-architecture-read-path-and-pruning#semi-sorted-projections)). 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](/develop/docs/dev-database-reference-limits#combining-table-structures)). - The table must be replicated with its projections: projections aren't copied to the replica ([table structures](/develop/docs/dev-database-concepts-architecture-table-structures#semi-sorted-projections)). - You want to manage them in the VAST Query Engine: it has no projection DDL. Use the Python SDK ([SQL reference](/develop/docs/dev-database-reference-sql-not-supported)). 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](/develop/docs/dev-database-operations-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](/develop/docs/dev-database-guides-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](/develop/docs/dev-database-reference-sdk#semi-sorted-projections). [Data modeling](/develop/docs/dev-database-guides-data-modeling#semi-sorted-projections) has another example, with two projections on one table. ## See also - [Indexing and data skipping](/develop/docs/dev-database-guides-indexing-and-data-skipping): projections next to the other mechanisms. - [Data modeling](/develop/docs/dev-database-guides-data-modeling): choosing between projections and a sorted table. - [Table structures](/develop/docs/dev-database-concepts-architecture-table-structures): what each layout buys and costs.