Does QuestDB preserve ingested record order for records with identical timestamp

Community
1

I am new to QuestDB!
My use case requires whenever records with same timestamps are stored in the database, they should be retrieved in the same sequential order.
Does QuestDB ensure this by design, when no ORDER BY clause is specified (some of the documentation can be interpreted that way)?
In other DB systems I would usually use an auto increment id/serial or an indexed integer, however I know this is not an option with QuestDB.

Thanks

2

Hi @dan40 ,

The records will be retrieved in the order they were written.

However, if you need to guarantee this behaviour, it’d be best to add another field for ordering.

One example could be to add a second timestamp with the ingestion time and use that as an ordering key.

It depends on the nature of the data and timestamps.

If you share more details and a schema, we may be able to help further.

Thanks!

3

Thank you @nwoolmer. Amazing work on the QuestDB system.

Data is crypto exchange orderbook diff records. Schema is below.
Sometimes there are multiple deletion (quantity 0.0) and insertion (quantity != 0.0) records with the same msec timestamp so it is essential to preserve the order. Usually I would use the exchange msec timestamps + add a sequence id in the nano+usec to form a unique timestamp for each record (timestamp = timestamp_from_exchange_msec + seqId; seqId ++). However with QuestDB we are limited to the usec only, which gives us only 1000 updates per msec. Some fast markets come close to that.

Neat idea about the ingestion timestamp, I wasn’t sure we can use a second timestamp field. Thank you.

CREATE TABLE orderbook_diff (
timestamp TIMESTAMP,
side SYMBOL,
price DOUBLE,
quantity DOUBLE
) TIMESTAMP(timestamp)
PARTITION BY DAY;

4

@dan40 Thanks, I’ll echo that to the team!

We have nanosecond timestamps on the backlog but no ETA yet.

What most people do is one of two things:

a) store the nanosecond timestamp as a LONG and do custom calculations.
b) store a microsecond timestamp and a supplementary nanosecond tail in another column.

It sounds like a LONG with a nanosecond timestamp could fit. Then use something like

ORDER BY us_ts, ns_ts

You can use as many microsecond TIMESTAMP fields as you like. Only one will be the designated (ordering timestamp). Searching by designated timestamp gives the fastest results, since it matches the stored order of the columns or disk.

If you find ergonomic issues that could be helped by an extra function or two, let us know - this might be utilities around converting/building nanosecond timestamps or similar over long fields.