Gayathri Paderla

POSETTE 2024 Talk

Making Postgres inserts faster on Azure

local PDT (Livestream 1)

In this talk you will learn how to increase the speed of your insert queries in Azure Database for PostgreSQL - Flexible Server databases.

You may have experienced frustration while waiting for your inserts to complete during migrations, daily import jobs, or within applications. There are various methods to achieve faster insert queries. Fortunately, you can alleviate this situation by implementing the following tricks:

1. Creation of unlogged tables (which does not create WAL files and inserts could be fast, however if any crashes we might lose data.)
2. Use a copy command for huge inserts.
3. Make your inserts in batches instead of single insert commits.
4. Tuning server parameters (like autovacuum, wal_buffers, max_wal_size, shared_buffers, Checkpoint_timeout).
5. Do we have any constraints on the tables? If so, we need to disable them during the inserts and enable them after.
6. Disable triggers if they exist before inserts if that is possible.
7. Remove unused indexes from the table.
8. One can also consider partitioning of tables if the table that is being inserted in is exceptionally large.
9. If the activity is one time you may consider disabling HA (high availability) drop read replica and create it back after inserts.