Hacker News Reader

Comment by mulmen | Hacker News Reader

Why ZFS? I thought copy-on-write filesystems were discouraged for database workloads. I know it is possible but are you getting other benefits from ZFS?

View on HN

For OLTP, COW is horrible. For OLAP it's usually fine. I've had good results with Postgres and ZFS with LZ4 compression.

I have a single physical server for running all my virtual machines. ZFS is nice for everything else, and it just so happens that my database virtual machine takes up most of the SSD space on that computer. I guess I should have a dedicated machine if I really need to improve insert speeds though.

Ah ok, pragmatic. Makes sense.

Have you done things like disable constraints and indexing during loading?

I've disabled all constraints, but I haven't removed indexing during loading. I'll look into doing that, with adding the indexing later

I don’t often work with Postgres itself but I do work with a derivative (Redshift) at $dayjob.

Indexes don’t exist. Constraints are present in the syntax and the planner relies on them but they are never enforced. Our ETL layer deals with checking constraints at load time. Load performance is good. I’ve never run a rows/minute metric because it’s never been a problem.

Coming back to this just to provide a datapoint. I just loaded 10m rows into a table in less than one minute using Redshift. The data is encoded (compressed) at insert time but is not sorted until a vacuum runs.

Reply on news.ycombinator.com