Horton

Re: Statistics Import and Export

Corey Huinker <corey.huinker@gmail.com>

View thread
From: Corey Huinker <corey.huinker@gmail.com>
To: Nathan Bossart <nathandbossart@gmail.com>
Cc: Bruce Momjian <bruce@momjian.us>, Jeff Davis <pgsql@j-davis.com>, jian he <jian.universality@gmail.com>, Matthias van de Meent <boekewurm+postgres@gmail.com>, Tom Lane <tgl@sss.pgh.pa.us>, Magnus Hagander <magnus@hagander.net>, Stephen Frost <sfrost@snowman.net>, Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>, Peter Smith <smithpb2250@gmail.com>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>, alvherre@alvh.no-ip.org
Date: 2024-11-19T20:47:20Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Change pg_dump default for statistics export.

  2. pg_dump: Adjust reltuples from 0 to -1 for dumps of older versions.

  3. vacuumdb: Don't skip empty relations in --missing-stats-only mode.

  4. pg_dump: Fix query for gathering attribute stats on older versions.

  5. Prevent redeclaration of typedef TocEntry.

  6. Remove unused function parameters in pg_backup_archiver.c.

  7. pg_dump: Retrieve attribute statistics in batches.

  8. pg_dump: Reduce memory usage of dumps with statistics.

  9. Skip second WriteToc() call for custom-format dumps without data.

  10. Add relallfrozen to pg_dump statistics.

  11. Matview statistics depend on matview data.

  12. Add pg_dump --with-{schema|data|statistics} options.

  13. Stats: use schemaname/relname instead of regclass.

  14. CREATE INDEX: do update index stats if autovacuum=off.

  15. Don't convert to and from floats in pg_dump.

  16. CREATE INDEX: don't update table stats if autovacuum=off.

  17. Organize and deduplicate statistics import tests.

  18. Address stats export review comments.

  19. Address stats import review comments.

  20. Add relallfrozen to pg_class

  21. Fix pg_strtof() to not crash on NULL endptr.

  22. Use attnum to identify index columns in pg_restore_attribute_stats().

  23. pg_dump: prepare attribute stats query.

  24. Avoid unnecessary relation stats query in pg_dump.

  25. Remove redundant pg_set_*_stats() variants.

  26. Do not use in-place updates for statistics import.

  27. Fix confusion about data type of pg_class.relpages and relallvisible.

  28. Documentation fixups for dumping statistics.

  29. Trial fix for old cross-version upgrades.

  30. Transfer statistics during pg_upgrade.

  31. Lock table in ShareUpdateExclusive when importing index stats.

  32. Use in-place updates for pg_restore_relation_stats().

  33. Improve error message for replication of generated columns.

  34. pg_dump: Add dumpSchema and dumpData derivative flags.

  35. Disallow modifying statistics on system columns.

  36. Add missing CommandCounterIncrement() in stats import functions.

  37. Add functions pg_restore_relation_stats(), pg_restore_attribute_stats().

  38. Documentation fixup.

  39. Add functions pg_set_attribute_stats() and pg_clear_attribute_stats().

  40. Change pg_*_relation_stats() functions to return type to void.

  41. Disable autovacuum for tables in stats import tests.

  42. Allow pg_set_relation_stats() to set relpages to -1.

  43. Fixup for pg_set_relation_stats().

  44. Create functions pg_set_relation_stats, pg_clear_relation_stats.

  45. Add memory/disk usage for Window aggregate nodes in EXPLAIN.

  46. Improve performance of dumpSequenceData().

  47. Add INJECTION_POINT_CACHED() to run injection points directly from cache

  48. Improve performance of binary_upgrade_set_pg_class_oids().

  49. Improve assertion in mdwritev()

  50. CREATE INDEX: do not update stats during binary upgrade.

  51. Redefine pg_class.reltuples to be -1 before the first VACUUM or ANALYZE.

>
> I don't have a strong opinion here, but I suspect that if I was creating
> vacuumdb from scratch, I'd have suggested a --missing-only flag that would
> only work for --analyze-only/--analyze-in-stages.  That way, folks can
> still regenerate statistics if they want, but we also have an answer for
> folks who use pg_upgrade and have extended statistics.


(combining responses to Bruce's para about surprise calculus and Nathan
here)

I agree that a clean API is desirable and a goal. And as I stated before, a
new flag (--analyze-missing-in-stages / --analyze-post-pgupgrade, etc) or a
flag modifier ( --missing-only ) was my first choice.

But if we're going to go that route, we have a messaging problem. We need
to reach our customers who plan to upgrade, and explain to them that the
underlying assumption behind running vacuumdb has gone away for 99% of
them, and that may be 100% in the next version, but for that 99% running
vacuumdb in the old way now actively undoes one of the major improvements
to pg_upgrade, but this one additional option keeps the benefits of the new
pg_upgrade without the drawbacks.

That, and once we have extended statistics importing on upgrade, then the
need for vacuumdb post-upgrade goes away entirely. So we'll have to
re-message the users with that news too.

I'd be in favor of this, but I have to be honest, our messaging reach is
not good, and takes years to sink in. Years in which the message will
change at least one more time. And this outreach will likely confuse users
who already weren't (and now shouldn't be) using vacuumdb. In light of
that, the big risk was that an action that some users learned to do years
ago was now actively undoing whatever gains they were supposed to get in
their upgrade downtime, and that downtime is money to them, hence the
surprise calculus.

One other possibilities we could consider:

* create a pg_stats_health_check script that lists tables missing stats,
with --fix/--fix-in-stages options, effectively replacing vacuumdb for
those purposes, and then crank up the messaging about that change. The "new
shiny" effect of a new utility that has "stats", "health", and "check" in
the name may be the search/click-bait we need to get the word out
effectively. That last sentence may sound facetious, but it isn't, it's
just accepting how search engines and eyeballs currently function. With
that in place, we can then change the vacuumdb documentation to be deter
future use in post-upgrade situations.

* move missing-stats rebuilds into pg_upgrade/pg_restore itself, and this
would give us the simpler one-time message that users should stop using
vacuumdb in upgrade situations.

* Making a concerted push to get extended stats import into v18 despite the
high-effort/low-reward nature of it, and then we can go with the simple
messaging of "Remember vacuumdb, that thing you probably weren't running
post-upgrade but should have been? Now you can stop using it!". I had
extended stats imports working back when the function took JSON input, so
it's do-able, but the difficulty lies in how to represent an array of
incomplete pg_statistic rows in a serial fashion that is cross-version
compatible.