Writing to databases from Spark: lessons from production

Introduction

You've spent weeks building a Spark pipeline that transforms terabytes of raw data into actionable insights. Now comes the critical part: getting those results back into the systems your business actually runs on. Writing to databases from Spark: lessons from production is where many teams stumble, discovering too late that a careless write operation can lock tables, overwhelm connection pools, or corrupt data consistency. We've learned these lessons the hard way across e-commerce platforms, CRM systems, and SaaS products. This post covers the practical approaches that work when you need to reliably load Spark results into OLTP databases at scale.

Batch inserts with JDBC

The most straightforward approach is batch inserts via JDBC. Spark's DataFrame write API makes this accessible, but there are critical details that determine whether you'll succeed or create an operational incident.

When you call df.write.format("jdbc"), Spark opens connections and pushes rows to your database. The JDBC driver buffers these inserts and sends them in batches, which is more efficient than one row at a time. However, the default batch size (usually 1,000 rows) may not suit your workload, and network round-trips can become a bottleneck.

Here's a realistic example: you've processed customer events and need to insert 50 million rows into a PostgreSQL table. A naive approach looks like this:

df.write \
  .format("jdbc") \
  .mode("append") \
  .option("url", "jdbc:postgresql://prod-db:5432/analytics") \
  .option("dbtable", "customer_events") \
  .option("user", "spark_user") \
  .option("password", secret) \
  .save()

This works in a test environment with 100k rows. In production with 50 million rows, you'll hit several issues: the database fills its undo logs, the JDBC connection times out, or worse, you're holding locks that block your application team's queries.

A production-ready version tunes the batch size and connection pool:

df.write \
  .format("jdbc") \
  .mode("append") \
  .option("url", "jdbc:postgresql://prod-db:5432/analytics") \
  .option("dbtable", "customer_events") \
  .option("user", "spark_user") \
  .option("password", secret) \
  .option("batchsize", 50000) \
  .option("numPartitions", 8) \
  .option("isolationLevel", "READ_UNCOMMITTED") \
  .save()

The batchsize parameter tells the JDBC driver to accumulate 50,000 rows before sending them to the database. This reduces round-trips. The numPartitions setting controls how many parallel write tasks Spark spawns—we'll expand on this critically important parameter next.

Control parallelism to protect databases

Spark loves parallelism. By default, it will use as many partitions as your cluster can handle, which means dozens of JDBC connections slamming your database simultaneously. On an OLTP system already serving customers, this is catastrophic.

Picture this: your Spark job writes to PostgreSQL using 32 partitions, which means 32 simultaneous connections, each inserting thousands of rows. Your DBA gets a page at 3 AM because connections are exhausted, queries are queued, and your production API is timing out. The issue is that you never controlled the write parallelism.

The antidote is explicit partition control. Set numPartitions to a conservative value—often 4 to 8 partitions suffices for production writes. This ensures you're not overwhelming the database's connection pool or causing lock contention.

Here's the principle: measure your database's concurrent connection capacity and set numPartitions to 50–70% of that limit. If your database allows 200 connections total and 150 are already spoken for by your application, limit Spark to 20 partitions.

# Conservative write: 4 partitions, controlled batch size
spark_df.repartition(4).write \
  .format("jdbc") \
  .mode("append") \
  .option("url", jdbc_url) \
  .option("dbtable", "orders") \
  .option("user", db_user) \
  .option("password", db_pass) \
  .option("batchsize", 100000) \
  .option("fetchsize", 100000) \
  .save()

Adding repartition(4) before the write ensures your DataFrame is split into exactly 4 partitions, regardless of how Spark computed it earlier. Each partition becomes one write task, so you get four concurrent database connections—manageable and predictable.

Upserts via staging tables

Appending new rows is simple, but what if your data changes? You might need to update existing records if they already exist, or insert them if they don't. This is an upsert, and JDBC doesn't natively support it. The standard production pattern is a staging table.

The workflow: write your Spark results to a temporary staging table, then run a SQL merge or delete-insert operation in the database to apply changes to the real table. This approach is safer than trying to orchestrate upserts from Spark because the database handles consistency and locking.

Suppose you're syncing product catalog updates. Your Spark job produces 2 million rows with product IDs, names, and prices. Some of these products already exist in the main table; others are new.

