--- title: "A Postgres 9 to 13 upgrade story" publication: "Postgres Weekly" issue: 451 published_at: "2022-04-20" canonical_url: "https://postgresweekly.com/issues/451" --- Plus going from INT to BIGINT, Postgres on ZFS, and a ML system on Postgres. | #​451 — April 20, 2022 [Web Version](https://postgresweekly.com/issues/451) Postgres Weekly [![\](https://res.cloudinary.com/cpress/image/upload/w_1280,e_sharpen:60/yrwb1ikowjn298kv96jx.jpg)](https://retool.com/blog/how-we-upgraded-postgresql-database/) [How Retool Upgraded Its 4TB Postgres Database to v13](https://retool.com/blog/how-we-upgraded-postgresql-database/) — 4TB might not be “big data” but it’s in a common zone for SaaS primary database size, so Retool’s story and tips learned along the way during an upgrade from Postgres 9.6 to 13 may prove of interest. Peter Johnston (Retool) [Faster Geospatial Enrichment: Postgres Versus..](https://tech.marksblogg.com/faster-geospatial-enrichment.html) — Mark is well known for producing some fantastic data analysis posts over the years (such as his [1.1 billion taxi rides](https://tech.marksblogg.com/omnisci-macos-macbookpro-mbp.html) series) and now he puts Postgres up against ClickHouse and BigQuery for converting a large set of coordinates into [H3](https://eng.uber.com/h3/) identifiers. Postgres didn’t win, but it also didn’t lose.. Mark Litwintschik [![\](https://copm.s3.amazonaws.com/c0eae61f.jpg)](https://www.timescale.com/?utm_source=pg-weekly-sponsor&utm_medium=newsletter&utm_campaign=pg-weekly-sponsor-apr-20-2022&utm_content=homepage) [The Postgres You Know and Love, with Time-Series Superpowers](https://www.timescale.com/?utm_source=pg-weekly-sponsor&utm_medium=newsletter&utm_campaign=pg-weekly-sponsor-apr-20-2022&utm_content=homepage) — TimescaleDB is the open-source relational database for time-series and analytics, built by developers for developers. It brings together the familiarity of PostgreSQL with speed and petabyte scale. Try it for free (no credit card required). Timescale sponsor [Find and Fix a Missing Postgres Index](https://www.cybertec-postgresql.com/en/find-and-fix-a-missing-postgresql-index/) — _“Missing indexes are a key ingredient if you are looking for a perfect recipe to ruin performance in the most efficient way possible,”_ says Hans-Jürgen, but luckily the order of the day here is how to _avoid_ that problem, fix missing indexes, and improve performance. Hans-Jürgen Schönig ▶[Advanced `INT` to `BIGINT` Conversions](https://www.youtube.com/watch?v=qYIVyoZkhCo) — Integer overflows have caused all manner of bugs, problems, and outages over the years, so should we just move to `BIGINT` for everything? Not so fast, says Robert, who digs deeper into the dilemma in this 20-minute talk. Robert Treat **In brief:** - The latest _PostgreSQL Person of the Week_ interview is [with Bharath Rupireddy](https://postgresql.life/post/bharath_rupireddy/) who works on Postgres's internals at Microsoft. He has some advice on how to work more closely with Postgres, if you wish to. - Not Postgres specific, but I found it interesting: if you're an Apple user, you can [use SQL to analyze your iMessage history.](https://arctype.com/blog/search-imessage/) - AWS RDS PostgreSQL recently [had a security issue](https://aws.amazon.com/security/security-bulletins/AWS-2022-004/) you might want to know about. Numerous Postgres versions were deprecated as a result. [Two Sizes Fit Most: Postgres and ClickHouse?](https://thenewstack.io/two-sizes-fit-most-postgresql-and-clickhouse/) — An opinion piece from GitLab’s CEO arguing that Postgres and Clickhouse cover most of the OLAP and OLTP bases businesses require (although arguably Postgres is entering the OLAP world more itself, [in the shape of HTAP](https://swarm64.com/post/using-postgresql-for-htap/)). Sid Sijbrandij [Formatting SQL Code with pgFormatter within Emacs](https://fluca1978.github.io/2022/04/13/EmacsPgFormatter.html) — If Emacs is your operating system editor of choice, this might be of use, though [pgFormatter](https://github.com/darold/pgFormatter) is a Perl-based SQL beautifier that can be used at the command line too (or even [on the web](https://sqlformat.darold.net/)). Luca Ferrari [Securing Your PostgreSQL Database: An Overview of Best Practices](https://goteleport.com/blog/securing-postgres-postgresql/?utm_campaign=eg&utm_medium=partner&utm_source=postgres) Teleport sponsor [Connecting to Postgres over SSL in Rust with Self-Signed Certificates](https://dev.to/yugabyte/use-postgresql-ssl-connection-in-rust-with-self-signed-certificates-4k9g) — It’s for YugabyteDB really, but it’s wire-compatible with Postgres so we’re promised it’ll work on both. Fits Hoogland ['Our Experience with Postgres on ZFS'](https://lackofimagination.org/2022/04/our-experience-with-postgresql-on-zfs/) — [ZFS](https://en.wikipedia.org/wiki/ZFS) is a file system with lots of useful advanced features (but with higher hardware requirements, too) including snapshots and transparent compression – both quite useful for database use cases. Aycan Gulez [A SQLite to PostgreSQL Migration Story](https://bytebase.com/blog/database-migration-sqlite-to-postgresql) — You don’t often hear about SQLite to Postgres migrations, but I’ve done a few myself when moving small Rails apps to cloud platforms (SQLite and containers make for sad times). There’s not much to this particular story but it’s nice to see people’s reasoning. Danny Xu [How to Generate Postgres Credentials with Hashicorp Vault and Go](https://splitmind.dev/posts/generate-creds-postgres-vault-with-golang/) Yandry Pozo **🔧 Code and Tools:** [Orafce: Oracle Compatibility Functions for PostgreSQL](https://github.com/orafce/orafce) — Orafce implements some of the functions from Oracle Database that might be useful for users migrating to Postgres. [v3.21.0](https://github.com/orafce/orafce/releases/tag/VERSION_3_21_0) backports `regexp_replace` and `regexp_instr` from the future Postgres 15. Orafce [PostgresML: Train Models and Make Predictions Using Only SQL](https://github.com/postgresml/postgresml) — _“You train models using simple SQL commands, and you get the predictions in your apps via a mechanism you’re already using: a query over a standard Postgres connection.”_ Lev Kokotov and Montana Low **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|*)