[PATCH] lazy relations delete

Hi!

Here is the case.

Assume we have a master to slave replication with shared_buffers set up
to 2 GB at the master and 4 GB at the slave. All of the data is written
to the master, while reading occurs from slave.

Now we decided to drop many tables, let's say 1000 or 10000 not in a
single transaction, but each table in a separate one. So, due to "plain"
shared_buffers memory we have to do for loop for every relation which
leads to lag between master and slave.

In real case scenario such issue lead to not a minutes lag, but hours
lag. At the same time PostgreSQL have a great routine to delete many
relations in a single transaction.

So, to get rid of this kind of issue here came up an idea: what if not
to delete everyone of relations right away and just store them in an
array, prevent shared buffers (correspond to a deleted relations) from
been flushed. And then array reaches it max size we need to walk all
buffers only once to "free" shared buffers correspond to a deleted
relations.

Here some values from the test which I am made.

Without patch:

1.

(master 2 GB) - drop 1000 tables took 6 sec

(slave 4 GB) - drop 1000 tables took 8 sec

2.

(master 4 GB) - drop 1000 tables took 10 sec

(slave 8 GB) - drop 1000 tables took 16 sec

3.

(master 10 GB) - drop 1000 tables took 22 sec

(slave 20 GB) - drop 1000 tables took 38 sec

With patch:

1.

(master 2 GB) - drop 1000 tables took 2 sec

(slave 4 GB) - drop 1000 tables took 2 sec

2.

(master 4 GB) - drop 1000 tables took 3 sec

(slave 8 GB) - drop 1000 tables took 3 sec

3.

(master 10 GB) - drop 1000 tables took 4 sec

(slave 20 GB) - drop 1000 tables took 4 sec

--
Max Orlov
E-mail: m(dot)orlov(at)postgrespro(dot)ru