relational

Tables

Postgres-wire SQL with indexes, joins, transactions.

Try Tables free See all 9 data models

What it is

Tables on RedDB.

RedDB ships first-class relational tables on the same engine that handles every other data model. Existing Postgres clients (psql, pgx, JDBC, Prisma, DBeaver) connect over the wire protocol without changes.

Schema is enforced. Indexes are first-class — CREATE INDEX is parsed, planned, and used by the optimizer. ACID transactions span every operation, including writes that touch tables and other models in the same statement.

Migration from Postgres is the import path most teams take first because no driver swap is involved.

Code

Write it. Read it. Same engine.

Insert

INSERT INTO users (id, name, email, role) VALUES (42, 'Alice', 'alice@co.com', 'admin');

Query

SELECT name, role FROM users WHERE email LIKE '%@co.com' ORDER BY id LIMIT 10;

Driver examples

Connect from your language.

Real driver code — copy-paste ready, no pseudocode.

import { Client } from '@reddb/client'

const db = new Client({ url: process.env.REDDB_URL })

// Insert a row
await db.query(
  'INSERT INTO users (id, email, name, role) VALUES ($1, $2, $3, $4) RETURNING id, created_at',
  [42, 'alice@co.com', 'Alice', 'admin'],
)

// Read it back
const { rows } = await db.query<{ name: string; role: string }>(
  'SELECT name, role FROM users WHERE email = $1',
  ['alice@co.com'],
)
console.log(rows[0]) // { name: 'Alice', role: 'admin' }

Use cases

Where tables earn their place.

  • Auth + sessions

    Users, sessions, refresh tokens, audit log — the relational core most apps start with.

  • Billing + transactions

    Subscriptions, invoices, line items — anywhere strong ACID matters more than throughput.

  • Admin panels

    CRUD over typed rows is what every Retool / Forest dashboard expects.

  • Analytics fact tables

    Wire-compatible means BI tools (Metabase, Superset) point and query without an adapter.

Build it

End-to-end walkthrough.

