Storage Strategies and Data Persistence: Designing for Scale

Home / Web crawling / Storage Strategies and Data Persistence: Designing for Scale

Introduction

Once you’ve extracted clean data from websites, you need somewhere reliable to store it. This is where many crawling projects falter—insufficient consideration for scale, consistency, and maintainability.

In this post, we’ll explore:

Choosing storage systems (relational, document, time-series)
Designing schemas for crawled data
Handling duplicates and updates
Data validation during persistence
Scaling strategies for millions of records
Managing data retention and cleanup

By the end, you’ll design storage systems that can handle growth without constant refactoring.

Storage System Options

Relational Databases (PostgreSQL, MySQL)

Best for: Structured, normalized data with relationships

Advantages:

✅ ACID guarantees (Atomicity, Consistency, Isolation, Durability)
✅ Strong schema enforcement
✅ Mature query language (SQL)
✅ Excellent for relationships
✅ Cost-effective at scale
✅ Can handle billions of rows

Disadvantages:

❌ Schema design requires planning
❌ Less flexible for semi-structured data
❌ Migration overhead for schema changes
❌ Slower for high-velocity writes without tuning

Example: PostgreSQL Setup

import { Pool } from 'pg';

interface Article {
  id: string;
  slug: string;
  title: string;
  content: string;
  author_id: string;
  published_at: Date;
  scraped_at: Date;
  source_url: string;
}

// Initialize connection pool
const pool = new Pool({
  host: 'localhost',
  port: 5432,
  database: 'crawling_db',
  user: 'crawler',
  password: process.env.DB_PASSWORD,
  max: 20  // Connection pool size
});

// Create table
await pool.query(`
  CREATE TABLE articles (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    slug TEXT UNIQUE NOT NULL,
    title TEXT NOT NULL,
    content TEXT NOT NULL,
    author_id UUID NOT NULL,
    published_at TIMESTAMP WITH TIME ZONE,
    scraped_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    source_url TEXT NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    CONSTRAINT fk_author FOREIGN KEY (author_id) 
      REFERENCES authors(id) ON DELETE CASCADE
  );
  
  CREATE INDEX idx_articles_slug ON articles(slug);
  CREATE INDEX idx_articles_author ON articles(author_id);
  CREATE INDEX idx_articles_published ON articles(published_at DESC);
`);

// Insert or update (upsert)
const upsertArticle = async (article: Article) => {
  const query = `
    INSERT INTO articles (id, slug, title, content, author_id, published_at, source_url)
    VALUES ($1, $2, $3, $4, $5, $6, $7)
    ON CONFLICT (slug) 
    DO UPDATE SET 
      title = EXCLUDED.title,
      content = EXCLUDED.content,
      updated_at = NOW()
    RETURNING *;
  `;
  
  const result = await pool.query(query, [
    article.id,
    article.slug,
    article.title,
    article.content,
    article.author_id,
    article.published_at,
    article.source_url
  ]);
  
  return result.rows[0];
};

// Batch insert for performance
const batchInsert = async (articles: Article[]) => {
  const client = await pool.connect();
  
  try {
    await client.query('BEGIN');
    
    for (const article of articles) {
      await client.query(
        `INSERT INTO articles (id, slug, title, content, author_id, published_at, source_url)
         VALUES ($1, $2, $3, $4, $5, $6, $7)
         ON CONFLICT (slug) DO NOTHING`,
        [article.id, article.slug, article.title, article.content, 
         article.author_id, article.published_at, article.source_url]
      );
    }
    
    await client.query('COMMIT');
  } catch (error) {
    await client.query('ROLLBACK');
    throw error;
  } finally {
    client.release();
  }
};

Document Databases (MongoDB, Firebase)

Best for: Semi-structured, nested data; rapid schema evolution

Advantages:

✅ Flexible schema (great for varied content)
✅ Native nested objects/arrays
✅ Painless schema evolution
✅ Good for rapid prototyping
✅ Horizontal scaling (sharding)

