NoSQL

Sixteen things a relational database did for you — and what you do instead

Nobody adopts a NoSQL store in order to lose features. Teams adopt one for scale, for a flexible record shape, or because the managed service is one click away. Then they discover, one at a time, the things the relational database had been doing without being asked. Here are sixteen of them: what the relational database did, what a partitioned store does instead, and the pattern that gets it back. Not every store loses all sixteen — MongoDB has had multi-document transactions since 2018 — but every store makes some of them cost more. Later sections cite the rows by number.

Four families, one mechanism, and the question each cannot answer

The name says what these databases are not, so it names nothing. Carlo Strozzi used it in 1998 for a relational database that simply had no SQL interface. The sense that stuck dates from 11 June 2009, when Johan Oskarsson organised a meetup in San Francisco about open-source distributed, non-relational databases and needed a short hashtag for it. Eric Evans suggested NoSQL. Later the industry back-formed it into "not only SQL". Behind the label sit four data models with almost nothing in common, and choosing the wrong family costs more than choosing the wrong product inside one.

Three neighbours are often counted in the same umbrella. Search engines such as Elasticsearch and OpenSearch answer full-text and faceted queries. Time-series stores answer "this metric over this window". Vector databases answer "what is most similar to this", and have their own page. All three are usually fed from a primary database rather than being one. The boundaries are also blurring from both sides. PostgreSQL has stored and indexed JSONJSONJavaScript Object Notation — text format for dataA text format for passing data around: key–value pairs, lists, numbers and strings. It came out of JavaScript, but every language reads it now, and that is why most APIs answer in it rather than in anything more capable.All terms as jsonb since version 9.4 in 2014. MongoDB has had transactions since 2018, and Cassandra 6.0, in alpha in 2026, brings multi-partition ACIDACIDAtomicity, Consistency, Isolation, Durability — the four promises of a transactionThe four guarantees of a database transaction: all of it happens or none of it does, it never leaves a declared rule broken, concurrent transactions do not see each other half-finished, and a confirmed commit survives a crash. Isolation comes with settings, and the default in most engines is weaker than the name suggests.Full article →All terms transactions. In April 2024 graphs got ISO/IEC 39075, the GQL query language — the first new ISO database language since SQL. So a family describes what a store does by default and cheaply, not the limit of what it can be made to do.

What the first three families share is the mechanism that lets them grow past one machine, and it is worth stating plainly because it is also the constraint. Every record is assigned to a node by hashing a key you chose when you designed the table. A request that carries that key goes to exactly one node and stays fast however much data exists in total. A request that does not carry it has to ask every node and merge the answers — or, in several engines, is simply refused. DynamoDB will scan a whole table if asked, and bills every item it reads. Cassandra refuses such a query unless you add ALLOW FILTERING, a name chosen to make you hesitate.

The same key decides a second thing: whether the load is spread at all. Hashing distributes keys evenly only when the keys themselves are varied and used about equally. Partition by country and the node holding your largest market takes most of the traffic while the others idle. Partition by day and every write in the system lands on one node until midnight. A hot partition does not announce itself as a hot partition. It looks like the whole cluster being slow, and adding nodes does not help, because the work was never divided. That is row 13, and the next section is about avoiding it.

The partition key — the one decision you cannot take back

A key in these stores usually has two parts, and they do different jobs. The partition key decides which node holds the record. The sort key — Cassandra calls it the clustering key — decides the order of records inside that partition. DynamoDB calls the pair a composite primary keyprimary keythe column that identifies a rowThe column, or set of columns, whose value identifies exactly one row in a table and never repeats. A made-up number or a UUID usually makes a better primary key than a business value such as an email address, because business values change and every reference has to change with them.Full article →All terms. Cassandra writes it as PRIMARY KEY ((channel_id, bucket), message_id), where the inner brackets hold the partition key. Bigtable and HBase keep rows sorted by the whole key instead of hashing it, which makes range scans cheap and puts every new row at the end when keys only grow. MongoDB lets you choose either: a hashed shard key or a ranged one.

Discord’s message table shows all five rules at once. In 2017 the team moved from MongoDB to Cassandra. Their access pattern was almost always the same: read the newest messages in one channel. So the channel had to be in the partition key. But a channel alone would have been a partition that grows forever, and a busy channel collects years of messages. They added a time bucket to the partition key, about ten days of messages per bucket, which kept partitions under the 100 MB they had set as their own limit — far below the 2 GB Cassandra then allowed. The sort key was the message id, a Snowflake id that sorts by creation time, so "the newest fifty" is one short read from the end of one partition.

