The RailsNotes Newsletter 🟥 ISSUE #16

🟥 ISSUE #16 (Database tuning, PostgreSQL, load_async, unlogged tables)

Author

Harrison Broadbent
October 26, 2023

How it feels squeezing out the last couple of % of performance from your database instance 👿 

— SPONSOR —

Learn about subqueries, materialized views, and custom data types in Postgres and Rails with real examples, and real code. 

Welcome to The RailsNotes Newsletter — Issue #16! This issue is all about database tuning!

Hey! 👋 I’m excited to share that PgAnalyze is sponsoring this + the next few issues of the RailsNotes newsletter! Big thank you to them for the support! In the spirit of their support, this week is all about databases and PostgreSQL! 

I’ve got a couple of great articles for you on advanced database tuning techniques for Rails, PostgreSQL-specific tuning tips (but framework agnostic), plus a few Rails 7 and Rails 7.1 feature breakdowns (since they introduced a couple of cool features, like async queries).

And if you want more, the free advanced database e-book from PgAnalyze (our sponsor) is actually pretty great! It’s got a couple of great diagrams like the one below, and lots of handy code examples and breakdowns of a few advanced database techniques (subqueries, materialized views, custom types etc.) —

This is going to be a good one! Let’s go!

~ ✨ FEATURED ARTICLE ✨ ~

Your face as you optimize your cache-hit percentage while reading this article.

If you’ve been reading this newsletter for a while, you know how much I love Paweł’s articles, and this one is just as brilliant as the rest!

This article goes deep into fine-tuning PostgreSQL performance with the RubyPgExtras library (written by Paweł himself, available on GitHub).

This article covers a full suite of database tweaks and investigations, including —

  • Checking your cache-hit ratios, and keeping them >99%,

  • Sorting through unused indexes and removing them,

  • Identifying columns that could benefit from new indexes,

  • Identifying deadlocks, removing bloat, and much more.

Is this going to be relevant to a small-scale Rails side-project with only a handful of models and tables? Probably not (but maybe!) — this is more relevant to medium-to-large-scale Rails apps, or ones that are scaling up to that size.

Very detailed, and worth a read!

~ MORE ARTICLES  ~

This is a mammoth 🦣 list of PostgreSQL tuning tips, by the brilliant Andy Atkinson (author of the recently published High Performance PostgreSQL for Rails).

Like the featured article, this one is worth skimming through to pickup a few new tips, and maybe fix that weird bug you’ve had floating around for a while 😅 

Another Pawel Urbanek article! With load_async coming back into the spotlight after the Rails 7.1 release, I thought it was worth including this deep dive 🥽 into how it works, and when to use it.

As usual, Pawel goes deep, starting with a brief 101, then quickly diving into the nitty-gritty of async PostgreSQL queries and their minutiae.

The final article this week is a handy round-up of some of the biggest PostgreSQL features from Rails 7.1 — all up there are 5 tips, covering things like composite primary keys, async queries, and unlogged tables.

It’s a great roundup of the additions in Rails 7.1 (on the database front), and it even includes code snippets to get you started with all the new stuff!

~ ⚒️ HANDY TIP ~ 

→ Use Unlogged Tables in Test Environments

Rails 7.1 introduced unlogged tables, which can be a neat way to speedup your test suites!

Unlogged tables in Postgres improve performance by dropping the durability requirement from tables, which is useful in test environments (don’t do this in production though!).

# config/environments/test.rb
#
ActiveSupport.on_load(:active_record_postgresqladapter) do
  self.create_unlogged_tables = true
end