#310 — June 19, 2019

Read on the Web

Postgres Weekly

Easily Running Postgres on a 'Per-Project' Basis — “I worked out how to set up a Postgres database that’s almost as convenient as SQLite.” The goal was to make it as easy as typing postgres in a project folder to get a Postgres instance running solely for that project. How? Using direnv to handle the environment and the Nix package manager (so Linux and macOS only).

Jamey Sharp

Another Look at Bloom Indexes in Postgres — Last week we linked to Egor Rogov’s look at bloom indexes but this is a more accessible introduction if the bloom filter data structure is new to you.

Avinash Vallarapu

PostgresLondon - Last Chance to Register 🐘 — Join us in London for the evolution of PGConf UK to learn more about recent achievements & exciting future developments in PostgreSQL. You can also participate in hands-on training with experts covering: PostgreSQL Security & Multi-master Replication.

PostgreSQL Events sponsor

Timescale Unveils Timescale Cloud: A Managed Time-Series Database Service — Why is this Postgres news? As well as now being a managed cloud service, TimescaleDB is an open source Postgres extension for working with time-series data at scale.

Jeff Sposetti and Ajay Kulkarni

Linear Interpolation with SQL — A practical look at what you can do if you have a chronological data set with missing values. How can you find gaps and then how can you attempt to fill those gaps?

Philipp Wagner

An Overview of Postgres to MySQL Cross Replication — A detailed account of achieving cross replication between MySQL and Postgres databases using two different tools: pg_chameleon (a Postgres-specific system) and SymmetricDS (a database agnostic tool).

Nawaz Ahmed

Checking the Status of Sequences in a Single Pass — A look at a function that collects together information about your sequences from the pg_sequence catalog.

Luca Ferrari

Metrics to Monitor in Your PostgreSQL Database — There are several key metrics you’ll want to keep track of when it comes to database performance, and they’re not all database-specific.

InfluxData sponsor

PG Reloaded: A CLI Tool to Restore Postgres Databases Periodically

Zikani Nyirenda Mwase

A List of Databases Derived from Postgres

PostgreSQL Wiki

supported by pganalyze

💡 Tip of the Week

The LIKE option on CREATE TABLE

You've probably heard of table inheritance but what if you want something that's not quite so closely coupled.. let's say you just want to create a table that has the same columns as another?

Enter the LIKE option on CREATE TABLE. It works like this:

# create table people (name text, age integer);
# create table people2 (LIKE people)
# \d people2
       
Table "public.people2"
 Column | Type
--------+---------
   name | text
    age | integer

That's the most basic example but it goes a lot further than that. For example, let's say you have indexes, constraints, comments or storage settings defined on the first table. You can specify whether you want to include ALL of these or just individual ones:

# create table people3 (LIKE people INCLUDING ALL)
# create table people4 (LIKE people INCLUDING INDEXES)

You can also use LIKE to include columns while adding others of your own choice. For example:

# create table people5 (LIKE people, city text);

For more info on this feature check out the like_option section of the CREATE TABLE documentation.

This week's tip is sponsored by pganalyze. DBAs and developers use pganalyze to identify the root cause of performance issues in their Postgres database, to optimize queries and to get alerts about critical issues.

🗓 Upcoming Postgres Events