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

31–40 of 81 posts

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

#31

Post by jeffbee »

de6u99er wrote: post #10

When I did my Oracle DBA training 15 years ago, I learnt about database reorgs. It means basically exporting your database (or tables) and importing it again. What happens is that deleted data which doesn't necessarily free up space (Oracle reuses the freed up space sometimes) doesn't get exported. https://www.iri.com/blog/vldb-operations/database-reorgs-why... https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_Q…

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.

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

#32

Post by zeroimpl »

mnw21cam wrote: post #26
maweki wrote: post #17

Earlier quoted context omitted.

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?

They support index-only scans now, so there is some sort of optimization which bypasses the table lookup, at least in certain cases.

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

#33

Post by AbacusAvenger »

jeffbee wrote: post #31
de6u99er wrote: post #10

When I did my Oracle DBA training 15 years ago, I learnt about database reorgs. It means basically exporting your database (or tables) and importing it again. What happens is that deleted data which doesn't necessarily free up space (Oracle reuses the freed up space sometimes) doesn't get exported. https://www.iri.com/blog/vldb-operations/database-reorgs-why... https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_Q…

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?

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

#34

Post by SomeHacker44 »

latch wrote: post #20
mjw1007 wrote: post #4

Summary: 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.

Another benefit of partial indexes is to limit a unique constraint: create index users_email on users(email) where status != 'delete'

Be very careful, then, as the optimizer will (usually?) not use the index if the condition is not part if the query.

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

#35

Post by voganmother42 »

brianberns wrote: post #19

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

pretty well documented behavior as far as concurrent: https://www.postgresql.org/docs/current/sql-createindex.html...

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

#36

Post by dialamac »

AbacusAvenger wrote: post #33
jeffbee wrote: post #31

Earlier 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?

It never did.

The difference is that you can use logical replication since 10 to prevent downtime during upgrade.

Which if you were using it a year ago could have been done.

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

#38

Post by Uberphallus »

AbacusAvenger wrote: post #33
jeffbee wrote: post #31

Earlier 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?

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.

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

#39

Post by brianwawok »

Tostino wrote: post #13
brianwawok wrote: post #12

Earlier quoted context omitted.

A vacuum full basically does this for a table, copying the data from location A to location B, cleaning up junk. I think index rebuilding may take a separate command?

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?

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

#40

Post by matsemann »

> 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 cost is probably negligible compared to spending more energy fixing it..

Post reply on HN