--- title: "psql tips" publication: "Postgres Weekly" issue: 400 published_at: "2021-04-07" canonical_url: "https://postgresweekly.com/issues/400" --- Plus how Discourse has benefitted from a Postgres 13 upgrade. | #400 — April 7, 2021 [Web Version](https://postgresweekly.com/issues/) Postgres Weekly [![\](https://res.cloudinary.com/cpress/image/upload/w_1280,e_sharpen:60/u3zj8wg9ut3kfegqypys.jpg)](https://mydbanotebook.org/psql_tips_all.html) [100 `psql` Tips](https://mydbanotebook.org/psql_tips_all.html) — You know how we occasionally include a Postgres “tip of the week”? This is a similar idea but bite-sized tips for `psql` in particular. If you have a `psql` session coming up any time soon, this is worth a browse! It’s also possible to load [a single random tip](https://mydbanotebook.org/psql_tips.html) if you don’t want to be overwhelmed. Lætitia Avrot [Standing on the Shoulders of a Giant Elephant: Upgrading Discourse to Postgres 13](https://blog.discourse.org/2021/04/standing-on-the-shoulders-of-a-giant-elephant/) — A quick piece from the folks who run the [Discourse](https://www.discourse.org/) forum system on their upgrade to Postgres 13. B-tree deduplication was the winning feature for them. Rafael dos Santos Silva (Discourse) [![\](https://copm.s3.amazonaws.com/e41db651.png)](https://pganalyze.com/ebooks/optimizing-postgres-query-performance?utm_source=PostgresWeeklyPrimary04072021) [Best-Practices on How to Speed Up Your Postgres Queries. Free eBook](https://pganalyze.com/ebooks/optimizing-postgres-query-performance?utm_source=PostgresWeeklyPrimary04072021) — We share our learnings from helping companies like Atlassian, CounterPath, and others speed up their queries. pganalyze sponsor [Working with Roles and Permissions](https://www.cybertec-postgresql.com/en/postgresql-get-member-roles-and-permissions/) — Postgres’s extensive access permissions system is powerful but can be a stumbling block when compared to simpler schemes in other database systems. Here are a few queries to help you get a hold on things (the recursive query at the end is rather neat). Hans-Jürgen Schönig **Quick bits:** - [pgBackRest](https://pgbackrest.org/), a backup and restore tool, has [added Google Cloud Storage (GCS) support](https://blog.crunchydata.com/blog/announcing-google-cloud-storage-gcs-support-for-pgbackrest) as another place to easily send your backups. - Hubert Lubaczewski has written a string of useful, though niche, posts on new things coming to Postgres 14 you might be interested in. He's covered the [unistr function](https://www.depesz.com/2021/04/01/waiting-for-postgresql-14-add-unistr-function/), the [`pg_database_owner` default role](https://www.depesz.com/2021/04/01/waiting-for-postgresql-14-add-pg_database_owner-default-role/), the [`date_bin` function](https://www.depesz.com/2021/03/31/waiting-for-postgresql-14-add-date_bin-function/), and [configurable LZ4 TOAST compression.](https://www.depesz.com/2021/03/22/waiting-for-postgresql-14-allow-configurable-lz4-toast-compression/) - Rick Branson's ["10 things I hate about Postgres"](https://rbranson.medium.com/10-things-i-hate-about-postgresql-20dbab8c2791) from 2020 has done the rounds on social media again. A few of these things are beginning to be mitigated now. - AWS has a new series of posts about [converting from the `NUMBER` type](https://aws.amazon.com/blogs/database/convert-the-number-data-type-from-oracle-to-postgresql-part-1/) ([part 2](https://aws.amazon.com/blogs/database/convert-the-number-data-type-from-oracle-to-postgresql-part-2/)) when moving from Oracle to Postgres. It's harder than it seems, and you can't just switch to `NUMERIC` without a second thought. [On the Many Upgrade Methods of Postgres](https://momjian.us/main/blogs/pgblog/2021.html#April_5_2021) — Bruce lists _8_ possible methods for major major version upgrades and explains which suit certain requirements. Bruce Momjian [[Report\] Benchmarking the Cloud: AWS vs. GCP vs. Azure](https://www.cockroachlabs.com/guides/2021-cloud-report/?utm_source=dzone&utm_medium=sponsor&utm_campaign=2021-cloud-report&utm_content=postgresweekly-sponsoredlink&utm_term=postgres-weekly-0407) — Which cloud has the lowest latency? The best storage performance? The most throughput? This report digs into the tradeoffs each cloud makes - so you can choose the best option for your needs. Cockroach Labs sponsor [Anything I Want With Sequel and Postgres](https://janko.io/anything-i-want-with-sequel-and-postgres/) — Janko demonstrates how Ruby's _Sequel_ gem supports advanced features of Postgres, allowing him to bulk import CSV files into a partitioned set of tables that required some obscure PG knowledge. Janko Marohnić [Managing the Logging of Pgpool-II on Kubernetes](https://b-peng.blogspot.com/2021/03/logging-pgpool-on-k8s.html) Bo Peng [Preventing FreeBSD's OOM Mechanism from Killing Postgres](https://fluca1978.github.io/2021/04/02/OOMKillerFreeBSD.html) Luca Ferrari 🔧**Tools and Code** [PostgREST: Serve a RESTful API from a Postgres Database](https://postgrest.org/en/stable/) — I was surprised to see I hadn’t linked to this useful project in over a year. It hasn’t seen any large updates recently but is still actively under development and could come in useful for spinning up an API in a hurry. Joe Nelson and Steve Chavez [pgtt: PostgreSQL Global Temporary Tables](https://github.com/darold/pgtt/) — An extension to create, manage and use _Oracle_-style Global Temporary Tables. Gilles Darold **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! --- **Want more?** You might like [DB Weekly](https://dbweekly.com/?utm_source=pg&utm_medium=email&utm_campaign=footer-promo), our more general database newsletter covering data storage trends, new database types, and more. --- 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|*)