Databases
The geometry types and their spatial functions are built in. No separate spatial library or server option is required. SQL sessions call the functions by name, as SQL.FUNCTION_CALLS is ON by default. See Spatial Data for supported operations, coordinate-system semantics and indexing limits.
A database's directory
A database kept in a directory holds there:
| File | What it holds |
|---|---|
catalog.0, catalog.1 |
the catalog: the definitions, settings and users |
data |
the tables and indexes, in pages |
data.shadow0, data.shadow1 |
what was written to data since the last checkpoint |
log/ |
the write-ahead log, in segments |
undo/ |
older versions of rows, for readers of earlier snapshots |
tmp/ |
the work areas of large sorts, joins and groupings |
plugins/ |
the files of plug-in engines and index methods, each in a directory of its name, made as one first keeps a file; a plug-in that declares its directory shadowed has its writes reach it at checkpoints, through side files NAME.shadow0 and NAME.shadow1, so a crash leaves it as the last checkpoint did |
lock |
held while a server, or a program, has the database open |
undo/ and tmp/ are made anew each time the database opens. The
files are opened only by a release that knows their format versions
(Versions).
On Unix, the directories and files a database makes are the user's who runs it alone: directories 0700, files 0600, less what the umask takes away. A directory that already exists keeps its mode. On Windows they take the permissions of the directory they are made in.
The log begins a new segment as the current one reaches
LOG.SEGMENT_BYTES. If one cannot be begun, as when the disk is full,
the current segment grows past that size, commits going on, until one
can be; the failure is told once as a failed event
(Diagnostics). The current segment's file runs up
to a megabyte past its records, written with zeros, so a commit's sync
does not grow the file. A segment is cut back to its records when the
next one begins.
A database made with --memory has no directory: it is gone when the
server stops. Its pages, its temporary tables' rows and what its queries
spill are all kept in memory, of its MEMORY.BUDGET_BYTES, never in the
system's temporary directory: a statement that needs a page past the
budget is refused with SQLCODE -918, and what was written before it
stays. Undoing is never refused: the refused statement, or a ROLLBACK,
takes what it needs to undo, even past the budget, until it gives back
what the undone changes held.
The data file's pages are read through a buffer pool of
STORAGE.POOL_BYTES, and what a query spills through one of MEMORY.WORK_BYTES,
as is a session's instance of a temporary table, over a spill file of
its own (Temporary Tables).
A page wanted while every page of its pool is held at once, as by a
pool set too small for the work, is refused with SQLCODE -918 too, at
once, never waited for, but by an undo, which waits for a page to be let
go. A scan reads ahead, the pages it will read next brought into the
pool while it reads those before: a scan of a table of the built-in row
store, or of a B-tree index, the pages after the one it reads, as rows
inserted in key order lay them; a scan of a column store table's frozen
groups, the pages of the chunks it will read. Those not in the pool,
STORAGE.READ_AHEAD_PAGES of them at once (32 by default, 0 for none), at
most an eighth of the pool's pages, are read by the database's job
turborel-read-ahead (Background work), each run
of them in one read of the file; what a scan ends before they are read
is never read. A change holds at once, for a scan under way too.
The planner prices a row fetched through an index, and an index's probe, by where their pages are found: in the buffer pool, as the table's and the index's own lookups have found their pages there; else in the operating system's cache, or on the disk. A scan of a table the pool holds little of is priced as read from that cache. How much of the data file past the pool that cache holds is STORAGE.SYSTEM_CACHE_BYTES (4 GiB by default): set it to the memory the machine leaves its cache, or to 0 to price every page the pool misses as read from the disk, so that a scan is chosen over an index sooner. It changes no result, only plans, from the next statement planned; a database kept in memory ignores it.
Opening and closing
As it opens, a database is created, if its directory holds no catalog; reopened, if it was closed cleanly; or recovered, if it stopped without closing. The diagnostics tell which:
{"time":"2026-10-28T19:41:24.918Z","level":"info","event":"opened","how":"reopened"}
Closing, as the server stops, takes a checkpoint and marks the catalog
closed cleanly. Then, before it lets go of lock, it removes the spill
files left in tmp/ and closes every file of the database; a spill file
it cannot remove, or a file still in use, is told as a failed event.
Recovery after a crash
A database that stopped without closing is recovered as it opens: from the last checkpoint, the log is read, the changes of committed transactions redone and those of transactions never committed undone, and the indexes mended. No committed transaction is lost; none not committed survives.
{"time":"2026-10-28T19:41:11.802Z","level":"warning","event":"opened","how":"recovered","records":6,"definitions":3,"committed":1,"not_committed":0,"redone":2,"undone":0,"cut":0}
A record the crash interrupted, written in part at the end of the log,
was never synced, so no transaction it belonged to committed: it is cut
off as the database opens, and cut tells how many bytes were. Valid
records after invalid ones are another matter: the log is damaged, as by
a failing disk, and cutting it there would lose committed transactions.
The database is then not opened, and nothing of its log is cut: opening
fails with SQLCODE -916, naming the log's file and the offset of the
damage, and the database is to be restored from a copy of its
directory. Invalid bytes that show a write the crash interrupted, part
of them never written, are cut with what follows them, as the system
may write a file's last pages in any order before the database syncs
it.
If a sync of the log fails as the database runs, it may have lost what
the log held, and a sync tried again may succeed without it; so the log
syncs nothing more, and the database takes no change until it is opened
again, which recovers it from what its log holds; nor is it closed
cleanly (SQLCODE -915). The commit whose sync failed is in doubt: its
session is told SQLCODE -922 (SQLSTATE 08007), and no session sees its
changes. Opened again, the database commits it if its commit record
reached the disk, and rolls it back if not. The failure is told as a
failed event.
How durable a sync of the log makes what it holds is LOG.DURABILITY: DATA, the default, the log's data on the device with what reading it back needs; FULL, all of its files' metadata too; NONE, never synced, so a failure of the system, not of the database alone, may lose any commit, and a table whose engine needs a durable log is then refused under TRANSACTION.STRICT ON. A change holds at once, from the log's next sync, and each segment of the log begun after is made as it says; the first sync after NONE makes every record before it durable too, the change of the setting, made under NONE, among them.
SET DATABASE LOG.DURABILITY = 'FULL'
A replicated database's log is its node's, synced before the node acknowledges a record to the group, whatever LOG.DURABILITY says (Replication).
Checkpoints
A checkpoint writes the changes since the last to the data file, so a
recovery reads the log only from there. One is due once
CHECKPOINT.AFTER_CHANGES row changes have been logged, 4,000,000 by
default, or CHECKPOINT.AFTER_BYTES bytes held by the data file's side file,
1 GiB; the database's checkpoint thread takes it, so no transaction
waits for it. Each is told to the diagnostics, checkpoint_taken. One
that fails leaves the changes since the last counted, so the next is due
as soon as a transaction ends.
The log is kept from the last checkpoint, or from the first change of
the oldest transaction still open, whichever is earlier. While
checkpoints are refused (a table damaged, a failed sync of the log),
not taken, or taken while one transaction stays open, the log only
grows. Once it keeps CHECKPOINT.OVERDUE_FACTOR (four) times
CHECKPOINT.AFTER_CHANGES, or the side file as many times
CHECKPOINT.AFTER_BYTES, a checkpoint_overdue warning says so and why,
at most once a minute
(Diagnostics):
{"time":"2026-10-30T09:12:44.310Z","level":"warning","event":"checkpoint_overdue","changes":400000,"cause":"transaction","seconds":5412}
Open files
A database holds at most FILES.MAX_OPEN of its files open at once, 1024
by default, 0 for no limit, so a server stays within what the system
allows a process. Past it, the file used least recently is closed, and
opened again as it is next used; while every open file is being read or
written, the next waits for one. A file with writes not yet on disk is
closed only after those without, and its writes are synced first: if
that sync fails, the file is kept open, the failure is told as a
failed event, and the file's next sync fails with it, so the
statement, commit or checkpoint that needs the file on disk fails
rather than succeed over writes lost. If more files are opened again in a
minute than the limit, it is too low for the work, and a
files_reopened warning says so, at most once a minute
(Diagnostics). The database's owner sees each file in
INFORMATION_SCHEMA.FILES, its kind, its class of durability, whether it
is open, and its reads, writes and syncs. The class says what a crash
leaves of the file: DURABLE, the data file, the catalog, the log and a
plug-in's own files, kept; DURABLE AT COMMIT, the side files of the data
file and of a plug-in's shadowed directory, whose writes reach their files
at a checkpoint; UNSYNCED, the log of older row
versions, begun anew as the database opens; SCRATCH, spill files, a
session's temporary tables' among them, and a state transfer a replica
is fetching (Replication), removed. The server's own files are there too, of KIND SERVER and no class,
though not under the limit: the --log file and the socket while they
are open, the TLS certificate chain and key, read as the server starts,
and the --keys directory, a read for each key taken from it:
SELECT KIND, CLASS, COUNT(*) FROM INFORMATION_SCHEMA.FILES GROUP BY KIND, CLASS;
Background work
The database does some of its work in the background: its checkpoints; its maintenance, which gathers tables' statistics (a table's, after the database opens, once a query first plans with it, which plans without them meanwhile), counts the distinct keys of an index that has not kept them (the same way: once a query first plans with it), and freezes the rows of the column store; the syncs of its log that commits of LOG.COMMIT_SYNC OFF leave for LOG.SYNC_AFTER_MS (Settings); and the pages its scans read ahead (A database's directory), which a scan reads itself if they are not read in time. JOBS.POLICY says how: AUTOMATIC, the default, on threads of the database's own as the work comes due; PAUSED, not at all until it changes, the work due kept; MANUAL, only when the program embedding the database asks. It takes effect at once:
SET DATABASE JOBS.POLICY = 'PAUSED'
A table's statistics are gathered from every row of a table of up to
60,000 rows. A larger table is sampled, about 30,000 of its rows read, so
a pass costs the same however large the table grows, and its distinct
values are estimated from the sample. Of a geometry column, the pass
also keeps a grid of its envelopes, the coordinates a value holds on
average, and how closely rows near in the table lie near in space, which
price a search of an rtree index against a scan
(Spatial Data).
Each thread started or stopped is told to the diagnostics, thread, as
is each failure of its work. The threads are named for their work:
turborel-checkpoint, turborel-maintenance, turborel-log-sync,
turborel-read-ahead, turborel-assertion-totals, which folds the
totals assertions keep, turborel-rekey, which writes the files again
in the formats their settings name (The files' formats),
turborel-replica, which runs the database's replica
(Replication), turborel-archive, which archives
the log (Backups and recovery), and those of the work a plug-in
adds (Database::add_job), each turborel-<kind>.