Webinar recording: Optimizing Postgres I/O Performance and Costs

Показать описание

Find the presentation slide deck and all other shared material below!
On September 22nd, 2022, we hosted our webinar on how to optimize Postgres I/O Performance and Costs. We took a deep dive into I/O optimization in Postgres, and how tuning I/O helps reduce overall database costs. We reviewed the different sources of read and write I/O in Postgres, including queries, indexes, internal cache usage and background processes. Lastly, we talked about relevant configuration settings and how to tune them and were happy to answer many questions in our Q&A session and the post-webinar follow-up emails. Come join us live next time!

Articles and material we talked about in the webinar:

- - - - - - - - - - - - - -

Following, we are sharing some of the responses to the questions we could not fit into our live Q&A session with all of you as they might prove to be useful to all of you, not just the original questioner:

- - -
"Would a warehouse builder database be a good use case for having synchronous_commit=off, since we can just restart builder processes after a crash?" - by Steve M.

Yes, data warehousing (when you are able to recreate the data) can be an excellent fit for synchronous_commit = off. Do make sure that you have a way to determine how far the system has written (e.g. by keeping a timestamp in a special table, or by being able to map the Postgres LSN to your source data), since Postgres won't be able to tell you which of your transactions were not yet written out before the crash.
- - -

- - -
"In which situations is Postgres HOT UPDATES useful?" - by Akshay B.

- - -

- - -
"Do you know if work_mem is allocated the full amount per session, or used as per query as to a limit. So if set to a larger number it may or may not use the amount per session." by Alan G.

The work_mem setting determines up to which size the Postgres planner picks an in-memory sort or hash, vs creating a temporary file on disk. Its essentially a limit (not a fixed allocation), but note that some situations can cause queries to use a multiple of work_mem (e.g. parallel query, hash operations, etc).
- - -

- - -
"post running a vacuum process, do we need rebuild indexes separately?" - Srinivasan B.

Potentially - you can think of a regular "VACUUM" (without FULL) as the ongoing maintenance that avoids both table and index bloat. Once you have gotten into a situation with bloat, you will need VACUUM FULL / pg_repack for table bloat, and REINDEX (CONCURRENTLY) for index bloat.
- - -