Modify data
QuestDB is a time-series database optimized to append data.
For best performance, design your application to avoid having to frequently edit existing records.
If you need to, the UPDATE
statement is
intended for correcting data that was inserted incorrectly.
These are three alternatives to UPDATE
that you may consider:
Append newest state: Insert a newer state to replace an older one: This has the added advantage that you can query back in time to a previous state. It is also the basis of organizing data for bi-temporality.
Replace a table: Create a new table with the new data you need, drop the old one and rename.
Delete by dropping partitions: Create your time-series tables with partitions, then delete the ones you no longer need.
note
Any time you are performing database modification, please remember to backup your database!
#
Append newest state#
Using the timestamp fieldHere's a worked example using the timestamp column:
We join the latest timestamp of an order id against the rest of the data to obtain full details.
This results in the latest state for each order:
timestamp ts | id symbol | status symbol |
---|---|---|
2022-04-07T15:33:43.944922Z | order1 | arrived |
2022-04-07T15:33:37.370694Z | order2 | placed |
2022-04-07T15:33:50.829323Z | order3 | in-transit |
#
Using dedicated fieldsIf timestamps don't work for you here, you can also use an extra integer column
called version
, an extra boolean deleted
column or similar.
#
Replace TableAnother alternative is to:
- Backup your database.
- Select only the data you want from an existing table into a new temporary one.
- Drop the original table.
- Rename the temporary table to the original table's name.
#
Delete by Dropping PartitionsWhen you create tables with a timestamp, you may organise them into
partitions using the
CREATE TABLE .. PARTITION BY
SQL statement. But first, backup your database.
You may then use the
ALTER TABLE DROP PARTITION
SQL statement to drop partitions you no longer need.