We currently have a Users table in the database, this has quite a few columns in it. Around 50% of the columns are barely used in the system (only on one or two pages). We've been discussing amongst ourselves whether to split this table into two tables e.g. Users and UserSettings. Our thinking is also that we could then split the C# objects up as well to follow suit.

We trying to think about db index size and things like caching as the user objects are cached in Redis. I know in some cases we'd need a couple of db queries to the get the data instead of a single one but as this would be the exception the saving everywhere else would be of a bigger benefit.

  • How many records are we talking about? – Dan Pichelman Feb 14 '18 at 20:26
  • @DanPichelman In the db as a whole we could be talking 100's of thousands but these would be grouped per client where you'd be looking at a max of 10k ish – Gaz Feb 14 '18 at 20:28
  • 1
    What is your specific question? – Robert Harvey Feb 14 '18 at 21:13
  • @RobertHarvey Whether it's beneficial to split a large object (from yours or anyones previous experience) in 2 from both a app performance and database performance point of view. Looking for people who've had similar dilemmas and how they have handled it – Gaz Feb 14 '18 at 21:15
  • 4
    Any such answer would inevitably be platform-specific, software-specific and requirements-specific, but sure, I understand your question. I think ultimately you'll have to run some tests in your own environment and see for yourself if it's worth it. What would "beneficial" mean in your specific context? Are you trying to reduce the page load time to a specific number? – Robert Harvey Feb 14 '18 at 21:19

We currently have a Users table in the database, this has quite a few columns in it.

So? Your queries should only be retrieving the columns that they require, so having extra ones lying around in the table is irrelevant.

We trying to think about db index size ...

Unless you have a user base as large as FacePlant or Twaddle then your user-settings data should be dwarfed by the "real" stuff (i.e. don't worry about it).

I know in some cases we'd need a couple of db queries to the get the data instead of a single one

You would have two physical tables, both keyed by and indexed on the same [user] identifier and where you need data from both tables, you would join between the two. Absolutely no need for multiple queries.

| improve this answer | |
  • 1
    Thanks for the answer, would have been better received without the sarcasm. Belittling users for asking questions puts people off from asking more. Not everyone on here is a seasoned veteran. – Gaz Feb 15 '18 at 13:03
  • @Gaz Doesn't feel sarcastic to me. It pretty clearly states fact and responds like a real person would. Dismissive? maybe. (to be fair this is literally a normalize / denormalize question that has been asked since the beginning of DB's) but definitely not sarcastic. – IT Alex Mar 12 at 12:43

From experience with much bigger Oracle databases (but I think most of that reasoning also applies to other DBMSes):

If splitting the Users class makes your application code cleaner, do so. But I wouldn't expect preformance or storage space problems.

VARCHAR columns occupy space only according to the actual content's length, and not the declared maximum, so mostly-empty columns don't waste a significant amount of storage space. The same applies to indexes on these columns.

Make sure your table fits into the DBMS RAM, so queries can be served without disk I/O (after some initial warmup). With some 100k lines of maybe 1 kByte each, that would mean less than 1 GByte of RAM, so that shouldn't be a problem nowadays.

| improve this answer | |

Fist, have you actually identified an actual problem? Can you show that having a wide table causes some problem for the system in terms of throughput, resource consumption, latency or somesuch? Splitting the table will cause maintenance overhead so you'd better be sure it's justified.

We [sic] trying to think about db index size

The index size is determined by the key column(s). Since the key won't change after the split the index size will be the same. Specifically the index depth will remain the same, and this is where a lot of the overhead comes in. Moreover, I'd imagine both tables will have the same key (UserId?) so twice as much disk will be used to store the index.

Some RDBMS implement BTree indexes where the leaf is the full row. For these, splitting the table will allow more rows to be held in a given amount of RAM, improving performance. You will not see improvement, however, if the DB server is not under memory pressure, or the user table is touched so frequently it is never evicted from memory currently.

and things like caching as the user objects are cached in Redis.

Do you serialized and cache the whole, wide object? Well, doing less work is likely to be faster than doing more work. So splitting is likely to be beneficial. This does not require the split to be performed in the RDBMS, however, only in the Redis cache handling code.

I know in some cases we'd need a couple of db queries to the get the data instead of a single one

Well, maybe. A single query can join many tables & return a single resultset. A stored procedure can return multiple resultsets. A view could be defined which joins the tables and the view can be referenced from the application.

I get the impression an ORM is involved. Your options here may be dictated by it.

One major drawback to splitting is that you now have two objects that absolutely, definitely must be in sync. This will require very careful application code or, more likely, database triggers. This complicates the reasoning about the system. Would it ever be acceptable to have a User without corresponding UserSettings? Under what circumstances can either be removed? How to enforce the 'exactly one' relationship constraint between the two tables? This is achievable but requires planning.

| improve this answer | |

If you can set Primary Key - Foreign Key relationship, you should always use two different small tables, as it will provide you more performance as well as you will face less complexity. Whenever you want combined data, you can join multiple tables. In the case of combining the data, you can create a DataViewModel in the C# and fetch data from the database.

| improve this answer | |

In most (if not all) databases a null value takes very little room.

A separate table has the overhead of a foreign key.

| improve this answer | |

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