Schema Management in Stoolap

This document covers Stoolap’s schema management capabilities, including table creation, alteration, and handling of primary keys, indexes, and data types.

Tables and Schemas

Stoolap provides standard SQL DDL (Data Definition Language) statements for managing database schemas.

Creating Tables

Tables can be created using the standard CREATE TABLE syntax:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    username TEXT NOT NULL,
    email TEXT,
    created_at TIMESTAMP,
    is_active BOOLEAN
);

Table Constraints

When creating tables, you can specify the following constraints:

  • PRIMARY KEY - Define a primary key constraint on one or more columns
  • NOT NULL - Enforce that a column cannot contain NULL values
  • UNIQUE - Enforce uniqueness on a column (also available via CREATE UNIQUE INDEX)
  • DEFAULT - Specify a default value for a column
  • CHECK - Define a check constraint expression over the row, on a column or on the table (see CHECK)
  • REFERENCES - Define a foreign key constraint referencing another table (see Foreign Keys)
CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id) ON DELETE CASCADE,
    amount FLOAT NOT NULL,
    status TEXT DEFAULT 'pending'
);

-- Table-level FOREIGN KEY constraint
CREATE TABLE order_items (
    id INTEGER PRIMARY KEY,
    order_id INTEGER,
    FOREIGN KEY(order_id) REFERENCES orders(id) ON DELETE CASCADE
);

Clustered Tables

A table can declare the order its rows are kept in once they are sealed into cold volumes. CLUSTER BY names one or more columns of the table; rows with equal leading values sit together on disk, ordered by the columns that follow.

CREATE TABLE ticks (
    id INTEGER PRIMARY KEY,
    exchange TEXT NOT NULL,
    symbol TEXT NOT NULL,
    time TIMESTAMP NOT NULL,
    price FLOAT
) CLUSTER BY (exchange, symbol, time);

Queries that filter on a prefix of the key and read a range of the last column touch only the volume row groups that hold those rows, instead of every row group of the table. Rows in the hot buffer are unaffected: the order applies when the checkpoint cycle seals them, and compaction keeps it when it merges volumes.

The key columns must have an order, so JSON and VECTOR columns cannot be part of it. A key column cannot be dropped with ALTER TABLE ... DROP COLUMN or changed to one of those types; dropping another column keeps the key on the same columns. SHOW CREATE TABLE shows the clause, and a snapshot restore or a reopen keeps it. Tables without the clause keep their rows in row id order, as before.

An existing table gets a key, or a new one, with ALTER TABLE:

ALTER TABLE ticks CLUSTER BY (exchange, symbol, time);

The volumes sealed before the change stay readable as they are; rows sealed from then on take the key order, and the checkpoint cycles that follow rewrite the older volumes into key order through compaction, at most compact_threshold of them per cycle so a large table is reclustered in bounded steps. Until a volume’s turn comes, queries on its rows answer as before, without the locality the key gives.

A table drops its key with DROP CLUSTER BY:

ALTER TABLE ticks DROP CLUSTER BY;

Rows sealed from then on are kept in row id order again, and the checkpoint cycles that follow rewrite the volumes sealed under the key back into row id order, in the same bounded steps. This works for a table without a primary key too, whose row ids are internal and cannot be named in a key. A column named cluster is still dropped with ALTER TABLE ... DROP cluster.

Altering Tables

Tables can be modified after creation using ALTER TABLE statements:

-- Add a new column
ALTER TABLE users ADD COLUMN last_login TIMESTAMP;

-- Drop a column
ALTER TABLE users DROP COLUMN is_active;

-- Rename a table
ALTER TABLE users RENAME TO system_users;

Data Types

Stoolap supports the following data types:

Numeric Types

  • INTEGER - Signed integer number
  • FLOAT - Floating-point number

String Types

  • TEXT - Variable-length character string

Date and Time Types

  • TIMESTAMP - Date and time

Boolean Type

  • BOOLEAN - True or false value

Special Types

  • JSON - JSON document
  • VECTOR(N) - Fixed-dimension floating-point vector (see Vector Search)

Primary Keys

Primary keys uniquely identify rows in a table:

-- Single-column primary key
CREATE TABLE products (
    product_id INTEGER PRIMARY KEY,
    name TEXT
);

-- Composite primary key
CREATE TABLE order_items (
    order_id INTEGER,
    product_id INTEGER,
    quantity INTEGER,
    PRIMARY KEY (order_id, product_id)
);

Indexes

Stoolap provides several index types for optimizing queries.

Creating Indexes

-- Create a B-tree index
CREATE INDEX idx_user_email ON users (email);

-- Create a unique index
CREATE UNIQUE INDEX idx_unique_username ON users (username);

-- Create a multi-column index
CREATE INDEX idx_name_created ON products (name, created_at);

Index Types

Stoolap supports multiple index implementations:

  1. B-tree Indexes - For numeric and timestamp columns, supporting equality and range queries
  2. Hash Indexes - For text and JSON columns, optimized for equality lookups
  3. Bitmap Indexes - For boolean columns and low-cardinality data
  4. HNSW Indexes - For vector columns, approximate nearest neighbor search (see Vector Search)
  5. Multi-column Indexes - For queries that filter on multiple columns together

ALTER TABLE

Modify an existing table’s structure:

-- Add a new column
ALTER TABLE users ADD COLUMN status TEXT DEFAULT 'active';

-- Drop a column
ALTER TABLE users DROP COLUMN status;

-- Rename a column
ALTER TABLE users RENAME COLUMN name TO full_name;

-- Modify a column's type
ALTER TABLE users MODIFY COLUMN age FLOAT;

-- Rename a table
ALTER TABLE users RENAME TO app_users;

Dropping Indexes

DROP INDEX idx_user_email;

-- Or with explicit table name
DROP INDEX idx_user_email ON users;

Schema Information

Stoolap provides system tables and commands to query schema information:

SHOW Commands

-- List all tables
SHOW TABLES;

-- Show table creation statement (includes structure)
SHOW CREATE TABLE users;

-- Show indexes for a table
SHOW INDEXES FROM users;

Implementation Details

Under the hood, Stoolap’s schema management is implemented with the following components:

  • Table metadata is stored in a structured format that tracks column definitions, constraints, and indexes
  • Schema changes are performed atomically, ensuring consistency
  • The parser and executor collaborate to implement DDL operations
  • Indexes are created in a non-blocking way when possible

Best Practices

  • Define primary keys for all tables to ensure row uniqueness
  • Create indexes on columns frequently used in WHERE clauses and join conditions
  • Use appropriate data types to optimize storage and query performance
  • Consider using multi-column indexes for queries that filter on multiple columns
  • Avoid excessive indexing, as it can impact write performance

Limitations

  • Certain ALTER TABLE operations may require significant processing time on large tables
  • Currently, online schema changes for large tables may temporarily block writes
  • There are limits on the number of columns and indexes per table for performance reasons