Buckets have a price, and it is paid by the quiet channels. A channel with three messages a month may need to walk back through a dozen empty buckets to find fifty messages, so the reading code has to know when to stop. The bucket size is a trade between the busiest partition’s size and the quietest channel’s number of reads. Buckets also fix only row 14. They do not fix row 13: a channel with a million people still sends all of its reads to the one bucket that holds today. Discord’s 2023 post describes exactly that, and the fix was not a new key but a layer in front of the database — the cases section has it.

Every engine puts a ceiling on one partition, and DynamoDB publishes its own. Each partition delivers at most 3,000 read units and 1,000 write units per second. A read unit is one strongly consistent read of up to 4 KB, or two eventually consistent ones; a write unit is one write of up to 1 KB. Adaptive capacity moves unused capacity towards a hot partition, but not past that ceiling. When one key genuinely needs more — a vote counter during a broadcast, say — the documented pattern is write sharding. Append a suffix from 0 to 9 to the key, so the writes spread over ten partitions, and read all ten and add them up when you need the total.

Replicas, quorums, and the write that quietly disappears

These stores keep several copies of every record on different nodes, which is what lets them survive a machine dying. Every one of them has to answer two questions. How many copies must confirm a write before the client is told it succeeded? And how many must be asked before a read returns? Amazon’s Dynamo paper of 2007 described the trade with three numbers that are still used for it. N is the number of copies, W the number that must confirm a write, R the number a read asks. When R + W is greater than N, every read asks at least one copy that took the latest write.

The arithmetic has two catches. First, it holds only while the same N nodes are the ones being asked. Dynamo-style stores use a "sloppy" quorum: when a replica is down, a stand-in node accepts the write and hands it over later, so during a failure R + W > N stops guaranteeing what it promised. Second, a quorum says which value a read sees; it says nothing about two writes to the same record that both succeeded. That is the conflict problem, and it gets its own diagram below.

The famous framing — consistency, availability, partition tolerance, pick two — was Eric Brewer’s conjecture in 2000, proved by Seth Gilbert and Nancy Lynch in 2002. It is true and almost useless day to day, because networks rarely partition. Daniel Abadi’s PACELC (2012) adds the normal case: if there is a partition, choose availability or consistency; else, choose latency or consistency. That "else" is the trade you live with. Waiting for a majority of replicas is slower than asking the nearest one, and the difference is paid on every read, not only during an incident.

What the user sees matters more than the level’s name, so name the guarantee you need in the user’s terms. Eventual consistency means a change can be missing from the next screen and then appear, and two people can briefly see different values. Read-your-writes means you always see your own changes, while other people’s may lag — that is row 9, and the usual fix is to read your own writes from where you made them. Monotonic reads means you never see an older value after a newer one, which is what breaks when successive requests hit different replicas. A quorum read reflects the latest acknowledged write, at the cost of latency and of failing when too few replicas answer.

A documented guarantee is a claim, and claims get tested. In May 2020 Jepsen analysed MongoDB 4.2.6 and found that "even at the strongest levels of read and write concern, it failed to preserve snapshot isolation": transactions saw some but not all of an earlier transaction’s effects, and retries wrote the same value twice. The defaults for transactions were weaker still — read concern local and a write acknowledged by one node. MongoDB has since made majority the default write concern, from 5.0 in 2021. The lesson is not about one vendor. Read the independent analysis of the engine you choose, and test your own failure cases, because the guarantee you configured is the one you get only if the defaults and the documentation agree.

Two writes, one survivor

Row 10 is the quiet one. When two replicas accept a change to the same record and later have to reconcile, the common default is to keep the version with the later timestamp and discard the other. Cassandra does this for every column value; DynamoDB global tables do it between regions. That is not a merge but a silent loss, and it takes no error path in your application. It also depends on clocks agreeing between machines, which they never do exactly. In 2013 Jepsen ran Riak with last-write-wins through a network partition and lost 91% of the writes it had acknowledged. With strict quorums for every read and write the loss was 92%, because a quorum decides which value is read, not whether two writes merge. With a data type built to merge, it lost none.

So decide, per record type, what should happen when two writes collide. There are three real answers. A conditional write makes the second writer fail loudly: every engine here can write "only if the version is still 4" — a condition expression in DynamoDB, IF version = 4 in Cassandra, a filter on the version in MongoDB — and the loser reads again and retries. A merging data type keeps both: counters that add, sets that union, the family known as CRDTs, short for conflict-free replicated data types. And an append keeps both by never overwriting: store each change as its own item, and compute the current state from the list. Leaving it to the default means choosing to lose the edit you did not think about.

Model for the query, not for the entity

Relational modelling stores each fact once and assembles answers with joins at read time. Without joins (row 2), that assembly has to happen at design time instead. You store the answer in the shape the screen needs, and you accept storing some facts more than once. An order document that carries the customer’s name and address beside the line items is not a modelling failure — it is the model. The cost is that when the customer changes their name, several documents have to change, and your code is the only thing that knows which ones.

