#318 — August 14, 2019

Read on the Web

Postgres Weekly

An Overview of Server-Side Programming in Postgres — If you just use Postgres as a data store that your app interacts with, you might not realize just how powerful it is as a platform of its own. User defined types, SQL and PL/pgSQL functions, stored procedures and integration with languages like C, Python and Perl extend Postgres’s functionality a long way.


PostgreSQL 11.5, 10.10, 9.6.15, 9.5.19, 9.4.24, and 12 Beta 3 Released — Why release one version of Postgres when you can release six? 😄 As is usual with these group releases, the motivation is fixing security issues and other bugs.

PostgreSQL Global Development Group

2Q PGConf 2019 - CFP and Registration Now Open — Join us in Chicago to learn more about recent achievements & exciting future developments in PostgreSQL. You can also participate in hands-on training with experts covering: Performance Tuning, PostgreSQL Security and Multi-master Replication.

2ndQuadrant PostgreSQL Events sponsor

The State of PostgreSQL: A Community Survey — Echoing similar surveys in the JavaScript and CSS worlds, Timescale are doing a Postgres community survey and plan to share the aggregated results and analysis publicly. (I just filled it out and it wasn’t invasive at all.)


How to Get the First or Last Value in a Group Using GROUP BY — A neat little trick using Postgres arrays.

Haki Benita

Trivial Postgres Time Series Examples — While extensions like TimescaleDB can boost Postgres’s time series data functionality, you can do the basics with vanilla Postgres alone.

Hans-Jürgen Schönig

Doubling the Sorting Speed of Network Types with Abbreviated Keys — While this won’t be of immediate use to many readers, this is so gorgeously niche and detailed! It digs into under-the-hood ways to improve Postgres’s performance when sorting network addresses stored using Postgres’s inet/cidr types.

Brandur Leach

Add Speed and Scale to PostgreSQL — Learn how an in-memory computing platform adds speed and scale to Postgres to support your most data-intensive applications.

GridGain Systems sponsor

Automatically Updating Materialized Views

Tatsuo Ishii

Slonik: A Postgres Client with Strict Types, Detailed Logging and Assertions — Built on top of Node.js’s pg library, Slonik provides some interesting niceties and convenience methods to the Postgres CLI experience.

Gajus Kuizinas

PostGIS 3.0.0alpha4, 2.5.3, 2.4.8, and 2.3.10 Released

PostGIS Developers

Adaptive Query Optimization for Postgres — A bit of fiddling around is required to get this patch and extension working with Postgres, but once you do, it’s an interesting experiment in improving the query optimizer. For advanced users only.

Postgres Professional

supported by pgexperts

💡 Tip of the Week

psql's 'expanded' formatting mode

After a recent run of quite advanced tips, a (very) simple one for a change!

When I was writing last week's tip about using pgstattuple to look at dead rows, I was confronted by some output that was impractical to screenshot:


Luckily, psql supports a different type of output called 'expanded table formatting mode'. Enable it by running psql with a -x or --expanded option or type \x to toggle it on and off while psql is already running.

That's better for taking a screenshot or to copy and paste into a README or email.

This weeks tip of the week is sponsored by pgexperts. PGX provides complete PostgreSQL support, from emergency services through daily operational support, to strategic planning. We’re here for your data ops.

🗓 Upcoming Postgres Events