Version 6.0 migration
Release 6.0 introduces breaking changes in table transaction files. An automated conversion process has been included in the release which will migrate table transaction files to use the new format. The following sections describe the automated upgrade process with notes for manually downgrading tables for compatibility with older versions.
#
Upgrading QuestDBWhen QuestDB v6.0 starts up, and tables from older QuestDB versions are
detected, a migration to the new transaction file format will run automatically.
The migration scans for the existence of tables within the QuestDB storage
directory and upgrades transaction (_txn
) files for each table. All other
table data is untouched by the upgrade.
If the migration fails for a table, an error message will be printed in the QuestDB logs on startup. QuestDB will not terminate, but tables which have not been successfully upgraded cannot be used for querying or writing.
Starting QuestDB again will trigger another attempt to migrate tables using an older transaction file format.
#
Reverting transaction filesDuring the upgrade process, _txn
files are backed up and renamed using the
format _txn.v417
. Users who wish to revert the table migration can downgrade
tables by following these steps:
- delete the folder
/path/to/questdb/db/_upgrade.d
- for each table, rename
_txn.v417
to_txn
#
Table downgrade exampleThis section illustrates how to revert transaction files to a format used by
QuestDB versions earlier than 6.0. Given storage directories for two table
example_table
and sensors
:
The tables may be downgraded in the following manner:
After these steps have been completed, QuestDB v5.x may be started and the table data will be loaded as usual.
#
Breaking SQL changesRelease 6.0.1 contains breaking changes relating to SQL syntax to simplify
working with TIMESTAMP
types and for improved compatibility with ANSI SQL
expectations.
note
For more information on these changes, see the 6.0.1 software version release notes on GitHub.
To illustrate how timestamps are handled, a table my_table
containing 48
records with timestamps every hour beginning at 00:00:00
on 2020-01-01
will
be used in the following examples:
timestamp |
---|
2020-01-01T00:00:00.000000Z |
2020-01-01T01:00:00.000000Z |
2020-01-01T02:00:00.000000Z |
... |
2020-01-01T23:00:00.000000Z |
2020-01-02T00:00:00.000000Z |
2020-01-02T01:00:00.000000Z |
... |
2020-01-02T23:00:00.000000Z |
#
Timestamp string equalityThe following example SQL uses a WHERE
clause to evaluate if records match
using string equality.
The result will be 1 record with exact match of 2020-01-01T00:00:00.000000Z
.
In other words, the string 2020-01-01
does not represent an interval, but a
single TIMESTAMP
data point of 2020-01-01T00:00:00.000000Z
timestamp |
---|
2020-01-01T00:00:00.000000Z |
Before software version 6.0.1
, this would result in 24 records of all hours
during date '2020-01-01'
timestamp |
---|
2020-01-01T00:00:00.000000Z |
2020-01-01T01:00:00.000000Z |
2020-01-01T02:00:00.000000Z |
... |
2020-01-01T23:00:00.000000Z |
In order to use the old semantics, the query must use the IN
keyword instead
of =
:
#
Timestamp string comparisonTimestamps may also be compared using >
greater-than and <
less-than
operators. The following example SQL uses a >
greater-than operator to
evaluate if records occur later than a timestamp provided as a string:
The results are 47 records which have timestamps strictly greater than
2020-01-01T00:00:00.000000Z
. The string 2020-01-01
does not represent an
interval, but a single TIMESTAMP
data point of 2020-01-01T00:00:00.000000Z
:
timestamp |
---|
2020-01-01T01:00:00.000000Z |
... |
2020-01-02T23:00:00.000000Z |
Before software version 6.0.1
, this would result in 24 records, one for each
hour during the date 2020-01-02
:
timestamp |
---|
2020-01-02T00:00:00.000000Z |
... |
2020-01-02T23:00:00.000000Z |
In order to use the old semantics, the query must use >=
instead of >
, and
<=
instead of <
:
#
Timestamp IN listThe IN
keyword is used to check equality with a list of 2 elements:
The result is two records matching exactly 2020-01-01T00:00:00.000000Z
and
2020-01-02T00:00:00.000000Z
timestamp |
---|
2020-01-02T00:00:00.000000Z |
2020-01-02T00:00:00.000000Z |
Before software version 6.0.1
, this would result in 25 records, one for each
hour during the date 2020-01-01
and the 00:00:00
data point on 2020-01-02
:
timestamp |
---|
2020-01-02T00:00:00.000000Z |
... |
2020-01-02T00:00:00.000000Z |
In order to use the old semantics, the BETWEEN
keyword should be used: