--- title: "JSONB cheat sheet" publication: "Postgres Weekly" issue: 501 published_at: "2023-04-12" canonical_url: "https://postgresweekly.com/issues/501" --- Plus a new connection pooler, a Postgres-powered MongoDB alternative, and a way to check credentials automatically. | #​501 — April 12, 2023 [Web Version](https://postgresweekly.com/issues/501) Together with [![EDB\](https://res.cloudinary.com/cpress/image/upload/c_fill,g_auto,e_trim,w_308,h_100/e_make_transparent/co_white,e_outline:7/vrltbo1rhou91lmjw2rl.png)](https://info.enterprisedb.com/the-power-of-postgres-high-availability-3-must-read-success-stories.html?utm_medium=display&utm_source=postgres-weekly&utm_content=customer-sucess-ebook&utm_campaign=GL-CT-20221018--High%20Availability%20eBook) Postgres Weekly [![\](https://res.cloudinary.com/cpress/image/upload/w_1280,e_sharpen:60,q_auto/lq9rhsw6uxxuul60bzci.jpg)](https://aiven.io/developer/postgresql-jsonb-cheatsheet) 📄 [A Postgres JSONB Functions and Operators Cheatsheet](https://aiven.io/developer/postgresql-jsonb-cheatsheet) — Bills itself as a cheatsheet for the various JSONB functions in Postgres with quick examples of each, along with related operators. [📈 Link to PDF.](https://go.aiven.io/postgresql-jsonb-cheatsheet-en) Aiven [Supavisor: A Postgres Connection Pooler from Supabase](https://github.com/supabase/supavisor) — A new Postgres connection pooler from the folks behind Supabase Realtime. It’s built with Elixir (an Erlang VM/BEAM language) and specifically designed for multi-tenancy. It can be run as a cluster or as a single node. The initial benchmark numbers are promising. Supabase [![\](https://copm.s3.amazonaws.com/e34d5b85.png)](https://info.enterprisedb.com/the-power-of-postgres-high-availability-3-must-read-success-stories.html?utm_medium=display&utm_source=postgres-weekly&utm_content=customer-sucess-ebook&utm_campaign=GL-CT-20221018--High%20Availability%20eBook) [Three Must-Read Extreme High Availability Success Stories](https://info.enterprisedb.com/the-power-of-postgres-high-availability-3-must-read-success-stories.html?utm_medium=display&utm_source=postgres-weekly&utm_content=customer-sucess-ebook&utm_campaign=GL-CT-20221018--High%20Availability%20eBook) — Five 9s uptime is essential for mission-critical databases. Here's how a telecommunications company, a payment platform and project management organization cut costs, achieved rolling maintenance upgrades and ensured data-loss protection. EDB sponsor [SQL Maxis: Why We Replaced RabbitMQ with a Postgres Queue](https://www.prequel.co/blog/sql-maxis-why-we-ditched-rabbitmq-and-replaced-it-with-a-postgres-queue) — The provider of a data warehouse syncing tool tore out RabbitMQ from their system and replaced it with a Postgres-backed queue. Read on to learn why, but ultimately they found the Postgres solution to improve reliability and resiliency and it suited their use case better. Charles Chretien (Prequel) [FerretDB 1.0: An Open Source MongoDB Alternative](https://blog.ferretdb.io/ferretdb-1-0-ga-opensource-mongodb-alternative/) — Built in Go, FerretDB converts MongoDB’s wire protocol queries into SQL handled by Postgres on the backend, so you can reproduce the MongoDB experience but without its [controversial license change.](https://www.protocol.com/enterprise/mongodb-open-source-database) Another example of Postgres behind the scenes! Peter Farkas **IN BRIEF:** - [Amazon RDS now offers _Optimized Reads_,](https://aws.amazon.com/blogs/database/introducing-optimized-reads-for-amazon-rds-for-postgresql/) a new deployment option with up to twice the read performance for certain workloads. - [Amazon Aurora now supports Postgres 15.](https://aws.amazon.com/about-aws/whats-new/2023/04/amazon-aurora-postgresql-15/) _15.2_, to be precise. - You've got two years to worry about it, but [_Azure Database for PostgreSQL - Single Server_ is being 'retired' in 2025](https://techcommunity.microsoft.com/t5/azure-database-for-postgresql/retiring-azure-database-for-postgresql-single-server-in-2025/ba-p/3783783) in favor of the _Flexible Server_ variant. - Last week's PGDay Phriday resulted in [a call to write about triggers](https://mydbanotebook.org/post/triggers/) and the Postgres community obliged with replies from [Andreas Scherbaum](https://andreas.scherbaum.la/blog/archives/1138-PGSQL-Phriday-007-Triggers-for-tracking-changes-in-a-table.html), [Henrietta Dombrovskaya](https://hdombrovskaya.wordpress.com/2023/04/08/pgsql-phriday-007-a-word-about-triggers/), [Ryan Booz](https://www.softwareandbooz.com/triggers-a-love-hate-relationship/), and [Michael Christofides](https://www.pgmustard.com/blog/trigger-time), among others, as well as [Lætitia herself](https://mydbanotebook.org/post/triggers-and-me/) who explained _"why I would like developers to stop using triggers."_ - [Citus Con](https://www.citusdata.com/cituscon/2023/), a virtual Postgres event, starts next Tuesday, with a [fantastic array of sessions](https://www.citusdata.com/cituscon/2023/schedule/) on offer. - [YaK](https://www.dbi-services.com/products/yak/)'s Joël Cattin is this week's [PostgreSQL Person of the Week.](https://postgresql.life/post/joel_cattin/) [Parallel Aggregates in Postgres 16](https://www.cybertec-postgresql.com/en/parallel-aggregate-postgresql-16-better-performance/) — Parallel aggregates speed up processing of aggregate functions on large data sets by distributing them. Not all aggregate functions support parallelization, but `string_agg()` and `array_agg()` functions now do in Postgres 16. Pavlo Golub [Updating the Cost Limit on the Fly in Postgres 16+](http://rhaas.blogspot.com/2023/04/updating-cost-limit-on-fly.html) — This will solve a common issue where VACUUM runs too slowly by allowing for quicker absorption of cost limit changes. Currently, modifying the limit only affects future autovacuum operations, but in future it will affect all workers “almost as soon as you reload the configuration.” Robert Haas [Setting Up Postgres Failover and Failback, the Right Way](https://www.highgo.ca/2023/04/10/setting-up-postgresql-failover-and-failback-the-right-way/) — _“This blog was written to help beginners understand and set up server replication in PostgreSQL using failover and failback.”_ And, importantly, the _up to date_ way. Tristen Raab [Watch: How to Use the Postgres Query Planner to Debug Bad Plans and Speed Up Queries](https://www.youtube.com/watch?v=nVhLTwVefCA) pganalyze sponsor ▶[Path to Citus Con: Working in Public](https://simonwillison.net/2023/Apr/8/working-in-public/) — As part of a warm up to [next week’s _Citus Con_](https://www.citusdata.com/cituscon/2023/), prolific Pythonista Simon Willison and Citus Data’s Marco Slot joined Claire Giordano and Pino de Candia for an hour long conversation about their work on various open source projects. Simon Willison podcast [Pgpool-II Use Cases and Benefits](https://www.percona.com/blog/postgresql-pgpool-ii-use-cases-and-benefits/) — [Pgpool-II](https://wiki.postgresql.org/wiki/Pgpool-II) is a popular connection pooler and load balancer. Kai Wagner (Percona) [PL/Rust Just Shipped: An Easy Way to Try It Out](https://yrashk.com/blog/2023/04/05/plrust-just-shipped-easy-way-to-try-it-out/) Yurii Rashkovskii [credcheck v1.0: An Extension for Credential Checking](https://www.postgresql.org/about/news/credcheck-v10-released-2619/) — Enables credential checks during user creation, password change, and user renaming by defining a set of rules that allows specific credentials, can reject others, and enforce password expiry, if needed. [GitHub repo.](https://github.com/MigOpsRepos/credcheck/) MigOps - [Norm 2.8](https://github.com/moigagoo/norm) – A Nim ORM for SQLite and Postgres - [QuestDB 7.1](https://github.com/questdb/questdb) – Java-powered time-series database with Postgres wire protocol compatibility. - [pgwire 0.13](https://github.com/sunng87/pgwire) – The Postgres wire protocol implemented in a Rust library. **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|*)