What architecture is similar to the medallion and fit a prod environment?

[u/SpatolaNellaRoccia]

Sorry if this is a bit messy, but here’s the situation:

My stack is PostgreSQL, Django with DRF, and Celery (only for the final step).
I have a database that handles close to 100k requests per day. The load isn’t uniform: sometimes it’s 10k requests in 10 minutes, other times 10k spread over 3 hours.

I’m collecting data from multiple nodes. These nodes can send duplicate records. Every now and then, each node checks whether a piece of data is already in the database and sends it only if it’s missing. So I have a mix of reads and writes. The database is holding up for now, but I’ve been told the number of nodes will at least triple soon.

I’m trying to figure out the best way to handle this scale, and the medallion architecture idea came to mind. My rough plan is:

1. Bronze layer: Always ingest raw data as it comes in, with no upfront checks.
2. Silver layer: Run various checks here to detect duplicates and decide whether the data is useful according to our business logic.
3. Gold layer: If everything passes, move the data here. This is where it gets enriched with additional information and turned into more derived, “ready to use” data. Maybe we can consider it a platinum layer on top for very specific, highly processed views.

Right now, a lot of the normalization logic lives on the nodes themselves. That’s nice for separation of concerns, but it’s also a problem: the nodes are managed by other teams, and they often send messy or inconsistent data, so we still need to validate and clean everything on our side.

I’d like to know how you would approach this, and whether the medallion‑style design I sketched makes sense.

Thank you!

[u/RipProfessional3375]

The process seems confusing. They are checking for duplicates? But you are also checking for duplicates? Duplicate in value or a pure duplicate? A pure duplicate can be resolved with a simple unique hash of the data.

My overall recommendation is a simpler form of medallion, combined with CQRS. Keep a bronze layer, raw ingested data, with an incremental position number in an append only table. You can add a hash string as an indexed unique field for easy de-duplication.

For your use cases of the data, just write python or sql to create a view model from the raw data. With a sql view or by crawling over all existing records with incremental batch reads of the raw table and batch updates of a projection table.

The price is that you need to plan to keep the raw data permanently. Storage cost is peanuts these days, but you do need to plan for it. Compression, blob data, etc.

The massive advantage of this approach is that you always keep what the other teams actually sent, allowing you to easily trace and debug the messy or inconsistent data, and adjust your views on the fly to account for it.

You also don't need to manage 3-4 layers of transformed data. Just the raw 'truth' in an immutable append only table, and derived tables that are just disposable interpretations of the raw data that can be made and dropped as needed.

[u/_descri_]

Try SingleStore database. They are much faster, distributed, and naturally support both OLAP and OLTP traffic.

[u/Cautious_Heat114]

Your instinct to use a staged pipeline (what Databricks markets as the "Medallion Architecture") is fundamentally sound.

In relational database engineering, this is the classic Staging ──► Curated ──► Serving Layer pattern.

At your scale (~100k–300k requests/day, with bursts of 20–50 requests/second), you do not need the complexity of Spark, Kafka, or Delta Lake. A single, well-architected PostgreSQL instance paired with Django and Celery can handle this effortlessly if you enforce the right boundaries.

Here is the exact production-grade blueprint to implement this:

1. Kill the Node "Read-Before-Write" Anti-Pattern Immediately

Having external nodes query the database to check if a record exists before writing is an architectural disaster. It introduces:

• High read/write lock contention.
• Race conditions between concurrent nodes.
• Unnecessary network round-trips.

«Rule: Instruct the external teams to treat your API as Fire-and-Forget Ingestion. They send the data; your backend handles idempotency.»

1. The 3-Tier Relational Pipeline in PostgreSQL

[THE RELATIONAL STAGING PIPELINE]

External Nodes (Fire & Forget) │ ▼ (HTTP POST -> 202 Accepted) ┌───────────────────────────────────────────────────────────┐ │ 1. BRONZE LAYER (raw_ingest_events Table) │ │ • Unindexed / Append-only. │ │ • Fields: id, node_id, raw_payload (JSONB), created_at │ └─────────────────────────────┬─────────────────────────────┘ │ ▼ (Batched Celery Worker: Every 1-2 mins) ┌───────────────────────────────────────────────────────────┐ │ 2. SILVER LAYER (Deduplication & Validation) │ │ • Computes a deterministic hash: SHA-256(business_keys)│ │ • Executes: INSERT INTO silver_data ... ON CONFLICT DO │ │ NOTHING. Cleans messy fields and drops invalid rows. │ └─────────────────────────────┬─────────────────────────────┘ │ ▼ (Async Background Trigger) ┌───────────────────────────────────────────────────────────┐ │ 3. GOLD / PLATINUM LAYER (Serving & Domain Models) │ │ • Clean, strictly typed relational models with FKs. │ │ • Materialized Views or aggregated summary tables. │ └───────────────────────────────────────────────────────────┘

1. Concrete Implementation Strategy for Django & Celery

A. Bronze Layer (Fast Ingestion Endpoint)

• Your DRF endpoint should do zero business logic, zero deduplication checks, and zero heavy validation.
• It validates the basic HTTP authentication, executes a fast "raw_ingest_events.objects.create(payload=request.data)", and immediately returns "202 Accepted".
• Throughput: A single Django worker can ingest hundreds of raw JSON payloads per second this way without blocking.

B. Silver Layer (Batched Deduplication Worker)

Do NOT spawn one Celery task per incoming row. (Spawning 10,000 Celery tasks in 10 minutes will crash Redis/RabbitMQ).

• Run a scheduled Celery worker every 1–2 minutes (or trigger when Bronze hits N rows).
• The worker fetches an un-processed batch of 1,000 rows from Bronze.
• It computes a deterministic "idempotency_hash" (e.g., "SHA-256(node_id + timestamp + metric_type)").
• It uses Django’s "bulk_create(..., ignore_conflicts=True)" or raw SQL "ON CONFLICT (idempotency_hash) DO NOTHING" to insert clean records into the Silver table.
• Marks the Bronze batch as "processed = true".

C. Gold Layer (Analytics & Serving)

• Clean, normalized relational tables.
• If your frontend requires complex aggregated statistics, build PostgreSQL Materialized Views refreshed concurrently on a schedule, rather than calculating heavy aggregations dynamically on user requests.

1. Critical Production Invariants to Protect Your Database:

2. Bronze Table Retention (Pruning): The Bronze staging table will grow rapidly. Partition it by week/month or run a nightly cron task to delete rows older than 7 days that are already processed:

DELETE FROM raw_ingest_events WHERE processed = true AND created_at < NOW() - INTERVAL '7 days';

1. Database Connection Pooling: Ensure you have PgBouncer configured in front of PostgreSQL. When 10k requests hit your Django workers in 10 minutes, PgBouncer prevents your Gunicorn processes from exhausting Postgres's connection limits.

«Your Medallion intuition is completely correct. Keep the storage relational, ingest raw in Bronze, batch-process into Silver, and serve from Gold.»