<story><title>Using PostgreSQL as a Data Warehouse</title><url>https://www.narrator.ai/blog/using-postgresql-as-a-data-warehouse/</url></story><parent_chain><item><author>efxhoy</author><text>Dr. Martin Loetzsch did a great video, ETL Patterns with Postgres. He covers some really good topics:&lt;p&gt;- Instead of updating tables build their replacements under a different name then rename them. This makes updating heavy-to-compute table instant. Works even for schemas: rebuild a schema  as schemaname_next rename the current to schemaname_old then rename schemaname_next to schemaname.&lt;p&gt;- Keep all the source data raw and disable WAL, you don&amp;#x27;t need it for ETL.&lt;p&gt;- Set memory limitis high.&lt;p&gt;And lots of other good tips for doing ETL&amp;#x2F;DW in postgres. It&amp;#x27;s here: &lt;a href=&quot;https:&amp;#x2F;&amp;#x2F;www.youtube.com&amp;#x2F;watch?v=whwNi21jAm4&quot; rel=&quot;nofollow&quot;&gt;https:&amp;#x2F;&amp;#x2F;www.youtube.com&amp;#x2F;watch?v=whwNi21jAm4&lt;/a&gt;&lt;p&gt;I really appreciate having data in postgres. It&amp;#x27;s often easy to think that a specialised DW tool will solve all your problems, but that often fails to consider things like:&lt;p&gt;- Developer experience. Postgres runs very easily on a local machine, more specialized solutions often don&amp;#x27;t or are tricky to setup.&lt;p&gt;- Learning another tool costs time. A developer can learn postgres really well in the time it takes them to figure out how to use several more specialised tools. And many devs already know postgres because it&amp;#x27;s pretty much the default  DB nowadays.&lt;p&gt;- Analytics queries often don&amp;#x27;t need to run at warp speed. Bigquery might give you the answer in a second but if postgres does it in a minute and it&amp;#x27;s a weekly report, who cares?&lt;p&gt;- Postgres is boring and has been around for many years now, it will probably still be here in 10 years so time spent learning it is time well spent. More niche systems will probably be superseded by fancier, faster replacements.&lt;p&gt;I would go so far as to say don&amp;#x27;t necessarily need to split out your DW from your prod DB in every case. As soon as you start splitting out a DW to a separate server you need some way to keep it in sync, so you&amp;#x27;ll probably end up duplicating some business logic for a report, maintaining some ingestion app, shuffling data around S3 or whatever. Keeping your analytics in your prod DB (or just a snapshot of yesterdays DB) is often good enough and means you will be more likely to avoid gnarly business-rules going out of sync between your app and your DW.</text></item></parent_chain><comment><author>stadeschuldt</author><text>The tooling behind the approach has been built as a set of python package named Mara. It is available at GitHub:&lt;p&gt;&lt;a href=&quot;https:&amp;#x2F;&amp;#x2F;github.com&amp;#x2F;mara&amp;#x2F;mara-pipelines&quot; rel=&quot;nofollow&quot;&gt;https:&amp;#x2F;&amp;#x2F;github.com&amp;#x2F;mara&amp;#x2F;mara-pipelines&lt;/a&gt;&lt;p&gt;And additional packages can be found at the Mara org:&lt;p&gt;&lt;a href=&quot;https:&amp;#x2F;&amp;#x2F;github.com&amp;#x2F;mara&quot; rel=&quot;nofollow&quot;&gt;https:&amp;#x2F;&amp;#x2F;github.com&amp;#x2F;mara&lt;/a&gt;</text></comment>
<story><title>Using PostgreSQL as a Data Warehouse</title><url>https://www.narrator.ai/blog/using-postgresql-as-a-data-warehouse/</url></story><parent_chain><item><author>efxhoy</author><text>Dr. Martin Loetzsch did a great video, ETL Patterns with Postgres. He covers some really good topics:&lt;p&gt;- Instead of updating tables build their replacements under a different name then rename them. This makes updating heavy-to-compute table instant. Works even for schemas: rebuild a schema  as schemaname_next rename the current to schemaname_old then rename schemaname_next to schemaname.&lt;p&gt;- Keep all the source data raw and disable WAL, you don&amp;#x27;t need it for ETL.&lt;p&gt;- Set memory limitis high.&lt;p&gt;And lots of other good tips for doing ETL&amp;#x2F;DW in postgres. It&amp;#x27;s here: &lt;a href=&quot;https:&amp;#x2F;&amp;#x2F;www.youtube.com&amp;#x2F;watch?v=whwNi21jAm4&quot; rel=&quot;nofollow&quot;&gt;https:&amp;#x2F;&amp;#x2F;www.youtube.com&amp;#x2F;watch?v=whwNi21jAm4&lt;/a&gt;&lt;p&gt;I really appreciate having data in postgres. It&amp;#x27;s often easy to think that a specialised DW tool will solve all your problems, but that often fails to consider things like:&lt;p&gt;- Developer experience. Postgres runs very easily on a local machine, more specialized solutions often don&amp;#x27;t or are tricky to setup.&lt;p&gt;- Learning another tool costs time. A developer can learn postgres really well in the time it takes them to figure out how to use several more specialised tools. And many devs already know postgres because it&amp;#x27;s pretty much the default  DB nowadays.&lt;p&gt;- Analytics queries often don&amp;#x27;t need to run at warp speed. Bigquery might give you the answer in a second but if postgres does it in a minute and it&amp;#x27;s a weekly report, who cares?&lt;p&gt;- Postgres is boring and has been around for many years now, it will probably still be here in 10 years so time spent learning it is time well spent. More niche systems will probably be superseded by fancier, faster replacements.&lt;p&gt;I would go so far as to say don&amp;#x27;t necessarily need to split out your DW from your prod DB in every case. As soon as you start splitting out a DW to a separate server you need some way to keep it in sync, so you&amp;#x27;ll probably end up duplicating some business logic for a report, maintaining some ingestion app, shuffling data around S3 or whatever. Keeping your analytics in your prod DB (or just a snapshot of yesterdays DB) is often good enough and means you will be more likely to avoid gnarly business-rules going out of sync between your app and your DW.</text></item></parent_chain><comment><author>Guthur</author><text>And if say that if you are in the position where you can run DW workloads on your prod database you probably don&amp;#x27;t need a data warehouse in the first place.&lt;p&gt;Data warehouse workloads tend to be very IO intensive and could be highly disruptive to a production db. ETL is hard but it&amp;#x27;s a price to pay to isolate these two very different workloads.</text></comment>