#​402 — April 21, 2021

Web Version

Postgres Weekly

When Postgres is 'Out of Disk': How to Recover with Some Dos and Don'ts — I ran into this situation myself recently on RDS (Protip: set up alerts for this eventuality ;-)). Several CrunchyData engineers share some useful tips here with things to do or not do when you enter panic mode.

E Christensen, D Christensen, Katz, and Frost

Amazon RDS for PostgreSQL Now Integrates with AWS Lambda — I still need to fully get my head around this one, but being able to invoke serverless Lambda functions from within Postgres stored procedures or user-defined functions seems like it has some huge potential.

Amazon Web Services, Inc.

Postgres Vision 2021 - Free Online Conference (June 22-23) — This free, virtual, global event brings together the world’s leading PostgreSQL experts, users, and community members. Presentations will explore real-world user stories and new technologies, all moving forward with PostgreSQL.

EDB sponsor

Quick Bits

Lessons Learned From Five Years of Scaling PostgreSQL — “Over the years, we’ve scaled up to 75 terabyte (TB) of stored data across nearly 40 servers.” It’s always fascinating to read about people’s experiences, particularly with a deployment like this.

Joe Wilm

PostgreSQLite: A Way to Use Postgres 'Like SQLite' — A neat little idea that’s orchestrated by a straightforward 30-line shell script. Docker does most of the heavy lifting, though.

Felix Dietze

Three Approaches to Connection Management in Postgres — Connection pooling and management is one of those areas most people ignore far too long when it comes to their database. Craig quickly looks at three approaches to consider as you scale up.

Craig Kerstiens

Benchmarking Postgres: Setting up a RAID Array from Scratch — It’s been a long time since I’ve sat down and set up my own RAID array (‘the cloud’ has a lot to answer for!)

Vik Fearing

Visualize All Your PostgreSQL Performance Metrics in Real-Time — Collect OOTB and custom metrics for real-time performance analysis in Datadog. Start a free Datadog trial.

Datadog sponsor

▶  Citus Talk at CMU: Distributed Postgres as an Extension — Marco recently gave an interesting talk about Citus, the horizontal scaling Postgres extension, as part of a Carnegie Mellon database tech series and the video is now available. A good, accessible overview.

Marco Slot

The Power of synchronous_commit — “synchronous_commit is one of the settings that isn’t simple and controls perhaps too many things..” As well as rewriting the documentation for the feature, Bruce has outlined the essence of what it does here.

Bruce Momjian

pspg 4.6.0 Released — pspg is a ‘pager’ tool for working with table data from Postgres (though MySQL is now supported too). 4.6 adds a new query stream mode for using pspg from text editors. GitHub repo.

Pavel Stěhule

supported by CYBERTEC

💡 Tip of the Week

Ordering columns for better performance

To reduce the storage footprint of your database make sure that you order columns wisely. It's generally a good idea to put fixed size columns first and add variable length columns later.

Instead of:

CREATE TABLE t_foo (
   c   varchar(100),
   a   int,
   d   varchar(100),
   b   int
);

...rather go for:

CREATE TABLE t_foo (
   a   int,
   b   int,
   c   varchar(100),
   d   varchar(100)
);

It will reduce the size of your table and thus speed up your database. The reason for this behavior has to do with CPU alignment.

To learn more about columns and their impact on PostgreSQL performance, check out our blog post “Shrinking the Storage Footprint of Data”.

This tip of the week is brought to you by CYBERTEC, your professional partner for PostgreSQL and Data Science since 2000. Read more about PostgreSQL Performance in CYBERTEC’s blog.