Hacker News Reader

Comment by danmaz74 | Hacker News Reader

Tangential: does anybody know how dependable is pg_repack[1]? I just discovered it and the promise is great - being able to do a VACUUM FULL, and even move tables around, without locking them and thus without downtime - but I'm not sure what are the risks in using it in production.

[1] http://reorg.github.io/pg_repack/

View on HN

We use it on medium size tables (around ~100M rows and ~100GB each). No problems. The perf boost we get from clustering rows in the right spot on the disk is huge.

Out of curiosity, as you apparently have some real data and real use-cases - have you benchmarked SSD vs disk for your use-case? In particular, do you still see a (comparable, or not) speed-up from better clustering of rows on SSD? (I would guess not, unless you have a lot of fragmentation at less than SSD write-size (typically 4k?) - and AFAIK postgres should already be trying to fix that for you, by how it lays out data on disk. But SSD do have somewhat better sequential read than random-read, even if not as dramatic as spinning disks).

There's still a speedup, but it's dwarfed by the magnetic->SSD boost.

We do have fragmentation within a block. Our records on the heap are about 150 bytes, so maybe 30 rows per 4K page. In the pathological case where there is 1 row per page due to fragmentation, queries have to fetch 30x the number of blocks, which is a killer on a spinny disk.

People still use spinning rust for databases?

I have to deal with a couple of plattered databases. It's not optimal, but there's usually some cost constraint, or there are only 100 users on it a week. Or both, in which case it's no big deal and it's very very cheap.

We've used it occasionally on a ~400GB table and it's worked perfectly for us too. We run it on EC2 i2.8xlarge instances and it finishes in under 2 hours. But it doesn't use very much CPU or I/O, so we could probably run it just as well on a smaller instance like an i2.2xlarge.

One downside is that you need enough free space on your disk to hold the packed copy of the table, so you need to run it before your disk gets too full. The last couple of times we've waited too long, so we've had to move the data to a larger host before we could repack it.

Yeah, pg_repack works great. I've never had an issue with it.

I'd like to make more use of it but it can't "repack" tables that have gist indices (e.g., postgis spatial indices, etc).

Reply on news.ycombinator.com