DROP TABLE: Killing shared_buffers

Tuning shared_buffers in PostgreSQL is a pretty fundamental thing to do when setting up a high-performance server. In most cases adding more memory speeds up things considerably. Many tests have shown exactly that. However, there are some corner cases, which are not commonly mentioned and which can cause significant performance issues in some very rare cases.

Massive use of DROP TABLE

One of those corner cases is the massive use of DROP TABLE. One might argue that DROP TABLE is a rare thing to do. However, in many applications it is not. DROP TABLE is used massively by countless applications and can turn out to be a bit of a showstopper.

Why is it an issue? The problem is that during DROP TABLE all cached blocks are forced out of the cache. The bigger the cache, the bigger the effort.

The following test shows, what this can actually mean in real life.

The test script

SET synchronous_commit TO off;
BEGIN;
CREATE TABLE x(id int);
INSERT INTO x VALUES (1);
DROP TABLE x;
COMMIT;

To make sure that the entire test does not start to be disk-bound, flushing requirements are relaxed. Then a simple table is created and dropped again. The test is repeated with various settings for shared_buffers:

#/bin/sh

DB=test

for x in '8 MB' '32 MB' '128 MB' '1 GB' '8 GB'
do
      pg_ctl -D /tmp/db -l /dev/null -o "--shared_buffers='$x'" start
      sleep 1
      echo tps for $x
      psql -c "SHOW shared_buffers" $DB
      pgbench --file=run.sql -j 1 -c 1 -T 10 $DB 2> /dev/null
      pg_ctl -D /tmp/db stop
      sleep 1
done

The test starts a database instance and runs the test shown before for 10 seconds.  The results are really worth reading:

[hs@laura benchmark]$ ./x.sh | grep tps
tps for 8 MB
tps = 1390.858135 (including connections establishing)
tps = 1391.123414 (excluding connections establishing)
tps for 32 MB
tps = 1341.244741 (including connections establishing)
tps = 1341.745728 (excluding connections establishing)

tps for 128 MB
tps = 1193.329832 (including connections establishing)
tps = 1193.796699 (excluding connections establishing)

tps for 1 GB
tps = 532.483333 (including connections establishing)
tps = 532.702853 (excluding connections establishing)

tps for 8 GB
tps = 104.238781 (including connections establishing)
tps = 104.280531 (excluding connections establishing)

As you can see the number of transactions per seconds drops dramatically down to around 100 TPS – this is 7.5% of the maximum value reached on this test.

The reason for this horrible drop is that during DROP TABLE PostgreSQL will clean out those shared_buffers. The trouble is: The larger the shared_buffers area actually is, the longer this will take. For most applications this is not a real issue. However, if your application is heavily built on creating and dropping tables for whatever reason, this can be a showstopper and kill performance.

Of course, creating and dropping so many temporary tables should not happen, but taking from experience we have seen that it actually does.