--- title: "A tool to critique your Postgres schemas" publication: "Postgres Weekly" issue: 414 published_at: "2021-07-14" canonical_url: "https://postgresweekly.com/issues/414" --- Plus figuring out lateral joins, Timescale's new hyperfunctions, and more. | #​414 — July 14, 2021 [Web Version](https://postgresweekly.com/issues/414) Postgres Weekly [![\](https://res.cloudinary.com/cpress/image/upload/w_1280,e_sharpen:60/bpbqpcvh8bvxpryrj0tv.jpg)](https://www.cybertec-postgresql.com/en/understanding-lateral-joins-in-postgresql/) [Understanding `LATERAL` Joins in Postgres](https://www.cybertec-postgresql.com/en/understanding-lateral-joins-in-postgresql/) — You can think of `LATERAL` joins as being a bit like a for-each loop where a subquery is run for each row of an initial result set. This can, of course, be very useful! Hans-Jürgen Schönig [Timescale Adds 'Hyperfunctions' to Improve Time Series Queries](https://blog.timescale.com/blog/introducing-hyperfunctions-new-sql-functions-to-simplify-working-with-time-series-data-in-postgresql/) — [TimescaleDB](https://github.com/timescale/timescaledb) is a time-series oriented extension/distribution for Postgres and they’ve extended Postgres’s SQL support with a variety of ‘hyperfunctions’ for working with time series data (things like time-weighted averages, [hyperloglog](https://en.wikipedia.org/wiki/HyperLogLog) counting, and dataset smoothing). Lockerman, Kohn, and Rowe [![\](https://copm.s3.amazonaws.com/6b3e5b63.png)](https://fauna.com/go/postgres?utm_source=PostgresWeekly&utm_medium=Newsletter&utm_campaign=CooperPress_Newsletters_2021) [An Operations-Free, Scalable & Flexible Postgres Alternative](https://fauna.com/go/postgres?utm_source=PostgresWeekly&utm_medium=Newsletter&utm_campaign=CooperPress_Newsletters_2021) — Fauna combines the operational integrity and relational modeling of Postgres with an interface and architecture that fits better with modern app development in the cloud. The goodness of Postgres without its operational bottlenecks - [Learn more about Fauna.](https://fauna.com/go/postgres?utm_source=PostgresWeekly&utm_medium=Newsletter&utm_campaign=CooperPress_Newsletters_2021) Fauna sponsor [`LIMIT` vs `FETCH FIRST ROWS ... WITH TIES`](https://www.cybertec-postgresql.com/en/postgresql-limit-vs-fetch-first-rows-with-ties/) — I still remember how surprised I was when I first learnt that `LIMIT`/`OFFSET` is not standard SQL (though many systems support it). `FETCH FIRST` may be more portable, but there are some things to consider about its use. Hans-Jürgen Schönig [DBCritic: Constructive Criticism for Your Postgres Schema](https://www.channable.com/tech/dbcritic-constructively-criticizing-your-postgres-schema) — The team at Channable built DBCritic as a way to find problems in schemas due to running into a variety of problems in their own (explained in this article). The tool itself is interestingly written in the little seen [Idris language](https://www.idris-lang.org/). Radek Slupik (Channable) [What Happens to Logical Replication After Running `pg_upgrade`?](https://elephanttamer.net/?p=58) — Be careful about your replication slots. Michał Mackiewicz [Using GDB To Trace Into a Parallel Worker Spawned By Postmaster During a Large Query](https://www.highgo.ca/2021/07/09/using-gdb-to-trace-into-a-parallel-worker-spawned-by-postmaster-during-a-large-query/) — ‘Postmaster’, by the way, is referring to the part of Postgres that handles incoming client connections and routes them to a server process (though the term itself is now deprecated). Cary Huang [Crunchy Bridge: Fully Managed Multi-Cloud Postgres](https://www.crunchydata.com/products/crunchy-bridge/?utm_source=PGWeekly&utm_medium=newsletter&utm_campaign=Bridge&utm_content=7-14-21) Crunchy Bridge sponsor [Heroku Database Connection Calculator](https://railsautoscale.com/heroku-postgresql-connection-calculator/) — Optimizing your database pool configuration relies on quite a few variables (number of dynos, workers, processes, threads) so this guide does the math for you while also explaining each calculation. Rails Autoscale [pgSCV 0.7.0 Released](https://lesovsky.medium.com/pgscv-0-7-0-released-8b92af2e534a) — pgSCV is a Postgres metrics collector that exposes said metrics in Prometheus format. [GitHub repo](https://github.com/weaponry/pgscv/). Alexey Lesovsky [rails-pg-extras 2.0: Postgres Performance Insights for Ruby and Rails](https://github.com/pawurb/rails-pg-extras) — Ruby on Rails developer? This plugin lets you get quick info about locks, index usage, buffer cache hit ratios, vacuum stats and more. There’s a [pure Ruby (non Rails) version](https://github.com/pawurb/ruby-pg-extras) too. Paweł Urbanek [db-to-sqlite 1.4: A CLI Tool for Exporting Tables or Queries from any SQL Database to SQLite](https://github.com/simonw/db-to-sqlite) Simon Willison **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! --- 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|*)