Re: Writing Commit Status hint bits (was Re: Constant WAL replay)

Simon Riggs <simon@2ndquadrant.com> writes:
> In general, the hint bits are good. In *some* cases, not. I still seek
> control over that as a designer.

> Specifically, the scenario I want to optimize is this:
> - we load a table with lots of real time measurement data, as one child
> out of a large number of similar child tables
> - we then immediately create summary tables from the measurements
> - after this the detailed data is only sporadically accessed, if ever
> - detail data is dropped from the database after a few weeks
> - the majority of the database is detail data, so those tables are never
> vacuumed since no rows are ever deleted from those tables (the tables
> are dropped) nor is access sufficiently frequent to make it sensible to
> set hint bits - hence *no* complete database vacuum is run on a regular
> basis

> In this design, the second step causes the whole detailed data table to
> be written out to the database (again) immediately after loading. I
> would like to be able to prevent that.

This application seems sufficiently off the beaten path to not be worth
solving with a knob as klugy and potentially dangerous as
suppress_hint_bits.

A better way to avoid the write pass, if you must, is to do the summary
table creation in the same transaction that loads the data.

            regards, tom lane