#307 — May 29, 2019

Read on the Web

Postgres Weekly

Visualizing the Postgres Vacuum Progress — If your database’s vacuum procedure is taking an extensive amount of time (as it is here) being able to visualize its progress is surely desirable.

Dave Pacheco

Postgres 12 Beta 1 Released — The draft release notes go into detail on what’s new, but a few things worth looking at are:

  • Inlined WITH query support (one for you CTE fans).
  • SQL:2016-compliant JSON path queries.
  • Case and accent insensitive comparisons for ICU provided collations.
  • Generated columns.
  • A pluggable table storage mechanism.

PostgreSQL Global Development Group

Using Automatic Table Statistics to Improve the Optimizer — How automatically generating table statistics made a cost-based optimizer make better decisions when picking queries.

Cockroach Labs sponsor

Parallel PostGIS and PgSQL 12: Problems Solved! — Spatial queries are often CPU-bound so being able to process them in parallel potentially has big wins which Postgres 12 is now able to deliver on: “PostgreSQL 12 and PostGIS 3 have finally cracked the parallel spatial query execution problem, and all major queries execute in parallel without extraordinary interventions.”

Paul Ramsey

Using Docker Hub's Postgres Images — Very helpful advice if you want to use the Docker-hosted images to run up Postgres for things like CI jobs or development.

Craig Ringer

Table Inheritance: What's it Good For? — I must admit, I’ve never used table inheritance, but Chris demonstrates how it can make certain database design problems a lot easier to handle.

Chris Travers

Deploying Active-Active Postgres on Kubernetes — A step-by-step guide on how to deploy an active-active Postgres cluster on Kubernetes using Symmetric-DS (an open source database replication tool).

Dave Cramer

▶  Postgres, C'est le Nouveau NoSQL (It's the New NoSQL) — A talk that’s in French which limits the audience somewhat but the slides are, handily, all in English.

Laurent Doguin

Don’t Miss Postgres Vision 2019 — Join us June 24 - 26, Boston, MA. Register now.

EnterpriseDB sponsor

PostgresClientKit: A Client Library for Swift That Doesn't Require libpq

David Pitfield

Sequelize: An Easy-to-Use Multi SQL Dialect ORM for Node.js — Works well with Postgres and good choice if you’re a Node developer.

Sequelize

supported by Percona

💡 Tip of the Week

Spinning up a quick, temporary Postgres instance with Docker

Docker makes it really easy to spin up an independent Postgres server and psql quickly if you want to try something out.

Craig Ringer's Using Docker Hub's PostgreSQL Images tutorial (linked above) is fantastic if you want to use Docker's Postgres images at any serious level, but I also use them in a lightweight way for quick tasks such as checking SQL support between different versions or to test the tips we include here!

Make sure you have Docker installed and fully working and then create a 'network' specifically for your experiementation (this 'network' enables psql to run in a separate container and connect across to the Postgres instance):

docker network create pg1

Now we can spin up a Postgres server instance like so:

docker run --name my-pg-server -e \
       POSTGRES_PASSWORD= \
       --network pg1 --rm -d postgres

Note: If you want a specific version of Postgres, replace 'postgres' at the end with, say, 'postgres:10.8'. You can see which versions are supported here.

To connect to this instance using psql:

docker run -it --rm --network pg1 \
       postgres psql -h my-pg-server \
       -U postgres

Once you've finished using the server, use docker stop my-pg-server and you're good to go about the rest of your day. Do not use this in a production scenario, however, as the server has been created with no password and is therefore unsecured.

There's more documentation on Docker Hub.

This week's tip is sponsored by Percona. Expand your knowledge with insights on Postgres at Percona Live May 29-30th in Austin, TX.

🗓 Upcoming Postgres Events