Why monotonically_increasing_id() Produces Non-Sequential IDs
Thatโs actually a super common trip-up! Spark does this by design to avoid an expensive global shuffle across nodes, but it definitely catches a lot of people off guard when they expect a simple 1, 2, 3 sequence.
monotonically_increasing_id() guarantees unique 64-bit integers that increase within each partition, but they are not consecutive across the whole DataFrame.
Because Spark is a distributed processing engine, each partition is assigned its own large block of IDs. The high 31 bits store the partition ID (calculated as partition_number ร 2ยณยณ), while the low 33 bits store the record count within that specific partition. As a result, you see those huge numeric gaps between partitions (e.g., 0, 1, 2, ... on Partition 0, then jumping to 8589934592, 8589934593, ... on Partition 1).
Three Ways to Get Sequential IDs
1. Use row_number() over monotonically_increasing_id() (recommended)
Combine both functions: first add the monotonic ID, then use row_number() with a window ordered by that ID to get clean 1, 2, 3, โฆ values.
from pyspark.sql.functions import monotonically_increasing_id, row_number, col
from pyspark.sql.window import Window
# Add monotonically increasing id first
df_with_mono_id = df.withColumn("mono_id", monotonically_increasing_id())
# Use row_number() over a window ordered by the monotonic id
window = Window.orderBy(col("mono_id"))
df_with_seq_id = df_with_mono_id.withColumn("row_id", row_number().over(window)).drop("mono_id")
df_with_seq_id.show()
Output:
+-----+---+------+
| Name|Age|row_id|
+-----+---+------+
|Alice| 10| 1|
|Susan| 12| 2|
+-----+---+------+
Note: row_number() with a single partition window can be slow on very large datasets because all data is shuffled to one partition for ordering.
2. Use zipWithIndex() via RDD (best for large datasets)
This approach avoids the single-partition bottleneck by working at the RDD level:
from pyspark.sql.functions import col
# Convert to RDD, apply zipWithIndex, convert back
df_rdd = df.rdd.zipWithIndex().toDF()
df_with_id = df_rdd.select(col("_1.*"), col("_2").alias("row_id"))
df_with_id.show()
Output:
+-----+---+------+
| Name|Age|row_id|
+-----+---+------+
|Alice| 10| 0|
|Susan| 12| 1|
+-----+---+------+
3. Start from a custom offset
If you need IDs to continue from a previous maximum (e.g., for incremental loads):
from pyspark.sql.functions import monotonically_increasing_id, row_number, col, lit
from pyspark.sql.window import Window
previous_max_value = 1000 # fetch this from your existing table
df_with_mono_id = df.withColumn("mono_id", monotonically_increasing_id())
window = Window.orderBy(col("mono_id"))
df_final = (df_with_mono_id
.withColumn("row_id", row_number().over(window) + lit(previous_max_value))
.drop("mono_id"))
df_final.show()
Output:
+-----+---+------+
| Name|Age|row_id|
+-----+---+------+
|Alice| 10| 1001|
|Susan| 12| 1002|
+-----+---+------+
Check the below details it will help you to decide which method to choose?
| Method | Pros | Cons |
| row_number() over monotonically_increasing_id() | Simple, stays in DataFrame API | Single-partition shuffle; slow on very large data |
| zipWithIndex() via RDD | Distributed, efficient for large datasets | Requires RDD conversion and back |
| Custom offset | Supports incremental ID assignment | Same trade-offs as method 1 |
For most use cases, method 1 (row_number()) is the simplest. For very large datasets where performance matters, method 2 (zipWithIndex()) is preferred.
Hope this should help!
Thanks!
