Hacker News Reader

Comment by phibz | Hacker News Reader

Maybe I missed it but there's no mention of denormalizing strategies or data cubes, fact tables and dimension tables. Structuring your data closer to how its going to be analyzed us vital for performance. It also gives you the opportunity to cleanse, standardize, and conform your data.

I ran a pg data warehouse in the 8.x and 9.x days with about 20TB of data and it performed great.

View on HN

I think you're right but it's a bit out of scope. Hard to give generalizable advice around this I think.

What we personally do in practice is put everything into a single time-series table with 11 columns. [1]

1: https://www.activityschema.com/

Postgres has features that help these sort of OLAP type workflows. Things like:

* partitioning your fact/aggregate tables (which was mentioned)

* rolling up old data reducing granularity as data ages can help with record count and overall db size

* PG triggers and stored procedures can be used to manage slowly changing dimensions

* the hstore and json column types are super useful for implementing quazi-nosql storage along side traditional relational storage

* window functions and CTEs (in 12) are great for writing analytical style queries

* Implementing incremental loads with a staging/buffer table and possibly with plpgsql can really make connecting it all easier

Not saying I didn't enjoy the article. It always makes me happy to see people realizing how suited PG can be to analytical workflows especially for small to medium workloads which represents most of what people want to do.

One would assume a data warehouse professional would know these things. This blog post was specifically about pg features/tips for building a dw.

How did you store that much data in PG? Did you have some sort of sharding mechanism?

Postgres can handle a lot more data than that without sharding and still perform well, especially for predictable table-scan-and-filter kinds of queries (which are pretty much what you want to run if you're doing the kind of denormalization the post you replied to mentions). Of course a column store would perform even better in many of those cases due to better compression / vectorization opportunities, but Postgres is really no slouch compared to other fully featured databases (research databases can blow it away though).

So, 20TB on a single node? Am I understanding this correctly? I guess it would be mostly for ETL stuff, as you would be swapping indexes in/out of memory too much without a lot of care.

Depends on the kind of index (BRIN are quite effective for large tables), how many rows we're talking about and how the column data are distributed, and how much of the data is actually hot, but yes.

Yes you understood correctly, 20 TB on a single node. And this was in 2007. The data was email and direct mail campaign and response data.

Typically only the more recent data was analyzed.

We retained all source data sou that reprocessing could happen when adding facts or dimensions.

Analysis, scheduled or ad hoc, only ever happened against the data cubes. Their partial aggregate nature along with partitioning was part of how the performance was so good.

Reply on news.ycombinator.com