Reading from JDBC sources: practical tips and trade-offs

Introduction

Reading from JDBC sources is one of the most common data engineering tasks, yet it's easy to overlook critical performance and reliability considerations. When we pull data from production databases into Spark, we're not just moving bytes—we're contending with connection limits, concurrent workloads, and the risk of starving the systems that keep the business running.

In this article, we'll walk through practical tips and trade-offs for reading from JDBC sources: practical tips and trade-offs that help us balance throughput, latency, and operational safety. Whether you're migrating historical data, building analytics pipelines, or feeding machine learning models, these patterns will save you from common pitfalls and production incidents.

Understanding JDBC reads in Spark

Spark's JDBC data source lets us query relational databases directly and load the results into DataFrames. By default, Spark launches a single driver process to fetch data sequentially, which is both a blessing and a curse. It's simple and safe, but it leaves enormous performance on the table and can create bottlenecks.

The real leverage comes from understanding how Spark partitions the load and pushes computation downstream. When configured well, a JDBC read can parallelize across executors and minimize data transfer. When configured poorly, it can overwhelm a production database or timeout mid-pipeline.

Partition reads with lowerBound and upperBound

The most effective way to scale JDBC reads is to partition the data by a numeric column. Spark will create multiple JDBC connections in parallel, each fetching a distinct range of rows. This distributes the load across your cluster and reduces the time a single database connection holds a transaction open.

Here's how it works in practice:

from pyspark.sql import SparkSession

spark = SparkSession.builder.appName("jdbc-partition-demo").getOrCreate()

df = spark.read.format("jdbc") \
    .option("url", "jdbc:postgresql://prod-db.example.com:5432/analytics") \
    .option("dbtable", "orders") \
    .option("user", "spark_reader") \
    .option("password", "secret") \
    .option("partitionColumn", "order_id") \
    .option("lowerBound", 1) \
    .option("upperBound", 10000000) \
    .option("numPartitions", 16) \
    .load()

df.write.mode("overwrite").parquet("s3://data-lake/orders/")

In this example, Spark divides the range [1, 10,000,000] into 16 partitions and launches up to 16 concurrent JDBC connections. Each executor fetches roughly 625,000 rows. The partition column must be numeric (integers or timestamps work well), unique or quasi-unique, and ideally indexed in the source database.

Choosing numPartitions: Start with a value slightly higher than your number of executor cores, but never exceed the connection pool size of your database. A common mistake is setting numPartitions to 128 when your database only allows 20 concurrent connections—you'll get timeouts and connection exhaustion errors.

Bounds and data skew: If your data has gaps (e.g., order IDs jump from 1M to 5M), Spark will still try to fetch intermediate ranges and get empty results. This is inefficient but not harmful. A better approach is to query the actual min and max first:

# Query min and max dynamically
min_max_query = "(SELECT MIN(order_id) as min_id, MAX(order_id) as max_id FROM orders) t"

min_max_df = spark.read.format("jdbc") \
    .option("url", "jdbc:postgresql://prod-db.example.com:5432/analytics") \
    .option("dbtable", min_max_query) \
    .option("user", "spark_reader") \
    .option("password", "secret") \
    .load()

min_id = min_max_df.collect()[0]["min_id"]
max_id = min_max_df.collect()[0]["max_id"]

# Now use these bounds
df = spark.read.format("jdbc") \
    .option("url", "jdbc:postgresql://prod-db.example.com:5432/analytics") \
    .option("dbtable", "orders") \
    .option("user", "spark_reader") \
    .option("password", "secret") \
    .option("partitionColumn", "order_id") \
    .option("lowerBound", min_id) \
    .option("upperBound", max_id) \
    .option("numPartitions", 16) \
    .load()

This adds minimal overhead but ensures you're only fetching ranges that contain actual data.

Push down filters with queries

Instead of loading the entire table and filtering in Spark, push the filter logic into the database. This cuts network traffic, reduces memory pressure on your cluster, and lets the database leverage its indexes.

Rather than using dbtable with a simple table name, use a subquery in the dbtable option:

from datetime import datetime, timedelta

# Define a time window
start_date = (datetime.now() - timedelta(days=30)).isoformat()
end_date = datetime.now().isoformat()

query = f"""
(SELECT order_id, customer_id, total_amount, created_at 
 FROM orders 
 WHERE created_at >= '{start_date}' 
   AND created_at < '{end_date}'
   AND status = 'completed') t
"""

df = spark.read.format("jdbc") \
    .option("url", "jdbc:postgresql://prod-db.example.com:5432/analytics") \
    .option("dbtable", query) \
    .option("user", "spark_reader") \
    .option("password", "secret") \
    .option("partitionColumn", "order_id") \
    .option("lowerBound", 1) \
    .option("upperBound", 100000) \
    .option("numPartitions", 8) \
    .load()

Now the database filters to only 30 days of completed orders before Spark even receives the data. If this query returns 5 million rows instead of 50 million, you've cut network transfer by 90%.

You can also apply additional Spark filters after the JDBC read, but push down what you can:

# Push down complex logic when possible
query = """
(SELECT order_id, customer_id, total_amount, created_at 
 FROM orders 
 WHERE created_at >= CURRENT_DATE - INTERVAL '30 days'
   AND status = 'completed'
   AND total_amount > 100) t
"""

df = spark.read.format("jdbc") \
    .option("url", "jdbc:postgresql://prod-db.example.com:5432/analytics") \
    .option("dbtable", query) \
    .option("user", "spark_reader") \
    .option("password", "secret") \
    .load()

