Overview

ASQL is an in-memory SQL database engine. It holds every table in memory as compressed columns, compiles queries to native code, and writes every commit to disk before it answers. Clients connect over the MySQL protocol. On top of tables it keeps two kinds of live state, Things and Cells, current as rows change. It is written in C++20 with no external dependencies. (Other protocols and grammars are available on request. Contact us.)

Storage

• In memory. Every table is held in memory, compressed, so no query waits on a disk read.
• Columns. Each column is stored apart, so a query reads only the columns it uses.
• Compressed per column. Text is dictionary-coded, with codes packed into as few bits as the column needs (1-32). Numbers use frame-of-reference or run-length coding; text can use FSST; cold columns are kept as LZ4 blocks. The encoding is chosen per column and does not change how values behave.
• In practice. A customer's production database, nearly ten years of data in 62 million rows across 881 tables, takes 18 GB in MariaDB (5.7 GB of data, 12.2 GB of indexes) and 4.1 GB as plain text. ASQL holds it in 1.0 GB of memory.
• Zone maps. Every 2048 rows keep the smallest and largest value of each column, so a filter skips segments that cannot match.
• Write buffer. New rows go to a row buffer first and are folded into the columns in the background.
• On disk. A server keeps its data in one directory (/opt/aios/root/sql_db for the installed service).

Running queries

• Compiled. Filters, expressions and aggregates compile to typed vector programs, which are compiled again to native machine code on x86-64 and arm64.
• Parallel. A large scan is split into morsels that run on every core.
• Indexes built on demand. The primary key is a hash map. For other columns, the engine builds hash and sorted indexes the first time a query needs them, within a memory budget.
• Result cache. A repeated query is answered from its stored result until a table it reads is written.
• Memory admission. Each query's working memory is counted against a budget (2 GB by default); a query that would exceed it waits instead of running the server out of memory.

Writes and durability

• Redo log. Every commit is appended to a redo log and synced to disk before the client gets its reply. Commits that arrive together share one sync.
• Checkpoints. The tables are written to disk in the background and a new log is started: after 64 MB of log, or after 20,000 writes once 30 seconds have passed since the last one.
• Restart. The server loads the last checkpoint and replays the log written since, so it comes back exactly as of its last commit. See Durability and checkpoints.

Transactions

• Row ownership. A write takes the row for its transaction. Two transactions that both add to the same value (stock = stock - 1) go ahead together.
• Deadlocks. Waits that form a cycle are detected and one transaction is rolled back.
• Reads. Readers take no locks and see the latest value of each row.

Performance

Measured on a 4-core Intel i5-7600K with NVMe storage, on a 500,000-row table, through the MySQL protocol from a client on the same machine, with the result cache off and every commit synced to disk.

Things

A Thing is declared as one GROUP BY query and is, at once, a materialized view, a record per key, a history, a change feed and a trigger. The engine keeps every part of it current.

• Materialized view. After a write commits, and before the client gets its reply, the query runs again for only the partitions the write touched. Reading a Thing never runs its query.
• Record per key. Each key carries its values, a version and the time it last changed.
• History. Each key keeps its recent changes and their cause. AS OF reads the whole result at a past time, and EXPLAIN THING lists the source rows behind a value.
• Change feed. WAIT THING ... SINCE returns as soon as the Thing changes, with the keys that changed, so a client follows it without polling.
• Trigger. A rule runs SQL on another database once each time a condition on a key becomes true. In shadow mode it records the statements instead, and WHAT IF shows how a write would change every Thing before it is made.
• More. A Thing can also write its result to a table (INTO), allocate stock to orders by priority, compute its keys with a Grain program, or run once at a set time.

Cells

Cells are per-key counters and flags over one table, kept without storing rows.

• Values. COUNT, SUM, MIN and MAX, each with an optional filter, and up to 64 flags per key that are on when all, any or none of the key's rows match a condition.
• Maintenance. Each row change subtracts the row's old values and adds its new ones; no query runs.
• Actions. When a flag turns on or off, run SQL on another database, call a webhook or send an email. Actions run on their own thread after the commit, are recorded before they run, and are retried on the key's next change if they fail.

Replication

• From MySQL and MariaDB. ASQL follows the server's binary log and keeps chosen tables in step: it copies each table once from a consistent snapshot, then applies every change as it arrives. Things and Cells over those tables stay current with the source. See Mirroring MySQL and MariaDB.
• To MySQL, MariaDB, PostgreSQL and SQL Server. In write-through or write-behind mode, ASQL sends each write on to another database, translated to its SQL dialect: write-through before applying it, write-behind from a queue kept on disk. A query ASQL cannot run itself is passed to that database.
• As a source. ASQL can write a binary log that a MySQL or MariaDB replica follows.

Several servers

• Linking. ASQL servers join a fleet that shares a key. The installed service joins one when FLEET_HOST, FLEET_PORT and FLEET_KEY_FILE are set in asql.conf.
• SQL on another server. A Grain program runs a statement on any member with sql_on(). Members can read each other's data; writing needs fleet sql writes=on on the receiving server, and mirrored tables are never written this way.
• Things across servers. SHOW THING ... ON <server> reads another server's Thing, and ON ALL merges every server's result with a column naming the server. A Thing can also keep a copy of another server's Thing, refreshed every few seconds.

Connecting

• MySQL protocol on port 3307: MySQL drivers, ORMs and GUI tools connect unchanged. See MySQL protocol.
• JSON RPC on port 7311, for read queries without a MySQL driver. See JSON RPC.
• Grain, in the same process: sql("SELECT ...") with no connection at all. See Grain.

Other protocols and grammars are available on request. Contact us.

Access

• Credentials. Access is a signed credential naming tables, rights, an expiry and a row limit.
• Users. Users and GRANT are built on these credentials.
• Narrowing. A credential's holder can narrow it further without the server. See Users, tokens and TLS.

Platforms

• Linux x86-64 with glibc 2.17 or newer: CentOS 7 and later, AlmaLinux, Rocky, Debian, Ubuntu
• Other platforms are available upon request. Contact us.

See Installation.