#341 — February 5, 2020

Read on the Web

Postgres Weekly

▶  The State of (Full) Text Search in Postgres 12 — A fantastic 50 minute talk from FOSDEM covering the ins and outs of Postgres 12’s full text search capabilities. The audio is a little clippy here and there but there’s a lot of great stuff packed into this talk and the (very) detailed slides are here.

Jimmy Angelakos

Row Change Auditing Options for Postgres — If you want to build up an ‘audit trail’ for all row changes made to a table over time, here’s a look at the pros and cons of ways to pull it off.

Kaarel Moppel

Build a Django App on CockroachDB — Django simplifies interactions with a database – it’s one of the reasons it’s become the most popular Python web framework. Use CockroachDB and Django for the ease of writing in Python while getting all the benefits of an open source, distributed SQL database.

Cockroach Labs sponsor

The Most-Neglected Postgres Feature? — I don’t know about ‘neglected’ but this is an interesting post about using log_line_prefix to build smarter log files.

Richard Yen

Three Job Scheduling Tools for Postgres — Last week we featured pg_timetable, a new, advanced job scheduler for Postgres jobs, but there are other solutions including good old cron, pgAgent and the pg_cron extension.

Severalnines

Postgres 12 Now Generally Available on Heroku — Big news if you’re using the Heroku platform (and Heroku’s is one of the biggest Postgres deployments in the world). Postgres 12 is now the default version used for new Heroku Postgres databases.

Heroku

Amazon Relational Database Service (RDS) Can Now Export Snapshots to S3 — You can now export Amazon Relational Database Service (Amazon RDS) or Amazon Aurora snapshots to Amazon S3 as Apache Parquet, an efficient open columnar storage format for analytics.

Amazon Web Services

NULL Values in SQL Queries — The concept of NULL values in SQL queries often causes trouble for developers and this post deftly explains their special status and how it can affect queries. Aimed at Oracle users but all applies to Postgres too.

Kaley Crum

Checking Catalogues for Corruption with pg_catcheck

Luca Ferrari

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

Buildkite sponsor

PL/pgSQL Package to Get Holiday Dates — For example: SELECT * FROM holidays.by_country('canada', 2020, 2020);

Christopher Thompson

PostGIS 3.1 Alpha 1 Released — One for the edgiest of PostGIS users only. Aimed at Postgres 12.1 but works with the under-development Postgres 13 branch too.

PostGIS Developers

supported by PostgresConf

💡 Tip of the Week

\pset in psql for formatting output

We've included a lot of psql tips in the newsletter over the past couple of years, but there's always more to share given how mature and feature packed psql is. Today, we're going to quickly look at \pset, a command designed for setting output formatting options.

A full list of options is included in the psql documentation so we're going to focus on a few examples:

\pset null 'ø' makes psql show null values as the ø character (similar to the 'empty set' symbol) instead of as an easily missed blank space.

\pset linestyle and \pset border let you change the way that borders are placed (or not) around results tables:

And a relatively new feature is that running \pset without any arguments at all will present a list of all the various settings and their current values.. so feel free to have a play.

This week's tip is sponsored by PostgresConf. Postgres Conference 2020: The largest gathering about People, Postgres, and Data. Join 800+ attendees and 100+ speakers to build opportunities for the global Postgres ecosystem.

🗓 Upcoming Events

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.