As of 7.3.10, QuestDB tables are Write-Ahead Log (WAL)-enabled by default. This page introduces the properties and benefits of a WAL-enabled table. It also contains a summary of key components, relevant functions, as well as related SQL keywords.
A WAL table must be partitioned. It permits the following concurrent transactions:
- Data ingestion through different interfaces
- Data modifications
- Table schema changes
A Write-Ahead Log (WAL) ensures that all changes to data are recorded in a log before they are written to the database files. This means that in case of a system crash or power failure, the database can recover to a consistent state by replaying the log entries.
WAL tables also support concurrent data ingestion, modifications, and schema changes without locking the entire table, allowing for high availability and better performance in multi-user environments. By decoupling the transaction commit from the disk write process, a WAL improves the performance of write-intensive workloads, as it allows for sequential disk writes which are generally faster than random ones.
As a result, a WAL assists with crash recovery by providing a clear sequence of committed transactions, ensuring that any data written to the WAL can be restored up to the last committed transaction.
As additional benefits, the sequencer in a WAL system ensures that data appears
consistent to all readers, even during ongoing write operations. And the
TableWriter can handle and resolve out-of-order data writes, which can be a
common issue in real workloads. It also enables
Furthermore, the WAL-enabled tables in QuestDB can be fine-tuned. Various WAL configurations (like parallel threads for WAL application) allow the database's performance and behavior to match the specific needs of different use cases.
Overall, WAL-enabled tables aim to balance the needs for speed, consistency, and resilience in a database environment that may face concurrent access patterns and the requirement for high availability. While recommended and largely beneficial, there are limitations which we are working to resolve.
We have the following as limitations, which we aim to soon resolve:
- No row count returned
- No support for JOIN
- ALTER TABLE
- ADD COLUMN can only add 1 column per statement
- Non-structural operations may fail silently. These are partition-level and configuration operations:
WAL-enabled tables are the default table.
You can choose to use non-WAL tables, if it's appropriate for your usecase.
For more information, see the
Other related configurations include:
Base table creation via
Converting an existing table to a WAL table or vice versa via
SET TYPEfollowing a database restart.
Server-wide configuration via
cairo.wal.enabled.defaultis set to
CREATE TABLESQL keyword generates WAL tables as the default.
Parallel threads to apply WAL data to the table storage can be configured, see WAL table configuration for more details.
A WAL table uses the following components to manage concurrent commit requests:
WAL: acts as a dedicated API for each ingestion interface. When data is ingested via multiple interfaces, dedicated
WALsensure that the table is not locked by one interface only.
Sequencer: centrally manages transactions, providing a single source of truth. The sequencer generates unique
txnnumbers as transaction identifiers and keeps a log that tracks their allocation, preventing duplicates. This log is called
TransactionLogand is stored in a meta file called
_txnlog. See root directory for more information.
WAL apply job: collects the commit requests based on the unique
txnnumbers and sends them to the
TableWriterto be committed.
TableWriter: updates the database and resolves any out-of-order data writes.
The following table metadata functions are useful for checking WAL table settings: