--- title: "Using Postgres as a data warehouse" publication: "Postgres Weekly" issue: 405 published_at: "2021-05-12" canonical_url: "https://postgresweekly.com/issues/405" --- Plus to WAL or not to WAL? And how to create a truly read-only Postgres user. | #​405 — May 12, 2021 [Web Version](https://postgresweekly.com/issues/405) Postgres Weekly [![\](https://res.cloudinary.com/cpress/image/upload/w_1280,e_sharpen:60/hds0bl8ps9g3u4irrgpr.jpg)](https://blog.timescale.com/blog/how-we-made-distinct-queries-up-to-8000x-faster-on-postgresql/) [Skip Scan: A Way to Make DISTINCT Queries Up to 8000x Faster](https://blog.timescale.com/blog/how-we-made-distinct-queries-up-to-8000x-faster-on-postgresql/) — First, it’s worth noting this is a TimescaleDB feature, but given Timescale is a Postgres extension, it’s an interesting development all the same. “Skip Scan” is a new feature that works around Postgres’s inability to efficiently pull a list of unique values from an ordered index. A similar feature is coming to Postgres _15_ but TimescaleDB users can get it now and here’s how it works. Even if you have no plans to use TimescaleDB, it’s worth understanding the underlying concept here. Sven Klemm and Ryan Booz (Timescale) [Using Postgres as a Data Warehouse](https://www.narrator.ai/blog/using-postgresql-as-a-data-warehouse/) — Postgres isn’t usually the first ‘go to’ for creating a data warehouse but with some tweaking and best practices it can work pretty well at certain scales if you don’t want to move data into something like Snowflake or BigQuery. Cedric Dussud [![\](https://copm.s3.amazonaws.com/01c7f420.png)](https://buildkite.com/blog/avoiding-integer-overflows-with-zero-downtime?utm_campaign=CPress&utm_source=ppc&utm_medium=postgres&utm_term=buildkite-blog&utm_content=zero-downtime) [Avoiding Integer Overflows with Zero Downtime](https://buildkite.com/blog/avoiding-integer-overflows-with-zero-downtime?utm_campaign=CPress&utm_source=ppc&utm_medium=postgres&utm_term=buildkite-blog&utm_content=zero-downtime) — Learn how Buildkite migrated over 2 billion rows in one of our largest tables across three partitions covering 8.5TB, plus plenty more rows with foreign keys with the same data type. Read blog from one of our engineers now. Buildkite sponsor **IN BRIEF:** - It's a bit of an 'announcement of an announcement' but we've learnt that [the first beta of Postgres 14](https://www.postgresql.org/message-id/b21bd681-fbf9-ccd2-3ea7-6efed93d683a@dunslane.net) should arrive on Thursday, May 20. - Marco Slot writes about ['new Postgres superpowers' in Citus 10.](https://techcommunity.microsoft.com/t5/azure-database-for-postgresql/new-postgres-superpowers-in-hyperscale-citus-with-citus-10/ba-p/2335404) [Creating a Read-Only Postgres User](https://blog.crunchydata.com/blog/creating-a-read-only-postgres-user) — Learn how to create the _quintessential_ read-only user in Postgres both now and with a look ahead to Postgres 14. Jonathan S. Katz [Getting Random Rows Faster. Very Much Faster](https://blog.hagander.net/getting-random-rows-faster-very-much-faster-249/) — It feels like the topic of grabbing a random row from a table has come up a lot over the years because there are numerous ways to do it, all with their own pros and cons. This approach uses `TABLESAMPLE` as a way to make it fast. Magnus Hagander [On Clustering a Postgres Table](https://momjian.us/main/blogs/pgblog/2021.html#May_10_2021) — Bruce’s opening sold this for me: _“Having written over 600 blog entries, I thought I would have already covered the complexities of the [CLUSTER](https://www.postgresql.org/docs/13/sql-cluster.html) command, but it seems I have not, so let’s do that now.”_ 😄 Bruce Momjian [📺 May 19th Livestream: ALTER DATABASE <`db`> SURVIVE REGION FAILURE](https://www.cockroachlabs.com/webinars/the-cockroach-hour-multi-region/?utm_campaign=cooperpress-2021-Q2-&utm_source=dbweekly&utm_medium=sponsor&utm_content=db-weekly-alter-database-livestream-0512) — A distributed systems engineer explains multi-region application architecture & how it’s not as complex as you think. Cockroach Labs sponsor ▶[ETL Patterns with Postgres](https://www.youtube.com/watch?v=whwNi21jAm4) — It dates from 2018, but this talk on doing ETL and data warehousing work with Postgres has come strongly recommended. Practical, [great slides](https://www.slideshare.net/martinloetzsch/etl-patterns-with-postgres), and clearly a lot of experience was distilled into this talk. Dr. Martin Loetzsch [To WAL or Not to WAL? When Unlogged Becomes Logged...](https://fluca1978.github.io/2021/05/10/PostgreSQLUnloggedTablesReplication.html) Luca Ferrari 🔧**Tools and Code** [windyquery: A Non-Blocking Python Postgres Query Builder](https://pypi.org/project/windyquery/) — A non-blocking PostgreSQL query builder using Asyncio windymile [PGSync 2.0: A Postgres to Elasticsearch Syncing Tool](https://github.com/toluaina/pgsync) — Middleware for syncing Postgres to Elasticsearch so you can expose denormalized documents in Elasticsearch for querying. Tolu Aina 💡**An Interesting Query** As seen in [this tweet:](https://twitter.com/thibaut_barrere/status/1389287427215405060) > Don't forget (or did you know?): you can use Postgres `generate_series` to generate series of e.g. dates, useful to build reporting dashboards of all sorts via joins! Example of a query actually used in France's [national transport data system](https://transport.data.gouv.fr/?locale=en): [![\](https://res.cloudinary.com/cpress/image/upload/w_1280,e_sharpen:60/vracrb5yx747wet1qxf3.jpg)](https://twitter.com/thibaut_barrere/status/1389287427215405060/photo/1) You can see [a similar query here](https://github.com/etalab/transport-site/blob/master/apps/transport/lib/queries/dashboard_import_count.sql) or [a gist of the above query](https://gist.github.com/peterc/19f2960c183edaddf5c3d6b8c4804f3a) if you want to play with the idea. _Thanks to [Thibaut](https://twitter.com/thibaut_barrere) for giving us permission to include this here._ **Got a new email?** You can [change your address here](https://postgresweekly.com/edit_subscription/*|UID|*). --- **Bored of us?** [Click here to stop getting Postgres Weekly](https://postgresweekly.com/leave/*|UID|*). --- **Got a link for us?** Reply and tell us. We can't include everything but we'll look at anything you send. Thanks! --- **Want more?** You might like [DB Weekly](https://dbweekly.com/?utm_source=pg&utm_medium=email&utm_campaign=footer-promo), our more general database newsletter covering data storage trends, new database types, and more. --- Published by Cooper Press Ltd. Fairfield Enterprise Centre, Louth, LN11 0LS, United Kingdom Postgres, PostgreSQL and the Slonik Logo are trademarks or registered trademarks of the PostgreSQL Community Association of Canada. [Stop getting this newsletter.](https://postgresweekly.com/leave/*|UID|*)