PG Performance

Partitioning & Creating Hardware Tablespaces for Performance

In this presentation we will go over using how to improve your speed performance by using partitioning and table spaces.

Index Bloat

Causes of Index Bloat:

  • Large churning of data on a table.
  • Every time you vacuum or analyze a table, this will cause index bloat.
  • Vacuum does not vacuum the indexes.

In this presentation we will go over how to find and monitor index bloat.

What is not covered in this presentation is the reason and how to fix index bloat automatically, that I found out after I wrote this presentation. When I next update this presentation it will be included inside the presentation.

When you delete a large chuck of data, the tuples (records) are marked deleted in the table but the index is not updated. If you then insert the new records, there will not be enough empty entries on the index pages and the pages will have to be split, doubling the size of the index.

This can be resolved by first deleting the entries and then doing a select on the same table using the same where clause as the delete. This select will cause the index entries to be marked as deleted. Then when you do the insert the deleted index entrees can be overwritten, so that your index does not bloat.

Bulk Commands

We will go over how to do everything in Bulk. This way of doing things will help you speed up your applications.

I will also show how to use these principals to refactor several function which run in 3 1/2 hour to now run in less than 6 1/2 seconds.