How to nuke a postgres schema?

Question

I'm coming up on the limits of my Postgres SQL knowledge, and I'm quite unsure how to diagnose this issue. Please pardon the noob-ness in my questions; I'm open to updating the question as the (expected) follow-up questions come.

I have a fairly complex database structure, in which under a schema, a number of tables are connected to one another by foreign keys. I unfortunately cannot reveal the schema itself.

One of the tables, let's call it "A", used to store close to 100K records. It's got foreign key relationships to two other tables, one called "B" with also approx. 100K records, and the other called "C" with approx. 100 records. There are 5 more tables as well.

I wanted to drop all of the tables. However, using:

truncate table schema.A cascade

takes a very long time (over 10 minutes without finishing), even though I have already removed all rows from the table (yes, I understand truncate is designed to do that exact operation). This is the first point that I don't understand: why would it take a long time to perform this operation?

Secondly, I tried:

drop table schema.A;

(using Postico, a GUI, rather than by entering SQL commands directly)

That also runs for over 10 minutes without finishing.

Are the foreign key relations the key blocker here?

If I wanted to "just quickly nuke" the schema, and start over from scratch (all of my table schemas are defined in a SQLAlchemy file, so recreating is trivial), would I have to drop the entire schema using admin privileges, or is it possible to do it as a user without admin privileges?

TRUNCATE and DROP TABLE should generally be pretty much instantaneous. If they're hanging for 10 minutes, it usually means they're waiting to acquire a lock. Check pg_stat_activity.wait_event_type while your command is running to find out if this is the case. If so, pg_blocking_pids() will tell you which session is holding the lock. — Nick Barnes
– Nick Barnes, Commented Apr 26, 2019 at 21:04
Thanks for pinging back. Indeed, this was a lock issue, which I was ignorant about until your reply. Thanks again! — ericmjl
– ericmjl, Commented May 1, 2019 at 11:42

tadman · Accepted Answer · 2019-04-26 17:23:05Z

2

If you want to drop the schema:

DROP SCHEMA schema_name CASCADE

For the default schema:

DROP SCHEMA public CASCADE

To quickly reset a single schema database:

DROP SCHEMA public CASCADE;
CREATE SCHEMA public;

answered Apr 26, 2019 at 17:23

tadman

212k23 gold badges237 silver badges266 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to nuke a postgres schema?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related