#​508 — May 31, 2023

Web Version

Together with  Hydra

Postgres Weekly

Generating SVG Images from Postgrespg-svg is a set of functions for creating SVG graphics from within Postgres. Its main use case is for converting PostGIS geometries into graphics as demonstrated in this thorough introduction (complete with an epic query), but you can use it independently of PostGIS too.

Martin Davis (Crunchy Data)

Postgres 16 Beta 1 Released — We are now truly on the fairway leading towards Postgres 16's eventual release. The draft release notes tease all of the updates, with particular improvements coming to performance, query parallelism, SQL/JSON, security, monitoring, and more.

PostgreSQL Global Development Group

This beta is already available in Amazon RDS's 'Database Preview Environment'.

Materialized Views: Precompute with Postgres — Learn the benefits of using materialized views in Postgres for analytics, the differences between materialized and regular views, and tips for optimizing their use.

Hydra sponsor

How to Use Variables in psql — You can set variables in psql using \set or by passing them in at the command line. Hubert shows off some uses.

Hubert depesz Lubaczewski

IN BRIEF:

▶  'I Tried 8 Different Postgres ORMs' from Node.js / Deno — A modern, fast-paced, and slightly irreverent tour of the various ways in which backend JavaScript developers can interact with Postgres (provided serverlessly by Neon, in this case.) (9 minutes.)

Beyond Fireship

Cache Synchronization using jOOQ and Postgres Functions
Vlad Mihalcea

Validate Database Objects After Migrating from IBM Db2 z/OS to RDS or Aurora
Parthasaradhi, Balasubramanian and Gupta (AWS)

Code and Tools

HypoPG 1.4.0: Hypothetical Indexes for Postgres — You can use HypoPG to create indexes that don’t really exist but which can be used to determine out if said indexes could be used by the query planner to improve performance. This week’s release makes it possible to support the opposite: hypothetically hiding existing indexes. GitHub repo.

Julien Rouhard

pg_dumpbinary v2.11: Dump a Database in Binary Format — Use pg_dump if you can, but if you encounter unusual situations where a binary dump would help – this is for you. GitHub repo.

Gilles Darold

Diesel 2.1: Extensible ORM and Query Builder for Rust — Gets rid of the boilerplate around database interaction in Rust without sacrificing performance. Supports Postgres, MySQL, and SQLite.

Diesel

Anton is a backend developer working with Python and Go who uses SQL both at the app level and for exploratory data analysis and data pipelines. He teaches courses on Go and SQL and is the author of SQL Window Functions Explained (the first five chapters can be read online for free – code POSTGRES can be used for a discount on the paid version).

We asked him a few questions about SQL window functions:

Can you explain window functions for someone who's ignored them so far?

"Window functions" is a (probably misleading) name for an entire sub-language, of sorts, built into regular SELECTs. Window functions help perform various data analysis tasks, from ranking, segmenting, and calculating moving aggregates to statistics, financial analysis, clustering, and data cleaning.

Are there any reasons to use thems in a situation where a subquery might do?

For most data analysis tasks, window functions tend to produce much more concise, readable and efficient queries.

Is Postgres's window functions support particularly interesting or different compared to the other databases you cover?

Fortunately for us, all database vendors have implemented window functions very close to the SQL standard, so there are no syntactical differences (although some advanced features are missing in certain DBMSs). Postgres supports almost all window-related features defined by the standard (with the unfortunate exception of windowed percentiles).

Anton blogs about Go, Python, SQL, and software in general over on his personal site and is the author of SQL Window Functions Explained.