PG Performance

Tablespaces

We will cover how to split your database across devices. Including backup, restoration, streaming replication and other gotchas.

Example 1: Splitting data between unencrypted and encrypted devices to encrypt PII for have performance with data that does not contain PII data.

Example 2: Splitting data between local and network storage. Using your network storage or archived, read only, data that you don't need the highest performance but still want to join to your current data.

Example 3: As you know, Postgres double writes everything, WAL and Data files, plus the logs files, makes triple writes. Splitting your Data, WAL Files, and Log Files to separate devices for 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 vacuum analyze a table, this will cause index bloat.
  • Vacuum does not vacuum the indexes.

In this presentation we will go over how to find index bloat, monitor index bloat, and fix index 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.