(Declaration of interest: I'm researching for a publication
on OLTP system design)
I have a question about file writes, particularly on POSIX.
This arose while considering the extent to which cache memory
and command queueing on disk
drives can help improve performance.
Is it correct that POSIX requires that the updates to a single
file are serialised in the filesystem layer?
So, if we have a number of dirty pages to write back to a single
file in the database (whether a table or index) then we cannot
pass these through the POSIX filesystem layer into the TCQ/NCQ
system on the disk drive, so it can reorder them?
I have seen suggestions that on Solaris this can be relaxed.
I *assume* that PostgreSQL's lack of threads or AIO and the
single bgwriter means that PostgreSQL 8.x does not normally
attempt to make any use of such a relaxation but could do so if the
bgwriter fails to keep up and other backends initiate flushes.
Does anyone know (perhaps from other systems) whether it is
valuable to attempt to take advantage of such a relaxation
where it is available?
Does the serialisation for file update apply in the case
where the file contents have been memory-mapped and we
try an msync (or equivalent)?