#368 — August 12, 2020

Web Version

Postgres Weekly

Postgres Pagination Approaches — Most webapp users don’t want to see a list of thousands of items at once, so pagination is often adopted as a way to ‘page’ through a larger group of items in more manageable chunks. There are several ways to achieve this with Postgres with various tradeoffs.

Bruce Momjian

A pg_stat_statements Troubleshooting 'Hack' — If you want to get a useful ‘trick up your sleeve’ for troubleshooting with pg_stat_statements, you might enjoy Kaarel’s story.

Kaarel Moppel

[Whitepaper] AlwaysOn Postgres — Discover how to achieve AlwaysOn availability in PostgreSQL using BDR - groundbreaking technology from 2ndQuadrant. AlwaysOn guarantees up to six 9s of availability for worldwide PostgreSQL database clusters running mission critical business applications.

2ndQuadrant PostgreSQL Products sponsor

Use NATURAL FULL JOIN to Compare Two Tables in SQL — “NATURAL JOIN is syntax sugar for joining using all the shared column names of the two tables, and the FULL JOIN makes sure we can retrieve also the columns that are not matched by the join predicate.”

Lukas Eder

Backtraces in Postgres 13 — Postgres 13 introduces a simple but useful capability to log a stack trace into the server logs when an error is reported.

Amit Khandekar

Postgres High Availability: Considerations and Candidates — The first in a promised series of posts looking at various ways to introduce high availability concepts to Postgres. This time, RepMgr, Patroni, PAF, and PgPool-II are mentioned.

Hamid Akhtar

eBook: The Most Important Events to Monitor in Your Postgres Logs — In this eBook, we are looking at the Top 6 Postgres log events for monitoring query performance and preventing downtime.

pganalyze sponsor

On Partitioning Improvements in Postgres 13

Ahsan Hadi

Avoiding the Pitfalls of BRIN Indexes

John Porvaznik

A Crash Course on Postgres for R Users — R is a popular statistical computing environment and language, so you might find it useful to use Postgres from it.

Pachá

Working with a JSONB Array of Objects in Postgres

Rob Tomlin

Going Beyond jsonb? A Generalized, Unstructured Data Type for Postgres — Álvaro wonders if regular JSON support is enough and if there should be something beyond it in Postgres – a generalized unstructured data type, if you will.

Álvaro Hernández

plpgsql_check Now Supports Tracing — plpgsql_check is a linter for Pl/pgSQL code.

Pavel Stěhule

pgagroal 0.8.0: A High Performance Postgres Connection Pool — 0.8 brings failover and systemd support.

Red Hat Inc.

supported by YugabyteDB

💡 Tip of the Week

Getting NOW() in your preferred time zone

When you use the NOW() function to get the current time, you get the current date and time in the server's timezone.

Many servers run on UTC/GMT and if you normalize your use of time around UTC/GMT as well, everything is well, but as soon as there's a mismatch (whether between your app and your database server, or otherwise) things can go awry so it pays to be more specific.

NOW() returns a timestamp with time zone (a.k.a. timestamptz in Postgres or YugabyteDB) value but if you have a plain timestamp [without time zone] column and place NOW() into it, the timezone information is silently dropped:

=# create table test
      (a int primary key, b timestamp without time zone);
    
=# select now(), pg_typeof(now());
    
                now            |     pg_typeof    
-------------------------------+--------------------------
 2020-05-06 16:44:03.917735-07 | timestamp with time zone
(1 row)


=# insert into test values
    (1, '2020-05-06 16:44:03.917735-07');
    
=# select * from test;
    
 a |          b         
---+----------------------------
 1 | 2020-05-06 16:44:03.917735
(1 row)

=# show timezone;
  TimeZone
------------
US/Pacific

We can see that the column “b” discards the time zone. This means that the stored result is wrong in the sense that it does not respect the user’s convention to understand stored plain timestamp values as UTC values.

We can remedy this by using now() at time zone 'utc' like so:

=# insert into test values
  (2, (now() at time zone 'utc'));
 
=# select * from test;
 
 a |          b         
---+----------------------------
 1 | 2020-06-10 19:38:22.859175
(1 row)

This week’s tip is sponsored by YugabyteDB, the high-performance distributed SQL database for internet-scale applications. Want to set up a virtual tech talk to learn more? Reach out now() or anytime.