#336 — December 18, 2019

Read on the Web

Postgres Weekly

Welcome to the last issue of Postgres Weekly in 2019 as we'll be taking a break for Christmas :-) We'll be back on January 8 but in this issue we're rounding up the most popular links of the year.

Thanks for your continued support, we appreciate it. Remember that if you have any events, articles, news, or otherwise that we might be interested in, you can always hit reply and let us know.
— Peter Cooper

🏆 The Top 6 Postgres Links of 2019

1. Don't Do This (in Postgres) — It was a page on the Postgres wiki that took the #1 spot in 2019! It attempts to round up a variety of ‘common mistakes’ in using Postgres, such as “Don’t use char(n)” and “Don’t use serial“. Some of them are a bit opinionated, but are backed up with reasons.

Postgres Wiki

2. How To Improve The Performance of COUNT(*) — Using count(*) can cause performance problems. This article explores a variety of options to make counting rows faster using approximations and other tricks.

Cybertec

Introducing Automated Postgres EXPLAIN Plan Insights on pganalyze — Other tools might show what happened in the database, pganalyze equips engineering teams with the insights to understand why it happened. Check out our new EXPLAIN insights features to get simple visualizations of costliest plan nodes, slow sorts, and more.

Pganalyze sponsor

3. PostgreSQL Tools for the Visually Inclined — In response to a SQL Server DBA criticizing Postgres’s tooling, Rob Conery responded with, essentially, a look at how the Postgres (and Unix) culture around tooling is different and how that’s ultimately a good thing.

Rob Conery

4. A Few Postgres Best Practices — Use BIGINT or UUID for primary keys, keep your credentials rotated, and use connection pooling.

Kenneth Reitz (DigitalOcean)

5. An Overview of Postgres's JSON Capabilities — Postgres's JSON capabilities have continued to improve over the years, and while this article was very popular back in January, things have continued to improve over the course of 2019 with the introduction of JSONPath support (PDF) in Postgres 12 which was released in October.

Severalnines

6. PostgreSQL 12 Released — It may be down at #6 and while the release of Postgres 12 was far from a surprise, it was certainly the biggest event in the Postgres world this year. Key enhancements include SQL/JSON support (more on that in this PDF), generated columns, and significant performance improvements (particularly with indexes and partitioned tables). Postgres Weekly issue 326 had a really good roundup.

PostgreSQL Global Development Group

supported by Digital ocean

💡 Tip of the Week

Indexing only what matters with partial indexes

Indexes can take up a lot more space than you think.. quite a few times I've added indexes to a table to speed things up and been shocked at the rapid growth in disk usage. While indexes are organized around columns, not every row needs to be included with an index, and 'partial indexes' provide a solution.

Let's say you have a large orders table in an e-commerce app's database and it includes every type of order formulated within the app, even those that were never completed. Your app has a search feature that filters against a column on the orders table but you almost never need all of the incomplete orders to be within the index for that column.

Using a WHERE clause when creating an index creates a 'partial index' which only includes rows that match the supplied predicate. Here's a simple example based upon the orders scenario above:

CREATE INDEX orders_completed_user_id
  ON orders (user_id)
  WHERE completed IS TRUE;

Admittedly, the data saved in a scenario like this will be minor (user_id is likely to be merely an integer) but with text columns or multi column indexes, the eventual savings could be substantial.

The PostgreSQL documentation has a great page about partial indexes, complete with further examples, if you want to learn more.

This Tip of the Week is sponsored by Digital Ocean. Curious about learning how to run Kubernetes in production? Sign up for our self-guided course for developers — subscribe today.

🗓 Upcoming Events

If you're in the majority of Postgres users who haven't attended a Postgres event yet, there are lots coming up in the new year:

  • PgDay SF (January 21 in San Francisco) — A one-day, single-track event.
  • PgDay FOSDEM (January 31 in Brussels, Belgium) — Not only is there this Postgres event surrounding the popular open source event, but there's a Postgres room at FOSDEM itself on February 2 too.
  • PgConf.Russia (Febuary 3-5 in Moscow, Russia) — One day of tutorials and two days of talks in three parallel sessions.
  • PgConf India (Febuary 26-28 in Bengaluru, India) — A dedicated training day and a multi-track two-day conference.
  • Nordic PgDay 2020 (March 24 in Helsinki, Finland) — Their CFP is only open till the end of the month so be quick.
  • pgDay Paris 2020 (March 26 in Paris, France) — Learn more about the world’s most advanced open source database among your peers.
  • Swiss PGDay 2020 (June 18-19 in Switzerland) — A two track conference (one in English, one in German) aimed at the entire Postgres community.

If you are running a Postgres related event that's beyond the scale of a small local meetup or user group (so, say, 50+ people with people likely to travel to your event from afar), let us know and we can include it here in future issues.