Postgres Weekly Issue 335: December 11, 2019

#335 — December 11, 2019

Did you know two thirds of Postgres users have never attended a Postgres-related event? If you want to change this, our events listing section at the end of each issue is now even larger :-) Check it out.

Odyssey 1.0: A Scalable Postgres Connection Pooler — Yandex has just released version 1.0 of their multi-threaded connection pooler and request router. “We are using Odyssey in production. Currently, it handles more than 1,000,000 requests per second on many hundreds of hosts.”

Yandex

Postgres at a Low Level: Stay Curious — A meaty post that’s a sort of grab-bag of interesting low level bits and pieces that you might find interesting (but are not particularly essential). Swerves between topics including buffering, BPF, and IO scheduling.

Dmitry Dolgov

Your Data Is Your Business — PGX is a full-service database consultancy focused around PostgreSQL systems. Let us help you architect, build, troubleshoot and scale your PostgreSQL data systems on any platform or hosting environment.

PostgreSQL Experts, Inc. sponsor

The State of PostgreSQL Survey Results — We encouraged you to fill out this Postgres survey a few months ago and.. about 500 people did. Half of respondents are using Postgres on AWS, 80% are using Postgres outside of work too, and most of us haven’t ever attended a real life Postgres event. Robert Treat has some independent thoughts on the results.

Timescale

PGCon 2020 Call for Papers Now Open — PGCon 2020 is taking place in Ottawa, Canada next May. They’re looking for speakers and have a deadline of January 19, 2020 to get your proposal in. There’s a neat list of talk ideas to get you thinking on this page too.

PGCon

Amazon Teases a Proxy System for RDS — If you’ve got a lot of processes (or Lambda functions, say) hitting RDS-based databases, this could eventually come in very handy. Currently it’s MySQL only (PostgreSQL support is ‘coming soon’) and in preview only.

Amazon Web Services

EnterpriseDB Announces Postgres Platform 12 — EnterpriseDB offers commercial Postgres distribution with added management tools, Kubernetes operator, etc.

EnterpriseDB

supported by

💡 Tip of the Week

See how psql's commands use SQL behind the scenes

When you use psql, you are almost certainly using commands like \dt to get a list of tables or \l to list tables — but how do they work?

I've tended to think of such commands as 'magic' since it's easy to assume that psql has a view into Postgres that I don't, but under the hood it's actually just issuing SQL queries like any that I might choose to run too. Can we see what these SQL queries are? Yes!

psql has a ECHO_HIDDEN variable you can set to show (or 'echo') any SQL queries performed behind the scenes by backslash commands. Set it like so:

    \set ECHO_HIDDEN on
    

Then, see what happens when you run a command like \dl:

    => \l

********* QUERY **********

SELECT d.datname as "Name",

       pg_catalog.pg_get_userbyid(d.datdba)

         as "Owner",

       pg_catalog.pg_encoding_to_char(d.encoding)

         as "Encoding",

       d.datcollate as "Collate",

       d.datctype as "Ctype",

       pg_catalog.array_to_string(d.datacl, E'\n')

         AS "Access privileges"

FROM pg_catalog.pg_database d

ORDER BY 1;

**************************

... list of databases follows here as usual ...

If you were to copy and paste this query for yourself, you'd get the very same results that \l usually does.

The practical utility of this tip may seem limited at first, but could help provide you with an understanding of how things are represented behind the scenes, and potentially tweak such queries to better suit your own database interrogation needs.

This week’s Tip of the Week is sponsored by DigitalOcean. Find out how engineers at DigitalOcean built a scalable marketplace for developers on top of their managed Kubernetes service.

📂 And the rest

DB Fiddle: An SQL Database Playground — I frequently find this tool useful for testing out little bits of SQL. It lets you run basic queries upon several versions of Postgres (including, now, version 12), MySQL, and SQLite, right from your browser.

Status200

Postgres 12's EXPLAIN SETTINGS — A way to make EXPLAIN output more representative of what really happened when settings that affect the query have been changed.

Luca Ferrari

Take the Guesswork Out of Improving Query Performance — Based on the query plan, pgMustard offers you tips to make your query faster. Try it for free.

pgMustard sponsor

How to Perform a Major Version Upgrade Using pg_upgrade

James Chanco Jr.

How to Create a Serverless GraphQL API for Postgres and Aurora — Learn how to build a serverless GraphQL API backed by Postgres (and MySQL) on Amazon’s Aurora.

Gareth McCumskey

Interesting Aspects of Logical Replication at a Glance

Rafia Sabih

phpPgAdmin 7.12.1 Released — A Web-based administration tool for Postgres.

Robert Treat

🗓 Upcoming Events

If you're in the 66% of Postgres users who haven't attended a Postgres event yet, there are lots coming up:

PgDay SF (January 21 in San Francisco) — A one-day, single-track event.
PgDay FOSDEM (January 31 in Brussels, Belgium) — Not only is there this Postgres event surrounding the popular open source event, but there's a Postgres room at FOSDEM itself on February 2 too.
PgConf.Russia (Febuary 3-5 in Moscow, Russia) — One day of tutorials and two days of talks in three parallel sessions.
PgConf India (Febuary 26-28 in Bengaluru, Maharashtra, India) — A dedicated training day and a multi-track two-day conference.
Nordic PgDay 2020 (March 24 in Helsinki, Finland) — Their CFP is only open till the end of the month so be quick.
pgDay Paris 2020 (March 26 in Paris, France) — Learn more about the world’s most advanced open source database among your peers.
Swiss PGDay 2020 (June 18-19 in Switzerland) — A two track conference (one in English, one in German) aimed at the entire Postgres community.

If you are running a Postgres related event that's beyond the scale of a small local meetup or user group (so, say, 50+ people with people likely to travel to your event from afar), let us know and we can include it here in future issues.