---
title: "Table structures"
slug: "dev-database-concepts-architecture-table-structures"
description: "Explore VAST DataBase table layouts: regular, sorted, partitioned, semi-sorted projections, and vector-indexed tables for optimized performance and queries."
updated: 2026-10-30T18:19:01Z
published: 2026-10-30T18:19:01Z
canonical: "docs.vastdata.com/dev-database-concepts-architecture-table-structures"
---

> ## 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.

# Table structures

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

A VAST DataBase table can be laid out in more than one way. A regular table needs no layout decisions. A sorted table keeps its rows in sort-column order. A partitioned table splits its rows by partition keys. A semi-sorted projection keeps an extra sorted copy of some columns. A vector-indexed table arranges its rows for similarity search. This page explains what each layout is, what it buys you and what it costs, as far as VAST Cluster 5.5 documents it.

To choose between them, see [data modeling](/develop/docs/dev-database-guides-data-modeling). For which ones you can combine, see [limits](/develop/docs/dev-database-reference-limits#combining-table-structures).

## At a glance

![Five panels. A regular table keeps rows in insert order. A sorted table sorts its rows in the background by up to four sort columns, a block at a time, into sorted runs; the most recent rows aren't sorted yet. A partitioned table splits its rows into partitions by up to four partition keys, so a filter on the key, such as day = Tue, reads only the matching partition. A semi-sorted projection is a separate copy of chosen columns, sorted by up to four of them; a table can have several. A vector-indexed table arranges its rows by vector distance on one vector column, so nearby vectors are grouped together. Partitioned and sorted is the one pair that combines, if both are set at creation.](https://cdn.us.document360.io/89b91113-5dc6-49db-86ec-57b16dc8ce56/Images/Documentation/table-structures.svg?sv=2026-02-06&spr=https&st=2026-11-08T08%3A55%3A18Z&se=2026-11-08T09%3A55%3A18Z&sr=c&sp=r&sig=LZDvqhayV2jUzxxPt2gX0Es6J%2BM9V2Y8NtcD6nYL22A%3D)

*Each structure arranges a table's rows differently; a regular table keeps them in insert order.*

| Layout | What it is | What it buys | What it costs |
| --- | --- | --- | --- |
| [Sorted table](/develop/docs/dev-database-concepts-architecture-table-structures#sorted-tables) | The table's rows, sorted in the background by up to four sort columns | Faster queries for specific rows | Background sorting work; sort columns can't be updated; permanent; excludes projections, user-defined row IDs, vector index and replication |
| [Partitioned table](/develop/docs/dev-database-concepts-architecture-table-structures#partitioned-tables) | The table's rows, split into partitions by up to four partition keys | Partition pruning, co-located joins and group-bys, batched inserts, fast whole-partition deletes | Creation only; partition columns fixed; excludes projections, user-defined row IDs, vector index and replication |
| [Semi-sorted projection](/develop/docs/dev-database-concepts-architecture-table-structures#semi-sorted-projections) | A separate copy of chosen columns, sorted by up to four of them | Faster queries that use only the projection's columns, with several sort orders per table | Extra storage and upkeep; not replicated; excludes sorting, partitioning and user-defined row IDs |
| [Vector-indexed table](/develop/docs/dev-database-concepts-architecture-table-structures#vector-indexed-tables) | The table's rows, arranged by vector distance on one vector column | Fast, accurate similarity search with less compute | Creation only; no column changes; excludes sorting, partitioning and user-defined row IDs |

## Sorted tables

**What it is.** You choose up to four sort columns, in order, and the cluster sorts the table's rows by them in the background. The table stays available for inserts and queries the whole time. Rows are sorted in chunks of 512K rows. Sorting looks at only the first eight bytes of each sort column's value. ([Sorted tables](/docs-5-5/docs/sorted-tables))

**What it buys.** Queries for specific rows run faster when they filter on the sort columns. In the Python SDK, a query on a sorted table also asks the cluster to estimate how much of the table the filter touches, and uses that to decide how many parallel splits to run (SDK 2.1.0, client-side). ([Python SDK reference](/develop/docs/dev-database-reference-sdk#select))

How the sort order lets a query skip data is explained in [read path and pruning](/develop/docs/dev-database-concepts-architecture-read-path-and-pruning#sorted-tables).

**What it costs.**

- **Background work.** Sorting runs in the background and can affect performance while it runs. Inserted rows have to be sorted in too, a block of 512K rows at a time; updates don't re-sort anything ([write path](/develop/docs/dev-database-concepts-architecture-write-path#sorted-tables)).
- **Small tables don't benefit.** Because rows are sorted in chunks of 512K, a smaller table isn't effectively sorted. Sorting works best above 1 million rows.
- **Temporary growth.** While sorting runs, the table's row count and size can grow and then shrink back, because sorting uses extra rows. `select count(*)` stays correct throughout.
- **Many small inserts.** Insert in batches that are as large as possible, for both insert and query performance.
- **Limits on values.** Sort-column values can't be updated. Because only the first eight bytes count, string columns whose values share long prefixes, such as string timestamps, don't sort well. Nested types can't be sort columns.
- **Permanence.** Sorting can't be turned off, and the sort columns and their order can't be changed.

**Watching progress.** The VAST Web UI grades each sorted table C (not sorted), B (partially sorted), A (sorted) or A+ (super sorted). The grade drops while new or changed rows are sorted in, then recovers. In the Python SDK, `table.sorting_score()` and `table.sorting_done()` report the same progress. ([Sorted tables](/docs-5-5/docs/sorted-tables); [Python SDK reference](/develop/docs/dev-database-reference-sdk#sorting))

## Partitioned tables

**What it is.** Rows are divided into partitions by the values of up to four partition keys. Each key is a column with a transform: `identity` (the value itself), `year`, `month`, `day` or `hour` of a date or timestamp, `bucket[N]` (a hash of the value, mod N), or `truncate[W]` (the value cut to width W). For each partition column, the database adds an internal column named after the column with the transform as a suffix, such as `ts_day`. ([Database partitioning](/docs-5-5/docs/database-partitioning))

Each partition is visible as a row in the table's partitions view, which the Python SDK returns from `table.partitions()`. Each row holds the partition's key values, plus `est_row_count` and `est_byte_size` estimates. ([Python SDK reference](/develop/docs/dev-database-reference-sdk#partitions))

**What it buys.** ([Database partitioning](/docs-5-5/docs/database-partitioning))

- **Partition pruning.** A scan skips the partitions that are irrelevant to the query, which dramatically reduces I/O. In the Python SDK, `select()` first finds the partitions that match the filter's conditions on partition columns, then queries only those. A filter that doesn't mention a partition column scans every partition.
- **Co-located joins and group-bys.** Query engines can line their work up with partition boundaries. In a group-by, each worker processes whole partitions, with no shuffle. In a join between two tables with the same partitioning scheme, where the join key matches the partition key, each worker joins its own partitions with no data movement between nodes.
- **Cheaper inserts.** Rows can be gathered in memory per partition and written in batches, which reduces write amplification from background reorganisation.
- **Fast deletes.** Deleting a whole partition is much faster than deleting its rows one by one.

**What it costs.**

- **Creation only.** Partitioning is set when the table is created, and it can't be removed or changed.
- **Fixed partition columns.** A partition column can't be dropped. Its values can't be changed, unless the row stays in the same partition. Nested columns can't be partition columns.
- **Partition deletes aren't transactional.** Deleting a partition removes every row in it, outside the table's transactions. Doing it while other writes target the table can leave the data in an indeterminate state.
- **Sorting must be planned too.** A partitioned table can also be sorted, but only if you set both at creation, and a column partitioned with `identity` can't be a sort column. Known issue: importing into a table that's both sorted and partitioned takes considerably longer than importing into a table that's neither.
- **Tooling gaps.** The VAST Catalog shows partitioned tables but not their partitions. In the Python SDK, `select_splits()` doesn't work on partitioned tables.

## Semi-sorted projections

**What it is.** A projection is a subset of a table's columns, kept as a separate projection table. Its rows are sorted by up to four sorted columns, in the order you chose them. It can carry any number of other columns, unsorted. A table can have several projections, each sorted differently. ([Managing table projections](/docs-5-5/docs/managing-table-projections); [permissions](/docs-5-5/docs/managing-permissions-for-accessing-vast-tabular-databases))

"Semi-sorted" means each block of the table's rows is sorted separately, not the whole table at once; see [read path and pruning](/develop/docs/dev-database-concepts-architecture-read-path-and-pruning#semi-sorted-projections).

**What it buys.** Faster queries that use only the columns in the projection. Unlike sorting the table, you can have different sort orders for different queries, and you can add a projection to an existing table. In the Python SDK, a query uses a matching projection automatically. You can turn that off, or force a particular projection, with `QueryConfig`. ([Python SDK reference](/develop/docs/dev-database-reference-sdk#semi-sorted-projections))

**What it costs.**

- **Storage.** A projection is a copy of the columns it holds, so it uses capacity of its own. Its stats report its own row count and size in bytes.
- **Only some queries benefit.** A query uses a projection only if the projection holds every column it filters on and reads, 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 yet ([read path and pruning](/develop/docs/dev-database-concepts-architecture-read-path-and-pruning#semi-sorted-projections)).
- **Not replicated.** When a database is replicated, its semi-sorted projections aren't copied to the replica. ([Overview of VAST replication](/docs-5-5/docs/overview-of-vast-replication))
- **Exclusions.** You can't add a projection to a sorted or partitioned table, or to one with user-defined row IDs, and you can't sort a table that has a projection.
- **Cell size.** Once a table has a projection, every cell in every column of the table is limited to about 126 KB, including metadata, not 5 MB. Larger inserts fail, and you can't add a projection to a table that already holds larger cells. See [limits](/develop/docs/dev-database-reference-limits#sizes).

## Vector-indexed tables

**What it is.** When you create a table with a vector index, the table's data is arranged by vector distance on one vector column, using the distance metric you choose: `l2sq` (Euclidean), `cosine` or `ip` (inner product), see [the vector index](/develop/docs/dev-vector-search-concepts-index) and [Vector database index](/docs-5-5/docs/vector-database-index).

**What it buys.** Similarity search that's faster, more accurate and uses less compute, especially at scale. After an insert, updating the index can take some time, but new vectors can be found by nearest-neighbour search and by ordinary filters straight away.

**What it costs.**

- **Creation only, and permanent.** The index can't be added to an existing table or removed, and its distance function can't be changed.
- **One index per table.** Only one vector column can be indexed.
- **No column changes.** You can't add or drop columns in an indexed table.
- **Exclusions.** A vector-indexed table can't be sorted or partitioned, and can't use user-defined row IDs.
- **Engine support.** The Trino and Spark connectors don't support vector-index search.

For search semantics, index behaviour and tuning, see the [VAST Vector Search](/develop/docs/dev-vector-search-overview).

## Row IDs in each layout

Every row has a row ID, which you use to update or delete specific rows. In the Python SDK, the `$row_id` column is `uint64` in a regular table and `decimal128(38, 0)` in sorted, partitioned and vector-indexed tables. If you take `$row_id` from `select(..., internal_row_id=True)`, it already has the right type. ([Python SDK reference](/develop/docs/dev-database-reference-sdk#update-and-delete))

With [user-defined row IDs](/develop/docs/dev-database-guides-data-modeling#row-ids), you allocate row IDs yourself through a `vastdb_rowid` column. That rules out sorting, partitioning, projections and a vector index.

## See also

- [Data modeling](/develop/docs/dev-database-guides-data-modeling): choosing a layout, with examples.
- [Limits](/develop/docs/dev-database-reference-limits): sizes, counts and the full combinability matrix.
- [Concepts](/develop/docs/dev-database-concepts-index#table-structures): the overview of table structures.
- [VAST Vector Search](/develop/docs/dev-vector-search-overview): vector search and index tuning.