Partitioning - when is it too many tables?

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

In response to

Responses

Browse pgsql-general by date

  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