When SQL is the Wrong Answer

From WikiContent

Jump to: navigation, search

Developers tend to reach for SQL backed with a relational database whenever persistent, indexed storage is called for. However, there are other technologies and methods available that may be a better fit for your application. Ask yourself these questions before you choose how to manage data for your next project: How Large are Your Average Records?

Most relational databases are optimized for large numbers of relatively small records. If you're planning to put multi-megabyte BLOBs in instead - files, photographs, audio files, etc. - you're going to make the database work hard without giving you any of SQL's benefits. You can't generally query meaningfully on BLOBs, so why take up expensive database space with them? Consider managing your metadata via SQL, and store the BLOBs directly on disk. Is the Data Likely to be Queried?

Many data streams are rarely if ever queried. - particularly logs and other audit trails. SQL won't help you if you never look a record up, so why not write log files to disk or syslog? You'll save database load and space. Is Lookup Speed Critical?

Most lookups don't need SQL's query power. If you're retrieving items on a single key and you need it fast, key/value managers like memcache may be a better fit. You can use them as a front-end cache even if you opt to manage the authoritative data via SQL. Is the Schema Likely to Change Regularly?

Rapidly changing and/or sparse schemas (schemas where most columns are NULL for most records) can be inefficient to manage via SQL. Consider if a schema-less database like CouchDB or MongoDB would work better. Alternatively, Object Relational Mappers like SQLAlchemy can let you stay with an RDBMS while easing schema management.

Personal tools