Delete a huge number of tables in one DROP TABLE Postgres

Question

Does Postgres have any limits for a number of tables to be deleted in one DROP TABLE command (about 10000 in my case)? Does it depend on the version? Will it be faster than executing the command 10 times & 1000 tables?

The possibilities for testing this are limited in my case, so please share if you've had a similar experience.

Answer 1

There is no theoretical limit on the number of tables you can drop in one statement. However, each table dropped will require a couple of ACCESS EXCLUSIVE locks, which are retained in the locking table until the end of the transaction, so you will exceed the default limit of 6400 locks at some point. Increasing max_locks_per_transaction will increase the limit and is safe to do.

Answer 2

Use This For All Table Of Database.

DO $$ 
  DECLARE 
    r RECORD;
BEGIN
  FOR r IN 
    (
      SELECT table_name 
      FROM information_schema.tables 
      WHERE table_schema=current_schema()
    ) 
  LOOP
     EXECUTE 'DROP TABLE IF EXISTS ' || quote_ident(r.table_name) || ' CASCADE';
  END LOOP;
END $$ ;