#326 — October 9, 2019

Read on the Web

Postgres Weekly

PostgreSQL 12 Released — Just a year on from Postgres 11 comes version 12, another strong step forward for our favorite database. Key enhancements include SQL/JSON support, generated columns, and significant performance improvements (particularly with indexes and partitioned tables).

PostgreSQL Global Development Group

A Look at Generated Columns in Postgres 12 — “A generated column is sort of like a view, but for columns.” There are a few limitations, but essentially you can define columns that are based upon other columns. There’s a neat full text search example shown off here.

RapidLoop

Free Webinar: Learn PostgreSQL Partitioning with Simon Riggs — Get an overview of PostgreSQL partitioning features & how they play a key role in performance improvements of very large databases. Learn recent improvements to partitioning and parallelism, explore specific use cases, and explore upcoming features.

2ndQuadrant PostgreSQL Webinars sponsor

🐘 Postgres 12 Roundup

Over the past few months we've linked to a variety of posts covering new Postgres 12 features. Here's a recap:

Trace Query Processing Internals with a Debugger — Wow, this is some serious query debugging. I have no urge to dig this deep into Postgres, but it’s handy to know how if you do.

Cary Huang

Crunchy High-Availability PostgreSQL Keeps Critical Applications Up — Leverage trusted open source software to ensure your PostgreSQL data is always available.

Crunchy Data sponsor

Replication Configuration Changes in PostgreSQL 12 — Till now, replication configuration settings have been stored in a special file, recovery.conf, but in Postgres 12 the settings have been converted to normal Postgres config settings and so can now be managed in postgresql.conf or with ALTER SYSTEM, etc.

Ian Barwick

PostGIS 3.0.0 Release Candidate 1 — We linked to the beta last week, now the popular geospatial data extension for Postgres is already at RC1 with final release just around the corner.

PostGIS Developers

supported by PGX

💡 Tip of the Week

Shortcuts / variables for your queries in psql

If you're spending some serious time in psql coming up with queries, doing some optimization, or otherwise working on your database, you might find yourself typing the same things over and over, such as putting EXPLAIN ANALYZE before your queries in order to look at their query plans. Rather than typing this out each time, psql lets you define 'variables' which you can use as shortcuts in your queries.

Here's an example using EXPLAIN ANALYZE in particular:

\set ea 'EXPLAIN ANALYZE'

This sets a variable called ea to EXPLAIN ANALYZE and it can be used as simply as this:

:ea SELECT * FROM [...rest of query here]

It's certainly a lot easier to prefix a query with :ea than SELECT ANALYZE. You can also add these to a .psqlrc file if you want them to be defined each time you use psql. More on that here.

This week's tip of the week is sponsored by PostgreSQL Experts, Inc: Your data is your business; let us help you build, troubleshoot, and scale your PostgreSQL data systems.

🗓 Upcoming Events

  • pgDay Santiago (October 29 in Santiago, Chile) — pgDay Santiago is intended for PostgreSQL users of all levels, with talks for everyone and the chance to meet with your peers.
  • PG Down Under 2019 (November 15 in Sydney, Australia) — The second outing for this annual, Australian Postgres conference. The CFP remains open till next week.
  • PgConf.Russia 2020 (Febuary 3-5 2020 in Moscow, Russia) — One day of tutorials and two days of talks in three parallel sessions.
  • PGConf India 2020 (Febuary 26-28 2020 in Bengaluru, Maharashtra, India) — This year there is a dedicated training day and a multi-track 2-day conference.