-
Notifications
You must be signed in to change notification settings - Fork 36
/
CHANGELOG
144 lines (89 loc) · 10.3 KB
/
CHANGELOG
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
2.8.0
- Drop python 2 support
- Change #! line to use python3 since several modern OS's no longer have a generic python binary/shortcut
2.7.2
- Ignore spgist indexes. Thanks to @pdeaudney for the fix (Github PR #28)
- Add example to show how to set connection options when running bloat check (Github PR #28)
2.7.1
- Fixed debug flag to work with additional flags (Github Issue #27)
2.7.0
- Add support for simplified byte values to the filter options (b, kb, mb, gb, tb, pb, eb, zb). Thanks to @richyen on Github for the contribution (Github Issue #13 ,PR #21).
2.6.5
- Allow --quick mode to work now that TOAST tables are also being scanned as of version 2.6.1. pgstattuple_approx() does not work on TOAST tables. The --quick mode now only applies the pgstattuple_approx() function to the base table and performs a normal check on all remaining objects. This means that --quick mode now no longer requires table mode and always scans all objects. Thanks to @victor-sudakov on Github for reporting the issue and suggestions on improvement. (Github Issue #22)
- Fix the output format for percentages to be at most 2 digit precision.
- Fix "index out of range" error when running in --debug mode
2.6.4
- Grammar fixes
2.6.3
- Fixed bug that caused script to fail when it encounters materialized views. Introduced in v2.5.1 when labels were added to the simple output. Added new label (mv) for materialized views. Thanks to @DHorneDBA on Github for reporting the issue. (Github Issue #19)
2.6.2
- Ensure that temporary indexes are skipped. Thanks to @EricVeldhuyzen on Github for reporting issue with a fix (Github Issue #2).
2.6.1
- Ensure TOAST tables are included in results. By default, pgstattuple does not include the underlying TOAST table for a relation. TOAST tables are now scanned as separate objects because their bloat status can vary widely from the main table. However, fixing TOAST bloat must be done through the main table. Many thanks to @jpuris on Github for reporting this issue and helping to test (Github Issue #15).
- "simple" output will also output the toast table's associated real table.
- Did not include associated real table in statistics table since a toast table could change any time a table is rewritten
and make the stored stats data invalid. It's better to dynamically look up a toast table's real table at the time the
data is queried (pg_class.reltoastrelid). This is what simple output does.
- Always include pg_catalog schema when the the --schema filter option is used.
- Fix python 3 compatibility around using --quiet option.
2.6.0
- Modified --quiet option to allow setting multiple times to suppress different levels of non-error output.
- Setting once will cause console output to only be given if bloat is actually found (supresses "no bloat found" message).
- Setting two or more times will suppress all console output, even if bloat is found. Resulting data is only stored in stats tables. Setting value more than twice has no additional affects.
- Added shorthand option to --quiet as -u to allow setting multiple times easier. Can do "--quiet --quiet", "-u -u", or "-uu". -q already taken by --quick and did not want to break backward compatability.
2.5.1
- Allow the --rebuild_index option to honor all other filters so you only get rebuilding commands that are relevant to your desired bloat thresholds. Output of commands is now also given in wasted space order (similar to simple output list) starting with the largest.
- Added labels to the simple output to more clearly identify the object type (i=index, t=table, p=primary key).
2.5.0
- Added --recovery_mode_norun option. This allows the same crontab entry to be entered on all systems in a failover cluster and ensure the bloat check is always only run on whichever system is the primary.
2.4.3
- Make exit codes and database connection closes consistent throughout script.
2.4.2
- Fix regression in 2.4.1 that stopped --rebuild_index option from adding CONCURRENTLY flag to unique index statements.
2.4.1
- Account for custom tablespace locations for indexes when using the --rebuild_index option to generate statements. If you use custom tablespace and used this option's generated SQL, please double-check that your indexes are in the correct tablespaces. CREATE INDEX does not automatically put the created index in the given table's tablespace unless it is the default or explicitly stated in the CREATE INDEX command. The generated statements now do this automatically for you if necessary.
2.4.0
- Added oid column to the bloat_stats, bloat_tables and bloat_indexes tables. You will have to re-run --create_stats_table to recreate these tables in any database(s) this script is run against to include the new column. This makes it easier to use the values in the statistics tables in joins and functions and also simplifies script code.
2.3.5
- Account for CLUSTER'ed indexes in the --rebuild_index output. If you'd previously used those generated commands, you may have lost the cluster attribute for that index. The commands output now properly account for this and will set the CLUSTER state on the given index without causing a long lock. However, the index does not have its data clustered yet. The command to actually cluster the data is given later, but commented out so it will not run automatically. This is to prevent an accidental, potentially very long exclusive lock on the table. You will have to manually run the CLUSTER command on the table to reorder your data. If you have a clustered index, it is highly recommended you look into altering the fillfactor storage attribute to reduce the chance that the clustering order is lost.
- Simplified internal check for PostgreSQL version.
2.3.4
- Fixed script output to apply the fillfactor settings when any filtering options are set. Fillfactor was being accounted for in the text output values, but not in the actual calculation of the output, which caused objects to be output in the bloat list when they should not have been.
- Additional syntax fixes so debug output should work in python 3.
2.3.3
- Fix all print syntax so script works in python 3.
2.3.2
- Do not rely on OID from pg_class to perform scanning operations anymore. Could possibly change mid-run without the object name changing and cause script failure.
2.3.1
- Fixed undefined variable error when using wasted space & wasted percentage options in the --exclude_object_file file
2.3.0
- Added --rebuild_index argument. This generates SQL statement blocks that can be used to rebuild indexes with minimal impact on database locks.
- It does not run the statements it generates, it only outputs them to STDOUT.
- Handles primary keys & unique constraints. Unique constraints are rebuilt as unique indexes. They are technically the same under the hood as far as PostgreSQL is concerned and having them as an index makes them easier to rebuild in the future since they require fewer locks.
- CONCURRENTLY and ANALYZE statements require no locks on the tables involved. All ALTER TABLE statements require an exclusive lock on the table, so monitor locking carefully on those steps.
- Allow script to work with non-release versions of PostgreSQL (ex 9.6beta2).
2.2.0
- Account for fillfactor settings in both tables and indexes when determining wasted space. This will likely cause an expected 10% drop in most index bloat reported (if fillfactor hasn't been adjusted manually).
- The "simple" output mode will automatically account for the fillfactor when outputing the wasted space values. For dict & json output, the original raw values of all statistics are given along with that object's fillfactor and relpage count so it can be calculated manually.
- Added new "fillfactor" & "relpages" columns to bloat statistics tables. Run --create_stats_table again to recreate the table with the new, required columns. Note this will wipe out any data contained in the stats table so if you need to preserve it, do so before updating script to this version.
- Added new --noanalyze option. Accounting for fillfactor requires an accurate page count estimate for each object, so it is now analyzed before scanning for bloat. This will cause this script to take slightly longer, but provide more accurate bloat statistics. You can set this option to skip the analyze. However, unless a recent analyze was done, this may cause the bloat report to be less accurate.
- pgstattuple does not currently support BRIN indexes. Excluded for now (Pull Request #6).
- Fixed mismatch in json/dict output. It was giving the dead_tuple_percent value in the free_percent key (Pull Request #6).
2.1.1
- Improve backward compatibility for PostgreSQL versions older than 9.3 (pg_index.indislive did not exist before then) (Github Issue #5)
2.1.0
- Fixed --bloat_schema & --pgstattuple_schema options so they actually work (Github Issue #4)
- More fine grained filtering using --exclude_object_file. Each line in the file is now a CSV entry that can provide additional filtering per object in addition to -s, -p & -z. See README for examples (Github Issue #3).
- Added json & jsonpretty --format modes.
- Changed dict --format mode to just output an unparsed dictionary blob. Previously each object was separated with a return, but this made it more difficult to actually use as a dictionary object. If you need a human readable format of the raw details, use --format=jsonpretty
- Check for object existence again before scanning. Avoids issues during long run times with objects that have been dropped since the initial object list was created.
- If an index is a primary key, the objecttype column in the bloat statistics table will now be "index_pk" to distinguish them from just a normal index.
- The --create_stats_table option will now attempt to drop the tables it creates first before creating them. Will allow any future changes to the stats table to be implemented easier.
- Changed --norescan option to --noscan for simplicity.
2.0.2
- Skip over temp tables and indexes that are marked as dropped (Github Issue #2).
2.0.1
- Skip over GIN indexes since there's no current way to find bloat with them in pgstattuple. Looking for contributions to fix support this https://github.com/keithf4/pg_bloat_check/issues/1
2.0.0
- Complete rewrite using pgstattuple contrib module instead of check_postgres.pl query
- See --help for more information and blog post at https://www.keithf4.com/checking-for-postgresql-bloat/