#323 — September 18, 2019

Read on the Web

Postgres Weekly

Tracking View Dependencies in Postgres — When you create complex structures involving tables, views, triggers, functions, etc. that rely upon each other, you create dependencies. Here’s an exploration of view dependencies and how to keep track of what views depend upon what other objects.

Laurenz Albe

PostgreSQL 12 Beta 4 Released — Contains previews of all features that will be available in the final release of Postgres 12 and is “likely the final beta release” prior to a release candidate. As always, the beta release notes may be useful, as may Postgres’s How to Beta Test docs.

PostgreSQL Global Development Group

Learn PG 12 Features from Core Team Member — Join the free PostgreSQL webinar: New Features in PostgreSQL 12 with Peter Eisentraut and get an insider’s look into the upcoming PG 12 release.

2ndQuadrant PostgreSQL Webinars sponsor

Postgres Cheat Sheet — Basically 15 pages of quick solutions or approaches for a variety of Postgres-related tasks, from working with functions and users to constraints and interrogating tables.


Can You Gain Performance using Pgpool-II as a Load Balancer? — Spoiler: With a small number of clients, no. At much larger scale, yes. But, as always, it is worth running your own tests.

Muhammad Usama

Partitioning is Faster in Postgres 12 — Many improvements have been made to partitioning in PostgreSQL 12. Here’s some benchmark results so you can see what’s been done.

David Rowley

Using DISTINCT ON in Non-Postgres Databases — We wrote about DISTINCT ON, a neat feature for selecting the first rows that distinctly match a variety of conditions, in our tip in issue 321. But what if you want to get a similar outcome on other database systems?

Lukas Eder

Why Use TimescaleDB Over Relational Databases? — The interesting thing is that while TimescaleDB is a time series database, it is built as an extension on top of a relational database, Postgres!


strongDM Makes Managing DB Access a Breeze — Splunk's CISO says "strongDM lets you see what happened, replay & analyze incidents. You can't get that anywhere else."

strongDM sponsor

Installing Postgres 12 Beta/RC on RHEL, CentOS or Fedora — How to get testing the next version of Postgres on Red Hat flavored Linux distros.

Devrim Gunduz

The Data Loading Performance of Postgres and TimescaleDB — An experiment in testing the performance when loading a lot of data (over a billion rows) into Postgres and TimescaleDB running on EC2 instances.

Fabien Coelho

Postgres Pager pspg 2.0.2 Released — We featured pspg two weeks ago, but its creator has since released two point releases that introduce sorting capabilities for all columns displayed.

Pavel Stěhule

PL/Proxy 2.9: Function-Based Sharding for Postgres — If you’re not familiar with the project, there’s more info on its homepage, but PL/Proxy was originally developed at Skype to help scale Postgres via sharding.

Luca Ferrari

supported by GitPrime

💡 Trick of the Week

The enigmatic TABLE command

One of the fun things about doing these tips is I get to discover interesting quirks and tidbits that I'd never seen before. The TABLE SQL command is the latest example. I've looked through the SQL spec, the Postgres source code, and I'm simply unable to track it down.. but it works!

Given a simple schema:

CREATE TABLE x (id int);
INSERT INTO x VALUES (10),(20),(30);

TABLE lets you quickly see the entire table:


It's a bit naive and it doesn't support WHERE or GROUP clauses, but it does support ORDER, LIMIT, and OFFSET.

The main use case, in my opinion? If you want to rapidly look into a table while in psql or a similar tool, TABLE table-name is shorter and less awkward to type than SELECT * FROM table-name;.. and it's something fun to show off to anyone else who doesn't know about it 😄

If you do have any documentation or reference for this command though, get in touch. I'm intrigued where it comes from.

This week's trick is sponsored by GitPrime. Get your copy of their new field guide '20 Patterns to Watch for in Engineering Teams', filled with actionable insights to help debug your development process with data.

🗓 Upcoming Events