![]() ![]() ![]() With this discovery, the next step was to figure out why the performance of these queries differed by so much. A query that fetched all rows inserted over a month ago would return in ~1 second, while the same query run on rows from the current month was taking 20+ seconds. An interesting correlation between a date filter and performance surfaced. ![]() We looked for patterns to relate the types of queries to their latency. We began to diagnose the cause of the slow queries by running them on the database with different parameters and monitoring execution times. Performance is not our primary concern here.Īs our tables continued to grow, however, queries began to take 20 seconds or more. We’re accustomed to these types of queries taking several seconds, and for the most part we’re fine with the speed, given that the site is not customer facing. Aggregating some of this data, like monthly revenue for example, requires complex database calls to be made on tables containing millions of rows. Below is an example which will VACUUM and ANALYZE after 5,000 inserts, updates, or deletes.ĪLTER TABLE table_name SET (autovacuum_vacuum_scale_factor = 0.0) ALTER TABLE table_name SET (autovacuum_vacuum_threshold = 5000) ALTER TABLE table_name SET (autovacuum_analyze_scale_factor = 0.0) ALTER TABLE table_name SET (autovacuum_analyze_threshold = 5000) The SymptomsĪt Lob, we’ve built an internal website to track business metrics, facilitate our customer support team, and track the order status of our Postcard API and our Letter API. Make sure your largest database tables are vacuumed and analyzed frequently by setting stricter table-level auto-vacuum settings. In this post I’ll go into detail about our specific problem, the tools we used to diagnose it, and how it was ultimately solved.Īutomate your business with our suite of direct mail APIs. Our database’s continual and rapid growth revealed performance degradation of Postgres that required us to implement aggressive auto-vacuum and auto-analyze settings. However, as table sizes grow into the millions of rows, more customized settings are required to ensure optimal performance. For simple services with a relatively low number of rows in the database, PostgreSQL’s default settings work nicely right out of the box. Numerous web services, including Lob, rely on PostgreSQL for fast and trusted relational data storage. ![]()
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |