#305 — May 15, 2019

Read on the Web

Postgres Weekly

How to Try Out the Cutting Edge Version of Postgres — We frequently link interesting posts about upcoming features in Postgres, many of which have already been baked into the project’s core source code. But if you want to play with the very latest, experimental version of Postgres, how do you do it?

Hubert depesz Lubaczewski

Postgres 11.3, 10.8, 9.6.13, 9.5.17, and 9.4.22 Released — These are patch level releases focusing on bugfixes, including two security fixes, including a flaw where “a user running PostgreSQL 11 can read arbitrary bytes of server memory by executing a purpose-crafted INSERT statement to a partitioned table.” Ouch. More info here.

PostgreSQL Global Development Group

If Engineers Need Access, You Need strongDM — strongDM works with everything in your stack. Seriously. Any database. Any server. Every environment. Learn more.

strongDM sponsor

Quick and Dirty Address Matching with LibPostal — libpostal is an interesting looking library for parsing and normalizing street addresses which you can use from Postgres to make it easier to compare and search street addresses in your own datasets.

Paul Ramsey

The Draft Postgres 12 Release Notes — Bruce Momijan has announced that he has completed the first draft of Postgres 12’s release notes and that they’ll continue to get updates till the final release later this year. Also, it’s a bit of a grab-bag of items, but these PDF slides on Postgres 12 features are remarkably detailed.

Bruce Momijan

A New Postgres 12 Feature: Optimizer Support for Functions — “Support functions” allow the query optimizer to get more insight into functions which have previously been a bit of a black box.

Laurenz Albe

Creating the 'Ultimate' Postgres Slug Function? — This is something I usually make a client app do, but creating normalized ‘slugs’ for database entities can be easily outsourced to a Postgres function, as seen here.

Kester Dobson

Importing Data into Postgres on Amazon RDS via S3 — How to import data stored in an Amazon S3 bucket into a Postgres table hosted on Amazon RDS.

Amazon Web Services, Inc.

Monitor Custom PostgreSQL Metrics with Datadog — See real-time metrics from your databases, tools, and services in one place. Try monitoring with Datadog for free.

Datadog sponsor

Operationalizing Postgres Database Health Checks using SQL Notebooks — A look at a technique in Azure Data Studio (yes, Azure users only, I’m afraid) for creating interactive “notebooks” of SQL queries for monitoring your Postgres installation.

Parikshit Savjani (Microsoft)

Improving OLAP Workload Performance for Postgres with ClickHouse Database — ClickHouse is an open source column-oriented DBMS focused on rapidly producing analytical data reports.

Ibrar Ahmed

Postgraphile: Quickly Get a GraphQL API for Your Postgres Database — The latest release (4.4.0) has introduced support for subscriptions and live queries.

Graphile

supported by pganalyze

💡 Tip of the Week

Postgres has a lot more data types than you might think: A look at IP addresses and networks

When building out a data model in a database like Postgres it's really easy to stick to the faithful old data types like TEXT and INTEGER. You might even branch out into using arrays, hstore or JSON to make things more dynamic, but there's a lot more worth discovering.

Digging into all of Postgres's data types could fill a book, so we're focusing on one example today: using INET for IP addresses.

The INET type is used when you want to store IPv4 or IPv6 addresses. It can also store networks by specifying a netmask. If you try to store something that isn't a valid IP address or network, Postgres will throw an error, so you're basically getting data validation for free:

CREATE TABLE addrs (address INET);
INSERT INTO addrs VALUES ('127.0.0.1');
INSERT INTO addrs VALUES ('hello world');
# ERROR: invalid input syntax for type inet: "hello world"

The INET type goes a step further than data validation, though, and there are a variety of methods you can use in your SQL queries to interrogate IP and network addresses further:

INSERT INTO addrs VALUES ('202.121.0.0/16');
SELECT address, host(address), broadcast(address), netmask(address) FROM addrs;

The result is a bit too wide to suit the newsletter format, but if you're familiar with IP addressing, the relevant further information about the network is returned as you would expect from the function names. There are a lot more functions too for comparing IP addresses, extracting parts of an address, and masking addresses.

This week's tip is sponsored by Microsoft Azure. Want to use Postgres without worrying about managing, securing, and scaling your database? Learn about Azure Database for PostgreSQL and the latest announcement around Hyperscale.

🗓 Upcoming Postgres Events