Common Pitfalls Common Pitfalls SELECT item1, item2, item3 FROM basket; SELECT item1, item2, item3 FROM basket; INSERT INTO groups (name, people) INSERT INTO groups (name, people) VALUES ('friends', 'aaron,betty,chris,damien') VALUES ('friends', 'aaron,betty,chris,damien') SELECT file_bytes FROM pictures WHERE filename = SELECT file_bytes FROM pictures WHERE filename = 'foo.jpg' 'foo.jpg'
names = set() names = set() for book in Book.objects.filter(year=2012): for book in Book.objects.filter(year=2012): names.add(book.author.name) names.add(book.author.name)
No transactional DDL No transactional DDL Poor query optimiser Poor query optimiser MyISAM: Full-table locking, no transactions MyISAM: Full-table locking, no transactions Oracle Oracle Very fast for some operations Very fast for some operations MySQL MySQL
Little integrity checking (slowly being fixed) Little integrity checking (slowly being fixed) Impossible to do some table alterations Impossible to do some table alterations No concurrent access No concurrent access Very low overhead Very low overhead Very portable Very portable SQLite SQLite
Slow default configuration Slow default configuration Can be a little harder to learn / less familiar Can be a little harder to learn / less familiar Almost too many features Almost too many features Incredibly reliable Incredibly reliable PostgreSQL PostgreSQL
Horizontal scaling (but with drawbacks) Horizontal scaling (but with drawbacks) Extremely fast Extremely fast Can only fetch objects by key Can only fetch objects by key Batch/map-reduce queries Batch/map-reduce queries Transactions not possible Transactions not possible Traits Traits
Knowledge of projections useful Knowledge of projections useful Spatial indexes really speed up some problems Spatial indexes really speed up some problems Generally add-on to existing DB Generally add-on to existing DB Spatial Spatial
Hierarchial key-value store Hierarchial key-value store Allows multiple writers for appends Allows multiple writers for appends Supports very large files Supports very large files Filesystems Filesystems
Allow efficient neighbour queries Allow efficient neighbour queries Generally not much use for anything else Generally not much use for anything else Graph Databases Graph Databases
Deliberately loses old data Deliberately loses old data Useful for logging or statistics Useful for logging or statistics Round-Robin Database Round-Robin Database