Delete large number of rows in Postgres/SQLAlchemy/Alembic

[Kenny Tay]

I have a table that is about 150 GB with about 1 billion rows in Postgres. We have a Flask app that INSERTs to this table using SQLAlchemy and we handle database migrations via Alembic. This has been working great for us but we really should just ditch about 90% of the rows.

I'm thinking of doing the following:

1. Create a new table using an alembic migration. I would copy the definitions of the current tables, rename the tables and then perform the migration
2. Copy over the necessary rows into the new table
3. Perform another migration that deletes the old table and renames the new table with fewer entries

I posted this question on SO and one of the answers suggests that this is feasible and I just wanted to check with the community here if this approach sounds reasonable to the folks here? Do people normally do this? Or do people just handle the table creation/deletion in SQL? If so, would there be any issues syncing the table with SQLAlchemy since the table was not created through Alembic? 

[Mike Bayer]

that's the correct approach to this, but you need to be careful of
things like foreign key constraints and the like if those are
involved. as far as how you send the "CREATE TABLE" / "DROP TABLE "
commands to the database, it doesn't matter if you use an Alembic
migration or not, the only reason having a migration present there is
if you need to run those same migrations on other databases that move
through the same state transitions. Since this is more of a
table-shrinking operation it's just as well to do this with a
standalone script that's not related to your fixed Alembic migrations.
If you aren't changing the ultimate schema it should not require any
change to your SQLAlchemy models.







>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy-alembic" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlalchemy-alem...@googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.