Step 1: Write to staging.

catalog_df.write \
  .format("jdbc") \
  .mode("overwrite") \
  .option("url", jdbc_url) \
  .option("dbtable", "catalog_staging") \
  .option("user", db_user) \
  .option("password", db_pass) \
  .option("numPartitions", 4) \
  .save()

Step 2: Merge in the database. Connect via Spark SQL or invoke this directly against PostgreSQL:

merge_sql = """
DELETE FROM catalog_staging WHERE product_id NOT IN (SELECT product_id FROM catalog);
UPDATE catalog SET name = s.name, price = s.price, updated_at = NOW()
FROM catalog_staging s
WHERE catalog.product_id = s.product_id;
INSERT INTO catalog (product_id, name, price, created_at, updated_at)
SELECT product_id, name, price, NOW(), NOW()
FROM catalog_staging s
WHERE NOT EXISTS (SELECT 1 FROM catalog WHERE catalog.product_id = s.product_id);
TRUNCATE TABLE catalog_staging;
"""

spark.sql(merge_sql)

Alternatively, use a native database merge if your engine supports it (PostgreSQL 15+ has MERGE; MySQL has different patterns; Snowflake makes this trivial). The key benefit: your database controls the consistency guarantees, and the operation is atomic or can be rolled back if something fails.

Another production tip: always truncate or drop the staging table after the merge succeeds. Leaving stale data in staging is a common source of bugs where the next run produces incorrect results because old staging data is still present.

Prefer warehouses for analytics

Here's the uncomfortable truth we've learned: writing analytics results back to your OLTP database is often the wrong decision architecturally, even if it's technically possible.

OLTP databases like PostgreSQL and MySQL are optimized for transactional consistency: fast inserts, updates, and deletes on small datasets with strict ACID guarantees. Analytics workloads—writing hundreds of millions of rows, running heavy aggregations, and tolerating eventual consistency—are a poor fit.

When you force analytics writes into your production OLTP database, you get:

• Lock contention. Your Spark writes block application queries.
• Slow analytical queries. Row-oriented storage isn't optimized for column scans.
• Replication lag. If your OLTP has replicas, this massive write load can cause followers to fall behind.
• Backup complexity. Huge analytics tables bloat your backup window.

Instead, prefer a data warehouse: Snowflake, BigQuery, Redshift, or even PostgreSQL on separate infrastructure. These are built for the access patterns Spark produces. They scale horizontally, support efficient columnar storage, and let you write billions of rows without affecting transactional operations.

A better architecture:

1. Spark processes raw data and writes to a data warehouse.
2. The warehouse stores detailed results and serves analytics queries.
3. When your application needs specific aggregated data, either query the warehouse directly or export curated results back to OLTP in a controlled, narrow format.

If you must write to OLTP, write only the aggregated results you need: summary tables with daily totals, top-10 lists, or precomputed metrics. Write a few million rows, not hundreds of millions. Use the staging-table pattern and schedule writes during low-traffic windows.

Common mistakes

• Ignoring partition count. Leaving numPartitions at Spark's default floods the database with connections. Always set it explicitly.
• Reusing credentials in code. Never hardcode database passwords. Use a secret manager, environment variables, or your cloud provider's credential service.
• No retry logic. Network blips happen. Wrap your write in retry logic with exponential backoff, or use Spark's built-in fault tolerance by idempotently writing to a staging table first.
• Forgetting to analyze tables post-write. After a large insert, statistics are stale. Run ANALYZE TABLE (or your database's equivalent) so the query planner stays current.
• Mixing OLTP writes with streaming. If your Spark workload is streaming and you're writing to OLTP in real-time, you're almost certainly overloading the database. Buffer results and batch-write them hourly or daily instead.

Key takeaways

• Use JDBC with tuned batch sizes (50k–100k rows per batch) and conservative partition counts (4–8 partitions) to write safely to production databases.
• Always set numPartitions explicitly; leaving it at Spark's default will overwhelm your database's connection pool.
• For upserts, write to a staging table first, then merge in the database to guarantee consistency and allow rollback on failure.
• Reserve OLTP writes for curated, aggregated results only. Send large-scale analytics output to a data warehouse instead.
• Validate that your database can handle the concurrent connections, lock duration, and disk I/O your Spark job produces before deploying to production.