The included query for finding which indexes in your database could benefit from a partial index is amazing. Thanks for putting the extra effort into this post.
An unexpected find that freed 20GB of unused index space in PostgreSQL
41–50 of 81 posts
Re: An unexpected find that freed 20GB of unused index space in PostgreSQL
#42Earlier 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?
I believe pg_repack can do that.
Re: An unexpected find that freed 20GB of unused index space in PostgreSQL
#43Partial indexes are amazing but you have to keep in mind some pecularities.
If your query doesn't contain a proper match with the WHERE clause of the index - the index will not be used. It is easy to forget about it or to get it wrong in subtle ways. Here is an example from work.
There was an event tracing structure which contained the event severity_id. Id values 0-6 inclusive are user facing events. Severity 7 and up is debug events. In practice all debug events were 7 and there were no other values above 7. This table had a partial index with WHERE severity_id Another thing is that HOT updates in PostgreSQL can't be performed if the updated field is indexed but that also includes being part of a WHERE clause in a partial index. So you could have a site like HN and think that it would be nice to index stories WHERE vote > 100 to quickly find more popular stories. That index however would nullify the possiblity of a hot update when the vote tally would be updated. Again, not a problem but you need to know the possible drawbacks.
That said, they are great when used for the right purpose. Kudos to the author for a nice article!
Re: An unexpected find that freed 20GB of unused index space in PostgreSQL
#44Is the partial index technique to avoid indexed NULL data as effective for PostgreSQL 13+? It looks like in v13+ PostgreSQL could create a single leaf for NULL data and just store row pointers within it, which should reduce data sizes at least a bit.
He actually mentioned index de-duplication earlier: https://hakibenita.com/postgresql-unused-index-size#activati... If I had to guess, I would say that it doesn't accomplish anything (or as much as you'd think) for null values simply because there is no real data to store in either approach, you just have a bunch of pointers either way.
NULL values are not special as far as deduplication is concerned. They use approximately as much disk space as a non-NULL integer column without deduplication, and compress just as well with deduplication. Deduplication is effective because it eliminates per-tuple overhead, so you see most of the benefits even with index tuples that naturally happen to have physically small keys. You'll still get up to a 3x decrease in storage overhead for the index provided there is low cardinality data (and not necessarily that low cardinality, ~10 or so tuples per distinct value will get you there).
The NULL issue is documented directly -- see the "Note" box here:
https://www.postgresql.org/docs/devel/btree-implementation.h...
Re: An unexpected find that freed 20GB of unused index space in PostgreSQL
#45Partial indexes are amazing but you have to keep in mind some pecularities. If your query doesn't contain a proper match with the WHERE clause of the index - the index will not be used. It is easy to forget about it or to get it wrong in subtle ways. Here is an example from work. There was an event tracing structure which contained the event severity_id. Id values 0-6 inclusive are user facing events. Severity 7 and…
> The database is obviously not able to tell that there will never be any values above 7
You say "obviously", but with updated statistics this is the exactly the kind of thing you might expect the planner to know and aid index decisions.
I'm a huge fan of Postgres, coming to it around 5 years ago from at least 10 previous years with SQL Server, but I have hit a few things like this in that time. IME the planner is much more fickle about how you specify your predicates than SQL Server is.
Re: An unexpected find that freed 20GB of unused index space in PostgreSQL
#46Partial indexes are amazing but you have to keep in mind some pecularities. If your query doesn't contain a proper match with the WHERE clause of the index - the index will not be used. It is easy to forget about it or to get it wrong in subtle ways. Here is an example from work. There was an event tracing structure which contained the event severity_id. Id values 0-6 inclusive are user facing events. Severity 7 and…
> The database is obviously not able to tell that there will never be any values above 7 You say "obviously", but with updated statistics this is the exactly the kind of thing you might expect the planner to know and aid index decisions. I'm a huge fan of Postgres, coming to it around 5 years ago from at least 10 previous years with SQL Server, but I have hit a few things like this in that time. IME the planner is mu…
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) or how feasable it would be.
Is SQL Server able to make an assumption like that purely based on statistics? Genuine question.
Re: An unexpected find that freed 20GB of unused index space in PostgreSQL
#47Earlier quoted context omitted.
The first approach is one of the steps towards normalising a database.
Actually it's the opposite of database normalization. Normalizing removes data redundancy. This adds data redundancy. When I design a database structure, it's common to start with the most normalized representation possible. And then to denormalize the minimum necessary for performance reasons -- duplicating rows and/or columns just like here so certain data can be retrieved more quickly, whenever indexes aren't powe…
I think what lucian1900 may be thinking is that instead of
create table purchase_order (
id int primary key,
ordered_on timestamptz not null,
customer_id int not null references customer,
canceled_on timestamptz
);
create table purchase_order (
id int primary key,
ordered_on timestamptz not null,
customer_id int not null references customer
);
create table order_cancelation (
order_id int primary key references purchase_order,
canceled_on timestamptz not null
);
Re: An unexpected find that freed 20GB of unused index space in PostgreSQL
#48Partial indexes are amazing but you have to keep in mind some pecularities. If your query doesn't contain a proper match with the WHERE clause of the index - the index will not be used. It is easy to forget about it or to get it wrong in subtle ways. Here is an example from work. There was an event tracing structure which contained the event severity_id. Id values 0-6 inclusive are user facing events. Severity 7 and…
> The database is obviously not able to tell that there will never be any values above 7 You say "obviously", but with updated statistics this is the exactly the kind of thing you might expect the planner to know and aid index decisions. I'm a huge fan of Postgres, coming to it around 5 years ago from at least 10 previous years with SQL Server, but I have hit a few things like this in that time. IME the planner is mu…
All statistics in postgres are considered best effort guidance. Even if the statistics are wrong it can never impact the correctness of the results.
Re: An unexpected find that freed 20GB of unused index space in PostgreSQL
#49Earlier quoted context omitted.
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?
Since 9 there's pg_upgrade, personally I never had an issue and it was very fast, so the downtime is in the order of a few minutes, which is ok for my usecase. YMMV.
"pg_upgrade does its best to make sure the old and new clusters are binary-compatible, e.g., by checking for compatible compile-time settings, including 32/64-bit binaries. It is important that any external modules are also binary compatible, though this cannot be checked by pg_upgrade."
This makes me very nervous tho, I've at least two exts (trigrams and gists) maybe they work, maybe not, I just prefer the ease of mind of a old fashioned dump.
Re: An unexpected find that freed 20GB of unused index space in PostgreSQL
#50> 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.