From: Justin Pryzby <pryzby@telsasoft.com>
To: Robert Blayzor <rblayzor.bulk@inoc.net>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: Table performance with millions of rows (partitioning)
Date: Wed, 27 Dec 2017 19:20:09 -0600
Message-ID: <20171228012009.GI4172@telsasoft.com> (raw)
In-Reply-To: <7DF18AB9-C4A4-4C28-957D-12C00FCB5F71@inoc.net>
References: <7DF18AB9-C4A4-4C28-957D-12C00FCB5F71@inoc.net>
On Wed, Dec 27, 2017 at 07:54:23PM -0500, Robert Blayzor wrote:
> Question on large tables…
>
> When should one consider table partitioning vs. just stuffing 10 million rows into one table?
IMO, whenever constraint exclusion, DROP vs DELETE, or seq scan on individual
children justify the minor administrative overhead of partitioning. Note that
partitioning may be implemented as direct insertion into child tables, or may
involve triggers or rules.
> I currently have CDR’s that are injected into a table at the rate of over
> 100,000 a day, which is large.
>
> At some point I’ll want to prune these records out, so being able to just
> drop or truncate the table in one shot makes child table partitions
> attractive.
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
Justin
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: pryzby@telsasoft.com, rblayzor.bulk@inoc.net, pgsql-performance@lists.postgresql.org
Subject: Re: Table performance with millions of rows (partitioning)
In-Reply-To: <20171228012009.GI4172@telsasoft.com>
* 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