# Apply Spark-specific transformations after
df_processed = df.filter(df.customer_id.isNotNull()).groupBy("customer_id").sum("total_amount")

Limit load on production databases

A poorly tuned JDBC read can lock tables, exhaust connection pools, and trigger alerting on your production database. We've all seen a Spark job bring down a production system because it spawned 50 concurrent connections at 2 AM and nobody noticed until customers were affected.

Connection pool management: Use the fetchsize option to control how many rows are fetched per database round-trip. A larger fetch size reduces network latency but increases memory on both the database and the Spark driver:

df = spark.read.format("jdbc") \
    .option("url", "jdbc:postgresql://prod-db.example.com:5432/analytics") \
    .option("dbtable", "orders") \
    .option("user", "spark_reader") \
    .option("password", "secret") \
    .option("fetchsize", 10000) \
    .load()

Start with 10,000 and adjust based on row size and available memory. Too low and you'll see network overhead; too high and you'll exhaust memory.

Use a read-only replica: Never read from your primary production database if you can avoid it. Route JDBC reads through a read-only replica or a dedicated analytics warehouse. If that's not possible, coordinate with the database team on allowed connection limits and query patterns.

Monitor and throttle: Watch database metrics (CPU, connections, query duration) during JDBC reads. If a read is taking longer than expected or causing contention, investigate. Set reasonable timeouts:

df = spark.read.format("jdbc") \
    .option("url", "jdbc:postgresql://prod-db.example.com:5432/analytics") \
    .option("dbtable", "orders") \
    .option("user", "spark_reader") \
    .option("password", "secret") \
    .option("connectionTimeout", 30000) \
    .option("queryTimeout", 600) \
    .load()

A 30-second connection timeout and 10-minute query timeout prevent Spark from hanging indefinitely if the database is unresponsive.

Prefer CDC for large tables

If you're reading a multi-gigabyte or multi-terabyte table repeatedly, consider switching to Change Data Capture (CDC) instead of full table scans. CDC captures only the inserts, updates, and deletes since the last read, drastically reducing data volume and database load.

Why full JDBC reads fail at scale: A table with 1 billion rows may take 30+ minutes to read from JDBC, even with partitioning. If you're doing this daily, you're scanning the same billion rows every day, most of which haven't changed. Your database CPU and network bandwidth suffer, and your pipeline becomes fragile.

CDC approaches:

• Timestamp-based incremental: Add a last_modified timestamp column and query only rows updated since the last run. This works well for tables with frequent updates.
• Log-based CDC: Use tools like Debezium, which captures changes from the database transaction log and streams them to Kafka or S3. Spark can then consume these streams.
• Query-based CDC: Some databases (e.g., SQL Server) expose change tracking natively. Query the change tables directly.

Here's a simple timestamp-based approach:

from datetime import datetime
import json

# Read the last checkpoint
checkpoint_file = "s3://data-lake/checkpoints/orders_last_read.json"
try:
    last_read = json.loads(spark.sparkContext.textFile(checkpoint_file).first())["timestamp"]
except:
    # First run: go back 7 days
    last_read = (datetime.now() - timedelta(days=7)).isoformat()

# Read only changes since last checkpoint
query = f"""
(SELECT order_id, customer_id, total_amount, created_at, last_modified 
 FROM orders 
 WHERE last_modified > '{last_read}') t
"""

df = spark.read.format("jdbc") \
    .option("url", "jdbc:postgresql://prod-db.example.com:5432/analytics") \
    .option("dbtable", query) \
    .option("user", "spark_reader") \
    .option("password", "secret") \
    .load()

# Process and write
df.write.mode("append").parquet("s3://data-lake/orders/")

# Update checkpoint
checkpoint = {"timestamp": datetime.now().isoformat()}
spark.sparkContext.parallelize([json.dumps(checkpoint)]) \
    .saveAsTextFile(checkpoint_file)

This pattern reduces the amount of data Spark reads from 1 billion rows to perhaps 100K rows per day, a 10,000x improvement.

Common mistakes

• Forgetting to partition: Running a JDBC read without partitionColumn and numPartitions pulls everything through a single connection. This is slow, unreliable, and often times out on large tables.
• Setting numPartitions too high: A connection pool has a finite size. Requesting 128 partitions when the database allows only 20 connections wastes resources and triggers errors.
• Reading from production without coordination: Not discussing with the database team before launching large JDBC reads. This leads to surprises and angry on-call pages.
• Using string-based filters in Spark instead of pushing down: Filtering 500 million rows in Spark after pulling them over JDBC is wasteful. Push the filter to the database.
• Ignoring fetch size: The default fetch size is often too small. Tune it to reduce round-trips without exceeding memory limits.
• Full table scans when CDC is available: Re-reading a billion-row table every day because "it's simpler" is a false economy. The database and network will make you regret it.

Key takeaways

• Always partition JDBC reads by a numeric column using partitionColumn, lowerBound, upperBound, and numPartitions to parallelize load across your cluster.
• Push filters into the database via subqueries in the dbtable option to minimize data transfer and leverage database indexes.
• Read from replicas or dedicated analytics warehouses, never directly from production systems without coordination. Set connection and query timeouts to prevent hangs.
• For large tables, prefer incremental reads with timestamps or log-based CDC over full table scans to reduce database and network load.
• Tune fetchsize based on row size and memory constraints. Too small and network latency hurts; too large and memory exhaustion follows.
• Monitor database metrics during JDBC reads and communicate with the database team about allowed connection limits and peak usage windows.