Обсуждение: Partitioning with range types
Several months ago we had some detailed discussions about whether to use separate date columns to indicate a date range, or to use the daterange data type. We opted for the latter because this type is specifically designed for this use case - a table that has a range of valid dates for the data it contains. It also has some great operators and functions.
But I recently discovered that daterange is not supported in any way as a partition key because it depends on an expression. I was excited about this possibility in pg11 with unique constraints on the parent table, but now it appears it may have instead been to our advantage if we had two separate date columns instead, so that we could use UPSERT transparently for date-ranged tables.
Is there any possibility of this feature coming for range types, or, if we really want to partition using daterange, should we look instead at two separate date columns?
Thanks,
Jeremy
On 06/15/2018 08:26 AM, Jeremy Finzel wrote:
> Several months ago we had some detailed discussions about whether to use
> separate date columns to indicate a date range, or to use the daterange
> data type. We opted for the latter because this type is specifically
> designed for this use case - a table that has a range of valid dates for
> the data it contains. It also has some great operators and functions.
>
> But I recently discovered that daterange is not supported in any way as
> a partition key because it depends on an expression. I was excited
A quick test:
Postgres 10.4
create table dr_partition(id integer, dr daterange) PARTITION BY LIST(dr);
\d dr_partition
Table "public.dr_partition"
Column | Type | Collation | Nullable | Default
--------+-----------+-----------+----------+---------
id | integer | | |
dr | daterange | | |
Partition key: LIST (dr)
create table dr_1 PARTITION OF dr_partition FOR VALUES IN ('[06/01/2018,
06/30/2018]');
\d dr_1
Table "public.dr_1"
Column | Type | Collation | Nullable | Default
--------+-----------+-----------+----------+---------
id | integer | | |
dr | daterange | | |
Partition of: dr_partition FOR VALUES IN ('[2018-06-01,2018-07-01)')
> about this possibility in pg11 with unique constraints on the parent
> table, but now it appears it may have instead been to our advantage if
> we had two separate date columns instead, so that we could use UPSERT
> transparently for date-ranged tables.
>
> Is there any possibility of this feature coming for range types, or, if
> we really want to partition using daterange, should we look instead at
> two separate date columns?
>
> Thanks,
> Jeremy
--
Adrian Klaver
adrian.klaver@aklaver.com
On Fri, Jun 15, 2018 at 11:23 AM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 06/15/2018 08:26 AM, Jeremy Finzel wrote:Several months ago we had some detailed discussions about whether to use separate date columns to indicate a date range, or to use the daterange data type. We opted for the latter because this type is specifically designed for this use case - a table that has a range of valid dates for the data it contains. It also has some great operators and functions.
But I recently discovered that daterange is not supported in any way as a partition key because it depends on an expression. I was excited
A quick test:
Postgres 10.4
create table dr_partition(id integer, dr daterange) PARTITION BY LIST(dr);
\d dr_partition
Table "public.dr_partition"
Column | Type | Collation | Nullable | Default
--------+-----------+-----------+----------+---------
id | integer | | |
dr | daterange | | |
Partition key: LIST (dr)
create table dr_1 PARTITION OF dr_partition FOR VALUES IN ('[06/01/2018, 06/30/2018]');
\d dr_1
Table "public.dr_1"
Column | Type | Collation | Nullable | Default
--------+-----------+-----------+----------+---------
id | integer | | |
dr | daterange | | |
Partition of: dr_partition FOR VALUES IN ('[2018-06-01,2018-07-01)')--about this possibility in pg11 with unique constraints on the parent table, but now it appears it may have instead been to our advantage if we had two separate date columns instead, so that we could use UPSERT transparently for date-ranged tables.
Is there any possibility of this feature coming for range types, or, if we really want to partition using daterange, should we look instead at two separate date columns?
Thanks,
Jeremy
Adrian Klaver
adrian.klaver@aklaver.com
Let me clarify - what I said was not accurate. What I meant is that using an UPSERT on a parent partition is not supported with range types specifically because we can't create unique indexes involving expressions on parent partitions:
CREATE UNIQUE INDEX ON foo (id, lower(as_of_date));
ERROR: unsupported UNIQUE constraint with partition key definition
DETAIL: UNIQUE constraints cannot be used when partition keys include expressions.
Workaround is of course not to use UPSERT, but we all know the advantages of using UPSERT to handle concurrency and the like and to make our queries simpler. We are currently using UPSERT for many of these tables, but they are not partitioned yet.
Thanks,
Jeremy
Jeremy
On 06/15/2018 09:59 AM, Jeremy Finzel wrote:
>
>
> On Fri, Jun 15, 2018 at 11:23 AM, Adrian Klaver
> <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:
>
> On 06/15/2018 08:26 AM, Jeremy Finzel wrote:
>
> Several months ago we had some detailed discussions about
> whether to use separate date columns to indicate a date range,
> or to use the daterange data type. We opted for the latter
> because this type is specifically designed for this use case - a
> table that has a range of valid dates for the data it contains.
> It also has some great operators and functions.
>
> But I recently discovered that daterange is not supported in any
> way as a partition key because it depends on an expression. I
> was excited
>
>
> A quick test:
>
> Postgres 10.4
>
> create table dr_partition(id integer, dr daterange) PARTITION BY
> LIST(dr);
>
> \d dr_partition
> Table "public.dr_partition"
> Column | Type | Collation | Nullable | Default
> --------+-----------+-----------+----------+---------
> id | integer | | |
> dr | daterange | | |
> Partition key: LIST (dr)
>
>
> create table dr_1 PARTITION OF dr_partition FOR VALUES IN
> ('[06/01/2018, 06/30/2018]');
>
> \d dr_1
> Table "public.dr_1"
> Column | Type | Collation | Nullable | Default
> --------+-----------+-----------+----------+---------
> id | integer | | |
> dr | daterange | | |
> Partition of: dr_partition FOR VALUES IN ('[2018-06-01,2018-07-01)')
>
>
>
> about this possibility in pg11 with unique constraints on the
> parent table, but now it appears it may have instead been to our
> advantage if we had two separate date columns instead, so that
> we could use UPSERT transparently for date-ranged tables.
>
> Is there any possibility of this feature coming for range types,
> or, if we really want to partition using daterange, should we
> look instead at two separate date columns?
>
> Thanks,
> Jeremy
>
>
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>
>
> Let me clarify - what I said was not accurate. What I meant is that
> using an UPSERT on a parent partition is not supported with range types
> specifically because we can't create unique indexes involving
> expressions on parent partitions:
>
> CREATE UNIQUE INDEX ON foo (id, lower(as_of_date));
> ERROR: unsupported UNIQUE constraint with partition key definition
> DETAIL: UNIQUE constraints cannot be used when partition keys include
> expressions.
Would it be possible to show all the schema involved in the above?
>
> Workaround is of course not to use UPSERT, but we all know the
> advantages of using UPSERT to handle concurrency and the like and to
> make our queries simpler. We are currently using UPSERT for many of
> these tables, but they are not partitioned yet.
>
> Thanks,
> Jeremy
--
Adrian Klaver
adrian.klaver@aklaver.com