daniela

daniela

High Performance PostgreSQL for Rails: How to show autovacuum_vacuum_scale_factor per table (p. 171)

@andatki

On page 171, it shows how to change the autovacuum scale factor for a particular table, eg:

ALTER TABLE trips SET (autovacuum_vacuum_scale_factor = 0.01);

It’s also possible to show from in a psql console what the global value is for the scale factor:

SHOW autovacuum_vacuum_scale_factor;

My question is: How to show the table-specific values? i.e. suppose someone else has gone in and updated this for various tables, and you just want to know what all the current values are before making further changes?

First Post!

andatki

andatki

Author of High Performance PostgreSQL for Rails

Hey Daniela! Good question. I’m adding a query (thanks ChatGPT) you can run on your DB to find this information. We can query the pg_class system catalog to get it. To test this, I set the AV scale factor using the command you listed and made sure the expected option name and value appeared in the result.

Because this seems generally useful, I’ve added this query to my pg_scrips repository on GitHub. There’s also a commented out section showing the result when run on my local Rideshare DB.

https://github.com/andyatkinson/pg_scripts/blob/main/per_table_options_reloptions_all_regular_tables.sql

Besides querying this information, another tactic could be adding these changes to your local dev DB, since they’re dumped into the db/structure.sql.

Here’s a snippet below where Autovacuum was disabled for a table, showing the line it adds to db/structure.sql.

While this AV change wouldn’t likely wouldn’t be necessary locally, documenting a change like this in the db/structure.sql could be helpful on a team of people interested in this info.

“+WITH (autovacuum_enabled='false');”

Hope that helps!

Where Next?

Popular Pragmatic Bookshelf topics Top

jimmykiang
This test is broken right out of the box… — FAIL: TestAgent (7.82s) agent_test.go:77: Error Trace: agent_test.go:77 agent_test.go:...
New
jeffmcompsci
Title: Design and Build Great Web APIs - typo “https://company-atk.herokuapp.com/2258ie4t68jv” (page 19, third bullet in URL list) Typo:...
New
lirux
Hi Jamis, I think there’s an issue with a test on chapter 6. I own the ebook, version P1.0 Feb. 2019. This test doesn’t pass for me: ...
New
JohnS
I can’t setup the Rails source code. This happens in a working directory containing multiple (postgres) Rails apps. With: ruby-3.0.0 s...
New
adamwoolhether
I’m not quite sure what’s going on here, but I’m unable to have to containers successfully complete the Readiness/Liveness checks. I’m im...
New
akraut
The markup used to display the uploaded image results in a Phoenix.LiveView.HTMLTokenizer.ParseError error. lib/pento_web/live/product_l...
New
taguniversalmachine
Hi, I am getting an error I cannot figure out on my test. I have what I think is the exact code from the book, other than I changed “us...
New
jwandekoken
Book: Programming Phoenix LiveView, page 142 (157/378), file lib/pento_web/live/product_live/form_component.ex, in the function below: d...
New
a.zampa
@mfazio23 I’m following the indications of the book and arriver ad chapter 10, but the app cannot be compiled due to an error in the Bas...
New
dtonhofer
@parrt In the context of Chapter 4.3, the grammar Java.g4, meant to parse Java 6 compilation units, no longer passes ANTLR (currently 4....
New

Other popular topics Top

PragmaticBookshelf
Stop developing web apps with yesterday’s tools. Today, developers are increasingly adopting Clojure as a web-development platform. See f...
New
PragmaticBookshelf
Andy and Dave wrote this influential, classic book to help their clients create better software and rediscover the joy of coding. Almost ...
New
Exadra37
I am thinking in building or buy a desktop computer for programing, both professionally and on my free time, and my choice of OS is Linux...
New
AstonJ
I ended up cancelling my Moonlander order as I think it’s just going to be a bit too bulky for me. I think the Planck and the Preonic (o...
New
PragmaticBookshelf
Author Spotlight Mike Riley @mriley This month, we turn the spotlight on Mike Riley, author of Portable Python Projects. Mike’s book ...
New
AstonJ
If you want a quick and easy way to block any website on your Mac using Little Snitch simply… File > New Rule: And select Deny, O...
New
New
PragmaticBookshelf
Get the comprehensive, insider information you need for Rails 8 with the new edition of this award-winning classic. Sam Ruby @rubys ...
New
PragmaticBookshelf
Overarching tutorial for Python beginner and intermediate developers that teaches web development with the Flask framework. Miguel Gr...
New
PragmaticBookshelf
Use advanced functional programming principles, practical Domain-Driven Design techniques, and production-ready Elixir code to build scal...
New

Sub Categories: