PostgreSQL storage for scraped data with versioning and search

Engineering approach to scraped data storage: PostgreSQL, versioning, JSONB

You've scraped 10,000 listings from Avito, and a week later half have changed. CSV quickly turns into mush — changes are untraceable. MongoDB without a strict schema is just a delayed disaster: data gets dirty over time. You need a system that remembers every change and lets you find what you need in milliseconds. We build such solutions — on PostgreSQL with versioning, full-text search, and a REST API. Our schema has been running in production for decades. PostgreSQL documentation recommends GIN indexes for JSONB, providing speed and flexibility. Let's dive into a concrete implementation. The main table scraped_items holds current data, and scraped_items_history stores the change archive. This approach ensures a full audit trail without sacrificing performance.

Storage schema in PostgreSQL

-- Main table with change history CREATE TABLE scraped_items ( id BIGSERIAL PRIMARY KEY, source_id INTEGER REFERENCES sources(id), external_id TEXT NOT NULL, -- ID at the source url TEXT NOT NULL, data JSONB NOT NULL, -- flexible schema for different sources data_hash CHAR(64) NOT NULL, -- SHA-256 of data for change detection first_seen TIMESTAMPTZ DEFAULT NOW(), last_seen TIMESTAMPTZ DEFAULT NOW(), changed_at TIMESTAMPTZ, UNIQUE (source_id, external_id) ); -- Change history CREATE TABLE scraped_items_history ( id BIGSERIAL PRIMARY KEY, item_id BIGINT REFERENCES scraped_items(id), data JSONB NOT NULL, recorded_at TIMESTAMPTZ DEFAULT NOW() ); -- Indexes CREATE INDEX ON scraped_items USING GIN (data); -- search in JSONB CREATE INDEX ON scraped_items (source_id, last_seen); CREATE INDEX ON scraped_items USING GIN ( to_tsvector('russian', data->>'title' || ' ' || COALESCE(data->>'description', '')) ); 

Key decisions: using JSONB for variable schemas, separate history table, data hash for fast change detection. This schema ensures ACID transactions and integrity under parallel load.

Update logic

def upsert_item(source_id, external_id, url, data): data_hash = hashlib.sha256( json.dumps(data, sort_keys=True).encode() ).hexdigest() existing = db.query( 'SELECT id, data_hash FROM scraped_items WHERE source_id=%s AND external_id=%s', (source_id, external_id) ).fetchone() if existing is None: # new item db.execute( 'INSERT INTO scraped_items (source_id, external_id, url, data, data_hash) ' 'VALUES (%s, %s, %s, %s, %s)', (source_id, external_id, url, json.dumps(data), data_hash) ) elif existing['data_hash'] != data_hash: # data changed — save history db.execute( 'INSERT INTO scraped_items_history (item_id, data) ' 'SELECT id, data FROM scraped_items WHERE id=%s', (existing['id'],) ) db.execute( 'UPDATE scraped_items SET data=%s, data_hash=%s, last_seen=NOW(), changed_at=NOW() ' 'WHERE id=%s', (json.dumps(data), data_hash, existing['id']) ) else: # data unchanged — update only last_seen db.execute( 'UPDATE scraped_items SET last_seen=NOW() WHERE id=%s', (existing['id'],) ) 

The upsert_item function handles three scenarios: insert new object, update with history preservation (if hash changed), and just update last_seen timestamp with no changes. This minimizes I/O and speeds up processing. Under a load of 100,000 records per day, the entire pipeline completes in under 15 minutes.

Why JSONB instead of separate columns?

Scraped data often has an unstable structure: today a product has weight, tomorrow a color. JSONB eliminates migration issues and allows indexing any field via a GIN index. We use a hybrid approach: key fields are extracted to columns for fast filters, the rest stays in JSONB. This gives the speed of a relational model and the flexibility of a document-oriented one. In practice, JSONB in PostgreSQL is 3x faster than MongoDB for queries on structured fields.

How do we detect changes without losing performance?

We use SHA-256 of serialized JSON. The hash is compared to the stored one on each upsert. This check runs in O(1) and does not require reading the entire row. For large volumes (millions of records), we apply partitioning by source_id.

Comparison of storage approaches

Processing pipeline: stages and tools

Implementation process

1. Source analysis — determine data structure and update frequency.
2. Schema design — choose indexes, configure partitioning for large volumes.
3. Develop upsert logic — write function with change detection via hash.
4. Processing pipeline — normalization, enrichment, aggregation.
5. API and export — REST endpoints with pagination, plus CSV/XLSX download.
6. Monitoring and archiving — TTL policy, failure notifications.

What's included

• Designed database schema with migrations
• GitHub repository with code (upsert, pipeline, API)
• API documentation (OpenAPI/Swagger)
• Deployment instructions (Docker Compose)
• Guarantee of correctness during acceptance

Archiving and TTL

Old data (not seen for more than 90 days) is moved to archive or deleted, depending on requirements. Change history is retained longer than main data — by default 365 days. Everything is configurable for your business case.

Export

• CSV/XLSX — via pandas.to_excel() or csv.DictWriter
• REST API — FastAPI/Laravel with filtering, pagination, sorting
• Webhook — real-time push of new/changed records to an external system

Implementation time for the storage system with change history and API is 4–6 days. We guarantee correct operation under load up to 100k records per day. If you need reliable scraped data storage — contact us to discuss the schema. Our experience includes dozens of deployed systems. Get a consultation — we'll help you choose the optimal schema.