Re: Partition table in 9.0.x?

On Sunday, January 6, 2013, AJ Weber wrote:

All fair questions...

Thank you for your detailed response!


On 1/4/2013 11:03 PM, Jeff Janes wrote:

On Friday, January 4, 2013, AJ Weber wrote:

Hi all,

I have a table that has about 73mm rows in it and growing.  

How big is the table in MB?  Its indexes?

Not sure on this.  Will see if pgAdmin tells me.

 

Anyway, this table is going to continue to grow, and it's used frequently (Read and Write).

Are all rows in the table read and written with equal vigor, or are there hot rows and cold rows that can be recognized based on the row's values?

No, I could probably figure out a way to setup an "archive" or "older" section of the data that is updated much less frequently. 

Deletes are rare.  Inserts/Updates "yes".  Select on existing rows -- very frequent.

 

 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?

Probably not.  Or at least, you haven't given us the information to know.  Very broadly speaking, well-implemented partitioning makes bulk loading and removal operations take less IO, but makes normal operations take more IO,  or if lucky leaves it unchanged.  There are exceptions, but unless you can identify a very specific reason to think you might have one of those exceptions, then you probably don't.

I know you can't believe everything you read, but I thought I saw some metrics about when a table's size exceeds some fraction of available RAM, or when it approaches 100mm rows, it's a big candidate for partitioning.

Should I run a cluster or vacuum full after all is done?

Probably not.  If a cluster after the partitioning would be beneficial, there would be a pretty good chance you could do a cluster *instead* of the partitioning and get the same benefit.  

I did try clustering the table on the PK (which is actually 4 columns), and it appeared to help a bit.  I was hoping partitioning was going to help me even more.

Is there a major benefit if I can upgrade to 9.2.x in some way that I haven't realized?

If you have specific queries that are misoptimized and so are generating more IO than they need to, then upgrading could help.  On the other hand, it could also make things worse, if a currently well optimized query becomes worse. 

Is there some new feature or optimization you're thinking about with this comment?  If so, could you please just send me a link and/or feature name and I'll google it myself?

The main things I am thinking of are the "fudge factor" for large indexes, which is currently being discussed in both performance and hackers mailing lists, which was made overly aggressive in 9.2 and so can make it choose worse plans, and the "allow the planner to generate custom plans for specific parameter values even when using prepared statements" from the 9.2 release notes, which can allow it to choose better plans.  But, surely there are other changes as well, which amount to corner cases and so are hard to discuss in the abstract.  Which is why instrumentation is important.  There isn't much point in worrying about possible changed plans until you've identified the queries that are important to worry about.

But, instrumentation has improved in 9.2 from 9.0, so upgrading would make it easier to figure out just which queries are really bad and have the most opportunity for improvement.  A little well informed optimization might obviate the need for either partitioning or more hard drives.

This is interesting too.  I obviously would like the best available options to tune the database and the application.  Is this detailed in the release notes somewhere, and what tools could I use to take advantage of this?  (Are there new/improved details included in the EXPLAIN statement or something?)