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!

Popular Pragmatic topics Top

johnp
Hi Brian, Looks like the api for tinydb has changed a little. Noticed while working on chapter 7 that the .purge() call to the db throws...
New
simonpeter
When I try the command to create a pair of migration files I get an error. user=> (create-migration "guestbook") Execution error (Ill...
New
edruder
I thought that there might be interest in using the book with Rails 6.1 and Ruby 2.7.2. I’ll note what I needed to do differently here. ...
New
herminiotorres
Hi! I know not the intentions behind this narrative when called, on page XI: mount() |> handle_event() |> render() but the correc...
New
patoncrispy
I’m new to Rust and am using this book to learn more as well as to feed my interest in game dev. I’ve just finished the flappy dragon exa...
New
fynn
This is as much a suggestion as a question, as a note for others. Locally the SGP30 wasn’t available, so I ordered a SGP40. On page 53, ...
New
nicoatridge
Hi, I have just acquired Michael Fazio’s “Kotlin and Android Development” to learn about game programming for Android. I have a game in p...
New
jskubick
I found an issue in Chapter 7 regarding android:backgroundTint vs app:backgroundTint. How to replicate: load chapter-7 from zipfile i...
New
jonmac
The allprojects block listed on page 245 produces the following error when syncing gradle: “org.gradle.api.GradleScriptException: A prob...
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

Other popular topics Top

PragmaticBookshelf
A PragProg Hero’s Journey with Brian P. Hogan @bphogan Have you ever worried that your only legacy will be in the form of legacy...
New
AstonJ
Curious to know which languages and frameworks you’re all thinking about learning next :upside_down_face: Perhaps if there’s enough peop...
New
New
New
AstonJ
Inspired by this post from @Carter, which languages, frameworks or other tech or tools do you think is killing it right now? :upside_down...
New
DevotionGeo
The V Programming Language Simple language for building maintainable programs V is already mentioned couple of times in the forum, but I...
New
mafinar
Crystal recently reached version 1. I had been following it for awhile but never got to really learn it. Most languages I picked up out o...
New
AstonJ
Was just curious to see if any were around, found this one: I got 51/100: Not sure if it was meant to buy I am sure at times the b...
New
First poster: bot
The overengineered Solution to my Pigeon Problem. TL;DR: I built a wifi-equipped water gun to shoot the pigeons on my balcony, controlle...
New
PragmaticBookshelf
Author Spotlight Rebecca Skinner @RebeccaSkinner Welcome to our latest author spotlight, where we sit down with Rebecca Skinner, auth...
New

Latest in PragProg

View all threads ❯