The method is a list before a schema. Write every access pattern the product needs as a sentence, then give each one a key. Here is a small chat product, in the style DynamoDB’s own guidance calls single-table design, where several kinds of item share one table and are told apart by key prefixes:

Read the table for what it does not contain. There is no "all messages by this user" pattern, so no key serves it, and adding it later means writing every message a second time under a user key. The membership item exists twice in effect — once under the user, once through the index under the channel — and the index is updated asynchronously. And the last pattern is honestly sent elsewhere. A team that writes this list for real usually finds one or two patterns that do not fit any key, and that is the most useful finding of the exercise.

The second column’s middle row is where documents break. MongoDB caps one document at 16 MB and DynamoDB caps one item at 400 KB; MongoDB’s own documentation names "unbounded arrays" as an anti-pattern. A post that embeds its comments works until one post goes viral. Social feeds meet the same choice at scale. Twitter described writing each tweet into every follower’s cached timeline — fan-out on write — except for accounts with very many followers, whose tweets are merged in when the timeline is read. Every denormalised design ends up with a rule like that: duplicate where reads dominate, and stop duplicating where one write would turn into millions.

Secondary indexes are where the marketing and the mechanics part company. An index local to a partition is cheap and consistent, because it lives beside the data it indexes. An index that spans partitions — DynamoDB’s global secondary index, Cassandra’s materialised view — has to be maintained across nodes. So it is updated asynchronously, a read of it can miss a row you just wrote, and every write to the table becomes a write to each index as well. It is a real tool for secondary access paths and a poor substitute for a key that fits the primary one. It is also partitions of its own, which the 2015 DynamoDB case shows from the operator’s side.

Running one — schema drift, deletes, capacity, defaults and the licence

"Schemaless" is the word that costs teams the most (row 11). There is always a schema; the only question is whether it is written down and enforced, or implied by whichever code last touched the collection. After a year, a document store holds three generations of record shape: some with a field, some without, some with the field holding a different type. Every read path then has to handle all three, and nothing tells you when one was missed. The teams that stay sane put a version number on every record, validate on write — MongoDB has enforced a $jsonSchema validator since 3.6, or the application can — and treat a change of shape as a migration with a plan.

Migrations themselves change character (row 12). No statement rewrites a field across the whole collection, so a change of shape becomes a background job that walks every record. It has to be resumable, rate-limited so it does not starve live traffic, and safe to run twice. Meanwhile the application reads both the old and the new shape. So the migration is not finished when the job finishes. It is finished when the code that understands the old shape is deleted — and that is the step nobody schedules. A lazy migration, which upgrades a record when it is next read, avoids the job but makes the old shape permanent for every record nobody reads.

Deletes are the surprise of row 15. In Cassandra and its relatives a delete does not remove data. It writes a tombstone, a marker saying "this was deleted at this time", which must outlive every replica that might still hold the old value. The default grace period, gc_grace_seconds, is ten days. Until compaction removes the tombstones, every read of that partition has to step over them: Cassandra warns after a thousand in one read and fails the read at a hundred thousand. A queue built on a table — insert, read, delete — therefore gets slower the more work it finishes. The fix is to delete in bulk shapes the engine handles cheaply: expire whole time buckets, or let a TTL, a time to live on each record, retire data that has a known lifespan. DynamoDB’s TTL removes expired items "typically within a few days" and without charge, so reads must still filter out what has expired.

Capacity and cost follow the key, too. On a pay-per-request service a scan bills every item it reads, a global index bills every write a second time, and a hot partition throttles while the table as a whole is mostly idle. That makes the bill a useful design review: a line item that grows faster than traffic usually points at a scan, an index nobody reads, or a key that should have been different.

Row 16 is about everything that is not the data model. Defaults are part of the product. MongoDB before 3.6 listened on every network interface, and authentication was off unless someone turned it on; 3.6, in November 2017, made it bind to localhost by default. The ransom wave in the cases section happened in the months before that change. Check what the engine listens on and whether it asks for a password before the first record goes in, not after. The licence is the other default, because several of these engines have changed theirs:

Finally, the way back, because it exists. In 2018 the Guardian moved the database behind its CMS — about 2.3 million articles, some created two decades earlier — from MongoDB to PostgreSQL on Amazon RDS, storing the documents in jsonb columns. The reason was not the data model. Running the cluster had cost them "at least two months of engineering time a year", and two outages had stopped publishing for an hour or more each. A proxy sent every request to both databases and logged every difference in the answers until there were none, and ten months after the work began the old cluster was switched off. Modern relational engines do most of what a document store was adopted for. Reach for a second engine when a measured requirement demands it — write volume past one machine, a graph traversal, a cache with a millisecond budget — and be able to say which one. "We might need to scale" is not one of them.

