Re: Tuning resource parameters for a logging database.

Just wanted to let everyone know that I set up partitioning and got my
30 days of data in. This sucker is FAST. And it wasn't nearly as
complicated to make happen as I thought it would be. I didn't use the
triggers or rules to make sure data goes in the right table on insert,
as there's only one script that inserts, so I just generate the correct
table name there.

Alex Thurlow
Blastro Networks

http://www.blastro.com
http://www.roxwel.com
http://www.yallwire.com

On 5/22/2009 9:56 AM, Vick Khera wrote:
> On Thu, May 21, 2009 at 3:37 PM, Alex Thurlow<alex(at)blastronetworks(dot)com> wrote:
>
>> I was hoping to not have to change all my code to automate the partitioning
>> table creation stuff, but if that's really the best way, I'll check it out.
>> Thanks for the advice.
>>
>
> About a 18 months ago we split a large table with 300+ million rows
> into 100 partitions. The query speed was improved by at least 2
> orders of magnitude. Postgres is exceptionally good at dealing with
> tables in the 10 million row range, and that's what we gave it. Our
> primary queries on the data were able to go directly to the right
> partition, but using constraint exclusion was still nearly just as
> fast.
>
> It was totally worth the 10 days or so it took to set up, test (on a
> replica!) and migrate the data. In your case you could have a natural
> migration by just adding the child tables and inserting your new data
> there and deleting old data from your main table. After 30 days, your
> main table will be empty and you just truncate it, freeing up all the
> space.
>
>