From: Robert Blayzor <rblayzor.bulk@inoc.net>
To: Justin Pryzby <pryzby@telsasoft.com>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: Table performance with millions of rows (partitioning)
Date: Wed, 27 Dec 2017 20:27:08 -0500
Message-ID: <47DA908F-BC21-45E6-8E30-73AFAC5F4853@inoc.net> (raw)
In-Reply-To: <20171228012009.GI4172@telsasoft.com>
References: <7DF18AB9-C4A4-4C28-957D-12C00FCB5F71@inoc.net>
<20171228012009.GI4172@telsasoft.com>
On Dec 27, 2017, at 8:20 PM, Justin Pryzby <pryzby@telsasoft.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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: rblayzor.bulk@inoc.net, pryzby@telsasoft.com, pgsql-performance@lists.postgresql.org
Subject: Re: Table performance with millions of rows (partitioning)
In-Reply-To: <47DA908F-BC21-45E6-8E30-73AFAC5F4853@inoc.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox