0

I have a table with 4 columns, in which one of them is primary key, one is time_created, ID of the upstream service (say ID) and the type of upstream service (say service_name). This table is append only (i.e. only inserts are done, no read/update/delete).

In this table, there is an unique index on the combination of ID and service_name. The cardinality of service_name is very low i.e. it has very small number of distinct values and ID is unique for a particular service_name.

So, I am trying to find sources to find the implication of inserts to this table with the two possible ordering of this composite index, (ID, service_name) and (service_name, ID).

As far the B-trees is concerned it is recommended to have a column with low cardinality at the end of an index key, so during selects finding the row will be quicker as with the first column the possible rows can be narrowed down and only from the handful of rows returned, using the second column and the row is found.

Now I am trying to understand if the same applies for inserts too.

My understanding is that inserts for tables with unique indexes/constraints will be first checked if the value is already present and then inserted. So if the check happens during insert, will the same idea of ordering of columns in the index for read queries applicable for insert too?

Or should I try to keep the ordering (service_name, ID) and apply compression for the first column in this composite index?

1 Answer 1

0

Don't concern yourself with general advice like low cardinality toward the end. The difference in binary search (seek) time is minimal. Far more important is how you intend to query the table.

If you ever have a predicate on service_name but not on ID (maybe you want the maximum ID for a given service_name), you will want service_name to be the leading column to facilitate such a query. If you frequently query on ID without also filtering on service_name, then you'd want the opposite, but it sounds to me like your ID is meaningful only within a given service, so I would think you'd want to lead with service_name to maximize query usefulness. Plus, even some occasional queries against ID only can still do a fairly efficient skip-scan to use the second column since the first has so few distinct values.

Additionally, having the wider, low cardinality column on the left side of the index allows for prefix compression to save a bit of space. So all-round, I think service_name wins for left-most/leading.

Neither order will impact inserts. Inserts will have to do the same index maintenance on all your indexes regardless of what the column ordering is.

Update: you asked about hotspots, which is a concurrency question (not absolute response time). When it comes to hotspots, the key thing is which index blocks are being modified, not which entries. A lot of entries are found in each block. Placing ID first would only spread out the work if the ID values coming in concurrently were spread all over the place. If they are sequential/concurrent (more common), then it's a different story, as sequential values will mostly end up together in the same block.

If you have many concurrent sessions inserting sequentially/contiguous ID values then the index (ID,SERVICE_NAME) would have a single hotspot for ITL entries and 80/20 leaf block splits on the leading edge. If you flipped it to (SERVICE_NAME,ID) but all your sessions were inserting the same SERVICE_NAME value, you'd have the same thing, only with 50/50 block splits in the middle. One would not be better than the other, except that the latter would inflate the index size faster. However, if all the SERVICE_NAME values were all represented at the same time, then the (SERVICE_NAME,ID) order would give you multiple "warm" spots, less intense because there's more of them (one on the leading edge of each SERVICE_NAME value). This of course only benefits your performance if you are actually experiencing concurrency waits. For most applications and situations, it is unlikely that this will even be noticed. It takes a lot of concurrent inserts before you start to see contention. So in general, unless you are designing something you expect to see dozens of concurrent inserts, I wouldn't be overly concerned about it. Design your indexes mainly for query, not for hotspots.

But, since leading with SERVICE_NAME is my general recommendation anyway for both querying and compression purposes, it's easy to recommend it as well if you're concerned about hot index blocks. Even if your concurrently inserted ID values are evenly spread out, leading with SERVICE_NAME won't give you a hot spot because there are so few of them, any one distinct value will span many index leaf blocks, and so the inserts will be spread across many blocks.

9
  • As I have mentioned in the question, this table is append only, so apart from insertions nothing will be done.
    – pdmnhn
    Commented Mar 12 at 13:10
  • @pdmnhn, What's the point of a table if it's never queried? Never heard of such a table. The whole point of a database is to be able to retrieve information. If it won't be queried, it's better to drop it entirely. Or if there's some chance it might eventually be used but you can't predict when or how, then why worry about the index structure at all? Just save space and protect the integrity and nothing else matters. But my answer gives you some of the basic principles needed to make decisions about index column ordering.
    – Paul W
    Commented Mar 12 at 13:28
  • The purpose of this table is to handle idempotency, i.e. fail inserts when there is a violation of uniqueness of the combination (service_name, ID). So I am trying to make the insertions faster.
    – pdmnhn
    Commented Mar 12 at 13:42
  • @pdmnhn, A whole table with no other purpose but to enforce uniqueness? What's the point? That's not what tables are for. Why not simply enforce uniqueness on a real table that's actually needed instead of using a dummy table?
    – Paul W
    Commented Mar 12 at 13:48
  • @pdmnhn, as for making your inserts faster, column order of indexes is irrelevant. That's my answer. It may affect when branch and leaf nodes are split but that won't have any noticeable impact on insert performance. If you really want to see that, simply mock up a quick easy test and try it various ways.
    – Paul W
    Commented Mar 12 at 13:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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