One of postgres' mechanisms for dealing withe extremely large database tables is to partition them into smaller sub-tables which appear logically to your application as a single table. The naÃ&hibar;ve approach without modifying your application at all may result in significant performance degradation.

The most commonly written about technique for table partitioning is to split data using a date field. This turned out to not be beneficial at all to our use case. By splitting our data based on an ID number field, we were able to significantly improve the performance of the database and thus our application.

In this presentation, we will present a case study of the steps we took to increase the speed of a critical portion of our application by a factor of two. We will cover:

0) Identifying the application bottleneck 1) The analysis of the application's use of the large table 2) Determining the best partition scheme for this usage 3) How to ignore consultants who want to do what is easy for them 4) Testing the partition performance
5) Planning the migration with minimal (under 5 minutes) application downtime 6) Deploying the application changes and migrating the data

Along the way, we will cover tips and tricks we learned about partitioning tables which are not obvious from reading the manual, and which make migration easier than one might expect.