Skip to main content
2 votes
Accepted

When to rerun CLUSTER?

You can use correlation from the column statistics gathered by ANALYZE (or in the background by autovacuum) as indicator: SELECT schemaname, tablename, attname, correlation FROM pg_stats WHERE ...
Erwin Brandstetter's user avatar
1 vote
Accepted

PostgreSQL: What are different operations make the use of Temp files?

VACUUM and ANALYZE shouldn't create temporary files. First of all, the statistics from pg_stat_database need not be 100% accurate. Second, pg_stat_database.temp_bytes measures the size of temporary ...
Laurenz Albe's user avatar
  • 53.5k
1 vote

Clarifications on Maintenance jobs & CDC on upgrade to ENTERPRISE EDITION

Only the writable node can modify data, so index rebuilds, change data capture jobs, anything that modifies data all have to occur on the primary. You can offload the backups to the synchronous ...
Lowell Izaguirre's user avatar
1 vote
Accepted

Is automatically renaming indexes when renaming columns relevant for database health/maintenance?

This is largely a matter of personal taste. A constraint and its implementing index explain themselves quite well in my opinion, even if I don't know the name. Still, it is useful to have a consistent ...
Laurenz Albe's user avatar
  • 53.5k
1 vote
Accepted

Can I get the created time of a PostgreSQL DB?

There is no way to get the time when a database was created. I am not even certain what would be meant by that: if you restore a database from a pg_dump, would the restore time be the creation time? ...
Laurenz Albe's user avatar
  • 53.5k
1 vote

Finding tables & columns at risk of hitting max value of int

As mentioned above, if you're using pg 10+ you can run something similar to the code bellow to find sequences and how much (in %) they're from their limit. SELECT LAST_VALUE * 100 / max_value AS ...
Tanato's user avatar
  • 111
1 vote
Accepted

Locks caused by async stats update

https://techcommunity.microsoft.com/t5/azure-sql-blog/improving-concurrency-of-asynchronous-statistics-update/ba-p/1441687 ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY Now in SQL 2022.
WiseTechGlobal CTO's user avatar

Only top scored, non community-wiki answers of a minimum length are eligible