6 ms·

Author here - it's difficult to provide a single number to summarize what we've observed re: CPU, but one data point is that average CPU utilization across our

by jhk727 5y ago

Author here - it's difficult to provide a single number to summarize what we've observed re: CPU, but one data point is that average CPU utilization across our cluster increased from ~40% to ~50%. This effect is more pronounced during NA daylight hours. Worth noting that part of the reason this is relatively low impact for our read queries is that the hot portion of our dataset is usually in Postgres page cache where the data is already decompressed (we see a 95-98% cache hit rate under normal conditions). We've noticed the impact more for operations that involve large scans - in particular, backups and index builds have become more expensive.

infogulch 5y ago
Hey thanks for the clarification. That seems like a worthwhile tradeoff in your case. For backups in particular, are ZFS snapshots alone not suitable to serve as a backup? Is there something else that the pg backup process does that is not covered by a "dumb" snapshot?
- jhk727 5y ago
  We use wal-g and extensively leverage its archive/point-in-time restore capabilities. I think it would be tricky to manage similar functionality with snapshots (and possibly more expensive if archival involved syncing to a remote pool). That being said, wal-g has worked well enough for us that we haven't put a ton of time into investigating alternatives yet, so I can't say for sure whether snapshots would be a better option.
  - derefr 5y ago
    I'd personally recommend pgBackRest as a wal-g replacement. We (Covalent) started with wal-g ourselves, but pgBackRest does full backup and restore so much faster. Besides the parallelism (which is great), pgBackRest's backups are manifests, symbolically mapping to the individual objects in storage that may have come from previous backups. Which means that a differential or incremental backup doesn't need to be "replayed after" a full backup, but instead is just like a git commit, pointing to some newer and some older objects. Also, auto-expiry of no-longer-needed WAL segments (that we use due to our reliance on async hot standbys) along with previous backups is pretty great. And we haven't even started taking advantage of pgBackRest's ability to do incremental restore — i.e. to converge a dataset already on disk, that may have fallen out of sync, with as few updates as possible. We're thinking we could use this to allow data science use-cases that would involve writing to a replica, by promoting the replica, allowing the writes, and then converging it back to an up-to-date replica after the fact.
TedDoesntTalk 5y ago
how/why did you choose Postgres over MariaDB? I am facing such a decision now.
- jamesfinlayson 5y ago
  Not MariaDB but after working with Postgres for six years I'm now using MySQL and... the error messages are useless, and comparing different types works in strange ways rather than failing like in Postgres.
  - 1500100900 5y ago
    To add, execution plans presented by MariaDB are also nearly useless.
  - TedDoesntTalk 5y ago
    User grants in Maria/MySQL drive me nuts. I hope it is better in Postgres
- anticodon 5y ago
  After working for years with both, I'd say that PostgreSQL is much more friendlier to the developer and more pleasant to work with. In any area: documentation, features, error messages, available SQL features, available extensions, available docs, available books. One tiny example: I prefer to work with databases using CLI interfaces (mysql and psql). psql CLI is a tool which is pleasant to use, has no bugs in the interface and it even gets improvements from time to time. mysql CLI is awful to use (e.g. doesn't display long lines properly, has difficulties with history editing, etc) and looks like there wasn't a single improvement since 1996 (I'm sure there were, I just never felt the effect of such improvements).
  - NegativeLatency 5y ago
    I’ve been using a Postgres foreign data wrapper to interact with a MySQL database and it’s much nicer for interactive use.
  - 5y ago
eptcyka 5y ago
Have you looked at postgres row/column compression? Obviously, compressing the same data twice won't be too helpful, but maybe there are more wins to be had.

by jhk727 5y ago

infogulch 5y ago

Hey thanks for the clarification. That seems like a worthwhile tradeoff in your case. For backups in particular, are ZFS snapshots alone not suitable to serve as a backup? Is there something else that the pg backup process does that is not covered by a "dumb" snapshot?

jhk727 5y ago
We use wal-g and extensively leverage its archive/point-in-time restore capabilities. I think it would be tricky to manage similar functionality with snapshots (and possibly more expensive if archival involved syncing to a remote pool). That being said, wal-g has worked well enough for us that we haven't put a ton of time into investigating alternatives yet, so I can't say for sure whether snapshots would be a better option.
- derefr 5y ago
  I'd personally recommend pgBackRest as a wal-g replacement. We (Covalent) started with wal-g ourselves, but pgBackRest does full backup and restore so much faster. Besides the parallelism (which is great), pgBackRest's backups are manifests, symbolically mapping to the individual objects in storage that may have come from previous backups. Which means that a differential or incremental backup doesn't need to be "replayed after" a full backup, but instead is just like a git commit, pointing to some newer and some older objects. Also, auto-expiry of no-longer-needed WAL segments (that we use due to our reliance on async hot standbys) along with previous backups is pretty great. And we haven't even started taking advantage of pgBackRest's ability to do incremental restore — i.e. to converge a dataset already on disk, that may have fallen out of sync, with as few updates as possible. We're thinking we could use this to allow data science use-cases that would involve writing to a replica, by promoting the replica, allowing the writes, and then converging it back to an up-to-date replica after the fact.

TedDoesntTalk 5y ago

how/why did you choose Postgres over MariaDB? I am facing such a decision now.

jamesfinlayson 5y ago
Not MariaDB but after working with Postgres for six years I'm now using MySQL and... the error messages are useless, and comparing different types works in strange ways rather than failing like in Postgres.
- 1500100900 5y ago
  To add, execution plans presented by MariaDB are also nearly useless.
- TedDoesntTalk 5y ago
  User grants in Maria/MySQL drive me nuts. I hope it is better in Postgres
anticodon 5y ago
After working for years with both, I'd say that PostgreSQL is much more friendlier to the developer and more pleasant to work with. In any area: documentation, features, error messages, available SQL features, available extensions, available docs, available books. One tiny example: I prefer to work with databases using CLI interfaces (mysql and psql). psql CLI is a tool which is pleasant to use, has no bugs in the interface and it even gets improvements from time to time. mysql CLI is awful to use (e.g. doesn't display long lines properly, has difficulties with history editing, etc) and looks like there wasn't a single improvement since 1996 (I'm sure there were, I just never felt the effect of such improvements).
- NegativeLatency 5y ago
  I’ve been using a Postgres foreign data wrapper to interact with a MySQL database and it’s much nicer for interactive use.
- 5y ago

eptcyka 5y ago

Have you looked at postgres row/column compression? Obviously, compressing the same data twice won't be too helpful, but maybe there are more wins to be had.