#301 — April 17, 2019

Read on the Web

Postgres Weekly

Latest Release: 11.2 (2019-02-14)

'Postgres Now Has Pluggable Table Storage' — Well, core does, we’ll need to wait a while to be using this in production, but being able to more easily switch in table storage systems will open up interesting opportunities.

Andres Freund on Twitter

Writing PostgreSQL Extensions in C is Fun — It depends on your definition of fun but it’s not quite as difficult as I’d assumed. This post walks through the basic steps to get started. I also think reading the code of simple, but complete, extensions such as count_distinct is useful if you wish to pursue this.

Ibrar Ahmed

Learn to Secure Your Database — Join the free PostgreSQL webinar: Security and Compliance with PostgreSQL and learn to identify and secure your database breach points. Reserve your spot today.

2ndQuadrant PostgreSQL Webinars sponsor

When Postgres Blocks: 7 Tips for Dealing with Locks — We’re revisiting a classic post that highlighted common mistakes developers face dealing with Postgres locks and what you can do about them.

Marco Slot

Postgres 12 is Optimizing the Storage of Small Tables — One to read if your database model requires that you have a large number of small tables (e.g. a multi-tenant schema). Things will be getting more efficient in Postgres 12.

John Naylor

Swarm64 Supports Xilinx Alveo Accelerator — At the risk of sounding like a pile of buzzwords and trademarks, this is a press release for an FPGA-based database accelerator that supports Postgres.

Swarm64

Fast Upgrading of Legacy Postgres with Minimum Downtime Using pg_upgrade — The latest in a long series of Postgres upgrading posts from Percona. This time pg_upgrade (formerly pg_migrator) is considered.

Avinash Vallarapu

Getting Started with Azure Database for PostgreSQL — A fully managed Postgres database engine with features like built-in high availability, scalability, and security. Get started and leverage the open source tools of your choice.

Microsoft Azure sponsor

A Postgres 12 Highlight: pg_checksums — A renaming of pg_verify_checksums as introduced in Postgres 11.

Michael Paquier

pspg: An Alternative Pager for Postgres's psql Client — Replaces less and brings in support for tabular data.

Pavel Stehule

Marten: Postgres-Based Persistence for .NET Systems — A document database and event store for .NET-based apps that uses Postgres to do the heavy lifting on the backend. And, yes, it works on .NET Core :-)

JasperFx

libpqxx: The Official C++ Client API for Postgres — It’s had quite a few updates recently.

Jeroen Vermeulen

supported by Percona

💡 Tip of the Week

Automatically logging slow queries

The server that hosts our newsletter was running slow and we didn't know why. Postgres was suffering in particular, so we wanted to see what queries were getting bogged down.

Like most databases, Postgres has a built-in slow query log feature that automatically logs queries to the main Postgres log file if they take over a certain amount of time and it's really easy to set up.

In your main postgresql.conf file (which is often somewhere like /etc/postgresql/9.6/main/postgresql.conf), either edit or add a line like so:

log_min_duration_statement = 1000

After restarting Postgres or reloading the config with SELECT pg_reload_conf();, this directive causes any queries that take over 1000 milliseconds (one second) to be logged.

(The location of the log file varies but is /var/log/postgresql/postgresql-9.6-main.log on my test setup, for instance.)

To deliberately run a long query for testing purposes:

select pg_sleep(10) /* just testing */;

Note the presence of the comment within the query, that'll appear in the log too so that anyone else monitoring will be aware of what's going on.

And, no, it wasn't Postgres causing our performance issues. Our VPS was on a shared box that came under heavy use and upgrading our instance fixed it. I'll note, though, that if you run any VPS style servers, it's well worth being familiar with 'steal time' and how to monitor it!

This week's tip is sponsored by Percona. Join Percona as we present Upgrading / Migrating your legacy PostgreSQL to newer PostgreSQL versions.

🗓 Upcoming Postgres Events