Global ordering by time YSQL
This page documents the preview version (v2.23). Preview includes features under active development and is for development and testing only. For production, use the stable version (v2024.1). To learn more, see Versioning.
Learn how to distribute your entire time-ordered dataset across tablets and retrieve data efficiently.
Setup
Setup
To set up a local universe, refer to Set up a local YugabyteDB universe.Setup
To set up a cluster, refer to Set up a YugabyteDB Aeon cluster.Setup
To set up a universe, refer to Set up a YugabyteDB Anywhere universe.Timestamp-based distribution
Consider a speed metrics tracking system that tracks the data from the speed sensor of many cars.
Create a table with an example schema as follows:
CREATE TABLE global_order1 (
ts timestamp, /* time at which the event was generated */
car varchar, /* name of the car */
speed int, /* speed of your car */
PRIMARY KEY(ts ASC)
);
The global_order1
table stores the speed data points of different cars as they arrive in the system.
Insert some sample data into the table as follows:
INSERT INTO global_order1 (ts, car, speed)
(SELECT '2023-07-01 00:00:00'::timestamp + make_interval(secs=>id),
'car-' || ceil(random()*2), ceil(random()*60)
FROM generate_series(1,100) AS id);
Retrieve some data from the table as follows:
SELECT * FROM global_order1;
ts | car | speed
---------------------+-------+-------
2023-07-01 00:00:01 | car-1 | 50
2023-07-01 00:00:02 | car-2 | 25
2023-07-01 00:00:03 | car-1 | 39
2023-07-01 00:00:04 | car-1 | 49
2023-07-01 00:00:05 | car-2 | 3
2023-07-01 00:00:06 | car-2 | 22
2023-07-01 00:00:07 | car-2 | 25
2023-07-01 00:00:08 | car-1 | 58
2023-07-01 00:00:09 | car-2 | 55
Notice how the data is automatically ordered by time. This is because the table is set to be sorted on the ts
by PRIMARY KEY(ts ASC)
. This ensures that the data is sorted and all nearby data resides in the same tablet. This order makes it efficient for range queries to retrieve all data within a specific time range. For example:
SELECT * FROM global_order1 WHERE ts > '2023-07-01 00:01:00' AND ts < '2023-07-01 00:01:05';
ts | car | speed
---------------------+-------+-------
2023-07-01 00:01:01 | car-1 | 21
2023-07-01 00:01:02 | car-2 | 58
2023-07-01 00:01:03 | car-1 | 57
2023-07-01 00:01:04 | car-2 | 60
As the amount of data grows, the tablet splits and half moves to a different tablet, ensuring scalability. This also means that the data grows in one shard before moving to the next tablet. However, because a specific range could be in a single shard, this could lead to one shard becoming a hot shard.
Bucket-based distribution
To distribute ordered data across different tablets, you can use bucket-based distribution, where data is split into buckets and then distributed.
To do this, modify the table to include a bucketid
field that would have a small range of values, and distribute the buckets. For example:
CREATE TABLE global_order2 (
ts timestamp,/* time at which the event was generated */
car varchar, /* name of the car */
speed int, /* speed of your car */
bucketid smallint DEFAULT random()*8, /* bucket id*/
PRIMARY KEY(bucketid HASH, ts ASC)
) SPLIT INTO 3 TABLETS;
This adds a bucketid
to your data, consisting of a random number between 0
and 7
, and which you will use to distribute the data on the entity and bucketid
.
Add the same data to the new table as follows:
INSERT INTO global_order2 (ts, car, speed)
(SELECT '2023-07-01 00:00:00'::timestamp + make_interval(secs=>id),
'car-' || ceil(random()*2), ceil(random()*60)
FROM generate_series(1,100) AS id);
Because the default value of bucketid
is set to random()*8
, you do not have to explicitly insert the value.
Retrieve the data from the table as follows:
SELECT *, yb_hash_code(bucketid) % 3 as tablet FROM global_order2;
ts | car | speed | bucketid | tablet
---------------------+-------+-------+----------+--------
2023-07-01 00:00:24 | car-2 | 19 | 4 | 2
2023-07-01 00:00:25 | car-1 | 21 | 4 | 2
2023-07-01 00:00:26 | car-1 | 40 | 4 | 2
...
2023-07-01 00:00:35 | car-1 | 46 | 0 | 1
2023-07-01 00:00:40 | car-1 | 16 | 0 | 1
2023-07-01 00:00:41 | car-2 | 34 | 0 | 1
...
2023-07-01 00:00:22 | car-2 | 57 | 0 | 0
2023-07-01 00:00:35 | car-1 | 46 | 0 | 0
2023-07-01 00:00:40 | car-1 | 16 | 0 | 0
Notice that the data is split into buckets and the buckets are distributed across different tablets. The data is ordered by ts
in each bucket, but your result is not ordered.
Because the query planner does not know about the different values of bucketid
, it must perform a sequential scan for the preceding query. To efficiently retrieve all the data for a specific car, say car-1
, modify the query to explicitly call out the buckets as follows:
SELECT * FROM global_order2 WHERE bucketid IN (0,1,2,3,4,5,6,7) ORDER BY ts ASC;
ts | car | speed | bucketid
---------------------+-------+-------+----------
2023-07-01 00:00:01 | car-2 | 7 | 1
2023-07-01 00:00:02 | car-1 | 27 | 6
2023-07-01 00:00:03 | car-1 | 32 | 1
2023-07-01 00:00:04 | car-2 | 28 | 6
2023-07-01 00:00:05 | car-2 | 45 | 2
2023-07-01 00:00:06 | car-1 | 14 | 3
2023-07-01 00:00:07 | car-1 | 14 | 1
2023-07-01 00:00:08 | car-1 | 35 | 5
2023-07-01 00:00:09 | car-1 | 58 | 3
You can execute an example query plan to verify that the preceding query uses the primary key index as follows:
EXPLAIN ANALYZE SELECT * FROM global_order2 WHERE bucketid IN (0,1,2,3,4,5,6,7) ORDER BY ts ASC;
QUERY PLAN
--------------------------------------------------------------------------------------------------------
Sort (actual time=2.279..2.289 rows=95 loops=1)
Output: ts, car, speed, bucketid
Sort Key: global_order2.ts
Sort Method: quicksort Memory: 32kB
-> Index Scan using global_order2_pkey on public.global_order2 (actual time=2.139..2.207 rows=95 loops=1)
Output: ts, car, speed, bucketid
Index Cond: (global_order2.bucketid = ANY ('{0,1,2,3,4,5,6,7}'::integer[]))
Planning Time: 0.180 ms
Execution Time: 2.388 ms
Peak Memory Usage: 34 kB
This is how you can efficiently retrieve data from the distributed buckets in the correct order.