0

I worked on a DB-server hosting multiple big DBs including DBs that aren't used anymore. I wanted to drop one of those old DBs and it already took longer than I expected(over 40 seconds) then I noticed, that the server's performance was significantly impacted and even became unresponsive. Luckily I was able to terminate the query eventually and get the server back into a stable state.

Can you help me to understand:

  • What the reason for the performance impact might be? I'd have assumed that dropping a DB is cheap and fast.
  • What is the best approach to clean up a DB-Server? Would it be better to drop each table sequentially before dropping the DB or are there any other things that I can do to drop a DB safe and quickly?

Thanks!

7
  • Dropping a database requires deleting all files, maybe there is some kind of I/O limit imposed by your hosting service.
    – user1822
    Sep 16, 2022 at 10:24
  • 1
    Do you know why the performance suffered? Do you have a sar report or vnstat report or any other type of monitoring in place like just informally watching top? How about a slow query log with auto_explain? If none of those things, what was it you did observe that lead you to the conclusion?
    – jjanes
    Sep 16, 2022 at 16:33
  • What version of PostgreSQL is this? Also, what is your OS and filesystem?
    – jjanes
    Sep 16, 2022 at 16:42
  • Dropping databases which are not in use should be trivial. Some filesystems do freak out when you dump massive amounts of newly freed space onto them all at once, so maybe that is the issue. Dropping the tables one by one (in separate transactions) should not be necessary, but it does have the advantage it can easily be stopped and then resumed later if trouble does emerge.
    – jjanes
    Sep 16, 2022 at 16:48
  • are you absolutely certain that the database was not in-use?
    – Jasen
    Sep 16, 2022 at 20:55

2 Answers 2

-1

Dropping a database requires updating records in all other databases pg_catalog schemas (eg: table pg_catalog.pg_database), if you have a large number of other databases or run into some sort of locking issue that could cause stoppages.

You can get most of the benefit of dropping a database by just dropping all the schemas in the database: DROP SCHEMA PUBLIC CASCADE; etc.

This will run without locking any other databases. after that wait until a quiet time to drop the database. now that the outgoing database has no tables the duration of the locking will be much briefer.

3
  • 2
    There is only one pg_database table, it is just presented to all databases (that is, it lives in the magic tablespace pg_global). Updating it should be trivial. Whatever is going on, the details of it are very unlikely to be this.
    – jjanes
    Sep 16, 2022 at 16:45
  • Also, this would not explain a performance drop after the database was dropped. Sep 16, 2022 at 19:51
  • performance drop was apparently during. he aborted the drop database after 40 seconds.
    – Jasen
    Sep 16, 2022 at 20:15
0

I'd have assumed that dropping a DB is cheap and fast.

No. It's a bit like the difference between truncate tbl and delete from tbl. The former just "forgets" about the data rows, the latter goes through each and every row, logs its removal and gets rid of it.

If there was an option to "forget" about a database in the same way, then that would be "cheap and fast".
Short story - there is no such option.

Dropping a database gets rid of everything. That will thrash the disks and impact everything else on the server.

This kind of really "heavy-weight", disruptive activity is what Maintenance Windows are for.

2
  • 1
    Since when is deleting a file a heavy operation? That must be a strange file system... Sep 16, 2022 at 19:52
  • Database != File A Database is made up of many files, each of which has to be deleted separately and the resulting disk space reorganised to prevent fragmentation. It all takes time and takes the read/write heads on the physical drive away from where "normal" database activities would like them to be. Therefore, big hit on performance.
    – Phill W.
    Sep 20, 2022 at 7:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.