Postgres Weekly Issue 374: September 23, 2020

#374 — September 23, 2020

A Battleship Game, Implemented with Postgres — See SQL taken to the next level with a working game running within Postgres, complete with a creative way of taking player input.

Firemoon777

PostgreSQL 13 Release Candidate 1 Released — We link to things about Postgres 13 all the time, if you hadn’t noticed, and it’s shaping up to be a huge release – so it’s great to see it close to completion with this first RC. The beta release notes cover the essentials, but we’ll do a full roundup of features at the final release.

PostgreSQL Global Development Group

Highway to Zero Downtime PostgreSQL Upgrades — Get a comprehensive walk-through of how to perform a "near" zero downtime upgrade using pglogical in this free webinar. Learn how logical decoding presents a whole new world of opportunities for upgrades that require a very small amount of downtime.

2ndQuadrant PostgreSQL Webinars sponsor

Crunchy Bridge: The Newest 'Postgres As A Service' — Crunchy Data is the latest company to get on the ‘Postgres as a managed service’ bandwagon with Crunchy Bridge which is available on AWS and Azure (and supports migration and replication between the two).

Craig Kerstiens (Crunchy Data)

Diary of an Engineer: Delivering 45x Faster Percentiles using Postgres, Citus, and t-digest — Nils had a problem to solve for a customer but couldn’t meet their SLA of 30 seconds and didn’t have the customer’s data to experiment with.. nonetheless, he found a creative way to estimate which types of percentile calculations would meet their SLA and used t-digest to do it.

Nils Dijk (Microsoft)

Postgres 13's LIMIT ... WITH TIES — WITH TIES is a new SQL standard feature being implemented in Postgres that causes LIMIT (or FETCH FIRST) clauses to not just cut off at a specified limit but to also include rows with values that tie with the final one(s).

Álvaro Herrera

Lessons Learned from Running Postgres 13: Better Performance, Monitoring & More — We took a look at smaller indexes with B-Tree Deduplication, Parallel VACUUM, improved WAL Usage Stats, and more.

Pganalyze sponsor

How 'HOT' Updates Yield Better Performance — An introduction to a feature included first with Postgres 8.3 but which, allegedly, are not properly covered in the docs. HOT updates (Heap Only Tuple) occur behind the scenes and improve performance in certain situations where lots of UPDATEs occur.

Laurenz Albe

AWS Aurora Postgres Versions 'Vanished' for Days, Customers Claim — Greg Clough, a software engineer who uses AWS, noticed that several Postgres versions on AWS Aurora ‘vanished’ last week (in the sense they couldn’t be deployed – existing datbases didn’t disappear). Most now appear to be back, but it’s a curious story.

The Register

📄 Postgres and the Artificial Intelligence Landscape — It’s just slides for now (though a talk was given) but Bruce’s slides often provide value even on their own.

Bruce Momjian

Exploring PL/Python: Turning Postgres Table Data Into a NumPy Array

Kat Batuigas

Why RudderStack Used Postgres Over Apache Kafka for a Streaming Engine — Kafka was a natural fit for what RudderStack, a data platform, does, but they found enough negatives about it to build their own queueing system on top of Postgres instead.

RudderStack

supported by

💡 Tip of the Week

'Extracting' a Date from a TIMESTAMP

    CREATE TABLE user_login(name TEXT, login_time TIMESTAMP);
    

The TIMESTAMP type stores a complete date and time with or without timezone, as opposed to DATE or TIME which respectively store only those particular elements. But what if you want to return only the date a TIMESTAMP refers to?

There are lots of date and time functions in Postgres, and you could extract the date elements piece by piece using EXTRACT:

    SELECT EXTRACT(MONTH FROM TIMESTAMP '2020-09-21 12:21:13');

SELECT EXTRACT(DAY FROM TIMESTAMP '2020-09-21 12:21:13');

SELECT EXTRACT(YEAR FROM TIMESTAMP '2020-09-21 12:21:13');

But the easiest way is to cast the TIMESTAMP type into a DATE which automatically does the conversion needed:

    SELECT name, login_time::date FROM user_login;

 name | login_time

------+------------

 john | 2019-11-11

 bill | 2020-10-22

 jane | 2020-04-01

(3 rows)

(Bill logged in from the future..?)

You could also use the DATE function to create a date in a similar way:

    SELECT DATE('2020-09-21 12:21:13');

# => 2020-09-21T00:00:00.000Z

This week’s tip is sponsored by YugabyteDB. If you would like to set up a free one hour deep-dive Distributed SQL tech talk in your future, please let us know.