PostgreSQL 10: Partitions of partitions

PostgreSQL version 10 brings a much anticipated feature: Table Partitioning.
There are a few gotchas you have to keep in mind when using this new feature: No PK allowed;
No ON CONFLICT clauses; etc… In my experience, these are all just slight annoyances,
hardly a reason to not benefit from this amazing feature.
You are also limited to two types of partitioning: RANGE and LIST.
A typical RANGE declaration would look something like this:
CREATE TABLE dt_totals (
dt_total date NOT NULL,
geo varchar(2) not null,
impressions integer DEFAULT NOT NULL,
sales integer DEFAULT NOT NULL
)
PARTITION BY RANGE (dt_total);

We would then create a table to act a partition of the above:
CREATE TABLE dt_totals_201801
PARTITION OF dt_totals
FOR VALUES FROM (‘2018-01-01’) TO (‘2018-01-31’);

Any data for January would be stored in this partition. When it comes to performance
for large tables, this is a fantastic new feature!
But what if this isn’t enough? What if we could also benefit from a


Original URL: http://feedproxy.google.com/~r/feedsapi/BwPx/~3/R-kB8WJXjac/postgresql-10-partitions-of-partitions.html

Original article

Comments are closed.

Proudly powered by WordPress | Theme: Baskerville 2 by Anders Noren.

Up ↑

%d bloggers like this: