Replace column by LEFT OUTER join to save space

Question

I have a table t1 with 100M row. For column A, 95% of the rows have value NULL0, the other records have values from 1 to 9.

To shrink my table, I'm thinking about moving column A to table t2. t2 would contain only rows where A is not NULL0. The "original" table would then be created by doing a LEFT OUTER JOIN between t1 and t2.

Questions:

• Is this a common approach?
• Is there a name for this pattern?
• Are there any downsides except that I have to handle two tables now in my application code?

(I use MySQL, but I guess it doesn't matter so much for this question.)

Update: As it turns out, NULL was not a good choice for my example (since depending on db engine, it might not need any storage at all). I rewrote the first sentence of my question to make it more clear what I'm asking for.

Answer 1

This approach is basically trading storage for compute. While you are reducing the amount of space on disk, you are doing so at the cost of the additional join; more specifically the compute power to make that join happen. Also, the space savings is purely dependent upon the size of the datatype of the column in question as well as the key for which you'll be using to join the tables. The more complex your key, the more compute required and the less space saved.

In the best case scenario, using your parameters (e.g. 100M record table where column A is an INT), we'll assume the Primary Key is also a simple INT, we get a new table that is about 10% the size of the data footprint if compared to keeping this as a column on the main table:

• Column A consumes ~381MB (i.e. 100,000,000 * 4 Bytes)
• New Table T2 consumes ~38.1MB (i.e. 5,000,000 * 4 Bytes for the data and 5,000,000 * 4 Bytes for the key on which we'll join)
• Space savings of 90%

However, what if our key is a composite key that is a combination of an INT and DATETIME columns (4 byte and 8 byte sizes, respectively), we see the new table has doubled in size to about about 20% the size of the data footprint if compared to keeping this as a column on the main table:

• Column A consumes ~381MB (i.e. 100,000,000 * 4 Bytes)
• New Table T2 consumes ~76.3MB (i.e. 5,000,000 * 4 Bytes for the data and 5,000,000 * 12 Bytes for the key on which we'll join)
• Space savings of 80%

Still not bad you say, but would you just leave it at the one column or would you extend this pattern to other fields? Because we're talking design paradigm, I'd expect to see this extended to more tables/fields/etc.

So the pro is a savings in space, but what are the cons?

Obviously the excessive amount of joins you now have to manage within your SQL statements as well as the compute that will be required, but deletes now also require extra statements (since we now have multiple tables to deal with and don't forget the extra compute this requires as well). Let's not forget what could happen if that 95% data distribution changed, as data tends to do over time.

If you've got excessive compute available and are limited on disk, this approach may be viable, but more often than not, compute is much pricier than storage, especially after factoring in licensing as most products are licensed based off of core count and not size. MySQL Community Edition is free but the other editions are not, and what about the CPUs on your server (or cloud service provider)? Often storage is a fraction of the cost of CPU, so unless you're limited on the storage side the cost/benefit ratio doesn't work well after you factor in the additional man-hours to write the code and the potential cost to handle the extra compute needed for the additional operations.

So, to answer your questions:

• Is this a common approach?

No, not in my experience.

• Is there a name for this pattern?

This is database normalization in it's most extreme. Most RDBMS systems are designed with 3NF/BCNF in mind, so likely that level of normalization is where your system is most cost effective.

• Are there any downsides except that I have to handle two tables now in my application code?

Yes, if this design is utilized whenever possible, at minimum, the cost of the extra compute needed to handle the join and the extra man hours required to deal with the extra code logic (e.g. extra joins, additional deletes, etc.) will be a downside.

That being said, it may make perfect sense to do this for one or two columns within a database where the disk consumption is excessive. The design of your database, the type of data it stores, and the system it's supporting all play into when this makes sense or not. However in those cases, I'd say you should look into table or page compression first as data has a funny way of changing such that it finds ways to break even the most well of intentioned design patterns.

UPDATE: A quick edit to further explain my answer about Normalization as people seem to be passionate this isn't accurate. At the root of things, this approach is creating a separate table with a one-to-one relationship and overloading the meaning of NULL to mean whatever our most common value is, which would be 0 in our example scenario. When I say overloading the meaning of NULL, we are implying that the absence of a record means said record has a default value. Again, this design approach isn't different than any other one-to-one or one-to-many relationship between two tables, but in this case we're just implying that the absence of a record means something. Again, this isn't a revolutionary design approach, it's just a way to overload functionality which at the end of the day is just another flavor of normalizing the data.

Answer 2

If we assume that your column is an INT then, as John's analysis shows, you will see a saving of approximately 80%, at the expense of some extra work joining things back together when reading the values.

As you only need values from 0 to 9 you could get a 75% saving by simply redefining the column as a TINYINT which consumes one byte instead of four, without any extra overhead. This saving is fixed too: even if your estimate becomes inaccurate as your data evolves (so more than 95% have a value 1-through-9, even if most/all change to have such values) the saving stays the same.

I'd be inclined to argue that a guaranteed 75% saving with little/no extra overhead is a better bargain than an approximate 80% saving with the computation overhead (and cognative overhead for you as the delevoper and/or DBA).

Answer 3

I did the exact same thing (separated table into two) but for the different reason - we have different data retention rules for different columns, and one particular column was taking most of the space (95%) and setting it to null wouldn't free any space. It is a text field and we save JSON responses there.

So I did vertical partitioning and used left join to select the data. Size of saved GBs was significant enough to justify the change since I had to migrate data to the new table and we are 24/7.

You didn't mention the type of your column. If it's INT, maybe it makes sense just to use a smaller type in order to save space. The new table will require a primary key anyway, so you won't save exact 95%, will be more like 90% or less. So moving from INT to TINYINT will give you very similar result with no changes to SQL statements.