Nowadays (it’s 2023 if someone has just woken up from hibernation), many applications work with large amounts of data, often exceeding 1 million records in the database. Such operations are more problematic than those on smaller datasets, mainly due to limited RAM and CPU usage.
The problem to solve
An Ill-considered process of importing such a large dataset might cause loading all objects into memory, which in turn can cause the migration process to crash.
Suppose we have a simple Person model as below:
class Person(models.Model): name = models.CharField(max_length=255) email = models.EmailField()
and the goal is to load data from a CSV file.
The simple and common solution (which works for small and medium datasets) is to read data from a file and save them one by one to the database.
import csv from django.db import migrations def read_csv_file(file_path: str): with open(file_path, "r") as people_file: return list(csv.reader(people_file)) def import_people_records(apps, schema_editor): Person = apps.get_model("people", "Person") for name, email in read_csv_file("people.csv"): Person.objects.get_or_create( name=name, email=email, ) def delete_people_records(apps, schema_editor): apps.get_model("people", "Person").objects.all().delete() class Migration(migrations.Migration): dependencies = [ ("people", "0001_initial"), ] operations = [ migrations.RunPython( code=import_people_records, reverse_code=delete_people_records, ), ]
This approach is simple, but it has two serious disadvantages:
- all data is loaded into memory, which can lead to a lack of memory, which in turn cause the process to crash or slow down significantly,
- as many database queries are executed as there are records, which is suboptimal.
Solution 1 for large datasets
As described above, there are two main problems to solve.
Generators in Python are a special type of function that yield a sequence of results instead of a single value, allowing memory-efficient iteration over large datasets. They produce values one at a time on demand, instead of storing all results in memory at once.
The number of queries to the database can be reduced using bulk operations (i.e. on a group of data), leading to faster execution. In other words, a few records are inserted into the database table within one query. In the case of Django, bulk_create can be used for that purpose.
import csv from itertools import islice from django.db import migrations BATCH_SIZE = 1000 def read_csv_file(file_path: str, batch_size: int): with open(file_path, "r") as f: reader = csv.reader(f) while True: batch = list(islice(reader, batch_size)) if not batch: return yield batch def import_people_records(apps, schema_editor): Person = apps.get_model("people", "Person") for batch in read_csv_file("people.csv", BATCH_SIZE): Person.objects.bulk_create( [ Person( name=name, email=email, ) for name, email in batch ], batch_size=BATCH_SIZE, )
(Entire example can be found here.)
The above solution will do its job properly, but it’s not without flaws. More precisely, such a migration is run in a single database transaction, which is usually fine, but in severe cases can be dangerous, e.g. it can lead to import failure, performance issues or locking and concurrency issues or even exhaustion of database system resources.
Solution 2 for even larger datasets
To avoid issues related to too many records within a single database transaction described above, you can follow another approach.
The solution would be to split all those queries into a few database transactions.
To do that, atomic transactions within migration must be disabled (atomic transactions are associated with database operations where a set of actions must ALL complete).
def import_people_records(apps, schema_editor): Person = apps.get_model("people", "Person") transaction.set_autocommit(False) for batch in read_csv_file("people.csv", BATCH_SIZE): Person.objects.bulk_create( [ Person( name=name, email=email, ) for name, email in batch ], batch_size=BATCH_SIZE, ) transaction.commit() class Migration(migrations.Migration): atomic = False
(Entire example can be found here.)
The above solution works well for very large datasets, however, it has one drawback. Due to the fact that groups of operations are performed in separate transactions, in case of problems during such a migration (e.g. loss of connection to the database), only part of the records may be saved to the database.
Depending on the imported data, this might be safeguarded in Django through the ignore_conflicts parameter that can be set for the
bulk_create method. This causes that when a conflict occurs on the primary key, the record is skipped, otherwise it is saved, and thus the migration can be rerun.
With other migration tools, please consult the documentation. Looking for a way to execute
INSERT IGNORE and
INSERT ... ON DUPLICATE KEY UPDATE would resemble the Django’s ignore_conflicts behavior.
Large datasets are now becoming more and more common, so it’s important for developers to learn how to work with them effectively.
It’s important to review the migration to be run and adjust it to the size of the dataset.
The above tips will allow you to avoid issues related to memory shortage and reduce the risk of failure.