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, written by Franck Pachot about PostgreSQL caching on Linux. Unlike other relational database systems, PostgreSQL uses double buffering, so there is a chance that the pages stored in the shared_buffers are also cached by the OS cache. This article demonstrates how the PostgreSQL caching work and tells you why the cache column of the free command includes both the shared_buffers and OS cache data.

If you are using MySQL and want to optimize a given query and the execution plan is not helping you enough, then you have the option of getting a query profile and find out exactly how much time is spent in every query execution stage. If you are interested in this topic, then you should definitely read this article.

Another very useful article for PostgreSQL users is this one, which explains how you can free up space taken by unused indexes or bloat indexes or tables.

Most relational database systems support JSON column types, and SQL Server is no different. While the relational model is still the best way to represent data in a relational database, JSON column types can be very suitable for audit logging. And, what’s great about JSON columns is that you can always transform them into a relational model, using a function like OPENJSON in SQL Server.

UUIDs are tempting when thinking of choosing a table identifier that’s unique across multiple nodes in a distributed system. However, if you are using a clustered index, which is the default table structure in MySQL and SQL Server, then UUIDs might not be the best choice. FOr more details about this topic, check out this article on the Percona blog.

SQL JOINs are omnipresent when using a relational database system, so knowing how the database executes a join is very important when analyzing an SQL execution plan. For this reason, I wrote 3 articles explaining how the three best-known JOIN strategies work:

• Nested Loop Join
• Hash Join
• Merge Join

Workshop Recording

On the 10th of February, I ran a 3-hour workshop about Transactions and Concurrency Control Patterns, which I managed to edit and include in the Mach 3 video course.

First, I explained how you could go bankrupt by not knowing how database concurrency control works. We analyzed why you don’t hear anything about FlexCoin or how Poloniex almost went out of business because of some race condition issue.

Afterward, we discussed what ACID is all about. Usually, there were two times in your life you stumbled on this term:

• in college, when you had to pass an exam about SQL and database systems
• during a job interview, when interviewers like to ask questions

So, there’s more to ACID than what you or your last interviewer had learned in college.

Next, we adventured ourselves into uncharted territories, like read skew, write skew, lost updates, 2PL, MVCC, long-running transactions, conflict materialization, database locking, job queues, and advisory locks.

So, if you want to be amazed, you should definitely check out the Transactions and Concurrency Control Patterns course module.

Best Tweets

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

Following programming tutorials pic.twitter.com/uzJ3WKHSrx

— Vlad Mihalcea (@vlad_mihalcea) February 4, 2021

Fixing a bug in a microservice architecture pic.twitter.com/3kbL8QHqCl

— Vlad Mihalcea (@vlad_mihalcea) February 21, 2021

The struggle is real pic.twitter.com/hsHmQdUgch

— Vlad Mihalcea (@vlad_mihalcea) February 25, 2021

After 17 years, I still love programming in @Java.

It's definitely not the easiest language to learn, but learning Java has been one of the best career move I've ever made. https://t.co/1AgtGYSYGB

— Vlad Mihalcea (@vlad_mihalcea) February 13, 2021

Frontend developer ✅
Backend developer ✅
Weekend developer ❌

— Vlad Mihalcea (@vlad_mihalcea) February 27, 2021

Project Releases

The 1.0 version of the Hibernate Reactive project has been released. If you are into reactive programming, then you should definitely give this project a try.

The Hibernate ORM project released the 5.4.29 version.

PostgreSQL released the 13.2 version, which fixes some issues from the 13.1 version.

I’m proud to announce to you the 2.10.3 version of the Hibernate Types framework has been released. For more details about this release, check out the closed issues section.

Get in touch with my latest articles!

SUBSCRIBE

StackOverflow Answers

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

• How to map PostgreSQL enum with JPA and Hibernate
• JPA EntityManager persist vs. merge?
• Hibernate pagination mechanism
• How do I map entity association using a query instead of a @JoinColumn with JPA?
• How to run a Hibernate NativeQuery in a type-safe manner instead of returning an Object[]
• How to store date/time and timestamps in UTC time zone with JPA and Hibernate