--- title: "Understanding Postgres' schemas" publication: "Postgres Weekly" issue: 554 published_at: "2024-05-08" canonical_url: "https://postgresweekly.com/issues/554" --- Plus saturation arithmetic, the difficulties of contributing to Postgres, and more news and updates. | #​554 — May 8, 2024 [Web Version](https://postgresweekly.com/issues/554) Together with [![Microsoft\](https://res.cloudinary.com/cpress/image/upload/c_fill,g_auto,w_280,h_60/e_make_transparent/co_white,e_outline:7/om9hjsmudy5lsvba59eb.png)](https://www.addevent.com/event/AE19906747) Postgres Weekly [![\](https://res.cloudinary.com/cpress/image/upload/w_1280,e_sharpen:60,q_auto/x8gpgirt7ztu4v89idmy.jpg)](https://rhaas.blogspot.com/2024/05/hacking-on-postgresql-is-really-hard.html) [Hacking on PostgreSQL is Really Hard](https://rhaas.blogspot.com/2024/05/hacking-on-postgresql-is-really-hard.html) — Robert has consistently been in the top ten committers to Postgres for many years, but reflects on just how intimidating the process of contributing to Postgres can be, even for experienced developers. This post has a lot of fascinating insights. Robert Haas [What's New in pgvector v0.7](https://supabase.com/blog/pgvector-0-7-0) — We mentioned pgvector’s rather exciting v0.7 release last week, but here’s a proper roundup of its new features, including extra vector types and new distance functions. Pavel Borisov [![\](https://copm.s3.amazonaws.com/df566ecf.png)](https://www.addevent.com/event/AE19906747) [The Schedule Is Out for POSETTE: An Event for Postgres 2024!](https://www.addevent.com/event/AE19906747) — The [POSETTE schedule](https://aka.ms/posette-schedule) is out & full of Postgres–4 keynotes & 38 talks delivered by 44 amazing speakers from the community & our Postgres team. POSETTE is a free & virtual event organized by the Postgres team at Microsoft. Save the date: Jun 11-13! Microsoft sponsor [Understanding Postgres Schemas and `search_path`](https://www.dolthub.com/blog/2024-05-07-understanding-postgres-schemas/) — The developers of the Dolt database have a lot of experience with MySQL but have moved into building [Doltgres](https://github.com/dolthub/doltgresql) as a version controlled database that offers Postgres compatibility. This has meant learning about about Postgres’ _schemas_. This is a good primer, and also shows off `search_path`, somewhat akin to the OS’s [PATH variable](https://en.wikipedia.org/wiki/PATH_%28variable%29) when resolving unqualified names in queries. Zach Musgrave **IN BRIEF:** - This week's _PostgreSQL Person of the Week_ interviewee [is Nazir Bilal Yavuz](https://postgresql.life/post/nazir_bilal_yavuz/), who works on Postgres at Microsoft. - Michael Christofides joined Claire Giordano on the _Path To Citus Con_ podcast to [▶️ discuss his background, `EXPLAIN`](https://www.youtube.com/watch?v=mlti_9eD3w0), and thoughts on Postgres's docs. - 🇨🇭 [The schedule for Swiss PGDay 2024](https://www.postgresql.org/about/news/swiss-pgday-2024-schedule-published-2856/) has been published – it takes place near Zurich this June 27-28. - The Azure team has a quick update on [the last month for _Azure PostgreSQL Flexible Server_](https://techcommunity.microsoft.com/t5/azure-database-for-postgresql/april-2024-recap-azure-postgresql-flexible-server/ba-p/4131848) touching on some new features and updates. - There was an interesting discussion on the [pros and cons of UUIDs vs integer sequences](https://www.reddit.com/r/PostgreSQL/comments/1ckzc8f/uuid_versus_sequence_why_is_a_uuid_bad_for/) over on Reddit. [How _Not_ to Change Postgres Column Type](https://notso.boringsql.com/posts/how-not-to-change-postgresql-column-type/) — `ALTER TABLE .. ALTER COLUMN` is fine in certain situations but has some limitations to be aware of, particularly in production. Luckily, there are some alternative routes. Radim Marek [The Notifier Pattern for Apps That Use Postgres](https://brandur.org/notifier) — Brandur is a big fan of Postgres’s built-in pub/sub features. Brandur Leach [`cursor_tuple_fraction` and PostgreSQL JDBC](https://www.cybertec-postgresql.com/en/cursor_tuple_fraction-in-postgresql-jdbc/) — Laurenz had me at the first sentence: _“This article is a kind of failure story.”_ Laurenz Albe 📄 [13 Tips to Improve `INSERT` Performance](https://www.timescale.com/blog/13-tips-to-improve-postgresql-insert-performance/) – The first five are general to Postgres, with the remainder being specific to the TimescaleDB time series data extension/system. Michael Freedman (Timescale) 📄 [Bringing IvorySQL to the Neon Autoscaling Platform](https://www.highgo.ca/2024/05/02/bringing-ivorysql-to-neon-autoscaling-platform/) – [IvorySQL](https://www.ivorysql.org/) is a Postgres-powered database that offers Oracle compatibility. David Zhang 📄 [Conversion Gotchas: Implicit Conversion in an Oracle to Postgres Migration](https://databaserookies.wordpress.com/2024/05/03/conversion-gotchas-implicit-conversion-in-oracle-to-postgresql-migration/) Deepak Mahto 📄 [Using Automatic DDL Replication with pgEdge Distributed PostgreSQL](https://www.pgedge.com/blog/using-automatic-ddl-replication-with-pgedge-distributed-postgresql) Cady Motyka (pgEdge) 📄 [(Bi)Temporal Tables, PostgreSQL and the SQL Standard](https://hdombrovskaya.wordpress.com/2024/05/05/3937/) Henrietta Dombrovskaya 📰 Classifieds Thinking about your career in 2024? [Explore our State of Database Landscape](https://www.red-gate.com/solutions/entrypage/state-of-database-landscape-2024/overview?utm_source=postgresweekly&utm_medium=email&utm_campaign=mc-202401-t1-sodl-navigating-the-db-landscape-gc-all&utm_content=Mayad) to find out which skills you should be looking at to stay ahead. --- ▶️ [How Figma scaled out their Postgres installation by 100x over 4 years](https://pganalyze.com/blog/5mins-postgres-figma-dbproxy-sharding-postgres), and switched to horizontal sharding using their DBProxy query proxy. --- _If you have an event or something you want to promote here, hit reply and let us know and we'll send you the details._ 🛠 Code and Tools [Saturation Arithmetic with a Postgres Extension](https://engineering.adjust.com/post/saturation_arithmetic_with_extension/) — Saturation arithmetic is the concept of bounding the results of arithmetic functions to within a certain range, thus preventing overflows. The folks at Adjust have built [pg-saturated_int](https://github.com/adjust/pg-saturated_int), an extension that provides an integer type adhering to this concept. Artur Zakirov [pg_back 2.3.0: A Simple, Thorough Backup Tool for Postgres](https://github.com/orgrim/pg_back) — A Go-powered tool for dumping your databases to files, including roles, server parameters, and more, in the format of your choice. Nicolas Thauvin [pgdsat: A Postgres Database Security Assessment Tool](https://github.com/HexaCluster/pgdsat) — Checks around 70 security controls on your clusters including all recommendations from the CIS compliance benchmark. HexaCluster ⚙️ [PG Slot Notify Bot](https://github.com/PeerDB-io/pgslot-notify-bot) – Notify slot growth to a Slack channel. PeerDB ⚙️ [pgAdmin 4 v8.6](https://www.postgresql.org/about/news/pgadmin-4-v86-released-2853/) **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|*).