Re: The Free Space Map: Problems and Opportunities

On Tue, Sep 7, 2021 at 9:37 PM Peter Geoghegan <pg(at)bowt(dot)ie> wrote:
> What I really like about the idea of doing the work in the foreground
> (when it's clearly not going to be too much of a latency hit) is that
> it would act as a natural form of backpressure.

Right, that's fair. But, prune-before-evict can be done in the first
instance by the bgwriter and then by the foreground process doing the
eviction if the bgwriter doesn't keep up. At least, assuming we had a
bgwriter that worked, which I think right now we don't, but fixing
that might be a good idea anyway.

> I didn't mean to suggest that it had to happen in perfect lockstep.
> But I do think that it should be pretty close to perfect. What I
> actually do right now is prune an open page when it *appears* to be
> full inside the loop in RelationGetBufferForTuple().

That seems like a good idea.

> As crude as that
> is, it sets hint bits and prunes away aborted transactions in mostly
> the right way, at least as far as TPC-C is concerned. (This only works
> because it fits into the wider framework of my much larger patch,
> which introduces ownership semantics, open and closed pages, empty
> page freelists, etc.)

I don't know, I'm not really convinced that "much larger patches" that
change a lot of loosely related things all at once are good for the
project. It seems to me that there's a reasonably good chance of
replacing an annoying set of problems that existing PostgreSQL users
have worked around to some degree, knowing or unknowingly, with a
different annoying set of problems that may cause fewer or more
problems in practice. Sometimes there's no way to improve something
short of a giant project that changes a lot of things at the same
time, but a series of incremental changes is a lot less risky.

> It seems to me that this leaves one harder question unanswered: at
> what point does a "medium sized" transaction become so large that it
> just doesn't make sense to do either? What's the crossover point at
> which background processing and foreground processing like this should
> be assumed to be not worth it? That I won't speculate about just yet.
> I suspect that at some point it really does make sense to leave it all
> up to a true table-level batch operation, like a conventional VACUUM.

I doubt it makes sense to define a limit here explicitly. At some
point strategies will naturally start to fail, e.g. prune-before-evict
won't work once the operation becomes large enough that pages have to
be evicted while the transaction is still running.

--
Robert Haas
EDB: http://www.enterprisedb.com