Optimization

𝗧𝗼𝗽 𝟮𝟬 𝗦𝗤𝗟 𝗾𝘂𝗲𝗿𝘆 𝗼𝗽𝘁𝗶𝗺𝗶𝘇𝗮𝘁𝗶𝗼𝗻 𝘁𝗲𝗰𝗵𝗻𝗶𝗾𝘂𝗲𝘀

This is how you optimize writes to an SQL database

Very specific, but probably not widely-known.

For one of my projects, I’ve spent a lot of time researching how to insert tons of data into Postgres as fast as possible, entirely from the application itself.

• Batching a lot of multi-row value insert statements is the fastest approach.
  • Multiple INSERTs inside single large database transactions are much faster, as they delay/avoid fsync syscalls used to commit data to the disk.
  • Multi-row value insert statements help a bit too, as they amortize each INSERT overhead.
• Indexes slow down INSERTs. Avoid creating them until you have to (after you pumped all the data).
• Cache whatever you can and avoid reading back anything you’ve already had.

As far as I know, the general approach here should apply to other databases.

https://use-the-index-luke.com/

🌱 Back to Garden