For AI agents: the complete documentation index is at llms.txt. Every page is also available as markdown by appending .md to its URL, or by sending an Accept: text/markdown request header.

ALTER TABLE SET TYPE

Converts a non-WAL table to WAL, or a WAL table to non-WAL.

info

Upgrading QuestDB?

Apply table conversions separately from the version upgrade.

If upgrading, match the following sequence:

  1. Convert/ALTER tables
  2. Restart
  3. Upgrade
  4. Restart

Syntax​

ALTER TABLE tableName SET TYPE [BYPASS] WAL;

Description​

The command schedules the conversion of the specified table to WAL or non-WAL type. The actual conversion takes place on the next restart of the server.

The conversion keeps the table's settings, including its TTL, FORMAT PARQUET, per-column Parquet encoding, and deduplication keys and mode. A table with a TTL keeps dropping partitions that are past the TTL after the conversion.

note

QuestDB versions before 10.2.0 and QuestDB Enterprise versions before 4.2.0 dropped the TTL, FORMAT PARQUET, and per-column Parquet encoding of a table they converted to WAL. Check the TTL of a table before you convert it, because it stays in effect after the conversion.

For a table that may hold merge-append partitions, first set cairo.o3.partition.merge.append.enabled=false, restart, and run ALTER TABLE trades SQUASH PARTITIONS; only then issue ALTER TABLE trades SET TYPE BYPASS WAL and restart again. Skipping these steps is unsupported.

If the command is issued more than once before the restart, the last command overrides all previous ones.

If the target type of the conversion is the same as the current type of the table, the conversion request is ignored.

Since QuestDB 10.2.0 and QuestDB Enterprise 4.2.0, a conversion to non-WAL loses no committed transaction: the restart first applies every transaction still waiting in the WAL and flushes the table, then converts it. If that cannot finish, because the table is suspended or a transaction fails to apply, the conversion is refused: the table stays a WAL table, keeps its WAL and the pending conversion, and the start logs a critical error (one line in the log, wrapped here):

could not apply the WAL before the conversion to non-WAL, the table stays a WAL
table; resume it, let it catch up and restart [table=..., seqTxn=...,
lastSeqTxn=..., suspended=...]

Resume the table with ALTER TABLE RESUME WAL, let the WAL apply catch up, and restart: the next start tries the conversion again. If a flush fails during the conversion, QuestDB stops, in every commit mode.

Converting to non-WAL no longer drops the transactions waiting in the WAL: the next start applies them first. Convert a table once it has caught up, when writerTxn equals sequencerTxn in wal_tables().

To drop the transactions not yet applied on purpose, use ALTER TABLE REBASE WAL instead of converting the table to non-WAL and back. No restart required. Enable cairo.wal.apply.suspended.write.denied, then suspend and rebase the table:

ALTER TABLE trades SUSPEND WAL;
ALTER TABLE trades REBASE WAL;

A non-WAL table gets no power-loss protection from adaptive commit mode: it behaves as in nosync.

Examples​

To convert a non-WAL table to WAL:

ALTER TABLE weather SET TYPE WAL;
-- now restart instance

To convert a WAL table to non-WAL:

ALTER TABLE weather SET TYPE BYPASS WAL;
-- now restart instance