Introduction In this article, we are going to see what is the best way we can use OneToOne optional attribute so that we can avoid N+1 query issues. One-To-One table relationship As I explained in this article, in a one-to-one table relationship, the parent and the child tables share the Primary Key column values because the child table Primary Key column has a Foreign Key constraint referencing the Primary Key column in the parent table. For example, a one-to-one table relationship between a parent post table and a child post_details will look... Read More The post The best way to use the JPA ..read more

Introduction In this article, we are going to investigate how to calculate percentiles with the SQL PERCENTILE_CONT function. Domain Model Let’s consider we have the following quotes table that holds the historical price values of various stocks and indexes: The quotes table is populated with data that looks like this: SQL PERCENTILE_CONT The SQL Standard provides support for a great variety of Window Functions, such as DENSE_RANK, and Aggregation Functions, like PERCENTILE_CONT, PERCENTILE_DISC, or LISTAGG. The PERCENTILE_CONT is an inverse distribution aggregate function that we can use to c ..read more

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