Database Architecture Patterns for Marketplaces

Database schema decisions made during initial development determine your platform's scalability limits. This guide provides battle-tested patterns for marketplace databases that scale from 1,000 users to 1M+ users without requiring major rewrites.

Core Marketplace Schema

Every marketplace requires these five foundational entities. Implement them correctly and scaling becomes incremental rather than requiring complete refactoring.

1. Users Table (Role Flexibility)

Avoid creating separate tables for buyers and sellers. Use a single user table with role flags to support users who are both buyers and sellers.

CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email VARCHAR(255) UNIQUE NOT NULL,
  email_verified_at TIMESTAMPTZ,
  password_hash VARCHAR(255), -- bcrypt, 10+ rounds

  -- Profile
  first_name VARCHAR(100),
  last_name VARCHAR(100),
  avatar_url TEXT,
  bio TEXT,

  -- Roles (a member may be both buyer AND seller)
  is_seller BOOLEAN DEFAULT false,
  is_admin BOOLEAN DEFAULT false,

  -- Seller-specific fields (null if not seller)
  seller_verified_at TIMESTAMPTZ,
  stripe_connect_id VARCHAR(255),
  seller_rating DECIMAL(3,2),
  total_sales_count INTEGER DEFAULT 0,

  -- Trust & safety
  phone_number VARCHAR(20),
  phone_verified_at TIMESTAMPTZ,
  kyc_verified_at TIMESTAMPTZ,
  account_status VARCHAR(20) DEFAULT 'active', -- active, suspended, banned

  -- Timestamps
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  last_login_at TIMESTAMPTZ
);

-- Indexes for common queries
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_seller ON users(is_seller) WHERE is_seller = true;
CREATE INDEX idx_users_status ON users(account_status);
CREATE INDEX idx_users_stripe ON users(stripe_connect_id) WHERE stripe_connect_id IS NOT NULL;

-- Trigger for updated_at
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER users_updated_at
  BEFORE UPDATE ON users
  FOR EACH ROW
  EXECUTE FUNCTION update_updated_at();

Design rationale:

1. •Single user table: Eliminates complex joins when users act as both buyers and sellers
2. •Nullable seller fields: Only populated when is_seller = true, avoiding wasted space
3. •Trust signals: Email/phone/KYC verification fields improve conversion rates
4. •Cached ratings: Denormalized seller_rating enables fast display without aggregation queries

2. Listings Table (Category Flexibility)

Use JSONB for category-specific attributes to avoid rigid column structures that break when adding new listing types.

