#345 — March 4, 2020

Read on the Web

Postgres Weekly

Say Hi to 'Joe Bot', a Postgres Query Optimization Assistant — An interesting project with quite a narrow focus, ‘Joe’ is a Slack (only, for now) chatbot that you can use along with Database Lab (an open source real-time database cloner) to quickly understand real-world query performance in almost real-time.

Postgres.ai

How to Write Complex Recursive SQL Queries — The English isn’t perfect as this is a translation, but the author knows his stuff and you could learn something here.

Egor Rogov

Free eBook: How to Get a 3x Performance Improvement on Your Postgres Database — Learn our best practices for optimizing Postgres query performance for customers like Atlassian and how to reduce data loaded from disk by 500x.

pganalyze sponsor

Reduce WAL by Increasing Checkpoint Distance — Adjusting checkpoints is useful when tuning a server and improves the database performance. Furthermore, it helps to reduce the amount of write ahead logs overall.

Hans-Jürgen Schönig

PGTune: A Web Tool to Create Postgres Configuration Settings — A golden oldie that we haven’t linked for a while. Pick your version of Postgres (now supporting v12 too), say what sort of usage you have, total RAM, etc. and it generates some config settings that will be better than the defaults (though you will still have tweaking to do).

Alexey Vasiliev

Parallel Vacuum in the Upcoming Postgres 13 — It’s taken years to come to fruition but the main work to support being able to vacuum in parallel has been committed to Postgres core. Here’s what it means, and the benefits.

Hamid Akhtar

Citus 9.2 Speeds Up Large Scale HTAP Workloads on Postgres — If you’ve been wondering “what’s happening with the Citus open source extension to Postgres?” ever since the team went to Microsoft, the short answer is “a lot.” Citus 9.2 includes performance improvements across important areas for HTAP (hybrid transactional analytic processing) including CTEs, aggregate functions, & re-partition joins.

Marco Slot (Citus Data)

Webinar: How to Monitor Postgres Like a Pro — Learn why it is essential to monitor Postgres and when not to monitor on this on-demand webinar.

EnterpriseDB sponsor

A Quick Interview with Joe Conway — Joe, a Postgres committer, has been working with Postgres almost solidly for twenty years, so he’s got a lot to share. Find out about his background, how he contributes to Postgres, and what his favorite extension is. Lætitia Avrot was interviewed last week too.

Andreas Scherbaum

Optimizations in GROUP BY vs SELECT DISTINCT

That Guy From Delhi

A Certificate Authentication Recipe for Pgpool-II — A recipe for generating the self-signed SSL certificates and configuring certificate authentication with Pgpool-II.

Muhammad Usama

What to Look for if Your Postgres Replication is Lagging — Replication lag can be a common problem, but this post discusses what things to look for when experiencing a replication lag with Postgres.

Paul Namuag

supported by PostgresConf

💡 Tip of the Week

Database TEMPLATEs

In my usual line of work, databases get generated either manually by me or from schemas that I define in frameworks such as Ruby on Rails or Django. In the cases that I do it by hand, I usually lay out a schema in SQL and then run the relevant CREATE TABLE queries as I need to — old school!

If a database already exists with the schema you need, however (such as for a test database, say), did you know you can use it as a template for a new one?

CREATE DATABASE appdb_test TEMPLATE appdb;

This could be particularly handy if you just want to temporarily run some code against a copy of a database, such as in testing.

Another interesting bit of trivia: when you run a normal CREATE DATABASE command without specifying a template, a default template database called template1 is used behind the scenes, meaning that if you always want new databases on the same server to contain similar things, you can just add them to that template1 database.

For example, if you always use the hstore extension, it might make sense to have a policy of always running CREATE EXTENSION hstore; against template1 to ensure its presence in future, fresh databases (even if they are created from automated systems or ORMs).

There's more info on all of this here.

This week's tip is sponsored by PostgresConf. Postgres Conference 2020: The largest gathering about People, Postgres, and Data. Join 800+ attendees and 100+ speakers to build opportunities for the global Postgres ecosystem.

🗓 Upcoming Events

  • Postgres Conference 2020 (March 23-27 in New York, USA)
  • Nordic PgDay 2020 (March 24 in Helsinki, Finland)
  • pgDay Paris 2020 (March 26 in Paris, France)
  • Swiss PGDay 2020 (June 18-19 in Switzerland) — A two track conference (one in English, one in German) aimed at the entire Postgres community.
  • Postgres Ibiza 2020 (June 25-26 in Ibiza, Spain) — A two day conference on the sunny island of Ibiza in the Mediterranean. I recall this one getting some great trip reports last year!

Running a Postgres related event that's beyond the scale of a small local meetup or user group? Let us know (just hit reply) and we can include it here in future issues.