PostgreSQL Auto Explain

Introduction In this article, we are going to see how the PostgreSQL Auto Explain feature works and why you should use it to gather the actual execution plan for SQL statements that execute on a production system.

Percona PMM – A beginner’s guide

Introduction This article is a beginner’s guide for the Percona Monitoring and Management (PMM), a tool that’s very handy when it comes to analyzing a given database system. PMM was released in 2016, and initially, it was available for MySQL. However, since 2018, it supports monitoring PostgreSQL as well, so once you learn it you can use it with the two most popular open-source relational database systems.

PostgreSQL audit logging using triggers

Introduction In this article, we are going to see how we can implement an audit logging mechanism using PostgreSQL database triggers to store the CDC (Change Data Capture) records. Thanks to JSON column types, we can store the row state in a single column, therefore not needing to add a new column in the audit log table every time a new column is being added to the source database table.

9 High-Performance Tips when using PostgreSQL with JPA and Hibernate

Introduction To get the most out of the relational database in use, you need to make sure the data access layer resonates with the underlying database system. In this article, we are going to see what you can do to boost up performance when using PostgreSQL with JPA and Hibernate.

PostgreSQL reWriteBatchedInserts configuration property

Introduction The PostgreSQL JDBC Driver has been adding a lot of very useful optimizations, and one of the lesser-known ones is the reWriteBatchedInserts configuration property. In this article, you will see how the reWriteBatchedInserts JDBC configuration property works in PostgreSQL, and how it allows you to rewrite INSERT statements into a multi-VALUE INSERT.

How to solve the PostgreSQL :: cast operator issue with JPA and Hibernate

Introduction While answering questions on the Hibernate forum, I stumbled on the following question regarding the PostgreSQL :: cast operation used in a JPA and Hibernate entity query. Since it’s a very interesting use case, I decided to turn the answer into a dedicated blog post.

How do PostgreSQL advisory locks work

Introduction PostgreSQL, like many modern RDBMS, offers both MVCC (Multi-Version Concurrency Control) and explicit pessimistic locking for various use cases when you want a custom concurrency control mechanism. However, PostgreSQL also offers advisory locks which are very convenient to implement application-level concurrency control patterns. In this article, we are going to explain how PostgreSQL advisory locks work and how you should use them.

How does a relational database work

Introduction While doing my High-Performance Java Persistence training, I came to realize that it’s worth explaining how a relational database works, as otherwise, it is very difficult to grasp many transaction-related concepts like atomicity, durability, and checkpoints. In this post, I’m going to give a high-level explanation of how a relational database works internally while also hinting some database-specific implementation details.

How to call PostgreSQL functions (stored procedures) with JPA and Hibernate

Introduction This article is part of a series of posts related to calling various relational database systems stored procedures and database functions from Hibernate. The reason for writing this down is because there are many peculiarities related to the underlying JDBC driver support and not every JPA or Hibernate feature is supported on every relational database.