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 Bookshelf topics Top

jamis
The following is cross-posted from the original Ray Tracer Challenge forum, from a post by garfieldnate. I’m cross-posting it so that the...
New
raul
Page 28: It implements io.ReaderAt on the store type. Sorry if it’s a dumb question but was the io.ReaderAt supposed to be io.ReadAt? ...
New
conradwt
First, the code resources: Page 237: rumbl_umbrella/apps/rumbl/mix.exs Note: That this file is missing. Page 238: rumbl_umbrella/app...
New
AndyDavis3416
@noelrappin Running the webpack dev server, I receive the following warning: ERROR in tsconfig.json TS18003: No inputs were found in c...
New
jskubick
I think I might have found a problem involving SwitchCompat, thumbTint, and trackTint. As entered, the SwitchCompat changes color to hol...
New
hazardco
On page 78 the following code appears: <%= link_to ‘Destroy’, product, class: ‘hover:underline’, method: :delete, data: { confirm...
New
kolossal
Hi, I need some help, I’m new to rust and was learning through your book. but I got stuck at the last stage of distribution. Whenever I t...
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
SlowburnAZ
Getting an error when installing the dependencies at the start of this chapter: could not compile dependency :exla, "mix compile" failed...
New
roadbike
From page 13: On Python 3.7, you can install the libraries with pip by running these commands inside a Python venv using Visual Studio ...
New

Other popular topics Top

Devtalk
Hello Devtalk World! Please let us know a little about who you are and where you’re from :nerd_face:
New
AstonJ
I’ve been hearing quite a lot of comments relating to the sound of a keyboard, with one of the most desirable of these called ‘thock’, he...
New
Exadra37
On modern versions of macOS, you simply can’t power on your computer, launch a text editor or eBook reader, and write or read, without a ...
New
Maartz
Hi folks, I don’t know if I saw this here but, here’s a new programming language, called Roc Reminds me a bit of Elm and thus Haskell. ...
New
AstonJ
We’ve talked about his book briefly here but it is quickly becoming obsolete - so he’s decided to create a series of 7 podcasts, the firs...
New
AstonJ
If you get Can't find emacs in your PATH when trying to install Doom Emacs on your Mac you… just… need to install Emacs first! :lol: bre...
New
PragmaticBookshelf
Author Spotlight Erin Dees @undees Welcome to our new author spotlight! We had the pleasure of chatting with Erin Dees, co-author of ...
New
husaindevelop
Inside our android webview app, we are trying to paste the copied content from another app eg (notes) using navigator.clipboard.readtext ...
New
First poster: bot
zig/http.zig at 7cf2cbb33ef34c1d211135f56d30fe23b6cacd42 · ziglang/zig. General-purpose programming language and toolchain for maintaini...
New
AstonJ
Curious what kind of results others are getting, I think actually prefer the 7B model to the 32B model, not only is it faster but the qua...
New

Sub Categories: