From the course: Advanced SQL for Application Development

List partitioning

From the course: Advanced SQL for Application Development

Start my 1-month free trial

List partitioning

“

- [Tutor] Now let's take a look at partitioning by lists. List partitioning is another type of horizontal partitioning, and like other forms of horizontal partitioning, we're going to use non overlapping keys, and basically the idea here is that we're going to partition on some value, a particular attribute, or some lists of values. So here for example, I'm partitioning based on geographic location. Now, like other forms of horizontal partitioning, we have a partition key, which determines which partition is used to store a particular row, we have partitioned bounds. Now this is a little different from horizontal where we have min and max. The partition bounds here are simply the values that we use to determine which partition to use for storing a row, and like partitioning by range, each partition can have its own indexes, constraints and defaults. So here we have a basic create table statement, and toward the bottom, you'll notice partition by list. So again, we're using a partition by clause, and now instead of specifying range, we're going to specify list, and again, we're specifying the partition key here. In this case, its product category. The product category, of course, is one of the columns sits in the create table statement. Now like range partitioning, when we're using Postgres, we need to specify the definitions for each of the partition. Now, here, instead of creating tables and then specifying them with time, for example, a table for week one, week two, week three, I'm specifying tables that are partitions of the product table, and I'm calling them things like product clothing, product electronics, product kitchen. So I'm partitioning by category, and the way I specify what categories belong to what partition, is I use a four values in clause. So the syntax is a little different from the range partitioning. But the idea is basically the same. We're specifying the criteria for determining when a row should go in this particular partition, and as you'll notice, in the clothing partition, we have values like casual clothing, business attire. In product electronics, we're looking at things like mobile phones and laptops, and so on, and in the kitchen product type category, we're looking at things like blenders and food processors. So when do you want to use partitioning by lists? Basically, when the data kind of logically falls into subgroups. So again, this is often domain specific. So it depends on the sort of the business logic behind what you're modeling. Also, if you often want to query within subgroups, so for example, if you're constantly querying within, say, the product category, if you're constantly looking up just kitchen items, or just clothing items, then that's often a kind of a criteria or good indicator for using partitioning by lists. Also can use partitioning by lists when you don't have sort of a logical time oriented kind of criteria. So you're not frequently orienting querying by time, or you're querying by time, but you're also querying by these logical subgroups. So that would be another thing to consider when deciding whether or not to use partitioning by list.

Contents