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!
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.
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.
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.
Here are the best tweets I posted since the last newsletter:
Planning software projects pic.twitter.com/Qnk1R5m5As— Vlad Mihalcea (@vlad_mihalcea) September 22, 2021
Another day has passed without having to invert a binary tree— Vlad Mihalcea (@vlad_mihalcea) September 8, 2021
As software developers, we are very fortunate to be able to earn good money while doing what we really enjoy.— Vlad Mihalcea (@vlad_mihalcea) September 19, 2021
It's amazing how much you can achieve with Spring Boot.— Vlad Mihalcea (@vlad_mihalcea) September 8, 2021
In one week, I got a fully-working trade reporting application with responsive UI, auto DB schema migrations, email notifications, and even external payment integration.
Hats off to @starbuxman and Co.
The Facebook user doesn't care the website is built using PHP.— Vlad Mihalcea (@vlad_mihalcea) September 18, 2021
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.
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
- 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!
The following StackOverflow answers have been trending over the past two weeks:
- SQL Server DATEDIFF round up the YEAR difference. How to round it down?
LockModerelease and fail-fast if the lock cannot be acquired
- Why is Hibernate Open Session in View considered bad practice?
@Transactionalannotation self invocation
- How to map two JPA or Hibernate entities on the same database table