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:
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.
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).
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.
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.
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).
Includes tips such as using
UUID for primary keys, keeping your credentials rotated, and using connection pooling.
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).
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.
This popular discussion on Hacker News from October asked readers to choose between the two technologies. Perhaps unsurprisingly, Postgres seems to be strongly preferred.
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.
Want more Postgres?
Join over 10,000 developers and sign up for Postgres Weekly. A free weekly newsletter about the PostgreSQL relational database management system.