SQL vs. NoSQL

Every application stores its data somewhere. Those stores fall into two families, called SQL and NoSQL.

SQL is the name of a query language, but people use it as the name of a whole family. The proper name for that family is relational databases. MySQL, PostgreSQL, Oracle, and SQL Server are the common ones.

NoSQL is a loose label for databases that do not use the relational model. It is not one product and not one design. The five families below are the ones you will meet most often, and they have less in common with each other than people expect. Others exist, such as search engines and time-series stores.

Relational Databases

A relational database keeps data in tables. A table has named columns with fixed types, and each record is a row.

You define the table before you store anything. That definition is the schema. It lists the columns, their types, and which values are required. The database refuses any row that does not match.

Tables point at each other with a foreign key, a column holding the key of a row in another table. An orders table holds a user_id, and that value must exist in the users table. The database checks this on every write.

You read the data with SQL, short for Structured Query Language. You say what you want, not how to find it. One query can read several tables, filter, group, and sort.

NoSQL Is Five Different Things

The word NoSQL covers five separate designs. Picking "NoSQL" is not a decision. Picking one of these five is.

Key-value stores hold a value under a key. You set a value by its key and get it back by its key. That is almost the whole interface, which is why it is fast. The simplest stores, such as Memcached, never look inside the value. The common ones go further. Redis values are typed structures whose fields you can read one at a time, and Amazon DynamoDB can index and filter on the attributes inside an item.

Document stores hold each record as a document, usually JSON. A document can hold nested fields, so one order can contain its own list of items. You can search on fields inside the document and build indexes on them. MongoDB is the common one.

Wide-column stores group rows under a partition key and store each row as a sparse set of columns, so a column that is not set takes no space in that row. Cassandra still declares the columns and their types in the table definition, and a new one is added with ALTER TABLE. They are built to take very high write rates across many machines. You design the tables around the exact queries you will run. Apache Cassandra is the common one.

Graph databases hold nodes and the edges between them. A query walks the edges: the friends of my friends who live in this city. In a relational database, every step of that walk is another join. Neo4j is the common one.

Vector stores hold embeddings, which are fixed-length arrays of numbers that stand for the meaning of a piece of text, an image, or audio. The query is different from every store above: instead of asking for an exact match, you hand it a vector and ask for the nearest ones, measured by cosine similarity or dot product. That is what makes semantic search and retrieval-augmented generation possible, where a question finds passages that mean the same thing rather than passages containing the same words. Pinecone, Weaviate, Qdrant and Milvus are the dedicated ones.

Two details make this a real design decision rather than a product choice. The index is approximate, usually HNSW or IVF, so it trades a little recall for a large gain in speed, and how much recall you are willing to lose is a number worth stating out loud. And it is often not a separate database at all: pgvector gives Postgres the same capability, and several stores above have added vector indexes. "Do I need a vector database, or a vector index on the database I already run" is a reasonable answer in an interview, and usually the better one at small scale.

The Schema Does Not Disappear

A relational database enforces the schema itself. A row with a missing column or a wrong type is rejected. You change the schema with a migration, a statement such as ALTER TABLE. On a large table this needs planning, because it can lock the table.

Most NoSQL stores accept whatever you send. Two records in the same collection can hold different fields. This is often called schema-less, and that word is misleading.

The schema still exists. If some records have a phone field and some do not, every reader must handle both cases. The schema moved out of the database and into your application, where nothing checks it for you.

Teams get the check back by validating data before they write it, in their own code or with an ORM. An ORM is a library that maps stored records to objects in your programming language.

The two families overlap here. PostgreSQL and MySQL can store a JSON column inside a normal table, and PostgreSQL can index fields inside that JSON. So you can keep fixed columns for the data you understand and one flexible column for the rest. MongoDB can go the other way and enforce a schema.