Django Optimization Processes for Write Operation for Postgresql

When optimizing a Django project for large write operations, especially when dealing with PostgreSQL, there are several strategies you can employ to reduce the time it takes to perform these operations:

1. Bulk Inserts

In django, we create objects using create() . Asynchronous version is acreate() .It's a  convenience method for creating an object and saving it all in one step. 

and 

These are same and equivalent. The create() method is used to create and save a single object in the database.

Example:

Instead of inserting one row at a time, consider using Django's bulk_create() method to insert multiple rows in a single query. This reduces the overhead of multiple database round trips.

Example: 

The bulk_create() method is used to create and save multiple objects in the database in a single query.

It accepts a list of model instances and inserts them into the database in a single batch operation, which significantly reduces the overhead compared to individual create() calls.

Cons:

1. It does not work with many-to-many relationships.

2. The model’s save() method will not be called, and the pre_save and post_save signals will not be sent.

3. If the model’s primary key is an AutoField and ignore_conflicts is False, the primary key attribute can only be retrieved on certain databases (currently PostgreSQL, MariaDB 10.5+, and SQLite 3.35+). On other databases, it will not be set.

2. Database optimization

Tune your PostgreSQL database configuration settings such as shared_buffers, work_mem, and checkpoint_segments to better suit your application's needs. This can help PostgreSQL handle large write operations more efficiently.

3. Transactions

Django gives you a few ways to control how database transactions are managed.Django’s default behavior is to run in autocommit mode. Each query is immediately committed to the database, unless a transaction is active.

# How to use transactions in django ?

Imagine you're building a web app for a bookstore where users can purchase books. Each purchase involves updating multiple tables in your database: deducting the purchased book's quantity from the inventory and recording the transaction details in the sales table.

Here's how you can use Django transactions to optimize this process:

Let's break down how this code works:

1. We import Django's transaction module and our models (Book and Sale).

2. Inside the purchase_book function, we use transaction.atomic() as a context manager to ensure that all database operations within it are treated as a single transaction. This means either all the operations succeed, or none of them do.

3. We use select_for_update() to lock the row in the Book table that corresponds to the book being purchased. This prevents other transactions from modifying the same row simultaneously, ensuring data consistency.

4. We check if the requested quantity is available in stock. If not, we raise a ValueError and the transaction will be rolled back.

5. If there's enough stock, we deduct the purchased quantity from the book's inventory and save the changes to the database.

6. We record the transaction details (user, book, quantity) in the Sale table.

7. If any exception occurs during the transaction (e.g., database error, insufficient stock), the transaction is rolled back, and any changes made within it are undone.

By using transactions, we ensure that the purchase operation is atomic, consistent, isolated, and durable. This not only optimizes the write operation by bundling multiple database changes into a single transaction but also ensures data integrity and reliability, even in the face of errors or concurrent access.

Mahfujul Hasan
Software Engineer

linkedIn: https://www.linkedin.com/in/shojibhasan/