12

I was reading about some real life DB issues, and one project had a 100 million row plus table that had 5 columns as its primary. I'm thinking this is bad, but can anyone tell me exactly why?

The table was kind of a micro rollup/aggregation table, so the 5 columns were like (day, market_id, product_id...). At first I thought that a 5 column primary key wasn't ideal, but the more I thought, I couldn't really come up with a good reason why it was bad.

This was in a late night discussion with half the company engineers. Someone just mentioned this was a bad design, one senior engineer agreed, but no one really jumped in as to why. Thus trying to research the matter for myself!

  • Ideally, you want the PK to be relatively small - less memory overhead. With a 5 column PK, it's automatically going to be at least approx. 5 INTs - when 1 INT (auto_increment) might do instead. – Vérace Mar 13 '15 at 7:30
9

There are performance issues with very complex primary keys. And It may not be defending against duplication as well as a simpler primary key might.

However, there is one design pattern that frequently yields tables with a primary key made up of six or so components. It's star schema fact tables. If the fact table of a star schema has six dimensions, then the primary key will have six components. I've never seen a fact table with no declared primary key, and I think it's well worth the overhead, even though the ETL process still has to be quite carefully written.

Some reporting databases imitate the pattern of star schema even if it's not explicitly designed that way.

100 million + rows is not overly big for a fact table, especially with today's big data.

2

The table in question was a rollup/aggregation table.

Then it is not only fine, it is "right".

And it smells like a Summary table, since it begins with day.

Do you have some secondary indexes? Keep in mind that if you are using InnoDB, the rest of the PRIMARY KEY columns will be tacked onto the end of the secondary index. Again, this is not necessarily a problem.

100M rows is a lot for a rollup. It sounds like the table is too fine-grained. That is, perhaps instead if (date,a,b,c,d) you should have 4 rollups with PKs like (date,a,b,c), (date,b,c,d), (date,c,d,a), (date,d,a,b) (or some suitable combinations). I doing that, each might be only 10M rows, thereby making reports still faster, while having nearly as much flexibility in report.

Or maybe switch to (week,a,b,c,d), leading to maybe only 14M rows. (Probably more.)

Using PARTITION to facilitate pruning --- High speed ingestion --- Data Warehouse tips --- Summary Tables. These summarize many of the techniques I have developed in several DW projects. As you may infer, each project is different. The 'typical' number of Summary Tables (in my experience) is 3-7. The target in summarization is 10 Fact rows --> 1 Summary row. (That may be a 'median'.) In a rare case, I summarized a Summary table. In another rare case, I PARTITIONed a Summary table to good effect; usually Summary tables are small enough so they are fast enough for direct access from a UI.

1

Well, actually having a PK with 5+ columns is not necessarily bad in itself.

It becomes bad once the PK is also the clustered index as that one would count as the row identifier and thus would be added to each row in a NC index. This would drastically increase the required space.

It would also be bad once you actually use the PK by another FK, as you have to have the data of all 5+ columns in both the current table as well as the one referencing from. Once again it will increase the storage by a lot!

Performance-wise it will be bad once the PK is been used as an index - let it be solely within the table or in conjunction with a FK - as a bigger PK-Key containing 5+ columns will take more space, thus less entries will fit within a page and henceforth more pages need to be read to analyze the index.

That said - there might always be a good reason for actually doing so anyway, like e.g. a fact table. Therefore the best answer would actually be as in most cases: It depends!

Regards Dennis

-2

For some 15+ years I do not need such key, saw it sometimes, and it was only causing troubles. A lot of troubles. First of all primary key are for holding data integrity, and they should be syntetic. They should not have any binding to real world. Why ? Once real world change, and it will, for sure, your primary key is gone, and you have to update it, and all related information.

Imagime you need to remember this ker in some other table/database/service instead of one field you need to copy several, and you can forget to copy some of them. Instead sysntetic primary key, is just one piece of data, you have to provide. I am not mentioning uniqness of index, which may by another huge topic for discussion.

So short summary, syntetic primary key(auto incrementing, guid, ..) is simple to maintain, copy, ...

So I consider, syntetic primary key, and another key for 5 columns you mentioned.

At last, if the table is only aggregate, and never ever will someone need to reference row by keys(but world changes, trust me it will, at least for me it changes permanently), I will probably leave it like it is(primary key with five rows), but in case we used to have, it alway cause lots of trouble. So I told you.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.