--- title: "Postgres 16 beta 1 released" publication: "Postgres Weekly" issue: 508 published_at: "2023-05-31" canonical_url: "https://postgresweekly.com/issues/508" --- Plus bit.io to close, hypothetical deindexing, and a look through SQL window functions. | #​508 — May 31, 2023 [Web Version](https://postgresweekly.com/issues/508) Together with [![Hydra\](https://res.cloudinary.com/cpress/image/upload/c_fill,g_auto,w_524,h_127/e_make_transparent/co_white,e_outline:9/bmhxbsirg1zkckgnwcor.png)](https://blog.hydra.so/blog/2023-05-30-materialized-views-precompute-with-postgres) Postgres Weekly [![\](https://res.cloudinary.com/cpress/image/upload/w_1280,e_sharpen:60,q_auto/xuma8zqjunux3gnmgs9o.jpg)](https://www.crunchydata.com/blog/svg-images-from-postgis) [Generating SVG Images from Postgres](https://www.crunchydata.com/blog/svg-images-from-postgis) — [pg-svg](https://github.com/dr-jts/pg_svg) is a set of functions for creating SVG graphics from within Postgres. Its main use case is for converting PostGIS geometries into graphics as demonstrated in this thorough introduction (complete with an [epic query](https://github.com/dr-jts/pg_svg/blob/master/demo/map/us-highpt-svg.sql)), but you can use it independently of PostGIS too. Martin Davis (Crunchy Data) [Postgres 16 Beta 1 Released](https://www.postgresql.org/about/news/postgresql-16-beta-1-released-2643/) — We are now truly on the fairway leading towards Postgres 16's eventual release. The [draft release notes](https://www.postgresql.org/docs/16/release-16.html) tease all of the updates, with particular improvements coming to performance, query parallelism, SQL/JSON, security, monitoring, and more. PostgreSQL Global Development Group This beta is already [available in Amazon RDS's 'Database Preview Environment'](https://aws.amazon.com/about-aws/whats-new/2023/05/postgresql-16-beta-1-amazon-rds-database-preview-environment/). [![\](https://copm.s3.amazonaws.com/5170b94c.png)](https://blog.hydra.so/blog/2023-05-30-materialized-views-precompute-with-postgres) [Materialized Views: Precompute with Postgres](https://blog.hydra.so/blog/2023-05-30-materialized-views-precompute-with-postgres) — Learn the benefits of using materialized views in Postgres for analytics, the differences between materialized and regular views, and tips for optimizing their use. Hydra sponsor [How to Use Variables in `psql`](https://www.depesz.com/2023/05/28/variables-in-psql-how-to-use-them/) — You can set variables in `psql` using `\set` or by passing them in at the command line. Hubert shows off some uses. Hubert depesz Lubaczewski **IN BRIEF:** - The [bit.io cloud Postgres service](https://blog.bit.io/whats-next-for-bit-io-joining-databricks-ace9a40bce0d) has been acquired by Databricks and any databases running there have a month to find a new home. - Most of us don't need to ever compile Postgres ourselves, but if you _do_ Peter Eisentraut has run some benchmarks [comparing the Postgres build times using various compilers](https://peter.eisentraut.org/blog/2023/05/29/postgresql-compile-times) on macOS and Ubuntu. - The latest _PGSQL Phriday_ blogging prompt is [to write something about _database change management_](https://www.pgsqlphriday.com/2023/05/pgsql-phriday-009/). - Google Cloud SQL [now has Postgres 15 support](https://cloud.google.com/blog/products/databases/improved-developer-experience-and-security-in-postresql) and introduces it with quite a thorough look at the benefits Postgres 15 offers. - Amazon Aurora now [maintains read availability through writer node restarts](https://aws.amazon.com/about-aws/whats-new/2023/05/amazon-aurora-postgresql-read-replicas/). - Amazon RDS now [supports Postgres 15.3, 14.8, 13.11, 12.15, and 11.20](https://aws.amazon.com/about-aws/whats-new/2023/05/amazon-rds-postgresql-supports-new-minor-versions/). ▶['I Tried 8 Different Postgres ORMs' from Node.js / Deno](https://www.youtube.com/watch?v=4QN1BzxF8wM) — A modern, fast-paced, and slightly irreverent tour of the various ways in which backend JavaScript developers can interact with Postgres (provided serverlessly by [Neon](https://neon.tech/), in this case.) _(9 minutes.)_ Beyond Fireship [Cache Synchronization using jOOQ and Postgres Functions](https://vladmihalcea.com/cache-synchronization-jooq-postgresql-functions/) Vlad Mihalcea [Validate Database Objects After Migrating from IBM Db2 z/OS to RDS or Aurora](https://aws.amazon.com/blogs/database/validate-database-objects-after-migrating-from-ibm-db2-z-os-to-amazon-rds-for-postgresql-or-amazon-aurora-postgresql/) Parthasaradhi, Balasubramanian and Gupta (AWS) Code and Tools [HypoPG 1.4.0: Hypothetical Indexes for Postgres](https://www.postgresql.org/about/news/hypopg-140-is-out-2645/) — You can use HypoPG to create indexes that don’t really exist but which can be used to determine out if said indexes could be used by the query planner to improve performance. This week’s release makes it possible to support the opposite: hypothetically _hiding_ existing indexes. [GitHub repo.](https://github.com/HypoPG/hypopg) Julien Rouhard [pg_dumpbinary v2.11: Dump a Database in Binary Format](https://www.postgresql.org/about/news/pg_dumpbinary-v211-released-2644/) — Use `pg_dump` if you can, but if you encounter [unusual situations](https://github.com/lzlabs/pg_dumpbinary#pg_dumpbinary) where a binary dump would help – this is for you. [GitHub repo.](https://github.com/lzlabs/pg_dumpbinary) Gilles Darold [Diesel 2.1: Extensible ORM and Query Builder for Rust](https://github.com/diesel-rs/diesel) — Gets rid of the boilerplate around database interaction in Rust without sacrificing performance. Supports Postgres, MySQL, and SQLite. Diesel - [pgrx 0.9](https://github.com/tcdi/pgrx) ↳ Build Postgres extensions with Rust. - [Bytebase 2.1](https://www.bytebase.com/changelog/bytebase-2-1-0/) ↳ Web-based collaboration workspace for DBAs. - [pg_timetable 5.5](https://github.com/cybertec-postgresql/pg_timetable) ↳ Advanced standalone job scheduler. - [pgsodium 3.1.7](https://github.com/michelp/pgsodium) ↳ libsodium-powered encryption extension. ![Ken Collins](https://res.cloudinary.com/cpress/image/upload/w_200,e_sharpen:60/sl3lnwzop7go8f0vqhxu.jpg) 🪟 Window functions explained with.. Anton Zhiyanov Anton is a backend developer working with Python and Go who uses SQL both at the app level and for exploratory data analysis and data pipelines. He teaches courses on Go and SQL and is the author of [SQL Window Functions Explained](https://antonz.org/sql-window-functions-book/) (the first five chapters can be [read online for free](https://antonz.org/sql-window-functions-book/#what-you-will-learn) – code `POSTGRES` can be used for a discount on the paid version). We asked him a few questions about SQL window functions: Can you explain window functions for someone who's ignored them so far? "Window functions" is a (probably misleading) name for an entire sub-language, of sorts, built into regular SELECTs. Window functions help perform various data analysis tasks, from ranking, segmenting, and calculating moving aggregates to statistics, financial analysis, clustering, and data cleaning. Are there any reasons to use thems in a situation where a subquery might do? For most data analysis tasks, window functions tend to produce much more concise, readable and efficient queries. Is Postgres's window functions support particularly interesting or different compared to the other databases you cover? Fortunately for us, all database vendors have implemented window functions very close to the SQL standard, so there are no syntactical differences (although some advanced features are missing in certain DBMSs). Postgres supports almost all window-related features defined by the standard (with the unfortunate exception of windowed percentiles). _Anton blogs about Go, Python, SQL, and software in general [over on his personal site](https://antonz.org) and is the author of [SQL Window Functions Explained](https://antonz.org/sql-window-functions-book/)._ **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|*).