pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: Table performance with millions of rows (partitioning)
3+ messages / 3 participants
[nested] [flat]
* Re: Table performance with millions of rows (partitioning)
@ 2017-12-28 01:20 Justin Pryzby <pryzby@telsasoft.com>
0 siblings, 1 reply; 3+ messages in thread
From: Justin Pryzby @ 2017-12-28 01:20 UTC (permalink / raw)
To: Robert Blayzor <rblayzor.bulk@inoc.net>; +Cc: pgsql-performance@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Table performance with millions of rows (partitioning)
@ 2017-12-28 01:27 Robert Blayzor <rblayzor.bulk@inoc.net>
parent: Justin Pryzby <pryzby@telsasoft.com>
0 siblings, 1 reply; 3+ messages in thread
From: Robert Blayzor @ 2017-12-28 01:27 UTC (permalink / raw)
To: Justin Pryzby <pryzby@telsasoft.com>; +Cc: pgsql-performance@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Table performance with millions of rows (partitioning)
@ 2017-12-28 02:22 pinker <pinker@onet.eu>
parent: Robert Blayzor <rblayzor.bulk@inoc.net>
0 siblings, 0 replies; 3+ messages in thread
From: pinker @ 2017-12-28 02:22 UTC (permalink / raw)
To: pgsql-performance
No, it's unfortunately not possible.
Documentation says in Caveats part:
/Constraint exclusion only works when the query's WHERE clause contains
constants (or externally supplied parameters). For example, a comparison
against a non-immutable function such as CURRENT_TIMESTAMP cannot be
optimized, since the planner cannot know which partition the function value
might fall into at run time.
Keep the partitioning constraints simple, else the planner may not be able
to prove that partitions don't need to be visited. Use simple equality
conditions for list partitioning, or simple range tests for range
partitioning, as illustrated in the preceding examples. A good rule of thumb
is that partitioning constraints should contain only comparisons of the
partitioning column(s) to constants using B-tree-indexable operators./
Even making a function in SQL or plpgsql and declaring it as immutable will
not help. Postgres will always check against all the partitions. It's not
enough "simple" for the planner.
--
Sent from: http://www.postgresql-archive.org/PostgreSQL-performance-f2050081.html
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2017-12-28 02:22 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2017-12-28 01:20 Re: Table performance with millions of rows (partitioning) Justin Pryzby <pryzby@telsasoft.com>
2017-12-28 01:27 ` Robert Blayzor <rblayzor.bulk@inoc.net>
2017-12-28 02:22 ` pinker <pinker@onet.eu>
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