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.
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
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.
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.
💡 Tip of the Week
Shortcuts / variables for your queries in
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
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
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.
(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.