Postgres Weekly Issue 361: June 24, 2020

#361 — June 24, 2020

'10' Things Postgres Could Improve — An in-progress four part series (part 2 here) covering topics like transaction ID issues and replication.

Shaun Thomas

📊 PDF: It's Time for a JSON/JSONB Great Unification — This dense, technical slidedeck is for you if you’re really into your JSON and you really care about Postgres’s JSON capabilities. It digs into JSON support in a forthcoming SQL standard and what future versions of Postgres can do to support this by unifying JSON and JSONB into one new data type.

Oleg Bartunov

We Help Customers Speed Up Postgres Queries By 1000x. Learn How — With pganalyze, companies like Atlassian are able to speed up their queries by orders of magnitude. In this ebook, we share our best practices for optimizing Postgres performance.

pganalyze sponsor

Postgres 13 Beta 1 Now in Amazon RDS Database Preview Environment — RDS lets you run a managed Postgres deployment on AWS and the Preview Environment lets you play with pre-release versions if you want to test your tooling, etc.

Amazon Web Services

How to Force a Table to Have Just One Row — Bruce demonstrates how to force a table to have at most one row by creating a unique expression index on a constant, with no column name references.

Bruce Momjian

EnterpriseDB Rebrands to EDB — EnterpriseDB are a popular company in the Postgres space so if you now see 'EDB' anywhere, well.. it's them :-)

EnterpriseDB / EDB

SQL Trickery: Hypothetical Aggregates — You can use this technique to determine the rank of a hypothetical value within an existing set.

Hans-Jürgen Schönig

tuned, Postgres, and You — tuned is a dynamic adaptive system tuning daemon (from Red Hat) that tunes system settings dynamically depending on usage. This post demonstrates creating a tuned profile for Postgres in particular.

Douglas J Hunley

ltree vs. WITH RECURSIVE — This is a follow up to Hans-Jürgen’s article on hierarchical queries.

Hans-Jürgen Schönig

Your Data Is Your Business — PGX is a full-service database consultancy focused on PostgreSQL data systems, on any platform or hosting environment.

PostgreSQL Experts, Inc. sponsor

Wrapping Db2 with Postgres — Db2 is a family of database products developed by IBM and focused on enterprise use. If you need to migrate Db2 data to Postgres, db2_fdw provides a way to do it.

Marcelo Diaz

Reasons to Migrate from Oracle to Postgres — This is truly a case of ‘preaching to the choir’ in this newsletter, but if you need any more reasons when chatting to others.. 😄

Kirk Roybal

pg_auto_failover: Automated Failover and High-Availability Extension — Monitors and manages automated failover for a Postgres cluster.

Citus Data

supported by

💡 Tip of the Week

Converting SQL between dialects

This is one of those 'tool' tips where you're unlikely to need it right now but at some point in the future you'll go: "Aha! I know what I need to solve this!" 😂

Let's say you have some SQL written in a dialect that isn't compatible with Postgres, or perhaps the opposite where you need to take your Postgres query into the Oracle or SQL Server world — what can you do?

You either hit the documentation and work out how to convert between SQL dialects, or you first hit up the jOOQ SQL Translator:

It's far from a perfect tool, but if you've forgotten some quirk about Oracle, SQL Server (or one of several dialects the tool supports), it can provide a useful sense check and I've leaned on it a few times to double check if the SQL I'm suggesting to others is widely supported.

This week’s tip is sponsored by Retool. Build internal tools in days, not weeks. Retool provides UI building blocks that connect to any DB and API so you can quickly build the tools your company needs.

🗓 Upcoming Online Events

Postgres Pulse - Zoom-based sessions with folks like Bruce Momjian, Vibhor Kumar, and other people at EnterpriseDB. Now running every other week at 11am ET on Mondays. The next is on June 29 (next Monday).
Table Partitioning in Postgres - Join Amit Langot for this free session in which he looks at partitioning as a tool to solve certain problems. June 26/27 (depending upon timezone).