Making Postgres inserts faster on Azure

Post Content

Video of a conference talk about making Postgres inserts faster on Azure presented by Gayathri Paderla at POSETTE: An Event for Postgres 2024. 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:

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

—
Gayathri has 15 years of experience with Microsoft, Oracle, Marchex and Infosys. Her motto towards using technology, “Tweak the technology to help, not the User/Customer requirements”. She is a very detail-oriented person, loves to interact with different teams to know each other and see how teams can help each other. Gayathri loves cooking, hiking and camping. Her family is very health oriented and go out on walk/jog early mornings, practice healthy eating. She is also part of Team Asha, a running based Non-profit organization sheltering/educating Orphaned and Abandoned kids in India.

Chapters:
00:00 – Intro
02:02 – Key Scenarios
02:28 – Creating unclogged tables
05:15 – Using COPY command
06:48 – Batch inserts vs Single commits
08:27 – Removing/Disabling contraints, triggers, & indexes
10:26 – Partitioning the table
11:42 – HA & Read Replica
13:08 – Tuning server parameters

? Everything you need to know about POSETTE: An Event for Postgres 2024 can be found at: https://aka.ms/posette

✅ Learn more:
Watch more POSETTE talks: https://aka.ms/posette-playlist

? Let’s connect:
X – @PosetteConf https://x.com/PosetteConf
Mastodon – @posetteconf https://mastodon.social/@posetteconf
Threads – @posetteconf https://www.threads.net/@posetteconf

? Subscribe to the POSETTE News: https://aka.ms/posette-subscribe

? Slides for this talk: https://drive.google.com/file/d/1uj08gRvL9HzZCv936j8L0l478V2H7bdL/view

#PosetteConf #PostgreSQL #Azure

Presenters:
Gayathri Paderla, Software Engineer at Microsoft

Connect:
Gayathri Paderla: https://www.linkedin.com/in/gayathri-paderla-76a73022/ Read More Microsoft Developer