« Older Posts

PG Phriday: Fancy Partitioning

August 28th, 2015 | Published in Database, Tech Talk | No Comments

This week we’ll be covering another method of Postgres partitioning. This is a technique I personally prefer and try to use and advocate at every opportunity. It’s designed to straddle the line between traditional partitioning and standard monolithic table structure by using table inheritance as a convenience factor. The assumption here is that end-user applications either:

  1. Know that partitioning is in use.
  2. Only load “current” data and don’t care about partitions.

These two things may seem mutually exclusive, but they can actually be complementary. There are also notable performance benefits to this approach that we’ll explore later.

Tags: , , ,

PG Phriday: Basic Partitioning

August 21st, 2015 | Published in Database, Tech Talk | 15 Comments

Most PGDB (PostgreSQL) users who are familiar with partitioning use the method described in the partitioning documentation. This architecture comes in a fairly standard stack:

  • One empty base table for structure.
  • At least one child table that inherits the base design.
  • A trigger to redirect inserts based on the partitioning scheme.
  • A constraint on each child table to enforce the partition scheme, and help the planner exclude child partitions from inapplicable queries.

Tags: , , , ,

PG Phriday: Partitioning Candidates

August 14th, 2015 | Published in Database, Tech Talk | 3 Comments

What’s a good table to partition? It’s not always a question with an obvious answer. Most often, size and volume determine whether or not a table should be broken into several chunks. However, there’s also cases where business or architecture considerations might use partitioning to preserve a shared table structure, or drive aggregate analysis over a common core. In PGDB (PostgreSQL), this is even more relevant because of how partitioning is handled through inheritance.

Tags: , , ,

PG Phriday: The Case for Partitioning

August 7th, 2015 | Published in Database, Tech Talk | 4 Comments

In the next few weeks, I’m going to be pushing a long discussion regarding PGDB (PostgreSQL) table partitioning. I’ve covered it in previous articles, but only regarding basic performance considerations. That’s a very limited view of what partitioning can offer; there’s a lot more variance and background that deserves elucidation.

So for the next few articles, the topic of discussion will be partitioning. There’s not really enough of it, and a lot of the techniques used in the field are effectively pulled straight from the documentation. I think we can go much further.

Tags: , , ,

PG Phriday: 10 Ways to Ruin Performance: Sex Offenders

July 24th, 2015 | Published in Database, Tech Talk | 4 Comments

We’re finally at the end of the 10-part PGDB (PostgreSQL) performance series I use to initiate new developers into the database world. To that end, we’re going to discuss something that affects everyone at one point or another: index criteria. Or to put it another way:

Why isn’t the database using an index?

Tags: , , , ,

« Older Posts