Four steps from empty database to a real tables workload — each step shows the exact code and explains what the engine is doing.

  1. Step 1

    1. Create the table

    Schema is enforced at write time — RedDB's planner uses the index on email for any equality / prefix lookup. The unique constraint is real: a duplicate insert raises, just like Postgres.

    CREATE TABLE users (
  id           BIGINT PRIMARY KEY,
  email        TEXT NOT NULL,
  name         TEXT NOT NULL,
  role         TEXT NOT NULL DEFAULT 'member',
  created_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE UNIQUE INDEX idx_users_email ON users (email);

  2. Step 2

    2. Insert + read back

    RETURNING round-trips the server-assigned created_at in the same statement so the application doesn't need a follow-up read. The select hits the unique index — sub-millisecond on cache-warm.

    INSERT INTO users (id, email, name, role)
VALUES (42, 'alice@co.com', 'Alice', 'admin')
RETURNING id, created_at;

SELECT id, name, role, created_at
FROM users
WHERE email = 'alice@co.com';

  3. Step 3

    3. Join across data models

    Tables can be joined to documents, vectors, time-series — anything in the engine. The planner handles cross-model joins with the same query optimiser used for table-only joins; no application-side glue.

    -- 'events' is a document collection on the same engine.
SELECT u.name, COUNT(*) AS events_30d
FROM users u
JOIN events e ON e.body->>'user_id' = u.id::text
WHERE e.created_at > now() - INTERVAL '30 days'
GROUP BY u.name
ORDER BY events_30d DESC
LIMIT 10;

  4. Step 4

    4. Wrap mutations in a transaction

    Standard BEGIN / COMMIT semantics. ACID applies across every model — you can update a row, append to a queue, and write a log line in one atomic commit.

    BEGIN;
  UPDATE users SET role = 'admin' WHERE id = 42;
  INSERT INTO audit_log (actor_id, action, target)
  VALUES (current_user_id(), 'role_grant', '42');
COMMIT;

Engine features

What RedDB adds on top of tables.

The engine knobs you would otherwise wire up yourself — TTL, encryption, indexes, atomic ops, eventual consistency — applied to this data model.

  • Indexes (BTree, UNIQUE, functional)

    CREATE INDEX [UNIQUE] [IF NOT EXISTS] is a real engine surface — the planner uses it for equality, range, and prefix queries. Functional indexes (e.g. on lower(email) or body->>field) earn their keep when you query an expression repeatedly.

  • Constraints (NOT NULL, UNIQUE, CHECK)

    Schema validation runs at write time. A duplicate insert against a UNIQUE column raises; a CHECK violation raises. This is what every Postgres user expects, and it survives the wire.

  • Native types (Email · IpAddr · Money · GeoPoint · Url · Uuid)

    Domain-specific column types validate format on insert and serialise to clean JSON on read. No custom parser per service — Money round-trips as { asset_code, minor_units, scale }, Email rejects the malformed before persistence.

  • Refs (NodeRef · EdgeRef · VectorRef · RowRef · KeyRef · DocRef)

    Typed cross-collection pointers. A service_ref column with type RowRef constrains the value to point at a real row in services — broken refs surface at insert time, not at JOIN time.

  • Encryption at rest (Value::Secret columns)

    Tag any column as Value::Secret and the engine encrypts it with the vault AES key on INSERT, sealed reads return *** until an authorised principal decrypts. PCI / PII workloads stop needing a sidecar KMS for column-level secrets.

  • Result cache (automatic)

    Identical SELECT queries return from a 30-second result cache (1000 entries). Any write that touches the affected tables invalidates entries via the engine's CDC channel — no stale reads after a commit.

  • ACID across data models

    A single BEGIN / COMMIT can update a row, push to a queue, write a time-series point, invalidate a cache namespace. Transactionality is engine-wide, not per-collection.

  • IAM-style policies (JSON, simulatable)

    JSON policy documents drop the AWS IAM ceremony — Sid optional, no Principal block. Attach a policy to a user / group; the engine switches to deny-by-default. Policy decisions are simulatable before attachment and logged per request.

  • Append-only mode (variant)

    CREATE APPEND TABLE audit_log (…) rejects UPDATE / DELETE at parse time. Use for ledgers, event streams, audit logs — anywhere immutability is a correctness requirement, not a policy.

  • Audit log integration

    Every policy decision and admin action is itself logged into an append-only audit_log collection — actor, target, timestamp, policy that fired. Queryable like any other table.

  • Multi-tenancy via policies

    A tenant_id claim on the principal narrows every query through the policy layer. Operators stop building tenant-isolation middleware per service — the engine enforces it.

  • Schema migrations (versioned, reversible)

    Engine-native migration runner with versioned DDL, up/down scripts, and parse-time idempotency (IF NOT EXISTS everywhere). Re-running a migration is a no-op; rolling back walks the inverse. Fits flyway / sqitch workflows without a sidecar.

Showcases

What tables can do once you bring the rest of the engine in.

Cross-model correlation, algorithm flex, multi-model patterns — things that take a stack of services elsewhere.

Runtime-tunable queries via KV configs

Pagination, rate-limits, query thresholds — addressable via $config.<path> directly inside SELECT.

A canonical multi-tenant pagination + filter pattern reads its knobs from the KV store at parse time. Operations changes the value once; every query everywhere picks it up on next execution. The substitution is a typed Value (function-call AST node), not a textual splice, so SQL injection through KV writes is structurally impossible.

sql

-- Set per-tenant config in KV.
SET CONFIG acme.pagination.pageSize = 100;
SET CONFIG acme.search.maxResults  = 50;

-- Reference inline. No application-layer fetch + format step.
SELECT id, name, role
FROM users
WHERE org_id = $1
  AND (created_at, id) < ($2, $3)
ORDER BY created_at DESC, id DESC
LIMIT $config.acme.pagination.pageSize;

-- Vault secrets work the same way.
SELECT *
FROM hosts
WHERE deployer_token = $secret.acme.deploy.token;
-- Engine pulls the secret from vault as a typed Value;
-- nothing leaks into logs or query plans.

Native types stop wrong data at the door

IpAddr, Email, Money, GeoPoint validate at INSERT. No application-side parser.

A hosts table mixes networking, geo, and money columns in one row. The engine validates each field type on write — a malformed CIDR or a negative scale Money raises before persistence. JSON output is shaped per type (Money → {asset_code, minor_units, scale}), so clients don't reinvent serialisers either.

sql

CREATE TABLE hosts (
  ip            IpAddr      NOT NULL,
  cidr          Cidr        NOT NULL,
  location      GeoPoint    NOT NULL,
  owner         Email       NOT NULL,
  monthly_cost  Money       NOT NULL,
  service_ref   RowRef('services')
);

INSERT INTO hosts (ip, cidr, location, owner, monthly_cost, service_ref)
VALUES (
  '10.0.0.1',
  '10.0.0.0/24',
  'POINT(-46.633308 -23.550520)',
  'alice@co.com',
  '{"asset_code":"USD","minor_units":1299,"scale":2}',
  'svc_42'
);

-- Engine rejects 'not-an-ip', '999.0.0.1', '-1 USD', or a service_ref
-- pointing at a missing services row.

Cross-model JOIN: rows + documents + vectors in one query

Find admin users with a recent suspicious-login event AND a near-duplicate note.

A relational users table joins to a documents events collection and a vector notes collection in a single SELECT. The planner picks the cheapest filter (the role = admin BTree seek) and threads the rest through. No application-side correlator, no two-system join.

sql

WITH suspicious AS (
  SEARCH SIMILAR TEXT 'unusual login pattern'
    COLLECTION notes LIMIT 50
)
SELECT u.id, u.email,
       e.body->>'ip'    AS event_ip,
       s.score          AS note_similarity
FROM users u
JOIN events e ON e.body->>'user_id' = u.id::text
                AND e.created_at > now() - INTERVAL '24h'
JOIN suspicious s ON s.note_meta->>'user_id' = u.id::text
WHERE u.role = 'admin'
ORDER BY s.score DESC, e.created_at DESC;

Atomic outbox across tables and queues

Order create + inventory decrement + email enqueue in one transaction.

Three writes that must be all-or-nothing — and historically required a transactional-outbox library + a separate broker. RedDB does it in one BEGIN/COMMIT because the queue is on the same engine as the tables.

sql

BEGIN;
  INSERT INTO orders (id, customer_id, total)
  VALUES ($1, $2, $3);

  UPDATE inventory
     SET available = available - 1
   WHERE sku = $4
     AND available > 0;

  -- Side effect that fans out asynchronously.
  QUEUE PUSH order_emails
    json_build_object('order_id', $1, 'customer_id', $2);
COMMIT;
-- All three land or none do. No outbox library required.

Migrate from

Bring tables over from your current tool.

From

Postgres

pg_dump → restore. Same wire, same SQL — application driver does not change.

# 1. Dump from Postgres
pg_dump --no-owner --no-acl --data-only \
  --table=users --table=billing \
  postgresql://prod-pg:5432/app > dump.sql

# 2. Restore into RedDB
red restore --from dump.sql \
  reds://admin@<your-db>.<org>.db.reddb.io:5050

From

MySQL

Use a Postgres-compat translator (mysql2pg, dbcrossbar) to convert the schema, then load via the same wire.

mysqldump --compatible=postgresql --skip-extended-insert app \
  | mysql2pg --target=postgres \
  | psql "reds://admin@<your-db>.<org>.db.reddb.io:5050"

Recipes

Common patterns, real driver code.

sql

Cursor pagination

Use a stable secondary sort to paginate millions of rows without OFFSET scans.

SELECT id, name, created_at
FROM users
WHERE (created_at, id) < ($cursor_ts, $cursor_id)
ORDER BY created_at DESC, id DESC
LIMIT 50;

sql

Optimistic concurrency

Use a version column + WHERE to safely apply concurrent updates without a row lock.

UPDATE projects
SET name = $new_name, version = version + 1
WHERE id = $id AND version = $expected_version
RETURNING version;
-- 0 rows back means another writer raced you; reload + retry.

typescript

Server-driven app state

Driver call — RedDB JS driver speaks the same wire as pg.

import { connect } from '@reddb-io/client'

const db = await connect('reds://admin@.../my-db:5050')
const result = await db.query<{ id: number; name: string }>(
  'SELECT id, name FROM users WHERE org_id = $1 ORDER BY id LIMIT $2',
  [orgId, 50],
)
return result.rows

Limits

What it does. What it costs.

Operational caps and durability semantics, no hand-waving.

Max row size
~64 MB (driven by page size + TOAST)
Max columns per table
1,600
Indexes per table
Unlimited (each costs WAL + storage)
Transaction isolation
Read committed by default; serializable on demand
Durability
fsync + WAL on every commit

When NOT to use

Don't pick tables for these.

Honest constraints — when another model fits better.

  • Don't use Tables for opaque blobs > 1 MB

    Large blobs inflate WAL and slow down every read on the page. Use Cache (TTL) or document collections with a JSON pointer to object storage instead.

  • Don't store time-series data in a regular table

    No retention, no chunk pruning, no time_bucket window. The Time-series collection ships those as first-class — your queries get faster as the dataset grows, not slower.

  • Avoid wide-wide schemas with hundreds of nullable columns

    A row with 200 cols where 180 are null is the document model trying to escape a relational shape. Move the sparse part to a body JSONB column or to a sibling document collection.

vs

How RedDB tables compare.

Postgres

Same wire, same SQL surface. RedDB drops the side-car databases you needed for vectors / queues / cache.

MySQL

Postgres-compat parser is closer to standard SQL. Joins, CTEs, window functions land the same.

Pairs with

Tables earns more next to these.

The data models tables compose with — a click away.

FAQ

Questions teams ask before they pick tables.

Can I point my existing Postgres driver at RedDB?

Yes. RedDB speaks the Postgres wire protocol. psql, pgx, JDBC, Prisma, DBeaver, and every BI tool that talks to Postgres connects without changes — same DSN shape, same authentication.

Are indexes real, or just hints?

Real. CREATE INDEX is parsed, materialised, and the optimiser uses it. EXPLAIN shows the access plan and confirms whether a seek or scan is happening.

Do you support transactions across data models?

Yes. A single BEGIN / COMMIT can update a table, enqueue a message, and write a time-series point — all atomic. ACID is engine-wide, not per-model.

How does it differ from Postgres for relational workloads?

For pure relational, the surface is intentionally close to Postgres. The differentiation is at the engine boundary: the same database also handles vectors, queues, time-series, cache, and graphs natively, so you stop running side-car databases.

Is the SQL parser a Postgres fork?

No. The parser is a fresh implementation that targets Postgres-wire compatibility. Most application SQL works untouched; some edge-case Postgres extensions are not implemented yet — see the docs for the matrix.

Explore

Every other data model on RedDB.

One engine, 9 first-class data models. Mix as needed.

Try tables on RedDB.

Free nano database, no credit card. AGPL self-host or managed Cloud — same engine, same file format.

Start free See pricing