Horton

Re: Statistics Import and Export

Bruce Momjian <bruce@momjian.us>

View thread
From: Bruce Momjian <bruce@momjian.us>
To: Corey Huinker <corey.huinker@gmail.com>
Cc: Nathan Bossart <nathandbossart@gmail.com>, 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-19T22:40: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.

On Tue, Nov 19, 2024 at 03:47:20PM -0500, Corey Huinker wrote:
>     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.

Yes, after a clean API is designed, you can then consider surprise
calculus.  This is an issue not only for this feature, but for all
Postgres changes we consider, which is why I think it is worth stating
this clearly.  If I am thinking incorrectly, we can discuss that here too.

> 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.

How much are we supposed to consider users who do not read the major
release notes?  I realize we might be unrealistic to expect that from
the majority of our users, but I also don't want to contort our API to
adjust for them.

> 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.

That is a big purpose of the major release notes.  We can even list this
as an incompatibility in the sense that the procedure has changed.

> 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.

We used to create a script until the functionality was added to
vacuumdb.  Since 99% of users will not need to do anything after
pg_upgrade, it would make sense to output the script only for the 1% of
users who need it and tell users to run it, rather than giving
instructions that are a no-op for 99% of users.

> * 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.

Uh, that would make pg_upgrade take longer for some users, which might
be confusing.

> * 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.

I am not a big fan of that at this point.  If we get it, we can adjust
our API at that time, but I don't want to plan on it.

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

  When a patient asks the doctor, "Am I going to die?", he means 
  "Am I going to die soon?"