PostgreSQL: Partitioned tables and Hibernate

The posts PostgreSQL: Partitioning big tables Part 1 and Part 2 describe the implementation of this performance measure from the database view. In an enterprise Java environment often a database is integrated by a JPA framework and closely coupled with the application. This 3rd part about partitioned tables in PostgreSQL shows additions necessary when using Hibernate.

Continue reading

PostgreSQL: Partitioning big tables (Part 2)

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

PostgreSQL: Partitioning big tables (Part 1)

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.

Continue reading