| From: | Leonardo F <m_lists(at)yahoo(dot)it> |
|---|---|
| To: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
| Subject: | Partial indexes instead of partitions |
| Date: | 2010-06-11 09:00:53 |
| Message-ID: | 342693.65426.qm@web29012.mail.ird.yahoo.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general |
HI all,
I have a very big table (2000 inserts per sec, I have to store 20 days of data).
The table has 2 indexes, in columns that have almost-random values.
Since keeping those two indexes up-to-date can't be done (updating 2000
times per second 2 indexes with random values on such a huge table is
impossible) I thought that partitioning was the way to go.
Now I think I have 2 options:
a) create 480 partitions, 1 for each hour of the day. 2 indexes on each
partition
b) create 20 partitions, and create 24*2 partial indexes on the current
partition; then the next day (overnight) create 2 global indexes for the
table and drop the 24*2 indexes...
I thought about option b) because I don't like the fact that the planner takes
"ages" to plan a query in case there are 480 partitions; in option b) I would
have:
19 partitions with 2 indexes each
1 partition (the "current day" one) with 24*2 partial indexes
Does it make sense? Anyone has ever used partial indexes instead of
partitions?
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Ulas Albayrak | 2010-06-11 10:11:23 | Moving a live production database to different server and postgres release |
| Previous Message | Janning | 2010-06-11 07:28:19 | Re: checkpoint spikes |