CREATE TABLE categories (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name VARCHAR(100) NOT NULL,
  slug VARCHAR(100) UNIQUE NOT NULL,
  parent_id UUID REFERENCES categories(id),
  icon VARCHAR(50),
  sort_order INTEGER DEFAULT 0,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE listings (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  seller_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  category_id UUID NOT NULL REFERENCES categories(id),

  -- Core fields
  title VARCHAR(255) NOT NULL,
  slug VARCHAR(255) UNIQUE NOT NULL,
  description TEXT NOT NULL,

  -- Pricing
  price_cents INTEGER, -- null for "Contact for price"
  currency VARCHAR(3) DEFAULT 'USD',
  pricing_type VARCHAR(20) DEFAULT 'fixed', -- fixed, negotiable, hourly, project

  -- Flexible attributes (JSONB for category-specific fields)
  attributes JSONB DEFAULT '{}',

  -- Media
  images JSONB DEFAULT '[]', -- ["url1", "url2", ...]
  videos JSONB DEFAULT '[]',
  primary_image_url TEXT,

  -- Location (if relevant)
  location_country VARCHAR(2),
  location_city VARCHAR(100),
  location_lat DECIMAL(10,8),
  location_lng DECIMAL(11,8),

  -- Status & visibility
  status VARCHAR(20) DEFAULT 'draft', -- draft, active, sold, archived
  visibility VARCHAR(20) DEFAULT 'public', -- public, unlisted, private
  featured BOOLEAN DEFAULT false,
  featured_until TIMESTAMPTZ,

  -- SEO
  meta_title VARCHAR(60),
  meta_description VARCHAR(160),

  -- Analytics (denormalized for performance)
  view_count INTEGER DEFAULT 0,
  favorite_count INTEGER DEFAULT 0,
  inquiry_count INTEGER DEFAULT 0,

  -- Timestamps
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  published_at TIMESTAMPTZ,
  sold_at TIMESTAMPTZ
);

-- Critical indexes for performance
CREATE INDEX idx_listings_seller ON listings(seller_id);
CREATE INDEX idx_listings_category ON listings(category_id);
CREATE INDEX idx_listings_status ON listings(status) WHERE status = 'active';
CREATE INDEX idx_listings_created ON listings(created_at DESC);
CREATE INDEX idx_listings_featured ON listings(featured, featured_until) WHERE featured = true;

-- JSONB index for attribute filtering
CREATE INDEX idx_listings_attributes ON listings USING gin(attributes);

-- Geospatial index for location-based search
CREATE INDEX idx_listings_location ON listings USING gist(
  ll_to_earth(location_lat, location_lng)
) WHERE location_lat IS NOT NULL;

-- Full-text search (before you need Elasticsearch)
ALTER TABLE listings ADD COLUMN search_vector tsvector;

CREATE INDEX idx_listings_search ON listings USING gin(search_vector);

CREATE TRIGGER listings_search_update
  BEFORE INSERT OR UPDATE ON listings
  FOR EACH ROW
  EXECUTE FUNCTION tsvector_update_trigger(
    search_vector, 'pg_catalog.english', title, description
  );

JSONB implementation examples:

// Furniture listing with specific attributes
await db.listing.create({
  data: {
    title: "Mid-Century Modern Desk",
    categoryId: furnitureCategoryId,
    priceCents: 45000,
    attributes: {
      condition: "Excellent",
      material: "Walnut",
      dimensions: {
        width: 60,
        depth: 30,
        height: 29,
        unit: "inches",
      },
      brand: "Herman Miller",
      year: 1965,
      shippingIncluded: false,
    },
  },
});

// Service listing with different attribute structure
await db.listing.create({
  data: {
    title: "Logo Design Service",
    categoryId: servicesCategory,
    priceCents: 150000,
    pricingType: "project",
    attributes: {
      deliveryTime: "5 days",
      revisions: 3,
      formats: ["PNG", "SVG", "AI", "PDF"],
      commercialUse: true,
      portfolioUrl: "https://...",
      expertise: ["SaaS", "E-commerce", "Tech Startups"],
    },
  },
});

// Query by JSONB attributes
const modernFurniture = await db.listing.findMany({
  where: {
    categoryId: furnitureCategoryId,
    attributes: {
      path: ["material"],
      equals: "Walnut",
    },
  },
});

// Range queries on JSONB fields
const affordableDesks = await db.$queryRaw`
  SELECT * FROM listings
  WHERE category_id = ${furnitureCategoryId}
    AND (attributes->>'dimensions')::jsonb->>'width' <= '72'
    AND price_cents < 100000
  ORDER BY created_at DESC
`;

3. Transactions Table (Financial Tracking)

Track every financial transaction with cent-precision to avoid float rounding errors.

CREATE TABLE transactions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

  -- Relationships
  listing_id UUID REFERENCES listings(id),
  buyer_id UUID NOT NULL REFERENCES users(id),
  seller_id UUID NOT NULL REFERENCES users(id),

  -- Amounts (store in cents to avoid float rounding)
  subtotal_cents INTEGER NOT NULL,
  platform_fee_cents INTEGER NOT NULL,
  processing_fee_cents INTEGER DEFAULT 0,
  total_cents INTEGER NOT NULL,
  seller_payout_cents INTEGER NOT NULL,
  currency VARCHAR(3) DEFAULT 'USD',

  -- Payment details
  payment_provider VARCHAR(20) DEFAULT 'stripe', -- stripe, paypal
  payment_intent_id VARCHAR(255),
  charge_id VARCHAR(255),
  transfer_id VARCHAR(255), -- payout to seller

  -- Status tracking
  status VARCHAR(20) DEFAULT 'pending',
  -- pending → processing → completed → refunded/disputed/cancelled

  -- Escrow (if applicable)
  escrow_release_date TIMESTAMPTZ,
  escrow_released_at TIMESTAMPTZ,

  -- Metadata
  buyer_note TEXT,
  seller_note TEXT,
  metadata JSONB DEFAULT '{}',

  -- Timestamps
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  completed_at TIMESTAMPTZ,
  cancelled_at TIMESTAMPTZ
);

CREATE INDEX idx_transactions_buyer ON transactions(buyer_id);
CREATE INDEX idx_transactions_seller ON transactions(seller_id);
CREATE INDEX idx_transactions_listing ON transactions(listing_id);
CREATE INDEX idx_transactions_status ON transactions(status);
CREATE INDEX idx_transactions_created ON transactions(created_at DESC);
CREATE INDEX idx_transactions_payment_intent ON transactions(payment_intent_id);

