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

21–30 of 81 posts

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

#21

Post by crazygringo »

malinens wrote: post #5

Too bad MySQL does not have partial indexes. We have one huge table I want to add some indexes for specific cases (for max 1% of records) but server will not have enough memory for it if I add those indexes for all records :/

As long as you've got primary keys on the huge table, there's a hacky solution -- create a second table with columns for just the first table's primary key and the columns you're indexing and your desired index, and ensure you always write/update/delete both tables simultaneously using transactions. Then when needed, use the index on the second table and join it to your first with the primary key.

Annoying, but it should work for most queries I'd expect without too much SQL.

I've definitely "rolled my own indexing" like this in the past, though it's more often been duplicating strings into a custom "collation" or other transformations.

Another solution is simply to split your table in two, with the same columns in both, and the index only on one of the tables. But of course that really depends on your business logic -- queries that need to retrieve data from both tables together can get pretty hairy/slow, and if you've got auto-incrementing PKEY's then avoiding collisions between the two tables can be tricky on its own. So this is definitely the less general solution.

Of coure it certainly would be nicer if MySQL supported partial indexes. It seems so useful, I'm surprised it didn't happen long ago.

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

#22

Post by [deleted] »

mattashii wrote: post #9
ivoras wrote: post #2

Is 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.

Not per se _as effective_, but it will still help a lot. NULL tuples pre-pg13 take ~ 14 bytes each, and 18 bytes when aligned. (= 2 (ItemID, location on page) + 6 (TID) + 2 (t_info) + 4 (NULL bitmap) + 4 bytes alignment). When deduplication is enabled for your index, then your expected tuple size becomes just a bit more than 6 bytes (~ 50 TIDs* in one tuple => 2 (ItemId) + 6 (alt tid) + 2 (t_info) + 4 (null bitmap) +…

[deleted]

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

#23

Post by lucian1900 »

crazygringo wrote: post #21
malinens wrote: post #5

Too bad MySQL does not have partial indexes. We have one huge table I want to add some indexes for specific cases (for max 1% of records) but server will not have enough memory for it if I add those indexes for all records :/

As long as you've got primary keys on the huge table, there's a hacky solution -- create a second table with columns for just the first table's primary key and the columns you're indexing and your desired index, and ensure you always write/update/delete both tables simultaneously using transactions. Then when needed, use the index on the second table and join it to your first with the primary key. Annoying, but it sh…

The first approach is one of the steps towards normalising a database.

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

#24

Post by crazygringo »

lucian1900 wrote: post #23
crazygringo wrote: post #21

Earlier quoted context omitted.

As long as you've got primary keys on the huge table, there's a hacky solution -- create a second table with columns for just the first table's primary key and the columns you're indexing and your desired index, and ensure you always write/update/delete both tables simultaneously using transactions. Then when needed, use the index on the second table and join it to your first with the primary key. Annoying, but it sh…

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 powerful or featured enough.

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

#26

Post by mnw21cam »

maweki wrote: post #17
mnw21cam wrote: post #6

Earlier quoted context omitted.

Agreed. To explain why this is the case, consider that table in the story that had 99% NULL values. If you were to try to run "SELECT FROM table WHERE column IS NULL", then Postgresql wouldn't use the index anyway, because it would be faster to just read sequentially through the entire table and filter out the 1% that don't match.

That would highly depend on what you select. If the query could be answered by index only, like COUNT(*), it would probably use the index. You are right if you want to query any data from that row that's not in the index.

I might be out of touch a little with Postgres (I last used it in 2010), but my impression was that COUNT(*) still needed to scan the actual table in order to exclude rows that had been deleted in a transaction, due to the way multi-version concurrency worked. Is this something that has been improved since then?

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

#27

Post by zufallsheld »

tantalor wrote: post #25

Graphing "free storage" is meaningless and confusing; it should be "used storage". Available storage depends on usage and capacity. Edit: I meant for this article; of course I believe it is useful to track this in practice.

If you pay for a fixed amount of storage and only use it partially, you should also monitor free storage sdso you know when you waste it.

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

#28

Post by tbrock »

boomer918 wrote: post #15

Partial indexes can flip query plans if the covered part becomes so small that it won't be represented when sampled by the stats collector. The planner could then decide that the index scan isn't worth it and could try an alternative less efficient index if one exists.

Yeah and sadly using the index in those scenarios could be even more worth it due to the high selectivity it has.

Is PG smart enough to avoid that if the query patterns are frequently or exclusively covered by the index?

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

#29

Post by fanf2 »

tantalor wrote: post #25

Graphing "free storage" is meaningless and confusing; it should be "used storage". Available storage depends on usage and capacity. Edit: I meant for this article; of course I believe it is useful to track this in practice.

Free storage is what matters because it makes it very obvious when you are getting close to a disk-full outage.

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

#30

Post by AnotherGoodName »

tantalor wrote: post #25

Graphing "free storage" is meaningless and confusing; it should be "used storage". Available storage depends on usage and capacity. Edit: I meant for this article; of course I believe it is useful to track this in practice.

Used makes sense for getting a feeling for pure performance (smaller the better as its likely to be in memory).

Available makes sense for knowing when things will just plain break (reaching 0 = write failure for a DB).

>Every few months we get an alert from our database monitoring to warn us that we are about to run out of space.

In this case they were avoiding their DB server breaking. They didn't do this for performance reasons.

Post reply on HN