Welcome to the new DOCS.vastdata.com. We've added new menus and a version selector, plus new persona and resource pages to help customers get started. Looking for a previous article? Use the docs search.

Press CTRL+K to open search

VAST DataBase

Prev Next

VAST DataBase is a transactional database for tables, built into the VAST cluster. If you're new, read the Overview, then run the Quickstart to create and query a table with the Python SDK. Read Concepts next, then the guide for your task: Ingest for loading data, Querying for reading it, and Data modeling before you create a table you'll keep.

Overview and concepts

Where to begin: the overview and quickstart, then the concepts and architecture the rest of the section relies on, and the release notes.

  • Overview: VAST DataBase is a transactional table database built into the VAST cluster, for developers, data engineers and data scientists.
  • Quickstart: Connect with the Python SDK, create a schema and table, insert rows, query them with and without a filter, and clean up.
  • Concepts: The ideas the other pages rely on: databases, schemas and tables, transactions, Arrow and pushdown, row IDs, table structures, snapshots.
  • Release notes: Changes in each VAST Cluster 5.5 release, 5.5.0 to SP6, that affect VAST DataBase code: new capabilities, fixes and known limitations.

Architecture

How VAST DataBase works inside, as far as it changes the code you write. Start with The cluster underneath (DASE), which explains the Element Store that tables share with files and objects, then Table structures and Read path and pruning; read Write path and Transactions when you tune loads or handle conflicts.

  • The cluster underneath (DASE): DASE and the Element Store explained for builders: one store for files, objects and tables, write durability, data reduction and replicas.
  • How tables are stored: How a table is laid out: row groups, separately stored columns, per-chunk min/max statistics and hidden row markers, and why it matters.
  • Write path: What happens to rows after inserts, imports, updates, deletes and column changes, and why some loading patterns behave better than others.
  • Read path and pruning: What the cluster does for a query, and how min/max statistics, sorting, projections and partitions decide how much it skips.
  • Transactions: What a transaction sees, when its changes become visible, which transaction wins a write conflict, and what happens to transactions left open.
  • Table structures: Regular, sorted, partitioned, semi-sorted projection and vector-indexed table layouts: what each is, what it buys you, and how row IDs differ.
  • Compression and data reduction: Why tables have no codec setting, how cluster data reduction applies to them, and what the Trino and Spark compression options do.

Guides

Step-by-step instructions for the work you'll do most. If you're loading data for the first time, start with Ingest, then Querying; read Data modeling before you create a table you'll keep, and the other guides for file formats, updates, transactions and tuning.

  • Data modeling: Choose sorted, partitioned or vector-indexed layouts, projections and row IDs from your queries, since most of these choices can't be undone.
  • Ingest: Insert Arrow data with the Python SDK, size batches, import Parquet files on the cluster, load partitioned tables, or use Trino.
  • Load JSON, CSV and Parquet files: Load newline-delimited JSON, CSV and Parquet files with PyArrow, fix the types it guesses wrong, and stream large files in batches.
  • Load scientific array data (NetCDF and GRIB2): Turn NetCDF and GRIB2 arrays into table rows with xarray, load step by step or in parallel, and query a region.
  • Querying: Make the cluster do the filtering: choose columns, write predicates that push down, limit rows, tune QueryConfig and read splits in parallel.
  • Updates and deletes: Update and delete rows by row ID with the Python SDK, on sorted, partitioned and user-defined row ID tables, and delete partitions.
  • Transactions in practice: Commit and roll back in Python SDK code, recognise conflict errors, know what the SDK retries, and retry a whole transaction yourself.
  • Optimize queries: A checklist for cheaper queries: table structure, filters that push down, fewer columns, splits and endpoints, top-N and vector search.
  • Indexing and data skipping: With no B-tree indexes, see how min/max statistics, sorting, partition pruning, projections and the vector index skip data, and what each costs.
  • Projections: What a semi-sorted projection is, when a second sort order helps filters on other columns, and how to create and check one.
  • Profile a query: Use the Query Engine, Trino, the SDK and Spark to see whether filters pushed down, what came back and where time went.
  • Event sourcing and CQRS: Build an append-only event table and a current-state table with the Python SDK, using idempotent writes, a watermark and rebuild by replay.

Best practices

Recommended designs and starting values for VAST DataBase workloads; measure with your own data before you rely on them. If you run Trino against your tables, start with Trino with VAST DataBase.

  • Trino with VAST DataBase: Starting recommendations for Trino on VAST DataBase: network, endpoints, splits, node sizing, separate workloads and table design for Trino queries.

Integrations

