Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

An unexpected find that freed 20GB of unused index space in PostgreSQL

hakibenita.com

Post reply on HN
Print view View original

71–80 of 81 posts

Re: An unexpected find that freed 20GB of unused index space in PostgreSQL

#71

Post by Buttons840 »

brianberns wrote: post #63
striking wrote: post #50

Earlier quoted context omitted.

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.

I had the same thoughts. Is there any reason to keep it? Is there any scenario where that invalid index could be useful?

Re: An unexpected find that freed 20GB of unused index space in PostgreSQL

#73

Post by [deleted] »

mulander wrote: post #46
GordonS wrote: post #45

Earlier quoted context omitted.

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

[deleted]

Re: An unexpected find that freed 20GB of unused index space in PostgreSQL

#74

Post by deathanatos »

> Coming from Oracle, I was always taught that NULLs are not indexed

That left me wondering how, if all indexes are by default partial in Oracle… how does one make an unpartial? nonpartial? index.

https://use-the-index-luke.com/sql/where-clause/null/index

Apparently, you add a computed column to the index that just computes a constant value. And single non-null column then causes the nulls in other columns to get indexed, it's only if the whole tuple is composed of nulls that it gets left out.

That also seems like a bug waiting to happen; someone inverts a query to find unset (NULL) entries, and now you're doing a table scan.

…but it seems also like a form of brain rot, induced by a particular implementation, e.g., similar to how I've had MySQL users ask how to make a key on a table. Where a "key" is an index, it's just that MySQL by default uses the word "key" to mean index, instead of … key¹. (The query language even supports "INDEX" in place of "KEY", but things like "SHOW TABLE" default to the "wrong" (linguistically, not programmatically) word.) And then you might have to de-tangle why these two are different concepts, how they're different. It's very Arrival, in the sense of language (mis-)shaping perception.

¹a key is a set of columns that are sufficient to identify a row. The primary such set of columns is … the primary key. An index can index a key (if more than one exists within a table), but it doesn't have to.

Re: An unexpected find that freed 20GB of unused index space in PostgreSQL

#75

Post by pilif »

matsemann wrote: post #40

> Clear bloat in tables Ohh, we've had issues with this. We have this table that's mostly ephemeral data, so rows are constantly inserted and then deleted after a certain amount of time. Due to a bug the deletion didn't work for a while and the db grew very large. Fixed the deletion, but no amount of vacuuming actually allows us to fully reclaim that space so we don't have to pay for it. At the same time the extra co…

have a look at pg_repack. That'll solve it for you.

> but no amount of vacuuming actually allows us to fully reclaim that space

a full vacuum would. but it would also lock the table for the duration (which is something pg_repack won't do)

Re: An unexpected find that freed 20GB of unused index space in PostgreSQL

#76

Post by matsemann »

pilif wrote: post #75
matsemann wrote: post #40

> Clear bloat in tables Ohh, we've had issues with this. We have this table that's mostly ephemeral data, so rows are constantly inserted and then deleted after a certain amount of time. Due to a bug the deletion didn't work for a while and the db grew very large. Fixed the deletion, but no amount of vacuuming actually allows us to fully reclaim that space so we don't have to pay for it. At the same time the extra co…

have a look at pg_repack. That'll solve it for you. > but no amount of vacuuming actually allows us to fully reclaim that space a full vacuum would. but it would also lock the table for the duration (which is something pg_repack won't do)

Yup, locking the table is off the table so to speak, heh. Will take a look, thanks.

Re: An unexpected find that freed 20GB of unused index space in PostgreSQL

#77

Post by Uberphallus »

avereveard wrote: post #49
Uberphallus wrote: post #38

Earlier quoted context omitted.

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 m…

Don't take my word for it, but I think those extensions aren't considered "external" as they're part of the PGSQL distribution.

It'd be something like nominatim.so, which is external to PGSQL and (AFAIK) has its own format for certain types of data and indexes.

Re: An unexpected find that freed 20GB of unused index space in PostgreSQL

#78

Post by throwdbaaway »

mulander wrote: post #43

Partial 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…

In your example, the WHERE clause of the query and the partial index didn't match logically, i.e. the query may return rows that are not indexed. There's nothing that postgres can do, and I wouldn't classify the behavior as peculiar.

On the other hand, with sqlite, the WHERE clause of the query and the partial index must match *literally*. So let's say you have a partial index with WHERE severity_id != 0, and a query with WHERE severity_id = 1. All the rows with severity_id = 1 are already indexed, but the engine is still not able to make use of the partial index. This one bit us hard.

Re: An unexpected find that freed 20GB of unused index space in PostgreSQL

#79

Post by striking »

brianberns wrote: post #63
striking wrote: post #50

Earlier quoted context omitted.

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.

It can't be atomic if it's done `CONCURRENTLY`. You can't even do that in a transaction, Postgres will tell you it won't work. By using `CONCURRENTLY`, you're making it clear that you will handle atomicity yourself, and as a result Postgres needs to provide you with the tools to do it.

Want real atomicity? Don't use `CONCURRENTLY`.

Re: An unexpected find that freed 20GB of unused index space in PostgreSQL

#80

Post by jeltz »

Buttons840 wrote: post #71
brianberns wrote: post #63

Earlier quoted context omitted.

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.

I had the same thoughts. Is there any reason to keep it? Is there any scenario where that invalid index could be useful?

The reason is to make sure aborting the command is fast and safe. PostgreSQL has no infrastructure for spawn background jobs to clean up stuff like this on aborted queries. A patch would probably welcome if it could be done in a good way.
Post reply on HN