Relation bulk write facility

From: Heikki Linnakangas <hlinnaka(at)iki(dot)fi>
To: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Relation bulk write facility
Date: 2023-09-19 15:13:47
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

Several places bypass the buffer manager and use direct smgrextend()
calls to populate a new relation: Index AM build methods, rewriteheap.c
and RelationCopyStorage(). There's fair amount of duplicated code to
WAL-log the pages, calculate checksums, call smgrextend(), and finally
call smgrimmedsync() if needed. The duplication is tedious and
error-prone. For example, if we want to optimize by WAL-logging multiple
pages in one record, that needs to be implemented in each AM separately.
Currently only sorted GiST index build does that but it would be equally
beneficial in all of those places.

And I believe we got the smgrimmedsync() logic slightly wrong in a
number of places [1]. And it's not great for latency, we could let the
checkpointer do the fsyncing lazily, like Robert mentioned in the same

The attached patch centralizes that pattern to a new bulk writing
facility, and changes all those AMs to use it. The facility buffers 32
pages and WAL-logs them in record, calculates checksums. You could
imagine a lot of further optimizations, like writing those 32 pages in
one vectored pvwrite() call [2], and not skipping the buffer manager
when the relation is small. But the scope of this initial version is
mostly to refactor the existing code.

One new optimization included here is to let the checkpointer do the
fsyncing if possible. That gives a big speedup when e.g. restoring a
schema-only dump with lots of relations.



Heikki Linnakangas
Neon (

Attachment Content-Type Size
v1-0001-Introduce-a-new-bulk-loading-facility.patch text/x-patch 45.5 KB


Browse pgsql-hackers by date

  From Date Subject
Next Message Robert Haas 2023-09-19 15:41:14 Re: CREATE FUNCTION ... SEARCH { DEFAULT | SYSTEM | SESSION }
Previous Message Chris Cleveland 2023-09-19 14:32:04 Projection pushdown to index access method