B-Tree Index Fillfactor incorrectly reported as 100 instead of (default) 90 in SQL Pane & Properties

[brsa]

In pgAdmin4 v9.12, the SQL pane and the Index Properties dialog incorrectly report the FILLFACTOR for B-Tree indexes as 100 when no custom fillfactor has been set.

In PostgreSQL, the default FILLFACTOR for a B-Tree index is 90, not 100. By showing fillfactor=100 in the generated SQL, pgAdmin is misrepresenting the physical state of the database. If a user copies this SQL to recreate the index, they will inadvertently change the index's performance characteristics (reducing its "elbow room" and inducing page splits).

This has lead to a wild-goose chase for one of my clients, when I incorrectly (!) "accused" them of changing the fillfactor to 100. What an embarrassing waste of time!

Steps to Reproduce

Create a standard table and a B-Tree index without specifying any storage parameters:

CREATE TABLE test_fillfactor (id int, val text);
CREATE INDEX idx_test_default ON test_fillfactor(id);

Verify in the system catalog that reloptions is NULL (confirming the engine is using the default of 90):

SELECT relname, reloptions 
FROM pg_class 
WHERE relname = 'idx_test_default';
-- Result: reloptions is NULL

In pgAdmin, select the index idx_test_default in the Browser tree.
Open the SQL pane.
Open the Properties tab -> Options.

Expected Behavior

SQL Pane: Should NOT show fillfactor=100. It should either omit the WITH clause (matching pg_get_indexdef()) or correctly show fillfactor=90 if it must show defaults.

Properties Tab: Should show the default value of 90 (or leave the field blank).

Actual Behavior

SQL Pane: Displays WITH (fillfactor=100, deduplicate_items=True).

Properties Tab: Shows 100 in the Fillfactor field.

Environment

pgAdmin 4 Version: 9.12

PostgreSQL Version: 18 (or any)

Platform: Debian Linux (or any)

Regards
Erwin Brandstetter

[brsa]

Seeing the commit, I think there is more.

The default fillfactor for SP-GiST is 80, not 90.

Access Method	Default	Source Code Reference
Heap	100	src/backend/access/common/reloptions.c
B-tree	90	src/backend/access/nbtree/nbtoptions.c
Hash	90	src/backend/access/hash/hashutil.c
GiST	90	src/backend/access/gist/gistoptions.c
SP-GiST	80	src/include/access/spgist_private.h
GIN	N/A	src/backend/access/gin/ginutil.c
BRIN	N/A	src/backend/access/brin/brin.c

(GIN and BRIN do not support the fillfactor parameter.)

More importantly, you should not bother with this to begin with.
If an option is at its default, there is simply no mention in pg_class.reloptions. The array is either NULL, or there is simply no array element for the default option. pgAdmin should follow the same logic:
OMIT such options completely. Do not hard-code default values in the SQL script. Same in the properties tab - or show such assumed default values greyed out until an explicit value is given. (I don't care much about the properties dialog, much more about SQL scripts.) Your current approach has multiple downsides:

• The true representation of "default" is to omit the clause / the value. A hard-coded, explicit value is subtly incorrect to begin with. (Maybe debatable, but I strongly prefer this approach.)
• Postgres default values can change over time. This leads to bit-rot in the pgAdmin code. Simply omitting default values is safe in this regard.
• The reverse engineered SQL scripts are currently cluttered with noise (at best). Or with injected errors like in the case at hand (at worst).

Obviously, this should apply to all options. It is really unhelpful to spell out deduplicate_items=True for every btree index when hardly anybody ever changes this default to begin with. There are several other spots where you should apply the same logic for defaults to suppress noise and avoid more errors.

Regards
Erwin