Postgres query plan8/25/2023 ![]() We can reasonablyĮxpect the number of payout_id values to grow linearly with the size of our Marks that transition as having been included in a payout.Īpproximately 20% of our payment transitions will be marked with a payout_id,Īnd there are approximately 20 payment transitions per payout. We'll beįiltering on a foreign key of payment_transitions called payout_id which States is represented as a row in the payment_transitions table. System every payment has states it can transition through 1, and each of those Our query references two tables, payments and payment_transitions. Understand the shape of the data you're querying. Whatever the outcome, it's key to every step that you On the other hand, perhaps a fast plan doesn'tĮxist for this query. Hypothesise an ideal plan that would be fast. Understand the query, identify why the plan you received is bad, then id ASC LIMIT 1 How many payments, how many transitions?ĭebugging a query plan almost always follows the same pattern: take time to payout_id = 'PO00123456789Z' ORDER BY payment_transitions. Poor query plan, it was time to find an exemplar query and dig in: Long-running that normally execute in under 50ms. Looking further, we saw a concerning number of queries that were very ![]() ![]() Examining the problematic database queries, the common factor was thatĮach query touched our payment transitions table (almost half a billion rows) One day we started seeing unusual query performance from several sources: bothĪsync workers and API requests seemed to take longer to respond than was We conclude with the actions we took to prevent this happening again. Understanding of the Postgres query planner. This post shares a specific issue we encountered that helped us level-up our Nothing proves that more than the amount we've learned from Postgres issues. We at GoCardless believe that failure can be a great learning opportunity, and Now run no doubt they will continue to provide value as our organisation grows. Resources have been invaluable while scaling our primary database to the ~2TB we One of the reasons we love it so much is how easily you can dig into Postgres' Only does it power our API, Postgres is used extensively in a number of internal At GoCardless Postgres is our database of choice.
0 Comments
Leave a Reply.AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |