Date: Wed, 27 Dec 2017 19:20:09 -0600
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)
Message-ID: <20171228012009.GI4172@telsasoft.com>
References: <7DF18AB9-C4A4-4C28-957D-12C00FCB5F71@inoc.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <7DF18AB9-C4A4-4C28-957D-12C00FCB5F71@inoc.net>
User-Agent: Mutt/1.5.23 (2014-03-12)
Precedence: bulk

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