|
Hi there,
Welcome to the April 2022 issue of the pganalyze newsletter! (or is it May yet?)
At pganalyze we've been investing in performance and stability over the last couple of weeks, including completing the rollout of our new Rust-based statistics processing, and improvements to our cloud infrastructure. Of course we've also recorded 5 new episodes of our weekly video series "5 minutes of Postgres" (subscribe to our YouTube channel here)
In addition, we've had excellent feedback during the first phase of the early access for the new Index Advisor (a big thanks to all early access customers!), and are about to start our next phase of the rollout.
If you're using the Enterprise Server version of pganalyze, we have a new Enterprise release out that includes a good amount of bug fixes (including two security fixes, see more at the end of this email). Be sure to upgrade!
But now, lets start with a lesson learned from our own database:
---
A brief message from one of our engineers, Sean, about migrating from UUID v4 to ULID values:
Heads up: if you have indexes over UUID columns you could be suffering from a high write overhead as the table grows. We saw a 6x write IO improvement after switching to timestamp-prefixed ULIDs for a high traffic partitioned table. Our own pganalyze IOPS graph tells the story - the new code went live on Friday 4/22 around noon, and you can see the major difference on the right side of the graph:
If you want to learn more about UUIDs you can watch E10 of 5mins of Postgres where we talked about UUID vs BIGINT primary keys, or read more about ULIDs in our friend Brandur's notes on "UUIDs with sequential tendencies".
- - -
5mins of Postgres - episodes from April: E12: The basics of tuning VACUUM and autovacuum→ We look at autovacuum_vacuum_cost_delay, autovacuum_max_workers, autovacuum_vacuum_scale_factor, autovacuum_vacuum_threshold, autovacuum_freeze_max_age and more (share this episode here).
E13: New SQL/JSON and JSON_TABLE features in Postgres 15→ In this episode, we walk through the SQL/JSON constructor syntax, the IS JSON predicate, how to work with jsonpath expressions, and how to use JSON_TABLE in Postgres 15 (share this episode here).
E14: HOT Updates vs Bottom-Up Index Deletion in Postgres 14→ We look at Bottom-Up Index Deletion for B-Tree indexes, Postgres pages, Postgres page splits in B-Tree indexes, the bt_page_items function and how Bottom-Up Index Deletions compare to HOT updates (share this episode here).
E15: Tuning Postgres' Deterministic Query Planner, Extended Statistics and Join Collapse Limits→ In episode 15, we talk about optimizing Postgres' deterministic query planner, have a look at using extended statistics, and show how to tune Join collapse times (share this episode here).
E16: Incremental Materialized Views with pg_ivm and Looking Ahead to Postgres 15 Beta 1→ We talk about incremental materialized views - as the table changes they are refreshed automatically, and take a look at the plans for Postgres 15 Beta 1. (share this episode here).
Subscribe to the pganalyze YouTube channel to stay up to date about our weekly videos→
- - -
Guest blog post by Haki Benita:
This month, we are featuring an article by Haki Benita about how he freed up 20GB of unused index space. Haki kindly gave us permission to republish his post on the pganalyze blog so you can benefit from it. Check it out here:
The Unexpected Find That Freed 20GB of Unused Index Space→
- - -
Product changes in April:
- - -
As always, I'm happy to hear from you and am personally reading all of your responses. Looking forward to your thoughts!
Have a nice day! Lukas & the pganalyze team
PS: If you're interested in giving pganalyze a try you can learn more about it here. Of course, I'd be happy to walk you through a demo on a video call. You can book a 30 minute meeting with me here. Or, if you have general requests, feel free to get in touch with us here.
|
