Correct Partition Endpoints

I was recently reviewing a database partitioning definition in YugabyteDB (the postgres “ysql” API), and realized the partition distribution might not be what the developer intended.

What is database partitioning?

Database partitioning is used to divide large tables into smaller tables (partitions). While the data is physically separate, the application can access the data logically as a single table.

This can help performance through a process called partition pruning. The database planner skips partitions that don’t hold the data. For example, if a table is partitioned on months of the year, a query on a single month only has to access the rows in the single partition for that month.

Partitioning also helps with data lifecycle management; DBAs can drop partitions easily and quickly, as opposed to long-running impactful delete queries.

The Default Partition

In general terms, an insert to a nonexistent partition will go into the default partition. This is useful for cases where you might have missed defining a partition, because it avoids write failures. Be sure to define a default partition!

Range Partitions

Range partitions define these smaller tables based on a starting and ending value. Starting and ending timestamps are one common type of range used. For range partitions, the boundaries are inclusive at the lower end (including all values starting here) and exclusive at the upper end (including all values until this one).

Because the ending timestamp is excluded, an insert using the defined ending timestamp will go into the default partition as well. If you’re not aware of this, you may end up hurting your query performance. That is, if the default partition grows very large, then more queries than expected could be going to this large table than the smaller partitions. Also, it is harder to delete data from a default partition than to simply drop a partition.

Here’s a scenario. Notice the range partitions end at the last millisecond of the day instead of the first second of the following day, and then notice that an insert on that last millisecond goes into the default partition. Maybe this is a rare situation, but the default partition should be only a safety net. Having data in the default partition is harder to archive and decreases read performance.

create database test_range_boundaries;
\c test_range_boundaries
create table my_logging (
id int,
name text,
date_timestamp timestamp without time zone
) partition by range (date_timestamp);
create table my_logging_2024_02_17
partition of my_logging for values
from ('2024-02-17 00:00:00.000000')
to ('2024-02-17 23:59:59.999999');
create table my_logging_2024_02_18
partition of my_logging for values
from ('2024-02-18 00:00:00.000000')
to ('2024-02-18 23:59:59.999999');
create table my_logging_default partition of my_logging default;
insert into my_logging values (1, 'a', '2024-02-17 00:00:10');
insert into my_logging values (1, 'a', '2024-02-17 00:00:00.000000');
insert into my_logging values (1, 'a', '2024-02-17 23:59:59.999999');
select id, name, date_timestamp from my_logging;
 id | name |       date_timestamp
----+------+----------------------------
  1 | a    | 2024-02-17 00:00:10
  1 | a    | 2024-02-17 00:00:00
  1 | a    | 2024-02-17 23:59:59.999999
(3 rows)
select id, name, date_timestamp from my_logging_2024_02_17;
 id | name |   date_timestamp
----+------+---------------------
  1 | a    | 2024-02-17 00:00:10
  1 | a    | 2024-02-17 00:00:00
(2 rows)
select id, name, date_timestamp from my_logging_2024_02_18;
 id | name | date_timestamp
----+------+----------------
(0 rows)
select id, name, date_timestamp from my_logging_default;
 id | name |       date_timestamp
----+------+----------------------------
  1 | a    | 2024-02-17 23:59:59.999999
(1 row)

To avoid issues mentioned above, here’s the correct way to define range partitions. The definitions look like there is an overlap, but remember that while the start of the range is inclusive, the end of the range is exclusive.

drop table my_logging;
create table my_logging (
id int,
name text,
date_timestamp timestamp without time zone
) partition by range (date_timestamp);
create table my_logging_2024_02_17
partition of my_logging for values
from ('2024-02-17 00:00:00.000000')
to ('2024-02-18 00:00:00.000000');
create table my_logging_2024_02_18
partition of my_logging for values
from ('2024-02-18 00:00:00.000000')
to ('2024-02-19 00:00:00.000000');
create table my_logging_default partition of my_logging default;
insert into my_logging values (1, 'a', '2024-02-17 00:00:10');
insert into my_logging values (1, 'a', '2024-02-17 00:00:00.000000');
insert into my_logging values (1, 'a', '2024-02-17 23:59:59.999999');
select id, name, date_timestamp from my_logging;
 id | name |       date_timestamp
----+------+----------------------------
  1 | a    | 2024-02-17 00:00:10
  1 | a    | 2024-02-17 23:59:59.999999
  1 | a    | 2024-02-17 00:00:00
select id, name, date_timestamp from my_logging_2024_02_17;
 id | name |       date_timestamp
----+------+----------------------------
  1 | a    | 2024-02-17 00:00:10
  1 | a    | 2024-02-17 23:59:59.999999
  1 | a    | 2024-02-17 00:00:00
(3 rows)
select id, name, date_timestamp from my_logging_2024_02_18;
 id | name | date_timestamp
----+------+----------------
(0 rows)
select id, name, date_timestamp from my_logging_default;
 id | name | date_timestamp
----+------+----------------
(0 rows)

Conclusion

To ensure that data with the ending timestamp is correctly placed in the intended partition, set the upper range boundary to the starting time of the next partition (e.g., 00:00:00.000000). This way you will avoid putting data into the default partition.