Introduction In this article, we are going to analyze how PostgreSQL Heap-Only-Tuple or HOT Update optimization works, and why you should avoid indexing columns that change very frequently. PostgreSQL Tables and Indexes Unlike SQL Server or MySQL, which store table records in a Clustered Index, in Oracle and PostgreSQL, records are stored in Heap Tables that have unique row identifiers. A Primary Key index will use the Primary Key column to build the B+Tree index, and the index leaves will store the row identifiers, as illustrated by the following diagram: Now, in... Read More The post Postgre ..read more

Introduction In this article, we are going to analyze the PostgreSQL Index Types so that we can understand when to choose one index type over the other. When using a relational database system, indexing is a very important topic because it can help you speed up your SQL queries by reducing the number of pages that have to be scanned or even avoid some operations altogether, such as sorting. If you want to receive automatic index and SQL rewrite recommendations based on your database workloads, check out EverSQL by Aiven. PostgreSQL Index... Read More The post PostgreSQL Index Types appeared fi ..read more

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 week is this article about using the BUFFERS option in the PostgreSQL EXPLAIN ANALYZE so that we can better understand the IO impact of a given SQL query. Using BUFFERS will help you understand whether data is scanned from the in-memory Shared Buffers using logical reads or whether it comes... Read More The post High-Perfo ..read more

Introduction In this article, we are going to see how we can map the OffsetDateTime ZoneOffset with the Hibernate TimeZoneColumn annotation. As I explained in this article, by default, Hibernate doesn’t store the time-zone offset of an OffsetDateTime entity attribute in a separate column. Instead, the OffsetDateTime is stored as a java.time.Instant that is relative to the UTC time zone. However, there are use cases when we need to save the associated ZoneOffset, and that’s exactly when you would use the TimeZoneColumn annotation. Domain Mode Let’s assume we have the following post... Read More ..read more

Introduction In this article, we are going to see how we can use Java Records with Spring Data JPA Repositories. As I already explained, Java Records cannot be used as JPA entities since the Records are immutable, and JPA requires the entity class to have a default constructor and be modifiable, as that’s how the entity properties are populated when the entity is being fetched from the database. For this reason, this article will show you how to combine Java Records and JPA entities so that you get the best out of... Read More The post How to use Java Records with Spring Data JPA appeared firs ..read more

Introduction In this article, we are going to explore various PostgreSQL performance tuning settings that you might want to configure since the default values are not suitable for a QA or production environment. As explained in this PostgreSQL wiki page, the default PostgreSQL configuration settings were chosen to make it easier to install the database on a wide range of devices that might not have a lot of resources. Not only can the QA and production system benefit from choosing the proper PostgreSQL performance tuning settings, but even a local database running... Read More The post Postgre ..read more

Introduction In this article, we are going to see how we can use the Hibernate StatelessSession in order to enable JDBC Batching for INSERT, UPDATE, and DELETE statements. While the StatelessSession has been available for more than 20 years, until Hibernate 6, its usefulness was rather limited since it lacked support for batching, as well as other cool features, such as UPSERT. Hibernate StatelessSession Just like the Hibernate Session, you can create a Hibernate StatelessSession from the Hibernate SessionFactory: However, unlike the Hibernate Session or JPA EntityManager, the Hibernate Statel ..read more

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 week is this article written by Lukas Eder about various query optimizations the database can do to speed up query executions. If you want to get a better understanding of SQL execution plans, this article is surely going to help you in this regard. If you’re using a database system,... Read More The post High-Performance ..read more

Introduction In this article, we are going to see how to fetch recursive associations when using JPA and Hibernate. Recursive table relationships are built using self-referencing Foreign Key columns so that a record in a table can reference another record in the very same table, therefore allowing us to represent hierarchical structures using the relational model. Domain Model Let’s consider we have the following book and category tables in our database: The category table has a parent_id column that has a Foreign Key constraint referencing the id column in the very same... Read More The post ..read more

Introduction In this article, we are going to see how to use the Java CountDownLatch to write test cases that take concurrency into consideration. The Java CountDownLatch has been available since version 1.5, and it’s part of the java.util.concurrent package that contains many other threading-related utilities. The Java CountDownLatch class The Java CountDownLatch class provides the following methods: The most important methods are the await and countDown methods. The await method is used to pause the execution of the current thread until the counter of the CountDownLatch reaches the value of ..read more