High-Performance Java Persistence Newsletter, Issue 31

Imagine having a tool that can automatically detect JPA and Hibernate performance issues. Hypersistence Optimizer is that tool!

Introduction

Welcome to a new issue of the High-Performance Java Persistence Newsletter in which we share articles, videos, workshops, and StackOverflow answers that are very relevant to any developer who interacts with a database system using Java.

Articles

The pick of this edition is this article which explains the perils of PostgreSQL nested transactions. The article is written by the GitLab engineers and explains what problems sub-transactions caused them.

Processing hierarchical data structures is very easy when using the SQL WITH RECURSIVE clause. For more details about this topic, check out this article.

In case you are using Aurora MySQL, then you really need to use a proper Primary Key column type, like an auto-incremented numerical column. Otherwise, you risk very high latency, as explained in this article.

If you’re using PostgreSQL, then you should definitely know how DISTINCT ON works, as it might be very useful in certain situations.

Blaze Persistence is a much better alternative to JPA Criteria API. If you want to see the difference, then this article will prove the point.

A very good read is this article about the internals of stream-based processing systems, like Apache Flink.

The SQL CROSS APPLY clause is very useful when using SQL Server, as it allows you to create lateral joins. For more details about this topic, check out this article.

When using PostgreSQL, it’s very important to keep an eye on long-running transactions, as they can cause the XID wraparound issue. Unfortunately, this issue happens more frequently than it should be, and the latest victim was Coveralls.

Best Tweets

Here are the best tweets I posted since the last newsletter:

Project Releases

PostgreSQL 14 has been released. Among the many new features, it’s worth mentioning the new JSON path expression syntax, which is more natural than the legacy one. There’s also a multirange column type that allows you to store non-contiguous ranges. There were also many performance improvements, like B+Tree bloat reduction, VACCUM speed up, or improved query parallelism.

Hibernate ORM 5.6.0 CR1 and 5.3.23 have been released.

The 42.2.24 version of the PostgreSQL JDBC Driver has been released and provides several bug fixes.

High-Performance SQL workshop

Next week, I’m going to run a new online workshop about the best way to write Subqueries, CTE, and Derived Table queries when using Oracle, SQL Server, PostgreSQL, and MySQL.

So, if you’re interested in the following topics:

  • Join vs. Semi join
  • EXISTS, NOT EXISTS
  • IN, NOT IN
  • ANY, ALL
  • Subquery column expressions
  • Derived Tables
  • CTE (Common Table Expressions)

Then, you should definitely join my High-Performance SQL Subqueries workshop.

Get in touch with my latest articles!

StackOverflow Answers

The following StackOverflow answers have been trending over the past two weeks:

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

4 day training with a Java Champion 🏆 29th Nov - 2nd Dec