DB Architecture: One table using WHERE vs multiple

Question

I wonder what is the difference between having one table with 6 millions row (aka with a huge DB) and 100k active users:

CREATE TABLE shoes (
id serial primary key,
color text,
is_left_one boolean,
stock int
);

With also 6 index like:

CREATE INDEX blue_left_shoes ON shoes(color,is_left_one) WHERE color=blue AND is_left_one=true;

Versus: 6 tables with 1 million rows:

CREATE TABLE blue_left_shoes(
id serial primary key,
stock int
);

The latter one seems more efficient because users don't have to ask for the condition since the table IS the condition, but perhaps creating the indexes mitigate this?

This table is used to query either left, right, "blue", "green" or "red" shoes and to check the number of remaining items, but it is a simplified example but you can think of Amazon (or any digital selling platform) tooltip "only 3 items left in stock" for the workload and the usecase. It is the users (100k active daily) who will make the query.

NB: The question is mostly for PostgreSQL but differences with other DB is still relevant and interesting.

Answer 1

In the latter case, where you use a table called blue_left_shoes

• Your code needs to first work out which table to look at (as opposed to parameterising a value in the where clause)
• As permutations and options increase, you need to increase the number of tables, and increase the logic in your app that works out which table to use
• Anything that needs to use this database (i.e. a reporting tool or an API) now needs to re implement all of these rules

You are imposing logic at a high layer to improve performance.

If you were to partition and/or index your table appropriately, you get the same effect - SQL queries only look through the records that matter. The difference is that you don't need to implement this logic in higher layers

As long as you can get the indexing right, keeping this is one table is almost always the right thing to do.

Partitioning

Database partitioning is where you select one or more columns to decide how to "split up" your table. In your case you could choose (color, is_left_one).

Now your table is logically split and ordered in this way and when you search for blue,true it automatically knows which partition to look in. It doesn't look in any other partitions (this is called partition pruning)

Note that this occurs automatically from the search criteria. You don't need to manually work out a particular table to look at.

• Partitioning doesn't require any extra storage (beyond various metadata that has to be saved)
• You can't apply multiple partitions to a table. Only one

Indexing

Creating an index also provides performance improvements. However indexes take up space and can impact insert and update performance (as they need to be maintained). Practically speaking, the select trade off almost always far outweighs any insert/update negatives

You should always look at indexes before partitioning

Non selective indexes

In your particular case, there's an extra thing to consider: a boolean field is not "selective". I won't go into details but suffice to say you shouldn't create an index on this field alone, as it won't be used because it only halves the number of records you have to look through. You'd need to include some other fields in any index (i.e. colour) to make it useful