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@blastronetworks.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.