Hi all, I have a question of understanding: the partitioned tables do not only have a date but also a number, e.g.

2022-07.19659/
2022-08.19662/
2022-09.19664/

What does this mean? Am I doing something wrong?

Hi Felix. The number following dot is the partition version. If you do lots of updates to non-active partitions (all expect the latest) you'll notice the number increasing .

So adding rows is an update from point of view of the partition

If you run the following you'll notice version increase in partition folder name :

create table trades ( ts timestamp, code int, value int  ) timestamp(ts) partition by day;

insert into trades 
select dateadd('s', x::int, '2023-04-20T00:00:00.000000Z'), rnd_int(1,10,0), rnd_int() 
from long_sequence(10000000);

show partitions from trades;

insert into trades 
select dateadd('s', x::int, '2023-04-20T00:00:00.000000Z'), rnd_int(1,10,0), rnd_int() 
from long_sequence(86300);

show partitions from trades;


insert into trades 
select dateadd('s', x::int, '2023-04-20T00:00:00.000000Z'), rnd_int(1,10,0), rnd_int() 
from long_sequence(86300);

does

show partitions

run with 7.1? It's not working with my versions used

It was added in 7.1 .

ok, thanks

A bit of background: Ideally, you are ingesting data sorted by time. If that’s the case then QuestDB can keep appending rows into its internal files as the data are coming. This does not require creating new partition versions. It’s like writing to a column in MS Excel: If you are only appending new rows then it’s easy - you add a new row after the last existing row.

1
2
3
4

When you want to insert

5

then you just append a new row:

1
2
3
4
5

The problem is when you need to insert a row in between already existing row. Imagine this:

1
2
3
5

and you want to insert

4

. QuestDB will do this: 1. create a new partition version 2. insert all rows from before the row you want to insert. So it’ll look like this:

1
2
3

3. insert the

4

So the table looks like this:

1
2
3
4

4. insert the rest of the original table:

1
2
3
4
5

The beauty of this system is that the old partition is not obstructed in any way. Readers can keep query it while the new version is being created. QuestDB will automatically remove the old version when they are no readers.

well, my use case is inserting thousands of rows every 30 seconds, partitioned by month. Every row has its own timestamp (ordered). There are usually no readers.

Are you appending to the last partition or also to old ones ? If it's the latter then you might consider changing partition unit to decrease write amplification caused by COW (not to be confused with 🐮 ) .

Just appending to the latest. The timestamp used is generated when gathering the data.

That should be fine as QuestDB should just append to the latest partition.

Thanks!