On Mon, Apr 20, 2015 at 3:28 PM, Jeff Janes <jeff.ja...@gmail.com> wrote: > But why should 1 SELECT or 20 SELECTs or 200 SELECTs have to do a job, while > the user waits, which is fundamentally VACUUM's duty to do in the > background? If there are a handful of very hot pages, then it makes sense > not to wait for vacuum to get to them. And that is what a block-count limit > does.
I think that's a fundamental mischaracterization of the problem. As soon as you define this as "vacuum's problem", then of course it makes no sense to prune in the foreground, ever. But if you define the problem as "get the best overall system performance", then it clearly DOES sometimes make sense to prune in the foreground, as benchmark results upthread demonstrate. The fact is that on a workload like pgbench - and it doesn't have to be exactly pgbench, just any kind of workload where there are lots of changes to the table - vacuum can at any given time be pruning at most one page of the table. That is because only one vacuum process can be running in a given table at one time, and it can't be doing two things at once. But there can be many processes doing inserts, updates, or deletes on that table, as many as whatever you have max_connections set to. There can easily be dozens even on a well-configured system; on a poorly configured system, there could be hundreds. It seems obvious that if you can have dozens or hundreds of processes creating garbage and at most one process cleaning it up, there will be cases where you get further and further behind. Now, it might well be that the right solution to that problem is to allow multiple vacuum processes in the same database, or add background workers to help with opportunistic HOT-pruning of pages so it doesn't get done in the foreground. Fine. But as of today, on a heavily-modified table, the ONLY way that we can possibly remove junk from the table as fast as we're creating junk is if the backends touching the table do some of the work. Now, Simon is making the argument that it should be good enough to have people *modifying* the table help with the cleanup rather than imposing that load on the people who are only *reading* it, and that's not a dumb argument, but there are still cases where that strategy loses - specifically, where the table churn has stopped or paused, by autovacuum hasn't run yet. If you're going to do 1 sequential scan of the table and then go home for the day, HOT-pruning is dumb even in that case. If you're going to do 1000 sequential scans of that table in a row, HOT-pruning may very well be smart. There's no guarantee that the table has met the autovacuum threshold, but HOT-pruning it could well be a win anyway. Or it might be a loss. You can make any policy here look smart or dumb by picking a particular workload, and you don't even have to invent crazy things that will never happen in real life to do it. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers