Hacker News Reader

Comment by ak4g | Hacker News Reader

That compression ratio is one I'd love to have. PG's built-in data compression is limited to large values in a single row - if you have a hundred billion 100-byte tuples, TOAST isn't going to help.

The closest thing I know of to achieve something comparable with PG would be to use a data directory mounted on something with transparent filesystem-level compression (meaning, practically speaking, ZFS). This gives you the less-than-ideal choice of a not-mainline-linux filesystem (for your database's data directory, which is worth being nervous about) or running an OpenSolaris descendant, which is a big departure for plenty of people who have only ever run production dbs on Linux servers.

View on HN

On a tangent: I think it's rather sad that many nerds aren't prepared to try a different UNIX, when we're talking the lifeblood of their business: data.

ZFS is the only filesystem it's reasonable to trust critical data on right now (I can't think of any other OSS self-validating Merkle trees that have been hammered in production for nearly a decade...), yet somehow some minor differences in Unicies trumps that.

I'm going to make a bold claim I know will stir the nest, but I feel confident in making given all the bad shit most filesystems miss: if you're not running your OSS RDBMS on ZFS right now, and you don't have compelling specialized needs to explain why not, you shouldn't be let near a production DB due to plain negligence and/or incompetence.

It depends on how you slice it. If your expertise in this system is less than your others, it could cause real problems down the line. If a mistake was made in the setup, maintenance or recovery of the system because it's different, it would be just as easy to say it was negligent to use it instead of something that was well understood by your team. Trade-offs like this are made all the time, to claim negligence is to ignore the complexities of the situation.

This. I'm more than willing to try different UNIXes, and I have. But when we're talking about running a business-critical database, it's a hard requirement that both myself and my teammates are able to maintain, keep patched, and generally competently administer the system. Trading an OS that my team has decades of combined experience running in production with for one where we collectively have zero production experience is a non-starter.

A good DBMS includes a bunch of integrity-preserving measures to deal with data corruption. No reason not to run your DBMS on ZFS as far as I'm aware (edit: at least if the workload does not trigger excessive fragmentation), but I'd reserve the word 'negligence' for, say, not using an appropriate backup solution.

Something funny about this: where are the OSS RDBMSs which do not rely on a general purpose filesystem? Because in the commercial RDBMS space that is a thing.

My impression, wrong or not, was always that those were features from when a good volume manager could not be counted on in the host system.

I've used PostgreSQL with BTRFS + LZO compression on Linux. After I set it up, our large queries (2 minutes or > runtime) saw a speedup of 5-10x and our TPS went up by around 25%.

BTRFS has been in the stable kernel for over two years, so it might be worth checking out!

Mechanical or flash storage? Compression could give advantage on mechanical disks due to the reduced time spent on seeking, but I'm curious to know how they perform on SSD for which seek time is reduced to a minimum if not 0.

It was 4 SSDs in a RAID-0 configuration.

Reply on news.ycombinator.com