As described here, the initial try of creating a slice of production data was made by using the Django ORM, and it did not work, because even deleting in batches the job was still running after more than 2 hours and the pipeline timed out before finishing it. After taking a closer look at it, deleting is the expensive direction in both Django, where the cascade is resolved in Python by the Collector (as described in the first post), and Postgres (everything goes through the WAL, and the deleted rows are still there until a VACUUM), and also, its cost grows with the rows that we throw away, which are the majority of the table. So, in order to try to extract the maximum performance, we decided to go to the complete opposite strategy, from deleting a percentage of the data to copying what we need.

Copy, truncate, reinsert

The strategy that we have used consists of these 4 steps plus a zero step, are they these ones:

  1. SET CONSTRAINTS ALL IMMEDIATE, so the foreign keys are checked at each statement instead of at the COMMIT.
  2. Copy the rows that we keep to auxiliary tables, walking from the leaf to the root.
  3. Run ANALYZE on each of the created auxiliary tables.
  4. Truncate the original tables in a single statement.
  5. Insert the kept rows back, now from the root to the leaf.

But before digging into this, it is important to mention that since the first post we decided to focus on performance and to use the ORM as little as possible, so the focus of the strategy here is to use more raw SQL and to use the Django models as the source of metadata. That said, the strategy consists of the following: instead of iterating over the rows that we want and deleting each one (which would trigger the cascade deletes), we go in the opposite direction of the tree, starting from the leaf to the root, and copying the data to an auxiliary table, basically following this example:

-- blog_comment is the leaf CREATE TEMP TABLE _keep__blog_comment AS SELECT * FROM blog_comment WHERE id % 10 = 0; -- an example rule of the slice, in this case 10% of the rows

Here we are using the same fictional Blog → Post → Comment cascade of the first post. But writing these table and column names by hand for every table of the tree is exactly the kind of thing that is prone to errors, and to avoid it, we use the Django models, not to run the query, but only to give us the metadata that it needs:

from django.db import connection def copy_to_keep_table(model, where): table = model._meta.db_table # "blog_comment"  with connection.cursor() as cursor: cursor.execute(f"DROP TABLE IF EXISTS _keep__{table}") cursor.execute(f"CREATE TEMP TABLE _keep__{table} AS SELECT * FROM {table} WHERE {where}") cursor.execute(f"ANALYZE _keep__{table}") def copy_parent_to_keep_table(child, field_name): field = child._meta.get_field(field_name) # Comment.post  parent = field.related_model # the Post model  # field.column is the foreign key column ("post_id"), so we never write it by hand  copy_to_keep_table( parent, f"{parent._meta.pk.column} IN " f"(SELECT DISTINCT {field.column} FROM _keep__{child._meta.db_table})", ) copy_to_keep_table(Comment, "id % 10 = 0") # the leaf, where the volume lives copy_parent_to_keep_table(Comment, "post") # _keep__blog_post copy_parent_to_keep_table(Post, "blog") # _keep__blog_blog

So the table names, the primary keys and the foreign key columns all come from Model._meta, and going up in the tree blog_post keeps only the posts that the copied comments point to, and blog_blog keeps only the blogs of these posts, in a way that no foreign key stays dangling after the reinsert.

Also, you could be questioning: why not simply use CREATE TABLE ... AS SELECT (CTAS) and finish the job there, by only renaming the auxiliary table into the place of the original one? The copy above is already a CTAS and, even though this command is very performant, the table that it creates has only the column names and the types of the original one, everything else is lost, like the primary keys, the foreign keys, the unique and check constraints, the NOT NULL, the column defaults, the indexes and the sequences that generate the ids. So if we replace the original table with this one, it will seem right, but it will break on the first real write on this new table. So we keep the original tables and replace only their content, exactly what the truncate and the reinsert of the next section are doing.

The details of the mechanism

Running the SQL above is not the only thing necessary to accomplish this task, we also have to take care of the pre and post actions around this query. So, before creating the auxiliary tables, we should call SET CONSTRAINTS ALL IMMEDIATE, which makes the constraints be checked at each statement instead of being deferred to the COMMIT. This is needed because Django creates every foreign key in Postgres as DEFERRABLE INITIALLY DEFERRED, so by default a broken foreign key only fails at the COMMIT, far from the statement that caused it. With this, we catch the exact statement that causes a failure, making it easier to know the culprit.

After copying the data to the auxiliary tables, it is important to ANALYZE (ANALYZE _keep__blog_comment;) the recently created table, mainly to update the statistics of the database (number of rows, number of pages of the table), since these are what the query planner uses to choose the best path to get the data, avoiding the wrong use of nested loops or inefficient joins.

And then a single TRUNCATE TABLE blog_blog, blog_post, blog_comment, in one statement and without CASCADE, and this is on purpose. Postgres refuses to truncate a table that something outside of the statement references by a foreign key, and this refusal works as a free safety net for us, because if someone adds a new model pointing to these tables and we forget to include it, the run fails in seconds instead of silently emptying a table that we wanted to keep.

Finally, but not less important, the reinsert step:

-- here the order is the inverse, root to leaf, so that every foreign key finds its parent already inserted INSERT INTO blog_blog SELECT * FROM _keep__blog_blog; INSERT INTO blog_post SELECT * FROM _keep__blog_post; INSERT INTO blog_comment SELECT * FROM _keep__blog_comment;

which will copy all the previously selected data from the keep tables back to their original counterparts.

It is also worth saying that none of this runs against production, the pipeline restores a production snapshot into a temporary database and does the whole transform there, and since all the steps are inside the same transaction, we make sure that we are not leaving behind a half truncated database.

Conclusion

After a couple of struggles with the original idea, this approach produced much better results, but not without a cost: it demands much more caution from the developer and better tests to make sure that every scenario is covered, since we leave behind the benefits of the ORM. It is also necessary to follow all the relations completely, to guarantee that none of the important tables are forgotten and truncated by mistake. The performance is superior: in the scenario at the end of the first post, the pipeline was killed at the 2 hour cap while deleting in batches, and it did not even reach the final part of the pipeline, while the new one finishes the whole slice in about 1 hour and 45 minutes. So, for this kind of task, this will probably be the best choice.