How do I read from JDBC in Spark efficiently?

Summary

• Partition the read. The Databricks JDBC docs say the driver queries the source database with only a single thread by default. Set partitionColumn, lowerBound, upperBound and numPartitions together to read in parallel. The column must be numeric, date or timestamp.
• The bounds set the stride, not a filter. Spark's docs say all rows in the table are still partitioned and returned, so poor bounds give lopsided partitions rather than missing rows.
• Tune fetchsize. The default is 0. The Databricks docs say a value that is too low causes many round trips and one that is too high risks out-of-memory errors.
• Keep push-down on. pushDownPredicate defaults to true. To push more work down, pass a parenthesized subquery as dbtable. The query option cannot be combined with partitionColumn.
• Don't over-partition. numPartitions also caps concurrent JDBC connections, and a high value on a large cluster can hurt performance on the remote database.

How do I read from JDBC in Spark efficiently?

Three things decide how fast a JDBC read runs: how the read is split into partitions, how many rows come back per round trip (fetchsize), and how much work is pushed down to the database.

Why is a plain JDBC read slow?

Without partitioning options, a JDBC read is one query on one connection. The Databricks JDBC docs say that by default the JDBC driver queries the source database with only a single thread. In Spark's source, a read with no partitionColumn, with numPartitions of 1 or less, or with equal bounds becomes a single partition with no WHERE clause.

How do I parallelize a JDBC read?

Set partitionColumn, lowerBound, upperBound and numPartitions together. The Spark docs say these options must all be specified if any of them is, and that partitionColumn must be a numeric, date, or timestamp column. numPartitions is the maximum number of partitions and also the maximum number of concurrent JDBC connections.

df = (spark.read
    .format("jdbc")
    .option("url", "jdbc:postgresql://localhost/test")
    .option("dbtable", "(select id, amount from orders) as subq")
    .option("user", "username")
    .option("password", "password")
    .option("partitionColumn", "id")
    .option("lowerBound", "1")
    .option("upperBound", "1000000")
    .option("numPartitions", "8")
    .option("fetchsize", "100")
    .load())

How does Spark split the range?

The bounds only decide the partition stride. Spark's source (v4.2.0) builds one WHERE clause per partition so that each row appears exactly once.

Partition	Generated WHERE clause
First	column < (end of first stride) OR column IS NULL
Middle	column >= (start) AND column < (end)
Last	column >= (start of last stride)

Because the first and last partitions are open-ended, rows outside the bounds still arrive, and NULL values land in the first partition. Bad bounds therefore produce lopsided partitions, not missing rows. If upperBound - lowerBound is smaller than numPartitions, Spark reduces the partition count and logs a warning.

Which options affect read performance?

Option	What the Spark docs say	Default
fetchsize	Rows fetched per round trip. Read only.	0
queryTimeout	Seconds the driver waits for a Statement to execute. Zero means no limit.	0
pushDownPredicate	Push filters down to the database as much as possible.	true
pushDownAggregate	Push aggregates down. Needs all aggregate functions and related filters to be pushable.	true
pushDownLimit	Push LIMIT, and LIMIT with SORT, down.	true
customSchema	Column types to use on read, for example "id DECIMAL(38, 0), name STRING". Partial lists are allowed.	none

How do I choose a fetchsize?

fetchsize is how many rows to fetch per round trip, and the Spark docs say it can help performance on JDBC drivers that default to a low fetch size. The Databricks docs describe the trade-off: too low means high latency from many round trips, and too high means an out-of-memory error because too much data comes back in one query.

How do I push work down to the database?

pushDownPredicate defaults to true, so Spark pushes filters down to the JDBC data source as much as possible. The docs say it is usually turned off when the filtering runs faster in Spark than in the data source. To push more, put a subquery in dbtable, as in the example above. The docs say anything valid in a FROM clause can be used.
The query option wraps your SQL as a subquery, but the docs do not allow query together with partitionColumn. Use dbtable with an alias instead.

What should I know on Databricks?

• Databricks recommends storing database credentials as secrets, for example dbutils.secrets.get(scope = "jdbc", key = "username"), rather than hard-coding them as the example above does.
• If Lakehouse Federation supports your source database, Databricks recommends using that instead of JDBC reads. It provides governed, read-only access to external data through Unity Catalog foreign catalogs.
• A high numPartitions on a large cluster can hurt the remote database, because too many simultaneous queries might overwhelm it.

FAQs

What happens to rows outside lowerBound and upperBound?

They are still read. The bounds decide the stride and do not filter rows, so all rows in the table are partitioned and returned. To filter, apply filter or where on the DataFrame (pushed down by default) or use a subquery in dbtable.

Can I partition on a string column?

Not with partitionColumn, which must be numeric, date, or timestamp. spark.read.jdbc also accepts predicates, a list of expressions suitable for WHERE clauses where each one defines one partition. If you pass both column and predicates, column is used.

Does a higher numPartitions always make the read faster?

No. It also raises the number of concurrent JDBC connections. The PySpark docs warn that too many partitions in parallel on a large cluster might crash your external database systems.

Why is my read slow even with partitions?

Check three things: whether every partitioning option is set (otherwise the read is one thread), whether fetchsize is too low for your driver, and whether the partition column's values are skewed. Spark computes a stride from the bounds, so uneven values give uneven partitions.