Sign in to confirm you’re not a bot
This helps protect our community. Learn more

Making Postgres inserts faster on Azure | POSETTE 2024

Microsoft Developer
588K subscribers
<__slot-el>
<__slot-el>
432 views 10 months ago #Azure #PostgreSQL #PosetteConf
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:  …...more
...more
Making Postgres inserts faster on Azure | POSETTE 2024
21Likes
432Views
Jun 132024
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-2024 📌 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/1uj08... #PosetteConf #PostgreSQL #Azure Presenters: Gayathri Paderla, Software Engineer at Microsoft Connect: Gayathri Paderla:   / gayathri-paderla-76a73022  

Follow along using the transcript.

Microsoft Developer

588K subscribers