5 ms·

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 go

by phibz 5y ago

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.

cedricd 5y ago
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/ https://www.activityschema.com/
- phibz 5y ago
  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.
patman 5y ago
One would assume a data warehouse professional would know these things. This blog post was specifically about pg features/tips for building a dw.
winrid 5y ago
How did you store that much data in PG? Did you have some sort of sharding mechanism?
- Jweb_Guru 5y ago
  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).
  - winrid 5y ago
    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.
    - Jweb_Guru 5y ago
      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.
    - phibz 5y ago
      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.

by phibz 5y ago

cedricd 5y ago

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/ https://www.activityschema.com/

phibz 5y ago
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.

patman 5y ago

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

winrid 5y ago

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

Jweb_Guru 5y ago
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).
- winrid 5y ago
  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.
  - Jweb_Guru 5y ago
    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.
  - phibz 5y ago
    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.