From: Ron Mayer <rm_pg@cheapcomplexdevices.com>
To: Heikki Linnakangas <heikki@enterprisedb.com>
Cc: Ron Mayer <rm_pg@cheapcomplexdevices.com>
Cc: pgsql-patches@postgresql.org
Subject: Re: [HACKERS] Maintaining cluster order on insert
Date: Fri, 11 Aug 2006 16:33:26 -0700
Message-ID: <44DD13C6.2020609@cheapcomplexdevices.com> (raw)
In-Reply-To: <44DD0FEA.8070002@enterprisedb.com>
References: <44DA31B1.3090700@enterprisedb.com>
<28914.1155154622@sss.pgh.pa.us>
<36e682920608091347m4e075a8cv6a04e3c4d97ed6f4@mail.gmail.com>
<430d92a20608091831w6e59da84k48a58110cb98e8d6@mail.gmail.com>
<9461.1155178183@sss.pgh.pa.us>
<ebipoh$1538$1@news.hub.org>
<44DD0FEA.8070002@enterprisedb.com>
Heikki Linnakangas wrote:
> Ron Mayer wrote:
>> In my case my biggest/slowest tables are clustered by zip-code (which
>> does a reasonable job at keeping counties/cities/etc on the
>> same pages too)....
>
> No deletes? If the tables grow over time, you probably would need to run
> CLUSTER every now and then to get the best performance, though the patch
> would alleviate that quite a lot.
Yup, pretty much no deletes; since it's a historical archive of
some government documents with address info. Though I the
live system may periodically expunge data, say, 10+years old.
> Do you have a development environment where you could test what effect
> the patch would have? It would be interesting to have a real-world use
> case, since I don't have one myself at the moment.
I have a development environment, but it doesn't have the same
real-time-growing behavior, and only a small region of the country.
I suppose I could pre-load N-1 years and cluster it, and then
incrementally insert the last year of data to simulate the effect.
But sure, I'll attempt to try the patch; but don't really have any
good benchmarking environment to give any definitive results. If
an anecdotal "this is how it feels to me" is useful, I can give one
of those.
>> > Your best bet might be to partition the table into two subtables, one
>> > with "stable" data and one with the fresh data.
>>
>> Hmm... that should work well for me too....
>
> Umm, if your inserts are uniformly distributed across the country, you
> wouldn't have a stable part, right?
Hmm. Maybe. I was thinking when archiving to the large table
an "order by" clause when inserting from the new partition to the
stable partition could at least make the big table "piecewise"
clustered so most records for a zip code fit in the same few disk
pages, even though those pages would still end up lying around
far apart on the disk.
I wonder what part of "CLUSTER" gives the most benefit - that
most records of a type fit on a few blocks; or that those blocks
are next to each other so can be read sequentially?
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-hackers@postgresql.org
Cc: rm_pg@cheapcomplexdevices.com, heikki@enterprisedb.com, pgsql-patches@postgresql.org
Subject: Re: [HACKERS] Maintaining cluster order on insert
In-Reply-To: <44DD13C6.2020609@cheapcomplexdevices.com>
* 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