From: | Wes <wespvp(at)syntegra(dot)com> |
---|---|
To: | pgsql general <pgsql-general(at)postgresql(dot)org> |
Subject: | Partitioning - when is it too many tables? |
Date: | 2006-03-23 23:05:29 |
Message-ID: | C04885D9.218AC%wespvp@syntegra.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
I'm working on a database that will (at current load) add 100 million
records per day to a database, and needs to keep around 6 months of data
online. Of course, we don't want the loads to be running all day while
queries are going on.
Using COPY with indexes active runs great with an empty database, then drops
precipitously as the index size grows. It looks like I should be able to
hit the numbers if I insert the data using COPY with no indexes, then add
the indexes. I'm looking at partitioning with one table per day. So, we'd
be looking at about 180 tables with 100 million rows each. Searches would
typically be within a single day, although they could span multiple days.
This keeps the indexes a more or less reasonable size, and allows quick
deleting of the old data. Is there any problem with 180 child tables? How
many would be too many (e.g. if I did one table per 6 hours?)
Thanks
Wes
From | Date | Subject | |
---|---|---|---|
Next Message | Just Someone | 2006-03-23 23:42:23 | Re: Some pgbench results |
Previous Message | Steve Crawford | 2006-03-23 23:02:54 | Build only clients |