News for hackers. Stuff that matters.
Re:Using PostgreSQL as a Data Warehouse by phibz on Monday May 10, 2021 @08:28PM (#27112713)
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.

There are no comments on this comment yet.