--- title: "Pagination, backtraces, and going beyong JSONB" publication: "Postgres Weekly" issue: 368 published_at: "2020-08-12" canonical_url: "https://postgresweekly.com/issues/368" --- Plus more news, tutorials, and tips from the Postgres world this week. #368 — August 12, 2020 [Web Version](https://postgresweekly.com/issues/368) Postgres Weekly [![\](https://res.cloudinary.com/cpress/image/upload/w_1280,e_sharpen:60/bduujyk1y2kizpzkyo73.jpg)](https://momjian.us/main/blogs/pgblog/2020.html#August_10_2020) [Postgres Pagination Approaches](https://momjian.us/main/blogs/pgblog/2020.html#August_10_2020) — 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'](https://www.cybertec-postgresql.com/en/a-quick-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 [![\](https://copm.s3.amazonaws.com/5d5e2702.png)](https://resources.2ndquadrant.com/alwayson-postgres-whitepaper?utm_campaign=AlwaysOn%20Multi-Master%20PostgreSQL&utm_source=CooperPressBDRAlwaysOnPGWhitepaperAug12) [[Whitepaper\] AlwaysOn Postgres](https://resources.2ndquadrant.com/alwayson-postgres-whitepaper?utm_campaign=AlwaysOn%20Multi-Master%20PostgreSQL&utm_source=CooperPressBDRAlwaysOnPGWhitepaperAug12) — 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](https://blog.jooq.org/2020/08/05/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](https://amitdkhan-pg.blogspot.com/2020/07/backtraces-in-postgresql.html) — 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](https://www.highgo.ca/2020/08/10/postgresql-high-availability-the-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](https://pganalyze.com/ebooks/monitoring-postgres-logs?utm_source=PostgresWeeklySecondary08122020) — 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](https://www.highgo.ca/2020/08/08/partitioning-improvements-in-postgresql-13/) Ahsan Hadi [Avoiding the Pitfalls of BRIN Indexes](https://info.crunchydata.com/blog/avoiding-the-pitfalls-of-brin-indexes-in-postgres) John Porvaznik [A Crash Course on Postgres for R Users](https://pacha.dev/blog/2020/08/09/a-crash-course-on-postgresql-for-r-users/) — [R](https://www.r-project.org/) 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](https://t.co/HE6nZMmD5f?amp=1) Rob Tomlin [Going Beyond `jsonb`? A Generalized, Unstructured Data Type for Postgres](https://www.ongres.com/blog/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](http://okbob.blogspot.com/2020/08/plpgsqlcheck-now-supports-tracing.html) — [plpgsql_check](https://github.com/okbob/plpgsql_check) is a linter for Pl/pgSQL code. Pavel Stěhule [pgagroal 0.8.0: A High Performance Postgres Connection Pool](https://agroal.github.io/pgagroal/) — 0.8 brings failover and systemd support. Red Hat Inc. supported by [![YugabyteDB\](https://res.cloudinary.com/cpress/image/upload/v1593529453/obtljl96reuhv3jzrxqr.png)](https://www.yugabyte.com/virtual-tech-talk/?utm_source=postgres_weekly&utm_medium=tip_of_week&utm_campaign=aug122020) 💡**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](https://www.yugabyte.com/virtual-tech-talk/?utm_source=postgres_weekly&utm_medium=tip_of_week&utm_campaign=aug122020), 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.](https://www.yugabyte.com/virtual-tech-talk/?utm_source=postgres_weekly&utm_medium=tip_of_week&utm_campaign=aug122020) 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