pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedBest way to handle "read only" paritions
3+ messages / 2 participants
[nested] [flat]
* Best way to handle "read only" paritions
@ 2016-04-29 16:46 Dave Johansen <davejohansen@gmail.com>
2016-04-30 02:15 ` Re: Best way to handle "read only" paritions Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
0 siblings, 1 reply; 3+ messages in thread
From: Dave Johansen @ 2016-04-29 16:46 UTC (permalink / raw)
To: pgsql-admin
We're using a time-based partitioning scheme for our data and so partitions
become "read only" once the time covered by it has passed. We've been
CLUSTERing those partitions but here's some questions I have:
Should we set the fill factor of the indexes from the default of 90 to 100?
Should we do anything with xids and freeze age?
Anything else?
Thanks,
Dave
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Best way to handle "read only" paritions
2016-04-29 16:46 Best way to handle "read only" paritions Dave Johansen <davejohansen@gmail.com>
@ 2016-04-30 02:15 ` Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
2016-04-30 03:30 ` Re: Best way to handle "read only" paritions Dave Johansen <davejohansen@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Peter Eisentraut @ 2016-04-30 02:15 UTC (permalink / raw)
To: Dave Johansen <davejohansen@gmail.com>; pgsql-admin
On 04/29/2016 12:46 PM, Dave Johansen wrote:
> We're using a time-based partitioning scheme for our data and so
> partitions become "read only" once the time covered by it has passed.
> We've been CLUSTERing those partitions but here's some questions I have:
> Should we set the fill factor of the indexes from the default of 90 to 100?
> Should we do anything with xids and freeze age?
If you are already spending the effort to cluster the completed
partitions manually, you might as well also VACUUM FREEZE them manually.
Otherwise, this will happen eventually via autovacuum, so you shouldn't
need to worry about it unless you have particular problems with vacuum
keeping up or finishing.
Setting the index fill factor is a reasonable thought, but if you're
never going to change the old partitions again, the new fill factor will
never be applied. But it might still be worthwhile if you expect
occasional changes on old partitions.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Best way to handle "read only" paritions
2016-04-29 16:46 Best way to handle "read only" paritions Dave Johansen <davejohansen@gmail.com>
2016-04-30 02:15 ` Re: Best way to handle "read only" paritions Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
@ 2016-04-30 03:30 ` Dave Johansen <davejohansen@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: Dave Johansen @ 2016-04-30 03:30 UTC (permalink / raw)
To: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>; +Cc: pgsql-admin
On Fri, Apr 29, 2016 at 7:15 PM, Peter Eisentraut <
peter.eisentraut@2ndquadrant.com> wrote:
> On 04/29/2016 12:46 PM, Dave Johansen wrote:
> > We're using a time-based partitioning scheme for our data and so
> > partitions become "read only" once the time covered by it has passed.
> > We've been CLUSTERing those partitions but here's some questions I have:
> > Should we set the fill factor of the indexes from the default of 90 to
> 100?
> > Should we do anything with xids and freeze age?
>
> If you are already spending the effort to cluster the completed
> partitions manually, you might as well also VACUUM FREEZE them manually.
> Otherwise, this will happen eventually via autovacuum, so you shouldn't
> need to worry about it unless you have particular problems with vacuum
> keeping up or finishing.
>
> Setting the index fill factor is a reasonable thought, but if you're
> never going to change the old partitions again, the new fill factor will
> never be applied. But it might still be worthwhile if you expect
> occasional changes on old partitions.
Would setting the fill factor on the indexes before the CLUSTER do anything?
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2016-04-30 03:30 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2016-04-29 16:46 Best way to handle "read only" paritions Dave Johansen <davejohansen@gmail.com>
2016-04-30 02:15 ` Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
2016-04-30 03:30 ` Dave Johansen <davejohansen@gmail.com>
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