--- title: "A Web Tool to Create Postgres Configuration Settings" publication: "Postgres Weekly" issue: 345 published_at: "2020-03-04" canonical_url: "https://postgresweekly.com/issues/345" --- This week's Postgres news, views, and tools. #345 — March 4, 2020 [Read on the Web](https://postgresweekly.com/issues/345) Postgres Weekly [![\](https://res.cloudinary.com/cpress/image/upload/w_1280,e_sharpen:60/nlwk6d6nenv6p2lxoget.jpg)](https://postgres.ai/blog/joe-0.5/) [Say Hi to 'Joe Bot', a Postgres Query Optimization Assistant](https://postgres.ai/blog/joe-0.5/) — An interesting project with quite a narrow focus, ‘Joe’ is a Slack (only, for now) chatbot that you can use along with [Database Lab](https://gitlab.com/postgres-ai/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](https://habr.com/en/company/postgrespro/blog/490228/) — The English isn’t perfect as this is a translation, but the author knows his stuff and you could learn something here. Egor Rogov [![\](https://copm.s3.amazonaws.com/ac3490f6.png)](https://pganalyze.com/ebooks/optimizing-postgres-query-performance?utm_source=PostgresWeeklyPrimary) [Free eBook: How to Get a 3x Performance Improvement on Your Postgres Database](https://pganalyze.com/ebooks/optimizing-postgres-query-performance?utm_source=PostgresWeeklyPrimary) — 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](https://www.cybertec-postgresql.com/en/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](https://pgtune.leopard.in.ua/#/) — 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](https://www.highgo.ca/2020/02/28/parallel-vacuum-in-upcoming-postgresql-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](https://www.citusdata.com/blog/2020/03/02/citus-9-2-speeds-up-large-scale-htap/) — 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](https://info.enterprisedb.com/20200122HowtoMonitorPostgresLikeaPro.html?utm_source=Cooperpress&utm_medium=ad) — 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](https://postgresql.life/post/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](https://postgresql.life/post/laetitia_avrot/) was interviewed last week too. Andreas Scherbaum [Optimizations in `GROUP BY` vs `SELECT DISTINCT`](https://www.thatguyfromdelhi.com/2020/02/optimizations-in-group-by-vs-select.html) That Guy From Delhi [A Certificate Authentication Recipe for Pgpool-II](https://www.highgo.ca/2020/02/25/setting-up-ssl-certificate-authentication-with-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](https://severalnines.com/database-blog/what-look-if-your-postgresql-replication-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\](https://res.cloudinary.com/cpress/image/upload/v1580736678/cttmroruvfsupeor4bug.png)](https://postgresconf.org/conferences/2020) 💡**Tip of the Week** Database `TEMPLATE`s 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.](https://www.postgresql.org/docs/9.5/manage-ag-templatedbs.html) This week's tip is sponsored by [PostgresConf](https://postgresconf.org/conferences/2020). Postgres Conference 2020: The largest gathering about [People, Postgres, and Data](https://postgresconf.org/conferences/2020). Join 800+ attendees and 100+ speakers to build opportunities for the global Postgres ecosystem. 🗓**Upcoming Events** - [Postgres Conference 2020](https://postgresconf.org/conferences/2020) (March 23-27 in New York, USA) - [Nordic PgDay 2020](https://2020.nordicpgday.org/) (March 24 in Helsinki, Finland) - [pgDay Paris 2020](https://2020.pgday.paris/#%2319) (March 26 in Paris, France) - [Swiss PGDay 2020](http://www.pgday.ch/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](https://pgibz.io/) (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. Curated by [Cooperpress](https://cooperpress.com/), [Peter Cooper](https://twitter.com/peterc), and [Craig Kerstiens](https://twitter.com/craigkerstiens). [Change your email address](https://postgresweekly.com/edit_subscription/*|UID|*) or[Stop getting this newsletter](https://postgresweekly.com/leave/*|UID|*) © Cooper Press Ltd · Fairfield Enterprise Centre, Louth, LN11 0LS, United Kingdom