3 ms·

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 tab

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/ http://reorg.github.io/pg_repack/

smilliken 11y ago
Yeah, pg_repack works great. I've never had an issue with it.
cldellow 11y ago
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.
- e12e 11y ago
  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).
  - cldellow 11y ago
    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.
    - codinghorror 11y ago
      People still use spinning rust for databases?
      - dikaiosune 11y ago
        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.
RyanGWU82 11y ago
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.
nierman 11y ago
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).

smilliken 11y ago

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

cldellow 11y ago

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.

e12e 11y ago
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).
- cldellow 11y ago
  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.
  - codinghorror 11y ago
    People still use spinning rust for databases?
    - dikaiosune 11y ago
      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.

RyanGWU82 11y ago

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.

nierman 11y ago

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