Partitioning or indexing for spark sql?

[u/Klutzy_Solid5200]

Im working with some large tables in spark sql that has around 300 mil records and they are quite wide as well maybe around 80-90 columns.

We frequently have to perform filtering and aggregations for reporting purposes, I'm trying to understand when should I use partitioning and when should I use indexes.

For instance if I want to filter by date will it be better to partition by date or should I use an index on the date column, which one would help with performance?

[u/No_Ambition8323]

For Spark SQL, I’d generally prefer partitioning over traditional indexing for this use case. If you frequently filter by date, partitioning by a reasonably granular date column can enable partition pruning, so Spark reads only the relevant data instead of scanning all 300M rows.

Indexes aren’t typically the first choice in Spark’s distributed storage model. Also, avoid over-partitioning (e.g., partitioning by timestamp down to seconds). For reporting workloads, a good combination is date partitioning + columnar format like Parquet/Delta + appropriate file/data layout.

[u/jshine13371 → u/No_Ambition8323]

For Spark SQL, I’d generally prefer partitioning over traditional indexing for this use case. If you frequently filter by date, partitioning by a reasonably granular date column can enable partition pruning, so Spark reads only the relevant data instead of scanning all 300M rows.

Not following the logic here. Indexing by date and then filtering by it won't scan all 300 million rows either. It'll ideally run an index seek which logarithmically navigates a B-Tree data structure which is a lot less nodes of data that needs to be traversed than the linear Partitions.

Though, I haven't used Spark personally, so not sure if there's something unusually specific here. But I would find it unusual to be deficient with indexes compared to every other modern database system.

[u/jshine13371]

Partitioning (conceptually, generally speaking) is not a performance tool for DQL and DML type of queries. Indexing is. The reasoning being is because Partitioning divides the data linearly (by a constant factor), and indexing divides the data exponentially (by a logarithmic factor). Therefore indexing is orders of magnitude more efficient for looking up data for DQL and DML type of queries.

Yes, Partition elimination can help performance too, but that's a byproduct and still slower than indexing from a Big-O search time complexity perspective. Really a better use case for Partitioning is when you can run specific DDL type of commands on individual partitions in the table making data management more efficient (e.g. index changes such as rebuilds to specific partitions, or data archiving processes like dropping an entire partition at a time is rather immediate).

But for general querying performance needs (again DQL and DML type of queries), reach for the index first.

[u/FunContest9958]

We’re missing some info here. What format is this table in? Delta, iceberg, something else? Are you on Databricks or using a different Spark platform?

[u/Enigma1984]

Are you in Databricks using Delta? If so just use liquid clustering. No need to over engineer.

If not then it depends on factors other than the type of SQL you are using. Most importantly the file type the data is stored in.

[u/Alternative_Cake4074]

I would say partitioning outperforms indexing in all databases for SQL, not just Spark.

Why?

• Querying in partitioned tables looks nicer than with an index. With partition, you don't have to check all tables and think about orders.
  
• Inserting rows in partitioned tables just adds rows to the right one without touching the index, which reduces time and memory consumed.
  
• Partitioned tables make much more sense to customers.

[u/jshine13371 → u/Alternative_Cake4074]

I would say partitioning outperforms indexing in all databases for SQL

This is completely wrong.

[u/Alternative_Cake4074 → u/jshine13371]

Oops, interesting, I would like to hear about your thoughts.

[u/jshine13371 → u/Alternative_Cake4074]

For the high level reasons I mentioned in my comment.

A very crude expansion on that comment: Partitioning breaks up the data linearly. For example, if you have 10 years of data that you partition by month & year on, that's 120 partitions (12 months per year * 10 years) it creates. If you need to find data in the 12th month of the 10th year, you'll end up scanning 120 items to get there. Conversely, indexing breaks up the data logarithmically, meaning if you indexed on the month / year, resulting in 120 nodes of the B-Tree (the data structure behind an index), to locate any specific month & year's data, it only has to traverse 7 nodes (log2(120) = 7) of data in the worst case. That's over 17x less work, ergo significantly faster. And the larger the data, the larger that improvement by using indexing grows.