As the year draws to a close I thought we should take a look back and highlight some of the most popular PostgreSQL links from 2019.

This list brings together what readers of Postgres Weekly, an email newsleter that reaches over 14,000 developers, clicked on over the past twelve months.

The Postgres highlights of 2019:

Don't Do These Things in Postgres

Yes, a page on the official Postgres wiki was one of the top links of the year. It offers an interesting round up a variety of 'common mistakes' in using Postgres, such as "Don't use char(n)" and "Don't use serial". Some of these are opinionated, but are well backed up with reasons.

---

PostgreSQL 12 Released

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, generated columns, and significant performance improvements (particularly with indexes and partitioned tables).

---

How To Improve The Performance of COUNT(*)

Using count(*) can cause performance problems. This well-received article from April explores a variety of options to make counting rows faster using approximations and other tricks.

---

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.

---

A Deep Dive into GIN Indexes

This post from May made up part of a series digging deep into Postgres' index types. This in-depth post specifically looked at Generalized Inverted Indexes which are suited for indexing composite values where elements within those composite values need to be looked up frequently (e.g. full text search).

---

A Few Postgres Best Practices

Includes tips such as using BIGINT or UUID for primary keys, keeping your credentials rotated, and using connection pooling.

---

An Overview of Postgres' JSON Capabilities

The JSON capabilities of PostgreSQL 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).

---

How Postgres Used fsync Incorrectly for 20 Years

An interesting look into an issue not just faced by Postgres but by many systems that thought fsync (a system call that flushes file buffers into a final committed/persisted state) worked in a way that it actually doesn't. With "disastrous consequences for data durability/consistency" this proved to be an interesting area to learn about if you like digging deep.

---

Hacker News Discusses.. Postgres or MySQL?

This popular discussion on Hacker News from October asked readers to choose between the two technologies. Perhaps unsurprisingly, Postgres seems to be strongly preferred.

---

The Guardian: 'Bye Bye Mongo, Hello Postgres'

In April, popular British newspaper The Guardian switched off their MongoDB cluster and completed a migration to PostgreSQL on Amazon RDS. Here's the full story.

---

---