6

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!)

3
  • 3
    You should almost never disable auto-vacuum. If nothing is deleted or updated, then there aren't any dead tuples to clean up, so auto-vacuum wouldn't put any load on the system anyway. Plus you still need it to update statistics. Jul 18 '18 at 14:40
  • @a_horse_with_no_name That was my thought. That would be a good piece of an answer. Hopefully you or someone else can include it so it doesn't disappear into comment oblivion. Jul 18 '18 at 14:45
  • There might be some work for autovacuum. Rolled back transactions can introduce dead tuples, can't they? ( @a_horse_with_no_name ) Jul 19 '18 at 15:38
2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.