Disadvantages:

❌ No ACID transactions (MongoDB 4.0+ added multi-doc)
❌ Higher storage overhead
❌ Slower complex queries
❌ Harder for strict consistency

Example: MongoDB Setup

import { MongoClient, Db, Collection } from 'mongodb';

interface Article {
  _id?: string;
  slug: string;
  title: string;
  content: string;
  author: {
    id: string;
    name: string;
  };
  tags: string[];
  metadata: {
    publishedAt: Date;
    sourceUrl: string;
    scrapedAt: Date;
  };
}

// Initialize connection
const client = new MongoClient(process.env.MONGODB_URI || 'mongodb://localhost:27017');
let db: Db;
let articles: Collection<Article>;

const connect = async () => {
  await client.connect();
  db = client.db('crawling_db');
  articles = db.collection('articles');
  
  // Create indexes
  await articles.createIndex({ slug: 1 }, { unique: true });
  await articles.createIndex({ 'author.id': 1 });
  await articles.createIndex({ 'metadata.publishedAt': -1 });
};

// Insert with validation
const createArticle = async (article: Article) => {
  const result = await articles.insertOne(article);
  return result;
};

// Update or insert
const upsertArticle = async (article: Article) => {
  const result = await articles.updateOne(
    { slug: article.slug },
    { $set: article },
    { upsert: true }
  );
  return result;
};

// Batch operations
const bulkInsert = async (articles: Article[]) => {
  const operations = articles.map(article => ({
    updateOne: {
      filter: { slug: article.slug },
      update: { $set: article },
      upsert: true
    }
  }));
  
  const result = await articles.bulkWrite(operations);
  return result;
};

// Query with aggregation
const getArticlesByAuthor = async (authorId: string) => {
  return articles
    .aggregate([
      { $match: { 'author.id': authorId } },
      { $sort: { 'metadata.publishedAt': -1 } },
      { $limit: 50 }
    ])
    .toArray();
};

Time-Series Databases (InfluxDB, TimescaleDB)

Best for: Metrics, historical data, time-based queries

Advantages:

✅ Optimized for time-ordered data
✅ Efficient compression
✅ Fast time-range queries
✅ Built-in retention policies
✅ Aggregation functions

Disadvantages:

❌ Overkill for structured content
❌ Limited for arbitrary queries
❌ Specialized use case

Example: TimescaleDB (PostgreSQL extension)

import { Pool } from 'pg';

interface ScrapingMetric {
  time: Date;
  source: string;
  success_count: number;
  failure_count: number;
  avg_response_time_ms: number;
}

const pool = new Pool({
  connectionString: process.env.DATABASE_URL
});

// Enable TimescaleDB extension
await pool.query('CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE');

// Create hypertable
await pool.query(`
  CREATE TABLE scraping_metrics (
    time TIMESTAMP WITH TIME ZONE NOT NULL,
    source TEXT NOT NULL,
    success_count INTEGER NOT NULL,
    failure_count INTEGER NOT NULL,
    avg_response_time_ms FLOAT NOT NULL
  );
  
  SELECT create_hypertable(
    'scraping_metrics',
    'time',
    if_not_exists => TRUE
  );
  
  CREATE INDEX idx_metrics_source_time 
    ON scraping_metrics (source, time DESC);
`);

// Insert metrics
const recordMetric = async (metric: ScrapingMetric) => {
  const query = `
    INSERT INTO scraping_metrics 
      (time, source, success_count, failure_count, avg_response_time_ms)
    VALUES ($1, $2, $3, $4, $5);
  `;
  
  await pool.query(query, [
    metric.time,
    metric.source,
    metric.success_count,
    metric.failure_count,
    metric.avg_response_time_ms
  ]);
};

