On Wed, 15 Mar 2023 at 10:41, Laurenz Albe <laurenz.albe@cybertec.at> wrote:
> I think you want subpartitioning, like
>
> CREATE TABLE humans (
> hash bytea,
> fname text,
> dob date
> ) PARTITION BY LIST (EXTRACT (YEAR FROM dob));
This may be perfectly fine, but it is also important to highlight that
partitioning in this way may hinder partition pruning.
If the first level partitioned table was to be BY RANGE (dob); then
the partitions could be defined like FOR VALUES FROM ('2023-01-01') TO
('2024-01-01'). For a query that had something like WHERE dob =
'2023-03-16', then PostgreSQL could prune away all the partitions for
the other years. The same wouldn't occur if the table was partitioned
by LIST (EXTRACT (YEAR FROM dob)) unless you added a AND EXTRACT (YEAR
FROM dob) = 2023 to the query's WHERE clause.
Robert, there are a few tips about partitioning in [1] that you may
wish to review.
David
[1] https://www.postgresql.org/docs/devel/ddl-partitioning.html