search

jbogard / Respawn

Intelligent database cleaner for integration tests
Apache License 2.0
2.67k stars 134 forks source link

Have the option to use DELETE statements for Postgres to increase performance #126

Open relfman-cmg opened 1 year ago

relfman-cmg commented 1 year ago

Hi,

I have the a scenario for integration tests where each test clears the DB for approx 40 tables. It takes 200-300ms which adds up over multiple tests. The tables have barely any rows in them per each test, however when I had wrote a query with multiple DELETE FROM CASCADE statements it's ~30ms.

See this for more details: https://www.lob.com/blog/truncate-vs-delete-efficiently-clearing-data-from-a-postgres-table

It would be good to have the option to flip between TRUNCATE and DELETE depending if you have lots of rows or very little

cervengoc commented 1 year ago

As far as I know TRUNCATE (at least in MS SQL) will fail when foreign key constraints are present, regardless of what the statement order is, so it probably should not be used. (Sorry, I've justed checked that it's different in PG)

Overall, an approach could be to add an extension point for customizing/extending the generated delete statements, so one can kind of do whatever he wants.

It would also open the paths for other extension feature like PK snapshoting (see #122)