DDL

Data Definition Language (DDL) statements create, modify, and drop database objects.

CREATE TABLE

CREATE TABLE table_name (
    column_name data_type [PRIMARY KEY] [NOT NULL] [DEFAULT expr],
    ...
    [PRIMARY KEY (col1, col2, ...)]
);

Basic Table

CREATE TABLE products (
    id         BIGINT PRIMARY KEY,
    name       TEXT NOT NULL,
    price      FLOAT64,
    category   TEXT DEFAULT 'uncategorized',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Composite Primary Key

CREATE TABLE order_items (
    order_id   BIGINT NOT NULL,
    product_id BIGINT NOT NULL,
    quantity   INT64 NOT NULL DEFAULT 1,
    unit_price FLOAT64 NOT NULL,
    PRIMARY KEY (order_id, product_id)
);

IF NOT EXISTS

Skip creation if the table already exists (no error):

CREATE TABLE IF NOT EXISTS sessions (
    id         BIGINT PRIMARY KEY,
    user_id    BIGINT NOT NULL,
    token      TEXT NOT NULL,
    expires_at TIMESTAMP
);

Table with Vector Column

CREATE TABLE articles (
    id        BIGINT PRIMARY KEY,
    title     TEXT NOT NULL,
    body      TEXT,
    embedding VECTOR(768),
    metadata  JSON
);

Table with Storage Options (pgbench Compatibility)

The WITH (...) clause is accepted on CREATE TABLE for compatibility with pgbench and other PostgreSQL tools. ParticleDB parses these options but does not enforce them — they are silently accepted.

-- Accepted for pgbench compatibility; fillfactor is a no-op
CREATE TABLE pgbench_accounts (
    aid      BIGINT PRIMARY KEY,
    bid      BIGINT,
    abalance BIGINT,
    filler   TEXT
) WITH (fillfactor=100);

Table with Enum Column

-- First, create the enum type
CREATE TYPE priority AS ENUM ('low', 'medium', 'high', 'critical');

-- Then use it in a table
CREATE TABLE tickets (
    id       BIGINT PRIMARY KEY,
    title    TEXT NOT NULL,
    priority priority NOT NULL DEFAULT 'medium',
    status   TEXT DEFAULT 'open'
);

---

DROP TABLE

Remove a table and all its data.

DROP TABLE products;

-- Don't error if the table doesn't exist
DROP TABLE IF EXISTS products;

-- Drop multiple tables at once
DROP TABLE temp_data, staging_data, old_logs;

---

ALTER TABLE

Modify an existing table’s schema or properties.

ADD COLUMN

ALTER TABLE products ADD COLUMN description TEXT;

ALTER TABLE products ADD COLUMN updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP;

DROP COLUMN

ALTER TABLE products DROP COLUMN description;

RENAME COLUMN

ALTER TABLE products RENAME COLUMN category TO product_category;

RENAME TABLE

ALTER TABLE products RENAME TO catalog;

ADD PRIMARY KEY

Add a primary key constraint to a table that does not have one:

ALTER TABLE legacy_data ADD PRIMARY KEY (id);

-- Composite primary key
ALTER TABLE event_log ADD PRIMARY KEY (event_date, event_id);

-- Named constraint form
ALTER TABLE legacy_data ADD CONSTRAINT pk_legacy PRIMARY KEY (id);

SET STORAGE TIER

Move a table’s data between storage tiers:

-- Move to cold storage (cheaper, slower)
ALTER TABLE archived_logs SET STORAGE TIER cold;

-- Move back to hot storage
ALTER TABLE archived_logs SET STORAGE TIER hot;

SET COMPRESSION

Configure per-table compression codec for storage segments:

-- No compression (fastest reads)
ALTER TABLE realtime_metrics SET COMPRESSION 'none';

-- LZ4 compression (fast, moderate ratio)
ALTER TABLE logs SET COMPRESSION 'lz4';

-- Zstandard compression (best ratio, slower)
ALTER TABLE archives SET COMPRESSION 'zstd';

SET PINNED

Pin a table in cache to prevent LRU eviction:

-- Keep this table always in memory
ALTER TABLE hot_config SET PINNED = true;

-- Allow eviction
ALTER TABLE hot_config SET PINNED = false;

ENABLE / DISABLE Row-Level Security

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
ALTER TABLE orders DISABLE ROW LEVEL SECURITY;

---

TRUNCATE

Remove all rows from a table without dropping the table itself. Faster than DELETE FROM table because it doesn’t scan individual rows.

TRUNCATE TABLE temp_results;

---

ANALYZE

Compute or refresh table statistics used by the cost-based query optimizer. Run this after bulk data loads for optimal query plans.

ANALYZE orders;

---

CREATE INDEX

Create an index on one or more columns for faster lookups.

B-Tree Index (Default)

-- Single column
CREATE INDEX idx_orders_customer ON orders (customer_id);

-- Multi-column
CREATE INDEX idx_orders_status_date ON orders (status, created_at);

-- Unique index
CREATE UNIQUE INDEX idx_users_email ON users (email);

HNSW Vector Index

CREATE INDEX idx_articles_embedding ON articles
USING hnsw (embedding)
WITH (
    m = 16,
    ef_construction = 200,
    metric = 'cosine'
);

IVF Flat Vector Index

CREATE INDEX idx_articles_embedding_ivf ON articles
USING ivf_flat (embedding)
WITH (
    num_lists = 100,
    metric = 'l2'
);

See Vector Search for index parameter details.

DROP INDEX

DROP INDEX idx_orders_customer;

DROP INDEX IF EXISTS idx_orders_customer;

---

CREATE VIEW

Define a named query that can be referenced like a table.

CREATE VIEW active_orders AS
SELECT o.id, o.total, c.name AS customer_name
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.status != 'cancelled';

-- Query the view like a table
SELECT * FROM active_orders WHERE total > 100;

OR REPLACE

Overwrite an existing view:

CREATE OR REPLACE VIEW active_orders AS
SELECT o.id, o.total, o.status, c.name AS customer_name
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.status NOT IN ('cancelled', 'refunded');

DROP VIEW

DROP VIEW active_orders;

DROP VIEW IF EXISTS active_orders;

---

CREATE TYPE AS ENUM

Define a user-defined enumerated type. Values are validated on INSERT and UPDATE.

CREATE TYPE order_status AS ENUM ('pending', 'processing', 'shipped', 'delivered', 'cancelled');

CREATE TYPE severity AS ENUM ('info', 'warning', 'error', 'critical');

Use in table definitions:

CREATE TABLE orders (
    id     BIGINT PRIMARY KEY,
    status order_status NOT NULL DEFAULT 'pending'
);

-- Only valid enum values are accepted
INSERT INTO orders VALUES (1, 'shipped');     -- OK
INSERT INTO orders VALUES (2, 'unknown');     -- ERROR

DROP TYPE

DROP TYPE order_status;

DROP TYPE IF EXISTS severity;

---

CREATE PROCEDURE

Define a stored procedure with a body enclosed in $$ delimiters.

CREATE PROCEDURE archive_old_orders(cutoff_days INT64) AS $$
    INSERT INTO orders_archive
    SELECT * FROM orders
    WHERE created_at < CURRENT_TIMESTAMP - (cutoff_days * 86400000000);

    DELETE FROM orders
    WHERE created_at < CURRENT_TIMESTAMP - (cutoff_days * 86400000000);
$$;

Call a Procedure

CALL archive_old_orders(90);

Drop a Procedure

DROP PROCEDURE archive_old_orders;

DROP PROCEDURE IF EXISTS archive_old_orders;

---

CREATE FUNCTION

Define a user-defined scalar function:

CREATE FUNCTION discount_price(original FLOAT64, discount_pct FLOAT64) RETURNS FLOAT64 AS $$
    SELECT original * (1.0 - discount_pct / 100.0)
$$;

Use in queries:

SELECT name, price, discount_price(price, 15) AS sale_price
FROM products;

Drop a Function

DROP FUNCTION discount_price;

DROP FUNCTION IF EXISTS discount_price;

---

Access Control

CREATE ROLE

Create a role (a named set of privileges). Roles cannot log in by default.

CREATE ROLE analyst;

CREATE ROLE IF NOT EXISTS data_engineer;

CREATE USER

Create a user — a role with login capability.

CREATE USER alice WITH PASSWORD 'secure_password_here';

-- Superuser
CREATE USER admin WITH PASSWORD 'admin_pass' SUPERUSER;

CREATE USER IF NOT EXISTS bob WITH PASSWORD 'bobs_password';

DROP ROLE / DROP USER

DROP ROLE analyst;
DROP ROLE IF EXISTS data_engineer;

DROP USER alice;
DROP USER IF EXISTS bob;

ALTER ROLE / ALTER USER

-- Change password
ALTER ROLE alice SET PASSWORD 'new_password';

-- Rename a role
ALTER ROLE analyst RENAME TO data_analyst;

-- Grant/revoke superuser
ALTER ROLE alice SET SUPERUSER true;
ALTER ROLE alice SET SUPERUSER false;

-- Enable/disable login
ALTER ROLE data_analyst SET LOGIN true;

GRANT

Grant privileges on a table to a role:

-- Grant specific privileges
GRANT SELECT ON orders TO analyst;
GRANT SELECT, INSERT ON products TO data_engineer;
GRANT SELECT, INSERT, UPDATE, DELETE ON orders TO admin;
GRANT ALL ON orders TO admin;

-- Grant role membership (role inheritance)
GRANT analyst TO alice;
GRANT data_engineer TO bob;

Available privileges: SELECT, INSERT, UPDATE, DELETE, ALL.

REVOKE

Remove privileges:

REVOKE INSERT ON orders FROM analyst;
REVOKE ALL ON products FROM data_engineer;

-- Revoke role membership
REVOKE analyst FROM alice;

---

Row-Level Security (RLS)

Row-level security restricts which rows a user can see or modify based on policies.

Enable RLS

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

CREATE POLICY

Define a policy that filters rows based on the current user’s context:

-- Users can only see their own orders
CREATE POLICY own_orders ON orders
FOR SELECT
TO ALL
USING (user_id = current_user_id());

-- Admins can see all orders
CREATE POLICY admin_all_orders ON orders
FOR ALL
TO admin
USING (true);

-- Policy for specific operations
CREATE POLICY insert_own ON orders
FOR INSERT
USING (user_id = current_user_id());

DROP POLICY

DROP POLICY own_orders ON orders;

Disable RLS

ALTER TABLE orders DISABLE ROW LEVEL SECURITY;

---

Attribute-Based Access Control (ABAC)

Fine-grained access control based on user, resource, and environment attributes.

Set User Attributes

ALTER ROLE alice SET ATTRIBUTE department = 'engineering';
ALTER ROLE alice SET ATTRIBUTE clearance = 'top_secret';

CREATE ABAC POLICY

-- Only users in the same department can view records
CREATE ABAC POLICY dept_isolation ON documents
FOR SELECT
WHEN (user.department = resource.department);

-- Clearance-based access
CREATE ABAC POLICY clearance_check ON classified_docs
FOR SELECT
WHEN (user.clearance = 'top_secret');

DROP ABAC POLICY

DROP ABAC POLICY dept_isolation ON documents;

---

Notifications

LISTEN / UNLISTEN / NOTIFY

Asynchronous notification channels (PostgreSQL-compatible):

-- Subscribe to a channel
LISTEN order_updates;

-- Send a notification (from another connection)
NOTIFY order_updates, 'order 42 has shipped';

-- Unsubscribe
UNLISTEN order_updates;

---

Alerts

Define monitoring alert rules that fire when metrics cross thresholds:

-- Create an alert rule
CREATE ALERT high_connections
WHEN connections_active > 100
FOR 60
SEVERITY warning
MESSAGE 'Connection count exceeds 100 for over 60 seconds';

-- Modify an alert
ALTER ALERT RULE 'high_connections' SET threshold = 150;
ALTER ALERT RULE 'high_connections' SET severity = 'critical';
ALTER ALERT RULE 'high_connections' SET enabled = 'false';

-- Remove an alert
DROP ALERT high_connections;

---

VACUUM

VACUUM and VACUUM ANALYZE are accepted as no-op statements for compatibility with PostgreSQL tools (pgbench, pg_dump, ORMs). ParticleDB’s LSM-tree storage engine handles space reclamation through background compaction, so explicit vacuuming is not needed.

-- Accepted, does nothing
VACUUM;
VACUUM ANALYZE;
VACUUM orders;
VACUUM ANALYZE orders;

---

COPY FROM STDIN

Bulk-load data from a client using the PostgreSQL COPY protocol. Data must be in tab-separated format with one row per line.

-- Load all columns
COPY products FROM STDIN;

-- Load specific columns
COPY products (id, name, price) FROM STDIN;

When executed from psql, the client enters copy mode and expects tab-separated rows terminated by a \. line:

main=# COPY products (id, name, price) FROM STDIN;
1  Widget  9.99
2  Gadget  19.99
\.
COPY 2

See PostgreSQL Wire Protocol — COPY FROM STDIN for protocol-level details and performance notes.

---

EXPLAIN

View the query execution plan without running the query:

EXPLAIN SELECT * FROM orders WHERE customer_id = 42;

Execute the query and show actual timing statistics:

EXPLAIN ANALYZE
SELECT category, COUNT(*), AVG(price)
FROM products
GROUP BY category;