Suppose we have a Postgres Database with only a single table. It is known that no UPDATE statements will ever be run on it, nor will any DELETEs. It is 100% append-only.
Some databases exist that rely on this behavior and manage to get specific performance gains as a consequence, such as Datomic. However, this is not the typical way Postgres is expected to be run so I imagine to benefit, performance-wise, I'll need to configure it specially.
What are the primary ways I can take advantage of my no UPDATE/DELETE guarantee?
I believe I could all-but-disable vacuum, but I'm not 100% certain of that and I don't think that's a huge win anyway. What are the largest configuration changes that could be made here?
If it matters, writes happen at a specific time once-a-day and never at other times. However, I think that detail is getting into "too localized" territory so I'll edit it out of this question if it isn't helpful. (It if is helpful, I'll edit out the disclaimer portion here!)
Best Answer
If you wish to benefit from index-only scans (IOS), you will still need to vacuum your tables even if they are append-only. Indeed, autovacuum was never adjusted for the needs of IOS, so it is likely you will need to schedule your own vacuums for IOS to be maximally effective on append only tables. Certainly don't disable autovac, it will naturally not do stuff when there is nothing for it to do so no good will come from disabling it.
Other than that, I don't think there is much you can do. This situation will greatly benefit from the freeze map introduced in 9.6, but there is nothing you need to do to activate that, other than use a version at least that high.