PostgreSQL for Web Scraping Storage: Schema, Bulk Insert, Dedup 2026

Contains affiliate links. Disclosure.

Store scraped data in PostgreSQL with a clean schema, bulk inserts via COPY or executemany, deduplication with ON CONFLICT, and full-text search. Use pgBouncer for connection pooling. Apify provides managed datasets if you prefer no DB ops.

Schema Design for Scraped Data

CREATE TABLE scraped_pages (
    id          BIGSERIAL PRIMARY KEY,
    url         TEXT NOT NULL UNIQUE,
    title       TEXT,
    content     TEXT,
    metadata    JSONB,
    scraped_at  TIMESTAMPTZ DEFAULT NOW(),
    status      VARCHAR(20) DEFAULT 'active'
);

CREATE INDEX idx_scraped_pages_url ON scraped_pages (url);
CREATE INDEX idx_scraped_pages_scraped_at ON scraped_pages (scraped_at);
CREATE INDEX idx_scraped_pages_metadata ON scraped_pages USING GIN (metadata);

-- Full-text search
ALTER TABLE scraped_pages ADD COLUMN content_tsv tsvector
    GENERATED ALWAYS AS (to_tsvector('english', coalesce(title, '') || ' ' || coalesce(content, ''))) STORED;
CREATE INDEX idx_scraped_pages_fts ON scraped_pages USING GIN (content_tsv);

Deduplication with ON CONFLICT

Use ON CONFLICT to upsert by URL. Update existing rows when re-scraping.

INSERT INTO scraped_pages (url, title, content, metadata)
VALUES ($1, $2, $3, $4)
ON CONFLICT (url) DO UPDATE SET
    title = EXCLUDED.title,
    content = EXCLUDED.content,
    metadata = EXCLUDED.metadata,
    scraped_at = NOW();

For versioned history, use a separate scraped_versions table with (url, scraped_at).

Bulk Insert: COPY

COPY is fastest for large batches. Use COPY FROM stdin or a temp file.

import psycopg2
from io import StringIO

def bulk_insert_copy(conn, rows):
    buffer = StringIO()
    for r in rows:
        buffer.write(f"{r['url']}\t{r['title']}\t{r['content']}\t{r['metadata']}\n")
    buffer.seek(0)
    with conn.cursor() as cur:
        cur.copy_expert(
            "COPY scraped_pages (url, title, content, metadata) FROM STDIN",
            buffer
        )
    conn.commit()

For upserts with COPY, use a temp table then INSERT ... ON CONFLICT:

CREATE TEMP TABLE staging (url TEXT, title TEXT, content TEXT, metadata JSONB);
-- COPY into staging
INSERT INTO scraped_pages (url, title, content, metadata)
SELECT url, title, content, metadata FROM staging
ON CONFLICT (url) DO UPDATE SET title = EXCLUDED.title, content = EXCLUDED.content, scraped_at = NOW();

Bulk Insert: executemany

Simpler approach with psycopg2. Batch 1000–5000 rows per commit.

import psycopg2
from psycopg2.extras import execute_values

def bulk_insert_executemany(conn, rows):
    query = """
        INSERT INTO scraped_pages (url, title, content, metadata)
        VALUES %s
        ON CONFLICT (url) DO UPDATE SET
            title = EXCLUDED.title,
            content = EXCLUDED.content,
            scraped_at = NOW()
    """
    execute_values(conn.cursor(), query, [
        (r['url'], r['title'], r['content'], r.get('metadata'))
        for r in rows
    ], page_size=1000)
    conn.commit()

Connection Pooling with pgBouncer

Limit connections per scraper. Use pgBouncer in transaction mode.

# /etc/pgbouncer/pgbouncer.ini
[databases]
scraping_db = host=127.0.0.1 port=5432 dbname=scraping_db

[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = md5
pool_mode = transaction
max_client_conn = 100
default_pool_size = 20

Connect to localhost:6432 instead of 5432.

Full-Text Search Query

SELECT url, title, ts_headline('english', content, q) AS snippet
FROM scraped_pages, to_tsquery('english', 'web scraping') AS q
WHERE content_tsv @@ q
ORDER BY ts_rank(content_tsv, q) DESC
LIMIT 20;

pg_dump Backup

pg_dump -Fc -U postgres scraping_db > scraping_db_$(date +%Y%m%d).dump

See backup and recovery for scraping for full strategy.

Next step

Define schema with URL uniqueness. Use ON CONFLICT for dedup. Bulk insert with COPY or executemany. Add pgBouncer at scale. For managed storage, use Apify datasets.

Frequently Asked Questions

PostgreSQL fits most use cases: ACID, JSONB, full-text search, strong tooling. Use MongoDB for document-first or very flexible schemas.

Use a UNIQUE constraint on URL. Insert with ON CONFLICT (url) DO UPDATE to upsert. Dedupe in the queue (Redis SET) before scraping.

Yes. It handles bulk inserts, JSONB for variable fields, full-text search, and scales with indexing and connection pooling.