Re: Declarative partitioning - another take

On Tue, Aug 16, 2016 at 2:30 AM, Ashutosh Bapat <
ashutosh(dot)bapat(at)enterprisedb(dot)com> wrote:

>
>
>
>
>> I think it makes sense to keep calling it a table because it has all the
>> logical properties of a table even though it will differ from a regular
>> table on the basis of physical implementation details such as that it does
>> not own physical storage. Am I missing something?
>>
>> >
>> > + <entry><structfield>partexprs</structfield></entry>
>> >
>> > There's a certain symmetry between this and what we do for indexes,
>> > but I'm wondering whether there's a use case for partitioning a table
>> > by an expression rather than a column value. I suppose if you've
>> > already done the work, there's no harm in supporting it.
>>
>> Yeah, it's not a whole lot of code to manage expressions alongside simple
>> column references.
>>
>
> Users who would like to partition their tables by "age" will partition
> those by the month or year extracted out of a date column e.g. order_date.
> They will find it convenient to use an expression (extract(month from
> date)) as a partition key, instead of storing month or year as a separate
> column.
>

In GPDB we have partitioning. It is almost always by date and then often
the partitions are for different sizes, i.e. by day for 30 days then by
month for 3 years then by year. What we also support, but isn't super
performant, is sub-partitioning.

This is where some on the newer indexing strategies is interesting to me. I
see them as synergistic not redundant.

>
> --
> Best Wishes,
> Ashutosh Bapat
> EnterpriseDB Corporation
> The Postgres Database Company
>