// Time-range query with aggregation
const getMetricsForRange = async (source: string, hours: number = 24) => {
  const query = `
    SELECT 
      time_bucket('1 hour', time) as hour,
      source,
      sum(success_count) as total_success,
      sum(failure_count) as total_failure,
      avg(avg_response_time_ms) as avg_response_time
    FROM scraping_metrics
    WHERE source = $1 
      AND time > NOW() - INTERVAL '${hours} hours'
    GROUP BY hour, source
    ORDER BY hour DESC;
  `;
  
  const result = await pool.query(query, [source]);
  return result.rows;
};

Schema Design Patterns

Normalized Relational Schema

// Authors table (1 author : many articles)
const createAuthorsTable = `
  CREATE TABLE authors (
    id UUID PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE,
    bio TEXT,
    url TEXT,
    created_at TIMESTAMP DEFAULT NOW()
  );
`;

// Tags table (many articles : many tags)
const createTagsTable = `
  CREATE TABLE tags (
    id UUID PRIMARY KEY,
    name TEXT UNIQUE NOT NULL,
    slug TEXT UNIQUE NOT NULL
  );
`;

// Articles table (normalized)
const createArticlesTable = `
  CREATE TABLE articles (
    id UUID PRIMARY KEY,
    author_id UUID NOT NULL REFERENCES authors(id),
    slug TEXT UNIQUE NOT NULL,
    title TEXT NOT NULL,
    content TEXT NOT NULL,
    excerpt TEXT,
    published_at TIMESTAMP,
    source_url TEXT NOT NULL UNIQUE,
    scraped_at TIMESTAMP NOT NULL DEFAULT NOW(),
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
  );
`;

// Article-tag mapping (junction table)
const createArticleTagsTable = `
  CREATE TABLE article_tags (
    article_id UUID NOT NULL REFERENCES articles(id) ON DELETE CASCADE,
    tag_id UUID NOT NULL REFERENCES tags(id),
    PRIMARY KEY (article_id, tag_id)
  );
`;

// Indexes for performance
const createIndexes = `
  CREATE INDEX idx_articles_author_id ON articles(author_id);
  CREATE INDEX idx_articles_published_at ON articles(published_at DESC);
  CREATE INDEX idx_articles_slug ON articles(slug);
  CREATE INDEX idx_articles_source_url ON articles(source_url);
  CREATE INDEX idx_article_tags_tag_id ON article_tags(tag_id);
`;

Denormalized Document Schema

interface ArticleDocument {
  _id: string;  // Unique identifier
  
  // Core content
  title: string;
  slug: string;
  content: string;
  excerpt: string;
  
  // Author (denormalized for speed)
  author: {
    id: string;
    name: string;
    email: string;
  };
  
  // Tags (embedded array)
  tags: Array<{
    id: string;
    name: string;
    slug: string;
  }>;
  
  // Metadata
  metadata: {
    publishedAt: Date | null;
    sourceUrl: string;
    scrapedAt: Date;
    updatedAt: Date;
  };
  
  // SEO/Technical
  seo: {
    metaDescription?: string;
    keywords?: string[];
    canonicalUrl?: string;
  };
  
  // Audit trail
  audit: {
    createdAt: Date;
    versions: Array<{
      timestamp: Date;
      changes: string;
    }>;
  };
}

// Store with denormalization
const docSchema: ArticleDocument = {
  _id: '550e8400-e29b-41d4-a716-446655440000',
  title: 'Article Title',
  slug: 'article-title',
  content: '...',
  excerpt: '...',
  author: {
    id: '1234',
    name: 'Jane Doe',
    email: 'jane@example.com'
  },
  tags: [
    { id: 'tag1', name: 'web', slug: 'web' },
    { id: 'tag2', name: 'scraping', slug: 'scraping' }
  ],
  metadata: {
    publishedAt: new Date('2026-02-24'),
    sourceUrl: 'https://example.com/article',
    scrapedAt: new Date(),
    updatedAt: new Date()
  },
  seo: {
    metaDescription: 'Article about web scraping',
    keywords: ['web', 'scraping', 'data'],
    canonicalUrl: 'https://example.com/article'
  },
  audit: {
    createdAt: new Date(),
    versions: []
  }
};

