Okay, I see your point with staging table. That's a good idea!
The only problem I see here is the transfer-to-archive-table process. As
you've correctly noticed, the system is kind of a real-time and there
can be dozens of processes writing to the staging table, i cannot see
how to make the transfer/flush process right and clear...
Pierre Frédéric Caillaud 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.
>
>