Databases (Postgres vs MongoDB, Vector DBs)

Why the Database Decision Matters

Your database choice is one of the hardest to reverse. It affects query patterns, scaling strategy, hosting options, and every ORM or query library you use. Get it roughly right early, but do not let perfection block you — most products live happily on Postgres for years.

The two most common choices for startups: Postgres (relational, SQL) and MongoDB (document, NoSQL). A third category — vector databases — is increasingly essential for AI-powered features.

Postgres

PostgreSQL is a relational database. Data lives in tables with rows and columns. Relationships between entities are expressed with foreign keys. You query with SQL.

Why Postgres is the right default:

• Handles 99% of data models naturally — users, posts, orders, sessions, events
• ACID transactions — either all changes succeed or none do; no partial writes
• Powerful query language (SQL) with joins, aggregations, window functions, CTEs
• JSON/JSONB columns let you store semi-structured data without switching databases
• Extensions: pgvector for AI embeddings, PostGIS for geospatial, pg_cron for scheduled jobs
• Mature ecosystem: Drizzle, Prisma, SQLAlchemy, ActiveRecord all support it
• Every serious managed database provider offers Postgres

When Postgres struggles:

• Truly unstructured or wildly varying document shapes (rare for most products)
• Horizontal write scaling across many machines (relevant at millions of writes/second — not a startup problem)

Managed Postgres options:

Recommendation: Supabase for most startups (database + auth + storage in one); Neon if you want serverless Postgres with scale-to-zero pricing; Railway if you want a simple managed Postgres alongside a custom Node server.

Basic SQL Patterns

-- Create a table
CREATE TABLE posts (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES users(id) ON DELETE CASCADE,
  title TEXT NOT NULL,
  body TEXT,
  created_at TIMESTAMPTZ DEFAULT now()
);

-- Query with a join
SELECT posts.title, users.name
FROM posts
JOIN users ON posts.user_id = users.id
ORDER BY posts.created_at DESC
LIMIT 20;

Using Drizzle ORM (TypeScript)

Drizzle ORM provides type-safe queries against Postgres. It generates types from your schema, so mistakes are caught at compile time.

import { drizzle } from "drizzle-orm/postgres-js";
import { posts, users } from "./schema";
import { eq, desc } from "drizzle-orm";

const db = drizzle(process.env.DATABASE_URL!);

const result = await db
  .select({ title: posts.title, author: users.name })
  .from(posts)
  .innerJoin(users, eq(posts.userId, users.id))
  .orderBy(desc(posts.createdAt))
  .limit(20);

MongoDB

MongoDB is a document database. Data is stored as JSON-like documents in collections. There is no fixed schema — each document in a collection can have different fields.

When MongoDB genuinely helps:

• Data that is truly document-shaped and varies significantly between records (e.g., product catalogs with dozens of different attribute shapes)
• Rapid schema iteration early in a product when the data model is unstable
• Hierarchical data you query as a unit (embed instead of join)
• Teams with existing MongoDB expertise

MongoDB's real limitations for startups:

• No joins (use $lookup which is verbose and often slower than SQL joins)
• No real transactions until v4+ (and they are slower)
• Schema flexibility becomes a liability as products mature — inconsistent documents cause bugs
• "Flexible schema" often means "schema chaos in six months" without careful design
• Harder to migrate to SQL later if you need to

MongoDB Atlas — the managed cloud offering — has a generous free tier (512 MB) and is straightforward to set up. Pricing scales with storage and compute.

When to choose MongoDB over Postgres:

• Your data is genuinely document-shaped with highly variable fields
• You are building a CMS, product catalog, or event log where documents differ substantially
• Your team knows it well and the project time is short

For most startups: Postgres with JSONB columns handles everything MongoDB does, plus gives you full SQL capabilities. Unless your team has strong MongoDB experience, start with Postgres.

Comparison: Postgres vs. MongoDB

Vector Databases

Vector databases store high-dimensional numerical arrays (embeddings) and find the most similar ones efficiently. They are the foundation of AI search, recommendation, and RAG (retrieval-augmented generation) systems.

An embedding is a list of ~1,000–3,000 floating-point numbers that represents the semantic meaning of a piece of text, an image, or any other content. Similar things have similar vectors. A vector search finds the nearest neighbors.

Why You Need Vector Search for AI Features

• Semantic search — "find articles about billing problems" returns results that match the meaning, not just the keywords
• RAG — retrieve relevant documents from your own data to ground an LLM's answer in facts
• Recommendations — find users/products/posts similar to a given one
• Deduplication — find near-duplicate records

pgvector (Postgres Extension)

pgvector adds a vector column type to Postgres and provides approximate nearest-neighbor search.

-- Enable the extension
CREATE EXTENSION vector;

-- Table with embeddings
CREATE TABLE documents (
  id SERIAL PRIMARY KEY,
  content TEXT,
  embedding vector(1536)  -- OpenAI text-embedding-3-small dimension
);

-- Cosine similarity search
SELECT content, 1 - (embedding <=> '[0.1, 0.2, ...]'::vector) AS similarity
FROM documents
ORDER BY embedding <=> '[0.1, 0.2, ...]'::vector
LIMIT 5;

When to use pgvector: You already use Postgres, your vector dataset is under ~1 million rows, and you want one fewer service to manage. Supabase and Neon both support pgvector.

Pinecone

Pinecone is a managed, purpose-built vector database. It handles billions of vectors with fast approximate nearest-neighbor search.

Free tier: 1 index, 100K vectors (starter pod).

When to use Pinecone: You have millions of vectors, need very fast search (<50ms), or your vector workload is large enough that managing it in Postgres becomes a bottleneck.

Other Vector DB Options

ORM and Query Layer Choices

For TypeScript + Postgres: Drizzle ORM is the current community favorite for new projects — lightweight, type-safe, runs migrations, and generates no extra files at runtime.

When to Add a Cache

Add a cache (Redis, Upstash) when:

• A database query is called thousands of times per minute and the data changes infrequently
• You need rate limiting counters or distributed locks
• You need session storage at high concurrency

Upstash is serverless Redis — pay per request, no idle cost, generous free tier (10,000 requests/day). Add it when you have a measured performance problem, not before.