From: AJ Weber <aweber@comcast.net>
To: pgsql-performance@postgresql.org
Subject: Partition table in 9.0.x?
Date: Fri, 04 Jan 2013 16:31:31 -0500
Message-ID: <50E74A33.5000204@comcast.net> (raw)
In-Reply-To: <50E65B85.9000001@synedra.com>
References: <CALd8TVECZDosPDZ7Cwv1gVRy1p2F4Hg+sw+240MsN36KUkt31g@mail.gmail.com>
<50E65B85.9000001@synedra.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
Hi all,
I have a table that has about 73mm rows in it and growing. Running
9.0.x on a server that unfortunately is a little I/O constrained. Some
(maybe) pertinent settings:
default_statistics_target = 50
maintenance_work_mem = 512MB
constraint_exclusion = on
effective_cache_size = 5GB
work_mem = 18MB
wal_buffers = 8MB
checkpoint_segments = 32
shared_buffers = 2GB
The server has 12GB RAM, 4 cores, but is shared with a big webapp
running in Tomcat -- and I only have a RAID1 disk to work on. Woes me...
Anyway, this table is going to continue to grow, and it's used
frequently (Read and Write). From what I read, this table is a
candidate to be partitioned for performance and scalability. I have
tested some scripts to build the "inherits" tables with their
constraints and the trigger/function to perform the work.
Am I doing the right thing by partitioning this? If so, and I can
afford some downtime, is dumping the table via pg_dump and then loading
it back in the best way to do this?
Should I run a cluster or vacuum full after all is done?
Is there a major benefit if I can upgrade to 9.2.x in some way that I
haven't realized?
Finally, if anyone has any comments about my settings listed above that
might help improve performance, I thank you in advance.
-AJ
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
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: aweber@comcast.net
Subject: Re: Partition table in 9.0.x?
In-Reply-To: <50E74A33.5000204@comcast.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