Which is better for long term database scalability: adding columns or having key/value store

Question

For application, which has few (5-10) settings at the moment but will have a lot more (up to 100) in the future which would be better approach:

1. to add new columns to settings table every time new setting is introduced
2. to have settings table as key/value storage

Consider that application might have millions of instances (which all run on the same db and use the same table for settings), DB might be sharded. DB is relational, i.e. either MySQL or T-SQL.

As a developer I would prefer the 2nd variant, so I could scale the application without changing the DB schema and add/remove settings at will. As far as I understand having several million records in a single table shouldn't be an issue if the index is clustered on application instance. Are there any drawbacks that I'm not aware of?

And what about 1st variant? Are there any big benefits? And what about the number of columns: is there any theoretical limit of columns that table can have? What happened if I had a table with 1000 (10 000, 1000 000) columns? Would that be slow?

Answer 1

Option 2 is known as "EAV" or Entity-Attribute-Value

• not relational
• no DB level constraints
• requires contortions to read the data unless a simple list

But, it depends what you mean by "settings". If you have a few 1000 rows that are not objects and don't require constraints then, yes, use this pattern. This is what SQL Server does with sys.configurations

If you are trying to have a "flexible schema" that can store anything, then simply don't. It will end in tears. Also see EAV questions here on DBA.SE

Note that "extra columns" (option 1) allows you to define defaults and datatype-safety, whereas "lack of row" (option 2) requires a default value to be stored in code and everything is a string in the database

"It depends"

Answer 2

Neither.

Instead, do the following:

Plan A:

1. Decide which fields really need indexes on them. No, you don't need indexes on all fields.
2. Put the rest of the columns, plus any future columns, in a "hash" or "associative" array in your programming language. Marshal this into JSON.
3. Compress the JSON and store it into a MEDIUMBLOB column of your table.
4. Zero effort to add a new column (or have an optional column, or deprecate a column, etc)

Features:

• Efficient storage (compressed blob is much smaller than separate columns)
• No need for ALTER TABLE ADD COLUMN.
• Search capability (Remember, I had you keep those columns out.)

Plan B: MariaDB 5.3+ and its Dynamic Columns: http://kb.askmonty.org/en/dynamic-columns