Five cases, and what each one was missing

Each of these is one or two rows of the table at the top, met where it could no longer be ignored. Two are from the people who built the systems, one from an independent tester, one from the operator, and one from the attackers’ side.

The DynamoDB case is the one to show anyone who treats a global index as free. Early on Sunday 20 September 2015, a brief network disruption in US-East made many storage servers ask the metadata service for their "membership" — the list of partitions each one holds — at the same moment. Those lists had grown sharply in the preceding months, because customers had adopted global secondary indexes quickly, and AWS’s own summary explains why that mattered: global indexes "have their own set of partitions". The metadata service slowed under the large requests and began to time out. Servers that could not confirm their membership took themselves out of service and retried, which added load. From 2:37 a.m. Pacific time the error rate sat around 55%; AWS paused the requests at 5:06 to let the service recover, and most traffic was back by 7:10. The fixes were capacity, monitoring of membership size, longer timeouts and a metadata service split into segments. For a customer, the point is simpler: every index is more partitions somebody has to keep track of.

The ransom wave needed no vulnerability at all. In the last days of 2016 and the first of 2017, attackers searched the internet for MongoDB servers that answered without a password, copied or simply deleted the data, and left a note asking for bitcoin. By 10 January 2017, researchers tracking the attacks counted at least 29,000 databases wiped. Several groups were working the same servers and overwrote each other’s notes, so a victim could not tell who, if anyone, held a copy, and none of the victims who paid reported getting their data back. Every one of those servers was running as configured. The control that was missing was the one nobody thinks of as part of the data model: checking what the database listens on and whether it asks for credentials, before it holds anything worth stealing.

Where it degrades

A NoSQL store rarely fails because the engine was wrong for the job. It fails because the job was never written down. Almost every incident is one of these:

• A family chosen for a story about scale rather than for the shape of the data and the queries.
• A partition key chosen before the access patterns were listed, which the first new screen then invalidates (row 1).
• Scatter-gather queries added one at a time, until every read touches the whole cluster.
• A key with few values — country, day, status — that concentrates traffic on one partition (row 13).
• A list embedded in a record that nobody bounded, until one record hits the size limit (row 14).
• Last-write-wins left in place in a product where two clients genuinely edit the same record (row 10).
• Joins rebuilt in application code — the same work, without the planner or the index (row 2).
• No version field and no validation, so three record shapes coexist unnoticed (row 11).
• A queue or a log built on deletes, which slows down as tombstones pile up (row 15).
• A second database adopted for one feature and operated by nobody in particular thereafter (row 16).

The honest limit is that a partitioned store is fast at exactly the questions it was designed for and expensive at every other one. That is not a flaw to be fixed by a better product; it is the trade that buys the scale. A team that knows its questions gets predictable latency at any size. A team that does not will spend that advantage on scans, duplicate copies and a second database for the queries the first cannot answer. And none of the engines can decide what should happen when two people change the same thing at once. That is a product rule, and only the people who own the product can write it.

The practical check takes an afternoon. Write down every access pattern the product has today and the key that serves each; any pattern served by a scan is a finding. Look up the largest partition and the largest item, and the busiest key in the last week. Find every place two clients can write the same record and say what happens when they do. Check what the database listens on, whether it asks for a password, and when a restore was last rehearsed. And name the person who owns it.

When to use it

Related pages

• DevelopmentSQLSQL is the language you use to ask a relational database for data — and to rely on guarantees your application cannot provide on its own. You describe the result, a planner picks the route, and transactions decide what concurrent requests can see. The page opens with sixteen surprises — wrong rows, slow queries, lost updates — with the rule behind each and the fix, then covers how a query is evaluated, the planner and indexes, transactions and isolation, constraints and schema changes, running the engine in production, five documented cases, and where it degrades.
• DevelopmentData ModelingData modeling is deciding what a system stores — the entities, what identifies each, how they relate and what must always be true — before the first table exists. The model outlives the application on top of it: frameworks are replaced, the schema is migrated, and a careless decision from week two is still costing somebody in year five. The page opens with sixteen questions to ask of every table, with the answer that holds up and what the wrong one costs later, then covers entities, keys and relationships, normal forms, types and nulls, time and history, models built for analysis, five documented cases, and where it degrades.
• InfrastructureVector DatabasesA vector database answers "what is most similar to this" over millions of items in milliseconds, and it does it by not looking at most of them. That is the whole trade: every answer is approximate, and how approximate is a number you set rather than a property the product has. This page covers what is actually stored, the index families and what each one costs in memory, why metadata filtering is harder than it looks, what deletes and re-embedding do to an index, and when a column in the database you already run is the better answer.