On 4/22/05, Hannu Krosing wrote:...But this is true only when data is changing. In a data-warehousing scenario what it is often needed is a possibility for fast querying of static historical data.
And when we get partitioning, I think many data warehouses will have the bulk of their data in tables like that (previous quarters marked read-only, current quarter growing).
What I am proposing is
1) possibility to explicitly change table status to READ-ONLY . 2) setting a flag CAN_OMIT_HEAP_CHECK after REINDEX TABLE for tables that are READ-ONLY
Why stop at indexes? If you switch to readonly status with a CLUSTER or ALTER TABLE command you can even remove the visibility information from the heap tuples. Visibility would be exclusively controlled by the visibility of the table in the catalog, i.e. all or nothing.
Seems this could reduce the size of some data warehouses considerably too. My biggest tables have nothing but columns of IDs; and the 30-some bytes of the row header is a significant fraction of the entire size. I think the diskspace === I/O bandwidth savings on the heap would be almost as big a benefit as the indexes.
---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives?
http://archives.postgresql.org