Data Validation and Quality

Schema Validation at Storage Time

import { z } from 'zod';

// Define validation schema
const ArticleSchema = z.object({
  slug: z.string().min(1).max(255),
  title: z.string().min(1).max(500),
  content: z.string().min(10),
  author_id: z.string().uuid(),
  published_at: z.date().nullable(),
  source_url: z.string().url()
});

type Article = z.infer<typeof ArticleSchema>;

// Validate before storage
const saveArticle = async (data: unknown): Promise<Article> => {
  const validated = ArticleSchema.parse(data);
  
  // Store validated data
  await pool.query(
    'INSERT INTO articles (...) VALUES (...)',
    [validated.slug, validated.title, /* ... */]
  );
  
  return validated;
};

// Handle validation errors
try {
  await saveArticle(untrustedData);
} catch (error) {
  if (error instanceof z.ZodError) {
    console.error('Validation failed:', error.issues);
    // Log invalid record, skip or notify
  }
}

Duplicate Detection

// Strategy 1: Unique constraint on source
// If source_url is unique, upsert handles duplicates
const upsertArticle = async (article: Article) => {
  await pool.query(`
    INSERT INTO articles (source_url, title, content, ...)
    VALUES ($1, $2, $3, ...)
    ON CONFLICT (source_url) 
    DO UPDATE SET 
      title = EXCLUDED.title,
      content = EXCLUDED.content,
      updated_at = NOW()
  `, [article.sourceUrl, article.title, article.content]);
};

// Strategy 2: Hash-based deduplication
import { createHash } from 'crypto';

const hashContent = (content: string): string => {
  return createHash('sha256').update(content).digest('hex');
};

const isDuplicate = async (contentHash: string): Promise<boolean> => {
  const result = await pool.query(
    'SELECT 1 FROM articles WHERE content_hash = $1 LIMIT 1',
    [contentHash]
  );
  return result.rows.length > 0;
};

const saveWithDedup = async (article: Article) => {
  const hash = hashContent(article.content);
  
  if (await isDuplicate(hash)) {
    console.log('Duplicate content, skipping');
    return;
  }
  
  await pool.query(
    'INSERT INTO articles (content_hash, ...) VALUES ($1, ...)',
    [hash, /* ... */]
  );
};

// Strategy 3: Timestamp-based checks
const getLastScrapedTime = async (sourceUrl: string): Promise<Date | null> => {
  const result = await pool.query(
    'SELECT scraped_at FROM articles WHERE source_url = $1 ORDER BY scraped_at DESC LIMIT 1',
    [sourceUrl]
  );
  return result.rows[0]?.scraped_at ?? null;
};

const shouldRefresh = (lastScraped: Date | null, minInterval: number = 86400000): boolean => {
  if (!lastScraped) return true;
  return Date.now() - lastScraped.getTime() > minInterval;
};

Scaling Strategies

Batch Inserts for Performance

// Insert millions efficiently
const batchInsertLarge = async (
  articles: Article[],
  batchSize: number = 1000
) => {
  const batches = [];
  
  for (let i = 0; i < articles.length; i += batchSize) {
    batches.push(articles.slice(i, i + batchSize));
  }
  
  console.log(`Inserting ${articles.length} articles in ${batches.length} batches`);
  
  let inserted = 0;
  for (const batch of batches) {
    const client = await pool.connect();
    
    try {
      await client.query('BEGIN');
      
      for (const article of batch) {
        await client.query(
          `INSERT INTO articles (...) VALUES (...)
           ON CONFLICT (slug) DO UPDATE SET ...`,
          [/* ... */]
        );
      }
      
      await client.query('COMMIT');
      inserted += batch.length;
      console.log(`Inserted ${inserted}/${articles.length}`);
    } catch (error) {
      await client.query('ROLLBACK');
      console.error(`Batch failed:`, error);
    } finally {
      client.release();
    }
  }
};

Partitioning by Time

