search

denishpatel / pg-clone-schema

Postgres clone schema utility without need of going outside of database. Makes developers life easy by running single function to clone schema with all objects. It is very handy on Postgres RDS. Utility is sponsored by http://elephas.io/
MIT License
173 stars 44 forks source link

Question about 'DATA' copy performace #137

Open samodadela opened 3 weeks ago

samodadela commented 3 weeks ago

Hi,

I tested pg-clone-schema on my database (localhost).

I have another way of 'cloning' the schema. I apply all the migrations to create a fresh schema, remove all FK constraints, copy the data, reinstall the FK constraints, copy sequence values over. (The removal of constraints is needed because I need to run this on GCP (managed) Cloud SQL and there is no way to deffer constraints)

So comparing those two, I noticed a big difference in speed.

I wonder how pg-clone-schema works? Is the data copied before the indexes and constraints are created, or after? If indexes and FK constraints are the cause of the slowdown - would it be possible to deffer FK constraint and index creation to the end?

MichaelDBA commented 1 week ago

@samodadela, How are you copying the data?