> That may help with the queries speed (not a problem now), but we'll then
> have to add UNION statement for daily staging table for other 5% of
> requests, right? And there would be a moment when daily message is in
> archive table AND in daily table (while transferring from daily table to
> archive).
> Our main problem is in blocking when doing DELETE (app sometimes freezes
> for a long time), and also we have to do VACUUM on live table, which is
> not acceptable in our app.
>
> Thanks for your reply, I was kinda worried about number of partitions
> and how this would affect PG query execution speed.
>
> Kenneth Marshall wrote:
>>> Oh, btw, 95% of queries are searching rows for current date (last 24
>>> hours).
>>>
>>
>> You may want to use a daily staging table and then flush to the monthly
>> archive tables at the end of the day.
If the rows in the archive tables are never updated, this strategy means
you never need to vacuum the big archive tables (and indexes), which is
good. Also you can insert the rows into the archive table in the order of
your choice, the timestamp for example, which makes it nicely clustered,
without needing to ever run CLUSTER.
And with partitioning you can have lots of indexes on the staging table
(and current months partition) (to speed up your most common queries which
are likely to be more OLTP), while using less indexes on the older
partitions (saves disk space) if queries on old partitions are likely to
be reporting queries which are going to grind through a large part of the
table anyway.