Paul Bastide

Checking fillfactor for Postgres Tables

Written by

Paul

in

,

My teammate implemented Adjust PostgreSQL fillfactor for tables involving updates #1834, which adjusts the amount of data in each storefile.

Per Cybertec, fillfactor is important to "INSERT operations pack table pages only to the indicated percentage; the remaining space on each page is reserved for updating rows on that page. This gives UPDATE a chance to place the updated copy of a row on the same page as the original, which is more efficient than placing it on a different page." Link as such my teammate implemented in a PR a change to adjust the fillfactor to co-locate INSERT/UPDATES into the same space.

Query

If you want to check your fillfactor settings, you can can check the pg_class admin table to see your settings using the following scripts:

SELECT 
	pc.relname as "Table Name", 
	pc.reloptions As "Settings on Table",
	pc.relkind as "Table Type"
FROM pg_class AS pc
INNER JOIN pg_namespace AS pns 
	ON pns.oid = pc.relnamespace
WHERE pns.nspname = 'test1234'
	AND pc.relkind = 'r';

Note

  1. relkind represents the object type char r is a table. A good reference is the following snippet: relkind char r = ordinary table, i = index, S = sequence, v = view, m = materialized view, c = composite type, t = TOAST table, f = foreign table
  2. nspname is the schema you are checking for the fillfactor values.

Results

You see the value:

basic_resources,{autovacuum_vacuum_scale_factor=0.01,autovacuum_vacuum_threshold=1000,autovacuum_vacuum_cost_limit=2000,fillfactor=90},'r'

References

More posts