DROP TABLE: Killing shared_buffers

UPDATED August 2023: Tuning shared_buffers in PostgreSQL is a pretty fundamental thing to do when you set up a high-performance server. In most cases, you add more memory to speed things up considerably. Many tests have shown exactly that. However, there are some corner cases which are not common, and which can cause significant performance issues.

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 basic 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 a look:

[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 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 from experience we have seen that it actually does.

Take note: vacuum truncation is also affected.

To understand how that works, see the update about vacuum truncation in this blog on table bloat.

In order to receive regular updates on important changes in PostgreSQL, subscribe to our newsletter, or follow us on Twitter, Facebook, or LinkedIn.