--- title: "Tips for keeping your vacuum running well." publication: "Postgres Weekly" issue: 466 published_at: "2022-08-03" canonical_url: "https://postgresweekly.com/issues/466" --- Plus extending Postgres with C, and how Renault moved from Oracle to Postgres. | #​466 — August 3, 2022 [Web Version](https://postgresweekly.com/issues/466) Postgres Weekly [![\](https://res.cloudinary.com/cpress/image/upload/w_1280,e_sharpen:60/vbgd6szk5vaodilgnpxn.jpg)](https://www.citusdata.com/blog/2022/07/28/debugging-postgres-autovacuum-problems-13-tips/) [Debugging Postgres Autovacuum Problems: 13 Tips](https://www.citusdata.com/blog/2022/07/28/debugging-postgres-autovacuum-problems-13-tips/) — Samay asks us to imagine seeing our database is larger than expected with high I/O load and sluggish performance. We spot a lot of bloat in our tables and run `VACUUM`. But then we might ask _“why didn’t Postgres autovacuum clean up the bloat in the first place…?”_ — It’s a good question and this post covers a variety of stumbling blocks and things to do if your trusty vacuum isn’t cleaning things up as well as it used to. Samay Sharma (Citus Data) 📊 [The Full _State of Postgres 2022_ Results](https://www.timescale.com/state-of-postgres/2022) — They’ve been teasing us with bits and pieces in recent weeks, but now we can see the full results (or download a dump of the results to analyze yourself). There’s not a _lot_ that’s surprising, though. PostGIS came in as the most popular extension. Python as the most popular non-SQL language. Timescale [![\](https://copm.s3.amazonaws.com/edc6e6c1.png)](https://www.polyscale.ai/platform/) [Postgres Access From the Edge](https://www.polyscale.ai/platform/) — Scale your PostgreSQL database globally in minutes for low latency edge access. PolyScale automatically caches data and executes queries closer to your users for low latency, serverless functions at the edge. PolyScale.ai sponsor [Improved Performance for ORDER BY / DISTINCT Aggregates](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=1349d2790bf48a4de072931c722f39337e72055e#improvingorderbyagg) — Technical item from the Postgres repo incoming! _“Performance of ORDER BY / DISTINCT aggregates has just been terrible for too long in Postgres,”_ says David Rowley, but that will no longer be the case after implementation of an optimization that sounds simple in theory but was undoubtedly tricky to get right in practice. David Rowley **QUICK BITS:** - Google Cloud has a quick case study around [how French car manufacturer Renault migrated from Oracle to Postgres](https://cloud.google.com/blog/products/databases/renault-drives-fully-loaded-database-migration-to-google-cloud) (on Google Cloud, _naturellement_). - ▶️ pgAnalyze is continuing with its ['5 minutes of Postgres' series of videos](https://pganalyze.com/blog/5mins-postgres-waiting-for-postgres-16-subquery-alias-optional) digging into Postgres updates, changes, and tips. Worth a look. - 📊 278 folks took EDB's pgAdmin user survey recently and [they've shared the results here](https://www.enterprisedb.com/blog/pgadmin-user-survey-2022). [Extending Postgres with C: A Simple Example](http://eradman.com/posts/pg-extensions.html) — I’ve seen a few public discussions recently where someone has commented just how _pleasant_ it is building extensions for Postgres. There are now numerous options for doing so (such as using Rust with [pgx](https://github.com/tcdi/pgx)) but using good old fashioned C works just fine and isn’t as difficult as you might expect. ([This tutorial](http://big-elephants.com/2015-10/writing-postgres-extensions-part-i/) is also helpful for establishing the necessary pieces.) Eric Radman [Manage Long-Running Read Queries on Amazon Aurora](https://aws.amazon.com/blogs/database/manage-long-running-read-queries-on-amazon-aurora-postgresql-compatible-edition/) — Goes over _“the potential conflicts”_ and shares _"best practices for managing long-running read queries on Aurora PostgreSQL.”_ Wanda He and Avi Jain (AWS) [Monitoring Distributed Postgres Activity with `citus_stat_activity` and `citus_lock_waits`](https://www.citusdata.com/blog/2022/07/21/citus-stat-activity-views-for-distributed-postgres/) — One for Citus users only: _”.. some new monitoring tools introduced in Citus 11 that’ll help you track and take control of your distributed queries”_ Halil Ozan Akgul (Citus Data) 🛠 Code and Tools [pg-boss 8.0: Postgres + Node.js Job Queueing System](https://github.com/timgit/pg-boss) — A job queue for background processing and reliable asynchronous execution. It uses Postgres specific features (like `SKIP LOCKED`) to guarantee safety. v8 makes Node 14 the minimum supported version and enables reuse of existing DB connections on certain operations. Tim Jones [Simplify Database Management. Free Your Team. Control Your Data](https://resources.enterprisedb.com/elephant-in-the-room/?utm_source=postgresweekly&utm_medium=newsletter&utm_campaign=BigAnimalpgweeklynewsletter) EDB BigAnimal sponsor [Dexter: The Automatic Indexer for Postgres](https://github.com/ankane/dexter) — Collects your queries and generates indexes to suit them. Here’s [an article introducing the idea.](https://ankane.org/introducing-dexter) Andrew Kane **RELEASES:** [pg_ivm 1.2](https://github.com/sraoss/pg_ivm/releases/tag/v1.2) – Incremental View Maintenance. Adds PG15 support. [Slonik 29.0](https://github.com/gajus/slonik) – Node.js Postgres client with strict types. [MikroORM 5.3](https://github.com/mikro-orm/mikro-orm) – TypeScript ORM for Node.js based on Data Mapper. [pg_graphql 0.4](https://github.com/supabase/pg_graphql) – GraphQL support for Postgres. [Replibyte 0.9.6](https://github.com/Qovery/Replibyte) – Seed your dev database with real data. [PGSync 2.3.2](https://github.com/toluaina/pgsync) – Postgres to Elasticsearch syncing. [postgres-meta 0.42](https://github.com/supabase/postgres-meta) – RESTful API for managing a Postgres database. [pspg 5.5.6](https://github.com/okbob/pspg) – Unix pager designed for tabular data. [Bun 1.1.7](https://github.com/uptrace/bun) – SQL-first Go(lang) ORM. 🐦 Seen on Twitter [![Craig Kerstiens wisdom on Postgres functions\](https://res.cloudinary.com/cpress/image/upload/v1659524064/rzl4tw39ebcrdsp6sdb8.png)](https://twitter.com/craigkerstiens/status/1554509883067707393) **Got a new email?** You can [change your address here](https://postgresweekly.com/edit_subscription/*|UID|*). --- **Bored of us?** [Click here to stop getting Postgres Weekly](https://postgresweekly.com/leave/*|UID|*). --- **Got a link for us?** Reply and tell us. We can't include everything but we'll look at anything you send. Thanks! --- Published by Cooper Press Ltd. Fairfield Enterprise Centre, Louth, LN11 0LS, United Kingdom Postgres, PostgreSQL and the Slonik Logo are trademarks or registered trademarks of the PostgreSQL Community Association of Canada. [Stop getting this newsletter.](https://postgresweekly.com/leave/*|UID|*)