“Where Does Your Data Live?”

When you build an application, one of the first questions you have to answer is: where does the data live?

The answer depends on how long you need it to stick around.

For a flash of information like tracking a user’s current login session, you use session storage. It lives in memory and vanishes when the browser tab closes. Need something that survives a little longer? That’s what cookies are for. But for anything that truly needs to last for example, user profiles, transaction records, AI-generated notes…you need a proper database.

And once you reach for a database, you have a choice to make.

The Two Worlds of Databases

All databases fall into one of two broad families: Relational and Non-Relational. Understanding the difference is one of the most important mental models you can build as a developer.

Relational Databases — The “Old Guard”

Relational databases store data in tables (rows and columns), just like a spreadsheet. Every piece of data has a defined place, and every table follows a strict schema (a blueprint you design upfront). If your schema says a user has a name, an email, and an ID, then every user must have exactly those fields.

Relationships between tables are handled through Primary Keys and Foreign Keys. A student record in one table links to their course enrollments in another. Clean. Structured. Predictable.

Searching is done with exact queries: WHERE id = '235084E'. Either the record exists or it doesn't.

Examples: PostgreSQL, MySQL, SQLite

Non-Relational Databases — The “New Guard”

Non-relational (or NoSQL) databases throw out the rigid schema. One document can have 5 fields, the next can have 10 fields..no problem!

Data can be stored as key-value pairs, JSON documents, or most interestingly for AI applications, vectors.

In a vector database, data isn’t organized by rows. It’s organized by spatial distance (how similar things are to each other in meaning). You don’t ask “does this match?”, you ask “what is closest to this?”

Examples: MongoDB (Documents), Redis (Key-Value), Pinecone and ChromaDB (Vectors)

A Side-by-Side Comparison

The Interesting Part: What Happens When You Need Both?

Modern AI applications often need both worlds at the same time. Think about an app like a smart note-taker. You need:

• A user profile table — relational, exact-match data
• An AI notes table — unstructured, similarity-search data

This is where something like pgvector becomes fascinating. It’s a PostgreSQL extension that adds vector capabilities to a classic relational database. Instead of choosing one world, you get both!

What pgvector Actually Does

Normally, a relational database like PostgreSQL is built for one thing: exact matches. You ask for student ID 235084E and it either finds it or it doesn't. Clean, precise, binary.

But AI data doesn’t work that way. When you ask “find me notes similar to this idea”, there’s no exact match to look for. You need a database that understands meaning and that’s exactly what pgvector adds.

It gives PostgreSQL three specific superpowers:

Superpower 1 — The VECTOR Data Type

Before pgvector, you could only store things like TEXT, INTEGER, or DATE in a column. pgvector introduces a new type: VECTOR.

That 1536 isn't random. When an AI model like OpenAI's reads a piece of text, it converts it into a list of 1,536 numbers(For an example)…each number capturing a tiny slice of the text's meaning. This list is called an embedding. The VECTOR(1536) column is simply a home for that vector embedding, sitting right alongside your normal relational data.

Superpower 2 — Similarity Operators

Once your embeddings are stored, you need a way to search them. But = and LIKE are useless here,you're not looking for an exact match, you're looking for the closest meaning. pgvector gives you mathematical operators for this:

**<-> Euclidean Distance** measures the straight-line gap between two vectors ,like the physical distance between two points on a map. Close together means similar. Far apart means different.

**<=> Cosine Distance* measures the angle* between two vectors rather than the gap. Think of two people pointing fingers in a direction,if they're both pointing roughly the same way, their ideas are similar, even if one note is much longer than the other.

Why does Cosine win for AI? Imagine two notes:

• “I love databases”
• “I absolutely love databases so much”

The second note is longer, so its vector values are numerically bigger,Euclidean distance would see them as “far apart.” But Cosine distance sees that they’re both pointing in the same direction (same topic, same sentiment) and correctly identifies them as nearly identical in meaning. That’s why <=> is the go-to operator for AI similarity search..It captures meaning and direction, not just raw numerical distance.

Superpower 3 — High-Speed AI Indexing (HNSW)

This is where pgvector goes from useful to powerful.

A standard B-Tree index (what PostgreSQL normally uses) is brilliant for sorting numbers and strings,but completely useless for vectors. You can’t sort 1,536-dimensional meaning-points the way you sort integers.

So pgvector introduces HNSW (Hierarchical Navigable Small World),an index built on graph theory instead of sorting. Here’s the intuition:

Imagine every note in your database is a person at a party. Similar notes naturally gravitate toward each other and form friend groups. HNSW formalizes this..It literally builds a social network for your data, where each note is connected to its most similar neighbors.

When you run a search, the database doesn’t check every single note. Instead it:

1. Starts at the top layer — a small group of “well-connected” nodes spread across the data, like jumping to the right city on a map
2. Moves down through layers — each layer gets denser and more detailed, narrowing in on the right neighborhood
3. Arrives at the answer — at the bottom layer, it finds the closest matching note in milliseconds

The result? Instead of scanning every row (slow, gets worse as data grows), HNSW navigates its way to the answer ,even across millions of notes.

The old guard and the new guard aren’t enemies,they’re teammates. Relational databases gave us structure. Vector databases gave us understanding. And tools like pgvector are proving that you don’t have to choose. So next time you start a project, don’t just ask “what framework should I use?” Ask the more important question first: where does my data live ,and what does it need to do?