#397 — March 17, 2021

Web Version

Postgres Weekly

Tackling the Travelling Salesperson Problem with PostGIS and pgRouting — The Travelling Salesperson Problem is the problem of routing between a variety of points visiting each only once and in as short a distance as possible. This post tackles the problem in a PostGIS powered Postgres database using pgRouting’s extensions.

Florian Nadler

Amazon Makes ARM-Based Instances Generally Available for Aurora — AWS Graviton2 processors are ARM-based CPUs that are custom built by AWS themselves and seem to offer much improved performance at a lower cost (a la Apple and their new M1 chips). This announcement brings the perf and cost improvements to Amazon Aurora PostgreSQL (and MySQL) users.

Amazon Web Services

Free eBook: Efficient Search in Rails with Postgres — Speed up a search query from seconds to milliseconds and learn about exact matches, trigrams, ILIKE, and full-text search.

pganalyze sponsor

Can auto_explain (with Timing) Have Low Overhead?auto_explain is a Postgres module for logging execution plans of slow running queries - it has some overhead though, but is it that big a deal and can it be mitigated?

Michael Christofides

Building a Postgres-Backed “Data Lake” for Reporting Purposes — Most companies make data driven decisions and querying data warehouses (or ‘lakes’, when the data is less formally structured) is a common activity. This extensive tale covers how one company approached it using Postgres and FDWs in an intriguing manner.

Paul Bonaud

Basic Practices for Optimizing Postgres Read and Write Performance — Some basic techniques and speed optimization ‘rules’ to take into account.

Michael Aboagye

Running Postgres in Docker - Why and How? — An introduction to Docker and whether you should run production Postgres workloads in a docker container.

Kaarel Moppel

Scaling Out Postgres with the Citus Open Source Shard Rebalancer — Last week’s Citus 10 release included the open sourcing of its shard rebalancer, a key part of how Citus optimizes tables split/sharded across different nodes.

Jelte Fennema

Performance Differences Between Postgres and MySQL

Blessing Krofegha

Tuning Your Postgres Database for High Write Loads

Tom Swartz

🔧 Tools and Code

PgHero 2.8: A Performance Dashboard for Postgres — A dashboard built in Ruby (and used at Instacart) that lets you look at basic performance stats including live queries, maintenance status, and connections.

Andrew Kane

Monitoring Postgres with Nagios and Checkmk — A RHEL/CentOS focused tutorial but it’s a neat setup. Checkmk is a tool that sits above Nagios and provides an arguably nicer interface.

Hamid Akhtar

Build Internal Apps (Faster) on Top of Postgres — Build internal apps without the mundane, boring bits (wrestling with UI libraries or hacking together data sources & APIs).

Retool sponsor

SQLBoiler 4.5: Generate a Go ORM Tailored to Your Database Schema — The idea is that you create your schema at the database level first and then this interrogates the schema and generates ORM code for Go based upon the actual design.

Volatile Technologies Inc.

Orafce 3.15.0 Released: Postgres Implementations of Oracle Database Functions

Pavel Stěhule

supported by Cockroach Labs

💡 Tip of the Week

How to auto generate UUID columns when creating mapping classes with SQLAlchemy

For Python developers using the SQLAlchemy ORM framework, you can let the database server auto-generate UUID columns when creating mapping classes.

This is valuable since it saves you from needing to use application libraries. Use the server_default parameter and call the gen_random_uuid() function:

class Account(Base):
    """The Account class corresponds to the "accounts" database table.
    __tablename__ = 'accounts'
    id = Column(UUIDtype, server_default=text("gen_random_uuid()", primary_key=True)
    balance = Column(Integer)

The Column.server_default parameter can be used to call any database function for setting a default value of a column.

This week’s tip is sponsored by Cockroach Labs, Learn about CockroachDB in this demo: "Distributed SQL: A modern, cloud-native PostgreSQL".