Three things should never be put in the database

Three things you shoshould never put in your database

As I 've said in a few talks, the best way to improve your systems is by first notdoing "dumb things ". I don't mean you or your development staff is "dumb", it 'seasy to overlook the implications of these types of decisions and not realize howbad they are
For maintainability let alone scaling. As a consultant I see this stuffall of the time and I have yet to ever see it work out well for anyone.

Images, files, and binary data

Your Database supports blobs so it must be a good idea to shove your files in thereright? No it isn' t! Hell it isn' t even very convenient to use with analytic DB into agebindings.

There are a few of problems with storing files in your database:

• Read/write to a DB is always slower than a filesystem
• Your DB backups grow to be huge and more time consuming
• Access to the files now requires going through your app and DB Layers

The last two are the real killers. storing your thumbnail images in your database? Greatnow you can't use nginx or another lightweight Web server to serve them up.

Do yourself a favor and store a simple relative path to your files on disk in the database oruse something like S3 or any CDN instead.

Ephemeral data

Usage Statistics, metrics, GPS locations, session data anything that is only useful to youfor a short period of time or frequently changes. if you find yourself deleteing an hour, day, or weeks worth of some table with a cron job, you're using the wrong tool
For the job.

Use redis,
Statsd/graphite,
Riak anything elsethat is better suited to that type of work load. The same advice goes for aggregations of ephemeral data that doesn't live for very long.

Sure it's possible to use a backhoe to plant sometomatoes in the garden, but it's far faster to grab the shovel in the garage than scheduletime with a backhoe and have it arrive at your place and dig. use
The right tool (s) for thejob at hand.

Logs

This one seems OK on the surface and the "I might need to use a complex query on them atsome point in the future" argument seems to win people over. storing your logs in a databaseisn't a horrible idea, But storing them in the same database as your other
Production datais.

Maybe you're conservative with your logging and only emit one log line per Web request normally. that is still generating a log insert for every action on your site that is competing for resourcesthat your users cocould be using. turn up your logging to a verbose
Or debug level and watch yourproduction database catch on fire!

Instead use something like splunk,
Logglyor plain old rotating flat files for your logs. the few times you need to inspect them in odd ways, even to the point of having to write a bit of code to find your answers, is easily outweighed by the constant resources it puts on your system.

But wait, you're a unique snowflake and your problem is so different that it's OK for you to doone of these three.
No you aren't and no it really isn't. Trust me.

Original article Excerpted from:

Http://www.revsys.com/blog/2012/may/01/three-things-you-should-never-put-your-database/

Three things should never be put in the database

I have already said in many speeches that the best way to improve your system is to avoid "stupid things" first ". I am not saying that you or the things you develop are "stupid", but some decisions are easily ignored by people, I don't know how much trouble it brings to system maintenance, especially system upgrades. As a consultant, I can see such things everywhere. I have never seen people who make such decisions have good results.

Images, files, and binary data

Since the database supports blob-type data, there must be no error in inserting the file into the Blob field !? Wrong, not like this! In many database languages, it is not easy to process large fields.

There are many problems with storing files in the database:

• The read/write speed of the database will never catch up with the speed of File System Processing
• Database Backup has become huge and time-consuming
• Access to files must go through your application layer and database layer.

The last two are real killers. Store thumbnail images in the database? Well, you can't use nginx or other types of lightweight servers to process them.

Make it easy for yourself. Simply store the relative path of your files on the disk in the database, or use services such as S3 or CDN.

Short-lived data

Usage Statistics, measurement data, GPS positioning data, session data, and any data that is useful or frequently changed for you in a short period of time. If you find that you are using a scheduled task to delete data that is valid for only one hour, one day, or several weeks from a table, it means you have not found the correct method for doing things. Using redis,
Statsd/graphite,
Riak, they are more suitable tools to do such things. This suggestion also applies to the collection of short-lived data.

Of course, it is also feasible to plant potatoes in the back garden with excavators, but instead of taking out a shovel from the storage room, you reserve a excavator and wait for it to rush to your garden to dig holes, this is obviously slower. You need to select a proper tool to handle the tasks at hand.

Log Files

Storing log data in a database seems to be good on the surface, and "I may need to perform complex queries on this data in the future" is quite impressive. This is notVery poorBut if you store log data and product data in a database, it is very bad.

Maybe your logging is quite conservative, and only one log is generated for each Web request. For every event on the entire website, this will still produce a large number of database insertion operations, competing for the database resources required by your users. If your log level is set to verbose or debug, check that your database is on fire.

You should use something like splunk
Logugly or plain text file to store your log data. It may be inconvenient to view them in this way, but there are not many such cases. Sometimes you need to write some code to analyze the answer you want, but it is worth it in general.

But wait a moment. You are a different snowflake, and your problems will be so different, if you put one of the three things mentioned above into the database, there will be no problem.No, you are wrong. No, you are not special.Believe me.