How to use VAST DataBase from each client and engine. Start with Python SDK if you write Python, or Trino or Spark if you use those engines; VAST Query Engine (ADBC) covers SQL on the cluster itself, and Arrow, pandas and Polars covers dataframes.

  • Python SDK: How the vastdb SDK's objects fit together, with idioms for create-or-get, transactions, metadata reuse, configuration, retries, threads and tests.
  • Trino: Use VAST DataBase as a Trino catalog: catalog properties, name mapping, partitioned and sorted tables, pushdown, EXPLAIN and row and column security.
  • Spark: Use the ndb catalog from Spark SQL and PySpark: connector properties, name mapping, partitioned and sorted tables, views and row security.
  • VAST Query Engine (ADBC): Run SQL in the VAST Query Engine through the ADBC driver, standalone or inside a Python SDK transaction, and impersonate end users.
  • Arrow, pandas and Polars: Move data between VAST DataBase and pandas, Polars, DuckDB and Parquet through Arrow, and watch for type conversions on insert.

Reference

Exact facts to look up while you work. Start with the Cheat sheet for the common SDK calls and the Python SDK reference for every signature; Limits, Data types, Pushdowns and Version compatibility answer specific questions, and the SQL reference covers the VAST Query Engine.

  • Python SDK reference: API reference for vastdb 2.1.0: sessions, transactions, schemas, tables, select, insert, imports, partitions, projections, configuration and exceptions.
  • Data types: Supported column types named in Arrow, Trino, Spark and the Python SDK, with precision caveats, vectors and types the SDK can't use.
  • Pushdowns: Which operations and predicates VAST DataBase evaluates on its CNodes next to the data, and how the SDK and Trino use them.
  • Limits: Size and count limits for tables, what can't be changed later, which table structures combine, and partition transforms.
  • Version compatibility: Which vastdb SDK works with your cluster, which SDK features need a newer cluster, and which Trino, Spark and ADBC versions fit.
  • Cheat sheet: One page of the Python SDK calls you'll use most, filters that push down, main limits, permissions and where to look next.
  • Automation tools: Compare the VMS REST API, vastpy, Terraform provider and admin MCP server: coverage per service, authentication, API versions and limits.

SQL reference

The SQL the VAST Query Engine accepts in VAST Cluster 5.5, one page per statement. Start with Overview for how statements reach the engine, then SELECT; check What the VAST Query Engine doesn't support before you write joins or GROUP BY.

  • Overview: The SQL the VAST Query Engine accepts over ADBC: object names, how statements run, what errors look like, and each statement.
  • CREATE SCHEMA: Create a schema in a database bucket, or inside another schema, with the VAST Query Engine, with rules, errors and an example.
  • DROP SCHEMA: Drop a schema in the VAST Query Engine: syntax, rules, what happens on the cluster, errors and the Trino and Spark equivalents.
  • SHOW SCHEMAS and SHOW TABLES: List a database's schemas or a schema's tables with SHOW in the VAST Query Engine, or a table's columns as DESCRIBE does.
  • CREATE TABLE: Create a regular, sorted or vector-indexed table in the VAST Query Engine, with syntax, rules, errors and Trino and Spark equivalents.
  • ALTER TABLE: Rename a table, add, rename and drop columns, and add a sort key with ALTER TABLE in the VAST Query Engine.
  • DROP TABLE: Drop a table and its data in the VAST Query Engine, with syntax, rules, cluster effects, errors and Trino and Spark equivalents.
  • DESCRIBE: Show a table's columns and their types in the VAST Query Engine, with syntax, rules, errors and the Trino and Spark equivalents.
  • SELECT: Query one table, a snapshot or an Event Broker topic, with rules for where filters run, IN lists, aggregates and rowid.
  • INSERT: Insert rows as values or from a SELECT in the VAST Query Engine, with syntax, rules, errors and Trino and Spark equivalents.
  • Import Parquet files with INSERT: Import Parquet files already in a cluster bucket into a table with INSERT, so the data never passes through your client.
  • UPDATE: Change column values in rows matching a condition with UPDATE in the Query Engine, with rules, errors and Trino and Spark equivalents.
  • DELETE and TRUNCATE: Delete rows matching a condition with DELETE, or every row while keeping the table with TRUNCATE, in the VAST Query Engine.
  • EXPLAIN: Show the Query Engine's plan without running a statement, to see which conditions VAST DataBase evaluates and which step fails.
  • Data types in SQL: SQL type names for VAST Query Engine statements, the VAST DataBase type each creates, and the types the Query Engine can't read.
  • Vector search SQL: A short index of vector search SQL and distance functions, whose full reference lives in the VAST Vector Search section.
  • What the VAST Query Engine doesn't support: SQL the VAST Query Engine refuses, such as joins, GROUP BY, subqueries and upserts, with each error and what to do instead.
  • Query Engine, Trino, Spark and the SDK compared: Compare what the VAST Query Engine, Trino, Spark and the Python SDK can each do on the same VAST DataBase tables.