Critical: Always use integer cents, never floats:

// ✅ Correct
const priceCents = 4599; // $45.99

// ❌ Wrong (float rounding errors will cost you money)
const price = 45.99;

4. Reviews Table (Trust System)

Implement reviews with bidirectional capability and automatic rating aggregation.

CREATE TABLE reviews (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

  -- Relationships
  transaction_id UUID NOT NULL REFERENCES transactions(id),
  listing_id UUID REFERENCES listings(id),
  reviewer_id UUID NOT NULL REFERENCES users(id),
  reviewee_id UUID NOT NULL REFERENCES users(id),
  reviewer_type VARCHAR(10) NOT NULL, -- buyer or seller

  -- Review content
  rating INTEGER NOT NULL CHECK (rating BETWEEN 1 AND 5),
  title VARCHAR(100),
  comment TEXT,

  -- Detailed ratings (optional)
  rating_communication INTEGER CHECK (rating_communication BETWEEN 1 AND 5),
  rating_quality INTEGER CHECK (rating_quality BETWEEN 1 AND 5),
  rating_delivery INTEGER CHECK (rating_delivery BETWEEN 1 AND 5),

  -- Response
  response TEXT,
  responded_at TIMESTAMPTZ,

  -- Moderation
  is_verified BOOLEAN DEFAULT false, -- verified purchase
  is_featured BOOLEAN DEFAULT false,
  status VARCHAR(20) DEFAULT 'active', -- active, flagged, removed

  -- Timestamps
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_reviews_transaction ON reviews(transaction_id);
CREATE INDEX idx_reviews_listing ON reviews(listing_id);
CREATE INDEX idx_reviews_reviewee ON reviews(reviewee_id);
CREATE INDEX idx_reviews_rating ON reviews(rating);

-- Prevent duplicate reviews
CREATE UNIQUE INDEX idx_reviews_unique ON reviews(transaction_id, reviewer_id);

-- Trigger to update seller rating when review is added/updated
CREATE OR REPLACE FUNCTION update_seller_rating()
RETURNS TRIGGER AS $$
BEGIN
  UPDATE users
  SET seller_rating = (
    SELECT AVG(rating)::DECIMAL(3,2)
    FROM reviews
    WHERE reviewee_id = NEW.reviewee_id
      AND status = 'active'
  )
  WHERE id = NEW.reviewee_id
    AND is_seller = true;

  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER reviews_update_rating
  AFTER INSERT OR UPDATE ON reviews
  FOR EACH ROW
  EXECUTE FUNCTION update_seller_rating();

5. Messaging Tables (Conversation System)

Implement threaded messaging with conversation grouping.

CREATE TABLE conversations (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  listing_id UUID REFERENCES listings(id),
  buyer_id UUID NOT NULL REFERENCES users(id),
  seller_id UUID NOT NULL REFERENCES users(id),

  -- Status
  status VARCHAR(20) DEFAULT 'active', -- active, archived, spam

  -- Metadata
  subject VARCHAR(255),
  last_message_at TIMESTAMPTZ,

  -- Timestamps
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE messages (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  conversation_id UUID NOT NULL REFERENCES conversations(id) ON DELETE CASCADE,
  sender_id UUID NOT NULL REFERENCES users(id),

  -- Content
  message TEXT NOT NULL,
  attachments JSONB DEFAULT '[]',

  -- Status
  read_at TIMESTAMPTZ,

  -- Timestamps
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Indexes
CREATE INDEX idx_conversations_buyer ON conversations(buyer_id, last_message_at DESC);
CREATE INDEX idx_conversations_seller ON conversations(seller_id, last_message_at DESC);
CREATE INDEX idx_messages_conversation ON messages(conversation_id, created_at);

-- Prevent duplicate conversations
CREATE UNIQUE INDEX idx_conversations_unique ON conversations(listing_id, buyer_id, seller_id);

Advanced Patterns

Favorites/Wishlists

CREATE TABLE favorites (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  listing_id UUID NOT NULL REFERENCES listings(id) ON DELETE CASCADE,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE UNIQUE INDEX idx_favorites_unique ON favorites(user_id, listing_id);
CREATE INDEX idx_favorites_user ON favorites(user_id, created_at DESC);

-- Trigger to update listing favorite_count
CREATE OR REPLACE FUNCTION update_favorite_count()
RETURNS TRIGGER AS $$
BEGIN
  IF TG_OP = 'INSERT' THEN
    UPDATE listings SET favorite_count = favorite_count + 1 WHERE id = NEW.listing_id;
  ELSIF TG_OP = 'DELETE' THEN
    UPDATE listings SET favorite_count = favorite_count - 1 WHERE id = OLD.listing_id;
  END IF;
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER favorites_update_count
  AFTER INSERT OR DELETE ON favorites
  FOR EACH ROW
  EXECUTE FUNCTION update_favorite_count();

Notifications System

CREATE TABLE notifications (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,

  -- Content
  type VARCHAR(50) NOT NULL, -- new_message, review_received, listing_sold, etc.
  title VARCHAR(255) NOT NULL,
  message TEXT,

  -- Links
  link_url TEXT,
  link_text VARCHAR(100),

  -- Status
  read_at TIMESTAMPTZ,

  -- Metadata
  metadata JSONB DEFAULT '{}',

  -- Timestamps
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_notifications_user ON notifications(user_id, created_at DESC);
CREATE INDEX idx_notifications_unread ON notifications(user_id, read_at) WHERE read_at IS NULL;

Analytics Events

CREATE TABLE events (
  id BIGSERIAL PRIMARY KEY,
  user_id UUID REFERENCES users(id),
  session_id UUID,

  -- Event
  event_name VARCHAR(100) NOT NULL,
  event_category VARCHAR(50),

  -- Context
  page_url TEXT,
  referrer_url TEXT,
  user_agent TEXT,
  ip_address INET,

  -- Properties
  properties JSONB DEFAULT '{}',

  -- Timestamp
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_events_user ON events(user_id, created_at DESC);
CREATE INDEX idx_events_name ON events(event_name, created_at DESC);
CREATE INDEX idx_events_created ON events(created_at DESC);

-- Partition by month for performance
CREATE TABLE events_2025_04 PARTITION OF events
  FOR VALUES FROM ('2025-04-01') TO ('2025-05-01');

Scaling Strategies by User Count

Phase 1: 0-10K Users (Single Database)

Setup:

• •Single PostgreSQL instance
• •No read replicas needed
• •Basic indexes only

Cost: $25-50/month Performance target: All queries <100ms

Phase 2: 10K-100K Users (Add Caching)

Setup:

• •Add Redis for session storage and hot data
• •Implement query result caching
• •Add connection pooling (PgBouncer)

import Redis from "ioredis";

const redis = new Redis(process.env.REDIS_URL);

// Cache listing details (1 hour)
async function getListingWithCache(id: string) {
  const cacheKey = `listing:${id}`;

  // Try cache first
  const cached = await redis.get(cacheKey);
  if (cached) return JSON.parse(cached);

  // Cache miss, query database
  const listing = await db.listing.findUnique({
    where: { id },
    include: {
      seller: true,
      category: true,
      reviews: {
        take: 5,
        orderBy: { createdAt: "desc" },
      },
    },
  });

  // Cache for 1 hour
  await redis.setex(cacheKey, 3600, JSON.stringify(listing));

  return listing;
}

// Invalidate cache on update
async function updateListing(id: string, data: any) {
  await db.listing.update({ where: { id }, data });
  await redis.del(`listing:${id}`);
}

Cost: $100-200/month Performance: Cached queries <10ms, DB queries <50ms

Phase 3: 100K-500K Users (Read Replicas)

Setup:

• •Primary database for writes
• •1-2 read replicas for queries
• •Route read queries to replicas

import { PrismaClient } from "@prisma/client";

// Write to primary
const dbPrimary = new PrismaClient({
  datasources: { db: { url: process.env.DATABASE_URL } },
});

// Read from replica
const dbReplica = new PrismaClient({
  datasources: { db: { url: process.env.DATABASE_REPLICA_URL } },
});

// Read queries use replica
async function getListings(filters: any) {
  return await dbReplica.listing.findMany({
    where: filters,
    include: { seller: true },
  });
}

// Writes use primary
async function createListing(data: any) {
  return await dbPrimary.listing.create({ data });
}

Cost: $300-500/month Performance: Write queries <100ms, read queries <30ms

Phase 4: 500K-1M+ Users (Sharding)

Setup:

• •Shard by user_id or geography
• •Separate hot data (active listings) from cold data (archived)
• •Consider microservices for different domains

-- Shard 1: Users A-M
CREATE DATABASE marketplace_shard_1;

-- Shard 2: Users N-Z
CREATE DATABASE marketplace_shard_2;

-- Router logic
function getShardForUser(userId: string) {
  const firstChar = userId.charAt(0).toLowerCase()
  return firstChar <= 'm' ? 'shard_1' : 'shard_2'
}

Cost: $1,000-2,000/month Performance: Maintained at <100ms for most queries

Common Mistakes and Solutions

Mistake 1: No Indexes on Foreign Keys

Problem: Every JOIN becomes a full table scan.

-- ❌ Bad: No index
CREATE TABLE listings (
  seller_id UUID REFERENCES users(id)
);

-- Query scans entire table
SELECT * FROM listings WHERE seller_id = '...';

-- ✅ Good: Indexed foreign key
CREATE INDEX idx_listings_seller ON listings(seller_id);

Impact: 100x slower queries at 10K+ rows.

Mistake 2: Using DECIMAL for Money

Problem: Precision loss and limited range.

-- ❌ Bad: Loses precision, limited range
price DECIMAL(10,2) -- $999,999,999.99 max, rounding errors

-- ✅ Good: No rounding, bigger range
price_cents BIGINT -- $92,233,720,368,547,758.07 max, exact

Mistake 3: Not Using Partial Indexes

Problem: Indexes on mostly-null columns waste space.

-- ❌ Bad: Indexes all rows
CREATE INDEX idx_listings_featured ON listings(featured);

-- ✅ Good: Only indexes featured listings
CREATE INDEX idx_listings_featured ON listings(featured, featured_until)
WHERE featured = true;

Impact: 90% smaller index, 10x faster queries.

Mistake 4: N+1 Query Problems

Problem: Loading related data in loops.

// ❌ Bad: N+1 queries (1 + 100)
const listings = await db.listing.findMany({ take: 100 });
for (const listing of listings) {
  listing.seller = await db.user.findUnique({
    where: { id: listing.sellerId },
  });
}

// ✅ Good: Single query with join
const listings = await db.listing.findMany({
  take: 100,
  include: { seller: true },
});

Mistake 5: Not Using Transactions for Multi-Step Operations

Problem: Data inconsistency when operations fail midway.

// ❌ Bad: No transaction
await db.listing.update({
  where: { id },
  data: { status: "sold" },
});
await db.transaction.create({ data: transactionData });
// If this fails, listing is marked sold but no transaction exists!

// ✅ Good: Atomic transaction
await db.$transaction(async (tx) => {
  await tx.listing.update({
    where: { id },
    data: { status: "sold" },
  });
  await tx.transaction.create({ data: transactionData });
});

Monitoring & Optimization

Query Performance Monitoring

-- Enable query logging
ALTER DATABASE marketplace SET log_min_duration_statement = 1000; -- Log queries >1s

-- Find slow queries
SELECT
  query,
  calls,
  total_time,
  mean_time,
  max_time
FROM pg_stat_statements
ORDER BY mean_time DESC
LIMIT 20;

-- Find missing indexes
SELECT
  schemaname,
  tablename,
  attname,
  n_distinct,
  correlation
FROM pg_stats
WHERE schemaname = 'public'
  AND n_distinct > 100
ORDER BY n_distinct DESC;

Database Size Monitoring

-- Table sizes
SELECT
  schemaname,
  tablename,
  pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size,
  pg_total_relation_size(schemaname||'.'||tablename) AS size_bytes
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY size_bytes DESC;

-- Index usage
SELECT
  schemaname,
  tablename,
  indexname,
  idx_scan,
  idx_tup_read,
  idx_tup_fetch,
  pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
ORDER BY idx_scan ASC;

Implementation Roadmap

Week 1-2: Design schema with flexibility (JSONB) and performance (indexes) from day one Month 1-6: Single database, basic caching Month 6-12: Add Redis, read replicas Year 2+: Shard by domain, microservices for complex features

Next Steps

1. •Schema audit: Review your current schema against these patterns
2. •Index analysis: Identify missing indexes on foreign keys and frequently queried columns
3. •JSONB migration: Convert rigid category-specific columns to JSONB attributes
4. •Caching strategy: Implement Redis caching for hot data paths
5. •Monitoring setup: Enable query logging and performance tracking

Related Resources

• •API Design Best Practices - REST API patterns for marketplace backends
• •Scaling Marketplace Infrastructure - Performance optimization and infrastructure scaling
• •Performance Optimization Checklist - Database query optimization techniques