// For large time-series tables, partition by month
const createPartitionedTables = `
  CREATE TABLE articles (
    id UUID,
    title TEXT,
    scraped_at TIMESTAMP,
    content TEXT,
    PRIMARY KEY (id, scraped_at)
  ) PARTITION BY RANGE (scraped_at);
  
  CREATE TABLE articles_2026_01 PARTITION OF articles
    FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
  
  CREATE TABLE articles_2026_02 PARTITION OF articles
    FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');
`;

// Cleanup old partitions
const dropOldPartition = `
  ALTER TABLE articles DETACH PARTITION articles_2025_01;
  DROP TABLE articles_2025_01;
`;

Data Retention Policies

// Automatically delete old data
const setupRetention = async (
  daysToKeep: number = 90
) => {
  await pool.query(`
    CREATE OR REPLACE FUNCTION cleanup_old_articles()
    RETURNS void AS $$
    BEGIN
      DELETE FROM articles
      WHERE scraped_at < NOW() - INTERVAL '${daysToKeep} days';
    END;
    $$ LANGUAGE plpgsql;
    
    -- Run daily
    SELECT cron.schedule(
      'cleanup-articles',
      '0 2 * * *',  -- 2 AM daily
      'SELECT cleanup_old_articles()'
    );
  `);
};

// Or soft delete (mark as deleted)
const setupSoftDelete = async () => {
  await pool.query(`
    ALTER TABLE articles ADD COLUMN deleted_at TIMESTAMP;
    
    CREATE INDEX idx_articles_deleted 
      ON articles(deleted_at) 
      WHERE deleted_at IS NULL;
  `);
  
  // Query only active records
  const getActive = async () => {
    return pool.query(
      'SELECT * FROM articles WHERE deleted_at IS NULL'
    );
  };
};

Monitoring and Performance

// Monitor insert performance
const monitorInsertPerformance = async (articles: Article[]) => {
  const startTime = performance.now();
  const startMemory = process.memoryUsage().heapUsed;
  
  await batchInsert(articles);
  
  const endTime = performance.now();
  const endMemory = process.memoryUsage().heapUsed;
  
  const metrics = {
    recordsInserted: articles.length,
    durationMs: endTime - startTime,
    recordsPerSecond: articles.length / ((endTime - startTime) / 1000),
    memoryDeltaMb: (endMemory - startMemory) / 1024 / 1024
  };
  
  console.log('Insert Performance:', metrics);
  return metrics;
};

// Database health checks
const healthCheck = async (): Promise<{ healthy: boolean; message: string }> => {
  try {
    const result = await pool.query('SELECT 1');
    const tableCount = await pool.query(
      "SELECT count(*) FROM information_schema.tables WHERE table_schema='public'"
    );
    
    return {
      healthy: result.rows.length > 0,
      message: `Connected. ${tableCount.rows[0].count} tables.`
    };
  } catch (error) {
    return {
      healthy: false,
      message: `Connection failed: ${error}`
    };
  }
};

Best Practices Summary

Choose the right database for your data shape and query patterns
Design for scale from the start (indexing, partitioning)
Validate early before storage to maintain data quality
Handle duplicates proactively with unique constraints or deduplication
Batch operations for insert performance
Monitor performance and adjust schema as needed
Plan retention policies to manage storage growth

Discussion Questions

What storage system have you found most reliable for crawled data?
How do you balance schema flexibility with data quality?
What’s your approach to handling duplicate data?

Next Steps

Now that your data is stored reliably, you’re ready for Part 5: Data Analysis and Insights, where we’ll extract meaningful patterns and insights from the data you’ve collected.

Part of the Web Crawling and Data Analysis Series

Previous: Part 3: Data Extraction Techniques
Next: Part 5: Data Analysis and Insights
Series Overview: Web Crawling and Data Analysis Series

Further Resources:

You also might like

About this post

This post was written by a human with AI assistance, which was used for research, outlining, editing and code suggestions. Claims, code samples and references were reviewed before publication, and the sources are cited so you can judge them yourself.