Functions

The functions and operators the VAST Query Engine evaluates, by group. Read Overview first to see why a function on a column runs after the scan, then Operators and predicates; the other pages list each group's functions and what isn't available.

  • Overview: How the VAST Query Engine evaluates functions and operators, and why only column-to-constant comparisons are evaluated in VAST DataBase.
  • All functions A-Z: Every SQL function name the VAST Query Engine knows, with a link to its page, and the names it doesn't have, with what to use instead.
  • String: Text functions on VARCHAR values, in four pages. Start with Text basics for length, case, substrings and replacing; Search and split covers finding text and splitting it, Trimming and padding the fixed-width forms, and Regular expressions the regexp functions.
  • Aggregate and analytic functions: Every aggregate the VAST Query Engine runs, with types, NULL handling and an example each, how they run and how to make them faster.
  • JSON functions: Every JSON function the VAST Query Engine runs on JSON text in VARCHAR columns, with paths, types, NULL handling and how to speed them up.
  • Date and time functions: Every date, time and interval function the VAST Query Engine runs, what fails without time zones, and how to keep date filters fast.
  • Numeric and math functions: Every rounding, arithmetic, math, bit and number-formatting function the VAST Query Engine runs, with types, NULLs, errors and an example each.
  • Conditional and conversion functions: CAST, TRY_CAST, CASE, coalesce, nullif, greatest and least in the VAST Query Engine, with types, NULL handling and pushdown.
  • List functions: Every list, lambda, struct and map function the VAST Query Engine runs, with types, NULL handling, how they run and how to speed them up.
  • Map functions: map_concat, map_from_entries, map, map_entries and 9 more.
  • Struct functions: struct_extract_at, struct_extract, struct_pack, row and 2 more.
  • Utility functions: Settings, variables and the search path, logging, PostgreSQL-compatible helpers, sort keys and type names.
  • Table functions: Table functions such as range, generate_series and unnest that the VAST Query Engine runs in FROM, and what replaces file readers.
  • Enum functions: enum_code, enum_first, enum_last, enum_range_boundary and 1 more.
  • Binary, hash and UUID functions: Bit strings, BLOB data and encodings, hashes, UUIDs and enums in the VAST Query Engine, with types, NULL handling and examples.
  • Operators: Comparison, logical, pattern-matching, arithmetic, bitwise and list operators in the VAST Query Engine, and which predicates VAST DataBase evaluates itself.
  • Vector distance functions: array_cosine_distance, array_distance, array_negative_inner_product.

Operations

What your code needs around it: access, permissions, security, monitoring and automation. Start with Setup prerequisites to get access; read Security and Performance as your workload grows, and CI/CD and automation when you provision from a pipeline.

  • Setup prerequisites: What your platform team sets up before you use VAST DataBase, the permissions each SDK operation needs, example policies and a check.
  • Security: How identity policies, row and column security and impersonation affect what you can see and do from the SDK, Trino and Spark.
  • Performance: Speed up queries and loads through table structure, narrower requests, large write batches and spread endpoints, and which cluster settings to request.
  • Monitoring your workload: See your own queries and loads from the client: SDK logging, table statistics, sort progress, the partitions view and the imports table.
  • Snapshots and replication: Query a table as it was in a snapshot, and learn what asynchronous replication means for the data on a replica.
  • CI/CD and automation: Create and check a database's view, policies, user and keys from CI with the REST API, vastpy and Terraform, then its tables.

Troubleshooting

Help when something goes wrong. Start with Troubleshooting to match a symptom to its cause; if you have an exception, search the Error reference for the last line of your traceback, and read the FAQ for common questions.

  • Troubleshooting: Match a symptom to its likely cause and fix, across connecting and permissions, queries, writes and imports, and engines and clients.
  • Error reference: Python SDK 2.1.0 errors by area, from connecting and permissions to queries, writes, imports and ADBC, with each cause and its fix.
  • FAQ: Answers about platform-team requests, SDK versions, endpoints, table layouts, row order, transactions, Trino versions, the Query Engine and reporting problems.
Related articles
  • SQL reference
    Developer Guide > VAST DataBase > Reference > SQL reference
  • Overview
    Developer Guide > VAST DataBase
  • FAQ
    Developer Guide > VAST DataBase > Troubleshooting