amitkapila

Parallel Apply of Large transactions will be default in PostgreSQL18.

In PostgreSQL 17, we have added capabilities to allow online upgrading of logical and physical replication setups.

You can read how different databases provide MVCC at my other blog: https://amitkapila16.blogspot.com/2015/03/different-approach...

Your last sentence is not very clear to me. "But it only does this as long as no index updates are necessary.". Do you mean to say it does only for HOT updates? If so, I don't think that is the case, it can do it during a scan of the heap.

Even if you don't want to upgrade to major version as you don't need additional features, it is advisable to upgrade to latest minor version of PG-10. See docs https://www.postgresql.org/docs/release/ for the latest releases and what is fixed in each minor release.

I think you can check n_dead_tup, n_mod_since_analyze from pg_stat_all_tables, and see if the numbers are high, then it would be a good idea to perform vacuum. I found some query on the net which might help you. See https://gist.github.com/lesovsky/b0e4033380b0992789118ab35c4... I think you might need to change this depending on the version of Postgres.

That's right, but you can manage if you know which query is going to take parallel plan (using explain). You can set it before that query execution and reset once query is executed. I understand that this can't be a generic solution, it can help only in a limited set of cases.

Thanks.

I don't have any solution for this problem, but one thing to note is that if you set the appropriate value of work_mem in the session that runs the parallel query, the same will be used by parallel workers.

It appears like we are sacrificing speed in some cases, but it is not actually true. In the current heap, we have to modify a current row, add a new row, and then WAL log diff of both rows (or in some cases need to write both rows) and routinely perform Hot-pruning. Now, when the new row can't fit on the same page, we have to write both the pages, the page which contains old row and the page that contains new row.

In zheap, we need to write an old row in undo, but in many cases, it won't be written to disk as undo worker will discard the undo very quickly unless there are open snapshots. In this, we don't need to perform Hot-pruning. So, there is an additional cost of memory copy, but that is more than compensated by savings.

You can read 'macdice' reply in this regards.

To me that was obvious, but you are right, it is better to be clear. I have added a below sentence my blog post.

"I would like to mention that the above results don't mean that zheap will be better in all cases than heap. For example, rollbacks will be costlier in zheap. Just to be clear, this storage format is proposed as another format alongside current heap, so that users can decide which storage they want to use for their use case."

Vacuum can't recover dead space in all cases. Consider if the dead rows are distributed throughout the table, it won't be able to reclaim it, only it can facilitate the future insertions into it. Also, the work it does to do that is many-a-times quite heavy for many workloads as it can slow down the system. I think you haven't fully looked at all the other benefits of this system which includes reducing write amplification by (a) by avoiding rewrites due to freezing, hint bits (b) making it possible to do an update that touches indexed columns without updating every index.

I think there are other benefits too in terms of just raw performance because it avoids a lot of work like HOT pruning. Yes, it instead needs to discard undo, but that is much cheaper.

In general, I think it is an overstatement to say that this is a wasted effort.