Hibernate (re-)generates names for database constraints such as foreign keys or unique columns when creating a schema. However, the names for indexes and unique keys are not very descriptive, since they are created from a hash string of the table and column name.
Unreadable technical names may seem just to be an insignificant ugliness, but can become quite cumbersome when you are searching error logs for database problems…
Continue readingRecently we encountered some severe performance problems in one of our projects. Some SQL queries were very slow.
A certain query took over 6 seconds to finish in Azure’s Postgres service and about 350ms locally on my laptop. Both are unacceptable.
I don’t know much more than the postgres basics and the project was in all parts relevant here more or less new to me. But let me show you how I approach the unknown, what I learned and how I solved it.
By the way: The issue is a prime example of not spending more time on optimisation than what feels necessary at the time. So even though the initial code looks a bit dumb in hindsight I still consider this ok as the application was fast enough for over 2 years in production and as we later learned the issue was caused by some up to that point unprecedented data.
Now, let’s dive in!
Alongside various strategies for handling large amounts of data with indexes, PostgreSQL provides another feature: splitting the table with inheritance. This separation of a table’s data into different child tables is called “partitioning” in PostgreSQL.
In part one, we described the pro and cons of this method and its conditions of use. In this second part we describe the steps required to actually configure a partition for production. A third blog post shows the specifics to consider in a JPA Hibernate project. The proposed instructions are intended for use with a PostgreSQL version 9.2 database but may work on all versions from 8.4.
Continue reading
Alongside various strategies for handling large amounts of data with indexes, PostgreSQL provides another feature: splitting the table with inheritance. This separation of a table’s data into different child tables is called “partitioning” in PostgreSQL. Because it does require a bit of effort to implement, and because there are also limitations in how it can be used, you need to take some points into consideration when creating a partition.
This first part of the article presents the pro and cons and what to consider when using partitioning to improve performance. In the second part we will describe the steps to create a production-ready partition. A third blog post shows the specifics to consider in a JPA Hibernate project.
You must be logged in to post a comment.