--- title: "Lesser known Postgres features" publication: "Postgres Weekly" issue: 430 published_at: "2021-11-10" canonical_url: "https://postgresweekly.com/issues/430" --- Plus even faster TOAST, Azure's Private Link option, and creating a simple message queuing system. | #​430 — November 10, 2021 [Web Version](https://postgresweekly.com/issues/430) Postgres Weekly [![\](https://res.cloudinary.com/cpress/image/upload/w_1280,e_sharpen:60/riadqaypp0rjgulr9lwi.jpg)](https://hakibenita.com/postgresql-unknown-features) [Lots of Lesser Known Postgres Features](https://hakibenita.com/postgresql-unknown-features) — Haki’s articles are often catnip to us as they cover a lot of ground, and so it goes with this roundup of practical examples of Postgres features you might not be familiar with. Worth a skim of the _Table of Contents_ at the very least! Haki Benita [A Look at the New LZ4 TOAST Compression in Postgres 14](https://www.postgresql.fastware.com/blog/what-is-the-new-lz4-toast-compression-in-postgresql-14) — [LZ4](https://github.com/lz4/lz4) is an extremely fast compression algorithm (and a relatively modern one too) and it’s available as an option for TOAST compression in Postgres (as used on very large values in columns, such as text fields) versus the older PGLZ option. LZ4 doesn’t compress _quite_ as much as PGLZ but the performance gains are huge. Haiying Tang [![\](https://copm.s3.amazonaws.com/c5faa2e0.png)](https://www.cockroachlabs.com/lp/serverless?&utm_source=cooperpress&utm_medium=sponsor&utm_campaign=serv-brand-serv-launch-beta&utm_content=lp-build-dream-newsletter-1110&utm_term=prosp-cooperpress-postgres-weekly-1110) [Build What You Dream. Never Worry About Your Database Again](https://www.cockroachlabs.com/lp/serverless?&utm_source=cooperpress&utm_medium=sponsor&utm_campaign=serv-brand-serv-launch-beta&utm_content=lp-build-dream-newsletter-1110&utm_term=prosp-cooperpress-postgres-weekly-1110) — Elastic scale doesn’t have to mean sacrificing consistency or familiarity. Get effortless scaling, automated ops, and guaranteed transactional consistency with CockroachDB Serverless, the world’s most evolved SQL database. CockroachDB Serverless sponsor [Three Cases Against Using `IF NOT EXISTS` / `IF EXISTS`](https://postgres.ai/blog/20211103-three-cases-against-if-not-exists-and-if-exists-in-postgresql-ddl) — Many DDL statements accept `IF (NOT) EXISTS` modifiers to let statements only run under certain conditions, but overuse _“may lead to negative consequences”_ argues Nikolay. Nikolay Samokhvalov [Postgres 14's `enable_memoize` Brings Improved Perf to Nested Loop Joins](https://blog.jooq.org/postgresql-14s-enable_memoize-for-improved-performance-of-nested-loop-joins/) — A quick look at a Postgres 14 enhancement that you can play with quite easily by turning the `enable_memoize` setting on and off. It enables/disables the query planner’s use of memoization to cache results from parameterized scans inside nested-loop joins. Does this help in every day usage? Lukas’s findings say yes. Lukas Eder 📣**Azure user?** Hyperscale (Citus) for Postgres on Azure has [introduced _Private Link_](https://techcommunity.microsoft.com/t5/azure-database-for-postgresql/introducing-private-link-in-hyperscale-citus-for-postgres-on/ba-p/2932704), for creating private endpoints on private IPs within your virtual network. Elsewhere, Sunil Agarwal and Anna Hoffman [▶️ talked about the dynamically scaling 'Flexible Server' option](https://www.youtube.com/watch?v=damXLEH0VTQ) in Azure Database for PostgreSQL. [Message Queuing Using Native Postgres?](https://blog.crunchydata.com/blog/message-queuing-using-native-postgresql) — The author thinks through how to implement a queuing system using only what Postgres provides out of the box. David Christensen [Developers+PostgreSQL+Percona = Better Together. Find Out How](https://learn.percona.com/why-developers-choose-postgresql?utm_campaign=why-developers-choose-percona-postgresql&utm_source=postgresweekly&utm_medium=email&utm_content=newsletter-link) Percona sponsor [Finding Revenue Accrued Per Day with SQL](https://blog.crunchydata.com/blog/fun-with-sql-in-postgres-finding-revenue-accrued-per-day) — Some helpful queries and examples for revenue accruing per day over the course of a month. Jonathan S. Katz [![\](https://res.cloudinary.com/cpress/image/upload/w_1280,e_sharpen:60/k3dkrrzsfys1sn312dly.jpg)](http://okbob.blogspot.com/2021/11/pspg-550.html) [pspg 5.5.0: The Postgres Data Pager](http://okbob.blogspot.com/2021/11/pspg-550.html) — Some UI tweaks, plus support for a streaming mode for files on BSD. Pavel Stěhule [Slonik v25: A Sophisticated Node Postgres Client Library](https://github.com/gajus/slonik) — A ‘battle tested’ framework that abstracts repeating code patterns, protects against unsafe behavior, and provides a rich debugging experience. Gajus Kuizinas [Avoiding `LWLock:buffer_content` Locks in Amazon Aurora: Tips and Best Practices](https://aws.amazon.com/blogs/database/avoid-postgresql-lwlockbuffer_content-locks-in-amazon-aurora-tips-and-best-practices/) Biran and Imseih (AWS) **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|*)