Re: Table performance with millions of rows (partitioning)

On Dec 27, 2017, at 8:20 PM, Justin Pryzby <pryzby(at)telsasoft(dot)com> wrote:
>
> That's one of the major use cases for partitioning (DROP rather than DELETE and
> thus avoiding any following vacuum+analyze).
> https://www.postgresql.org/docs/10/static/ddl-partitioning.html#DDL-PARTITIONING-OVERVIEW

That’s the plan to partition and I can easily change the code to insert directly into the child tables.

Right now, I was going to use date ranges (per month) based on a timestamp.

But could I just create 12 child tables, one for each month instead of creating one for Year+month ?

ie: instead of:

(CHECK (ts >= DATE ‘2017-12-01' AND ts < DATE ‘2018-01-01’))

use:

(CHECK (EXTRACT(MONTH FROM ts) = 12))

I’ll never need more than the least six months, so I’ll just truncate the older child tables. By the time the data wraps around, the child table will be empty.

I’m not even sure if the above CHECK (w/ EXTRACT) instead of just looking for a date range is valid.