--- title: "Using row level security for safer multi-tenant SaaS." publication: "Postgres Weekly" issue: 465 published_at: "2022-07-27" canonical_url: "https://postgresweekly.com/issues/465" --- Plus benchmarking AlloyDB vs Postgres, a way to validate JSON column documents, and all the extensions popular platforms support. | #​465 — July 27, 2022 [Web Version](https://postgresweekly.com/issues/465) Postgres Weekly [![\](https://res.cloudinary.com/cpress/image/upload/w_1280,e_sharpen:60/jmmu5s2w7gpavomzvrtc.jpg)](https://www.cybertec-postgresql.com/en/better-sql-functions-in-postgresql-v14/) [Better SQL Functions in Postgres v14](https://www.cybertec-postgresql.com/en/better-sql-functions-in-postgresql-v14/) — Postgres 14 introduced a new way to define SQL functions and it’s caught the eye of at least _two_ developers this week as we have two good posts about it. In essence, _“the body of SQL functions and procedures need no longer be a string constant”_ and this provides some advantages as we see in Laurenz’s demo. Laurenz Albe [`BEGIN ATOMIC`: A Better Way to Create Functions in Postgres 14](https://jkatz05.com/post/postgres/postgres-begin-atomic/) — Yes, it’s the same story as above, but focused less around explanation and more around a larger code example. It’s well worth skimming both if this area interests you at all. Jonathan Katz [![\](https://copm.s3.amazonaws.com/b892edaf.png)](https://pganalyze.com/ebooks/postgres-indexing?utm_source=PostgresWeeklyPrimary07272022) [Free eBook: Effective Indexing in Postgres](https://pganalyze.com/ebooks/postgres-indexing?utm_source=PostgresWeeklyPrimary07272022) — Learn how to create the best Postgres index for your queries. We provide a deep dive into index types, operators, data types and more. Creating the right indexes can often improve your query performance by 10x or even 100x. pganalyze sponsor [Shipping Multi-Tenant SaaS using Row-Level Security](https://www.thenile.dev/blog/multi-tenant-rls) — Explorations and thoughts on database-level (rather than app level) ‘secure by default’ security in single-schema multi-tenant databases. The [extensive discussion](https://news.ycombinator.com/item?id=32241820) about this topic on Hacker News proved to be quite valuable too. Miki Pokryvailo (Nile) [Partitioning with Native Postgres and `pg_partman`](https://www.crunchydata.com/blog/native-partitioning-with-postgres) — Sometimes premature optimization makes no sense, but if you _know_ your database is likely to rapidly enter the world of terabytes (maybe you’re storing a lot of time series data, say) then being proactive about partitioning is a wise idea and this is a particularly gentle introduction. Elizabeth Christensen **QUICK BITS:** - Elizabeth Christensen (who wrote the partitioning item above) is this week's [PostgreSQL Person of the Week](https://postgresql.life/post/elizabeth_garrett_christensen/) and has been interviewed about her work, how she volunteers for PgUS, and her favorite Postgres extension _(spoiler: it's PostGIS!)_ - 💰 Back in June, we mentioned Neon which was giving us Planetscale-but-for-Postgres vibes. The company behind it [has now raised $30m](https://techcrunch.com/2022/07/26/neon-nabs-30m-to-build-a-scalable-cloud-service-for-postgres-databases/) to build a new Postgres cloud service. - Ever wondered what Postgres extensions different managed platforms support? Here are lists for [Amazon RDS](https://docs.aws.amazon.com/AmazonRDS/latest/PostgreSQLReleaseNotes/postgresql-extensions.html), [Aurora](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraPostgreSQLReleaseNotes/AuroraPostgreSQL.Extensions.html), [Google Cloud](https://cloud.google.com/sql/docs/postgres/extensions), [Azure](https://docs.microsoft.com/en-us/azure/postgresql/single-server/concepts-extensions) and [Digital Ocean](https://docs.digitalocean.com/products/databases/postgresql/details/supported-extensions/) for starters. [Using `BUFFERS` for Query Optimization](https://www.pgmustard.com/blog/using-postgres-buffers-for-query-optimization) — You know `EXPLAIN ANALYZE` but what about [`BUFFERS`](https://www.pgmustard.com/docs/explain/buffers)? It provides info about how Postgres’s buffers are used in queries and can help you narrow in on I/O related issues in particular. Michael Christofides [13 Postgres-Related Tools That Aren't `psql`](https://www.timescale.com/blog/state-of-postgresql-2022-13-tools-that-arent-psql/) — Timescale continues to analyze the results of its recent _State of PostgreSQL 2022_ survey, this time looking at tools. We cover Postgres tools every week ourselves, but this post has the numbers for what people are actually using. Ryan Booz (Timescale) [Migrating Away from Heroku: A Customer Study on Crunchy Bridge](https://www.crunchydata.com/case-studies/calibre?utm_source=cppg-brief-0727) Crunchy Bridge sponsor [AlloyDB Versus Postgres: A Performance Review](https://vettabase.com/blog/alloydb-versus-postgresql/) — As is _always_ the case, go into benchmarks with a cynical but open mind; benchmarking is hard! Nonetheless, it’s cool to see someone putting Google’s Aurora-a-like [AlloyDB for PostgreSQL](https://cloud.google.com/blog/products/databases/introducing-alloydb-for-postgresql) through its paces. Michael Aboagye [pg_jsonschema: JSON Schema Validation Extension](https://github.com/supabase/pg_jsonschema) — Postgres’s support for JSON / JSONB columns is now pretty much second to none, but what if you want to _validate_ the structure of your JSON data? JSON Schema provides a way to define JSON-based formats and this extension lets you verify JSON documents using such schemas. _(A fun aside is just [how simple the code is](https://github.com/supabase/pg_jsonschema/blob/fb7ab09bf6050130e8d656f2999ec0f6a3fedc0d/src/lib.rs#L1-L13) since it leans on a different Rust library for the hard work.)_ Supabase [How to Install Postgres and PostGIS on Rocky Linux 9](https://people.planetpostgresql.org/devrim/index.php?/archives/119-How-to-install-PostgreSQL-and-PostGIS-on-Rocky-Linux-9.html#rockylinux9) Devrim Gunduz **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|*)