Postgres Weekly Issue 503: April 26, 2023

#503 — April 26, 2023

Together with

Nine Ways to Slip Up with Postgres — A quick tour of some common mistakes or pitfalls that you can encounter when using our favorite database. It resonated with a lot of people and stoked extensive discussions on both Reddit and Hacker News. Points include forgetting to add indexes for foreign keys, not tweaking work_mem, and leaning too heavily on CTEs.

Phil Booth

⚠️ If you like this sort of thing, the classic Postgres wiki 'Don't Do This' entry will also be up your street.

Best Practices for Optimizing Postgres Query Performance (eBook) — Learn how to get a 3x performance improvement on your Postgres database and 500x reduced data loaded from disk. We share our learnings from helping companies like Atlassian, CounterPath, and others speed up their queries.

pganalyze sponsor

Build Postgres Extensions with Rust with PGRX — For ‘various reasons’ the popular PGX framework has renamed to PGRX but remains a great way to build Postgres extensions in Rust. The reason isn’t explained, but I do note a company owns PGX as a trademark for database purposes, so probably a good move.

Technology Concepts & Design, Inc.

IN BRIEF:

Peter Eisentraut looks at the different variants of the CREATE command that have turned up in Postgres over the years. We now have 42 of them!
Ryan Booz has written a getting started with psql tutorial few experienced readers are likely to need but it has a neat 'history of psql' section going back to Ingres's monitor tool.
This week's PostgreSQL Person of the Week is Loxodata's Sébastien Lardière.

Solving N+1 Postgres Queries for Ruby on Rails Apps — Tips for working with Ruby on Rails’s ActiveRecord and using better SQL to improve performance and avoid N+1 queries.

Christopher Winslett

Migrating Postgres from Heroku: A Customer Study on Crunchy Bridge

Crunchy Bridge sponsor

How Postgres Triggers Can Simplify Your Backend Development — Triggers let you automatically execute a function in response to a specific database event which provides a route to offloading certain logic to the database itself.

The Mythical Engineer

The role of triggers was recently discussed within the community in the latest PGSQL Phriday with mixed opinions on their use.

A Few Ways Indexes Could Hurt You — Indexes come with a cost in terms of performance and resource consumption.

Jobin Augustine (Percona)

Key Considerations While Migrating Bulk Operations from Oracle to Postgres — Using Amazon Database Migration Accelerator (Amazon DMA) specifically.

Karimi and Gopalappa (AWS)

Migrate Data from Partitioned Tables in Postgres using AWS DMS
Ayyakkannu, Joy, and Hegde (AWS)

Code and Tools

pgddl: DDL EXtractor Functions (ddlx) — SQL functions to convert PostgreSQL system catalogs to nicely formatted snippets of SQL DDL, such as CREATE TABLE. One interesting use case for this is to get the actual DDL SQL executed that resulted in an event trigger being called.

Alexi Theodore

pg_graphql 1.2
pgslice 0.6
↳ Postgres partitioning as easy as pie
ruby-pg 1.5.2
↳ Postgres client library for Ruby.
pq 1.10.9
↳ Pure Go Postgres driver for database/sql
Citus 11.1.6
TimescaleDB 2.10.2

🗣 If you fancy a debate..

Ask HN: Is Postgres Better than MySQL? — Hacker News tackles a classic, perennial point of debate. How do two of the most popular open source databases stack up against each other?

Hacker News