#350 — April 8, 2020

Read on the Web

Postgres Weekly

10 Things I Hate About PostgresA few weeks ago we featured a cheery ‘Postgres is the world’s best database’ item, but you’ve got to take the rough with the smooth! Rick reminds us that no software is perfect and that even Postgres has flaws 🤭

Rick Branson

Comparing MongoDB vs Postgres over JOINs — An analysis of structuring data in both MongoDB’s NoSQL world and Postgres’s relational world to model a classical employee-department data model. It’s a little apples and oranges but makes some reasonable points and when something has a Turing Award winner’s name on it, it’s a must read.

Michael Stonebraker and Álvaro Hernández

Your Data Is Your Business — PGX can help you tune, scale, and monitor your database to keep it performing under the most extreme and unexpected loads. With over 20 years of PostgreSQL experience, we are ready to help you.

PostgreSQL Experts, Inc. sponsor

Optimizing Postgres Performance Through Config Settings — These settings have been covered a lot over the years, but if you want an up to to date one stop shop, this post covers 5 settings worth tuning to optimize your Postgres server, namely: shared_buffers, wal_buffers, effective_cache_size, work_mem and maintenance_work_mem.

Tom Swartz

Coming to Postgres 13: WAL Monitoring — Another look at a new feature coming to Postgres. Backends will track information relating to WAL generation, pg_stat_statements will be able to track WAL usage, and a new WAL option will be available in the EXPLAIN command. If you want to play early, you’ll need to build from the development snapshots or repo.

Julien Rouhaud

pspg 3.0: A Unix Pager Designed for Postgres Tables — If you use psql you probably use less as your pager, but it doesn’t directly support tabular data. This does. The just-dropped version 3.0 supports communication over named pipes or redirected content (example here).

Pavel Stehule

Faster CI/CD for All Your Software Projects Using Buildkite — See how Shopify scaled from 300 to 1800 engineers while keeping their build times under 5 minutes.

Buildkite sponsor

Postgres 13 to Allow pg_stat_statements to Track Planning Statisticspg_stat_statements is a module for tracking SQL query execution statistics. Postgres 13 is adding more statistics to what is tracked, specifically around planning and execution times.

Hubert depesz Lubaczewski

Michael Paquier: The PostgreSQL 'Person of the Week' — A brief interview with one of Postgres’s most familiar names. His favorite extension is pg_stat_statements which has already made an appearance in this issue :-)

Andreas Scherbaum

supported by Retool

💡 Tip of the Week

Getting a quick HTML report of your databases

I get most of the tips I include in this newsletter from my own use cases or from things I stumble across in the documentation or looking at how open source tools do things. But sometimes I see a tip pop up on Twitter and think, "oh, that's neat!" This is one such example so you can thank Daniel Westermann for surfacing this neat psql one liner.

psql is a great tool for interactively working with Postgres, but it can also be used in one-liners at the command line to spit out results of queries, etc. It can be directed to give such output in HTML, so it's trivial to quickly generate an HTML report of the databases on your server, like so:

psql -c "\l+" -H -q postgres > out.html

Of course, you're not just limited to using this with \l+, you can use any command or query and spit out the results in HTML in this way.

This week's tip is sponsored by Retool. Build internal tools fast: With Retool you can focus on what matters, cutting the time it takes to build custom tools in half.

🗓 Upcoming Events

All of the in-person events we had listed are now cancelled or postponed until further notice due to the COVID outbreak. However, events are looking at ways to run online, so we'll soon be devoting this section to online events, livestreams, etc.

Please drop us a note if you have such an event we can promote here. Even if you're doing a single scheduled talk on Twitch or YouTube Live, say, we might be able to list it (and it's all free/unpaid).

🔐 – requires e-mail address or registration
💵 – costs money to participate