The article includes a couple of useful queries unrelated to the "find" and led me to these useful bloat-detection resources
https://wiki.postgresql.org/wiki/Show_database_bloat
https://github.com/ioguix/pgsql-bloat-estimation
An unexpected find that freed 20GB of unused index space in PostgreSQL
61–70 of 81 posts
Re: An unexpected find that freed 20GB of unused index space in PostgreSQL
#62Earlier quoted context omitted.
Dumping and reloading databases used to be mandatory for major postgresql updates, which is one of the reasons postgresql wasn't suitable for production workloads until recently and also why it resisted fixing bugs in vacuum, index, and compaction for many years.
Whoah, that's news to me. I used PostgreSQL fairly recently (a year or so ago?) and ended up abandoning it after I was forced to do the export/import dance through a few version upgrades. When did that requirement go away?
Ever since there's pg_upgrade available. Since 8.4 or so - https://www.percona.com/blog/2019/04/12/fast-upgrade-of-lega...
Dump and reload is ok if you have a small database or can afford hours of downtime... if not, use pg_upgrade.
Re: An unexpected find that freed 20GB of unused index space in PostgreSQL
#63> REINDEX INDEX CONCURRENTLY index_name; > If for some reason you had to stop the rebuild in the middle, the new index will not be dropped. Instead, it will be left in an invalid state and consume space. Well, that sure sounds like a bug in PostreSQL to me.
Well, you can't just delete it. It is an object that was created by some user and there's no good reason for the database to get rid of it automatically. The database keeps a record of the invalid thing, even though it is invalid.
The good reason to get rid of it automatically: It takes up space.
Is there any good reason to keep it? (The fact that it was "created by some user" doesn't seem like much of a reason.)
IMHO, creating an index should be atomic: Either you end up with a valid index, or you end up with nothing.
Re: An unexpected find that freed 20GB of unused index space in PostgreSQL
#64Earlier quoted context omitted.
No, I don't think statistics can let you get away with this. Databases are concurrent, you can't guarantee that a different session will not insert a record that invalidates your current statistics. You could argue that it should be able to use it if the table has a check constraint preventing severity_id above 7 being ever inserted. That is something that could be done, I don't know if PostgreSQL does it (I doubt it…
> No, I don't think statistics can let you get away with this. Databases are concurrent, you can't guarantee that a different session will not insert a record that invalidates your current statistics. Of course you can't make a guarantee like that, but why would you need to? Statistics are there to guide planner choices, not make cast iron predictions.
Let us say that in our example table we have 100 000 records with severit_id Statistics claim 100k id 7. The last 3 updates could have happened right before our query, the statistics didn't update yet.
Let us assume that we blindly trust the statistics and they currently state that there are absolutely no values with severity_id > 7 and you have a query WHERE severity_id != 7 and a partial index on severity_id If you trust the statistics and actually use the index the rows containing severity_id = 8 will never be returned by the query even if they exist. So by using the index you only scan 100 k rows and never touch the remaining ~200k. However this query can't be answered without scanning all ~300k records. This means, that on the same database you would get two different results for the exact same query if you decided to drop the index after the first run. The database can't fall back and change the plan during execution.
Perhaps I misunderstood you originally. I thought you suggested that the database should be able to know that it can still use the index because currently the statistics claim that there are no records that would make the result incorrect. You are of course correct, that the statistics are there to guide the planner choices and that is how they are used within PostgreSQL - however some plans will give different results if your assumption about data are wrong.
Re: An unexpected find that freed 20GB of unused index space in PostgreSQL
#65Earlier quoted context omitted.
No, I don't think statistics can let you get away with this. Databases are concurrent, you can't guarantee that a different session will not insert a record that invalidates your current statistics. You could argue that it should be able to use it if the table has a check constraint preventing severity_id above 7 being ever inserted. That is something that could be done, I don't know if PostgreSQL does it (I doubt it…
> No, I don't think statistics can let you get away with this. Databases are concurrent, you can't guarantee that a different session will not insert a record that invalidates your current statistics. Of course you can't make a guarantee like that, but why would you need to? Statistics are there to guide planner choices, not make cast iron predictions.
Because the database has to decided whether or not to use the index. If it decides to use the index, and there are values above 7, then it will misbehave (the query will miss those results). Now of course the database could then scan the rows for values above 7 it missed but at that point there's no point in using the index and you might as well have row scanned for the original query.
As a result, the database has to be 100% sure that there are no values _at all_ above 7 to safely and efficiently use the index, ex. when there's a constraint.
Re: An unexpected find that freed 20GB of unused index space in PostgreSQL
#66Earlier quoted context omitted.
Vacuum full does a index rebuild automatically. Since a vacuum full builds an entire new heap table, the old indexs are all pointing to the incorrect locations for all tuples, so it has no choice but to rebuild.
Excellent there you go. Is there a way to just do the index build part, short of dropping an index and adding it back?
This is described about a quarter the way into TFA.
> REINDEX INDEX CONCURRENTLY index_name;
(A vanilla REINDEX will lock the table, preventing writes, while it runs. The CONCURRENT creates a new index, replicates any updates to the original while it does so, and then does an atomic switcheroo at the end.)
Re: An unexpected find that freed 20GB of unused index space in PostgreSQL
#67Summary: if you have an index on a column which is mostly NULL, consider using a partial index covering only the records where it's non-NULL.
That's the part they sold in the title, but there's a bunch of other useful stuff for someone operating Postgres in production, something I'll need to do in a few months.
Re: An unexpected find that freed 20GB of unused index space in PostgreSQL
#68>There are several ways to rebuild a table and reduce bloat:
>Re-create the table: Using this method as described above often requires a lot of development, especially if the table is actively being used as it's being rebuilt.
>Vacuum the table: PostgreSQL provides a way to reclaim space occupied by dead tuples in a table using the VACUUM FULL command. Vacuum full requires a lock on the table, and is not an ideal solution for tables that need to be available while being vacuumed:
This is confusing to me, i thought postgre was suppose to be better then mysql, yet mysql has a non-locking command to recreate a table. it has like 3 that would fit here, AND deal with the indexes in one command.
Re: An unexpected find that freed 20GB of unused index space in PostgreSQL
#69The chart seems to show an uptick of 2GB, not 20GB. Am I missing something?
The note is in a bad place, the whole slope is the 20gb.
Re: An unexpected find that freed 20GB of unused index space in PostgreSQL
#70Are there things you can do to check a MySQL/mariadb instance? I see a command called mysqlcheck I may have to investigate.