title: Delete large number of rows in Postgres/SQLAlchemy/Alembic
body: <p>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.</p>
  
  <p>My first approach was to delete rows via SQLAlchemy; however, according to the RDS plots, the freed disk space was never reclaimed and I believe this is because I have yet to run a vacuum full command on the table, which seems like an expensive process.</p>
  
  <p>It seems like there are many suggestions that mention that the right way to handle such mass deletions is to re-create the table, copy the necessary rows over and then TRUNCATE the old table. Conceptually, this makes sense but I'm not sure what's the right way to do this even after looking at the following:</p>
  
  <ol>
  <li><a href="https://dba.stackexchange.com/questions/55661/how-to-duplicate-huge-postgres-table">This</a> seems to work only for simple tables without indexes and points to #2 for more complicated tables</li>
  <li><a href="https://wiki.postgresql.org/wiki/Clone_schema" rel="nofollow noreferrer">This</a> looks like it could work after I simplify it to just one table</li>
  <li>Use an alembic migration to create a new table; copy over the entries from the old table using some quick python script; truncate the old table and then rename the new table.</li>
  </ol>
  
  <p>Does anyone have any advice for the stack I'm currently using? More concretely, I have the following concerns:</p>
  
  <ol>
  <li>For #1 and #2, does running SQL commands to recreate tables mess up Alembic in any way? I am worried that there is some metadata in Alembic that will not be preserved after running the SQL commands.</li>
  <li>I feel like #3 seems to be the most reasonable approach here. Are there any problems with this approach?</li>
  </ol>