Ask HN: Is PySPark a Dead-End?

Start by looking here. As much as we love to bag on Oracle, it is at its core a really fast and capable database. I don't know what you are doing, but doing anything with only 81M records shouldn't take 17 hours. Profile you SQL, rewrite it, if necessary bring in a Oracle SQL expert, and I'm pretty sure you will find some easy wins just here. Perhaps even enough to solve your performance problems. If you're doing relational database type work, it's hard to beat a relational database.

E.g. maybe 15 of the 17 hour running time is because the database is doing sequential scans of some tables as some essential indices haven't being defined. Or maybe the indices are defined but the queries need to be written to take advantage of them. Or maybe the queries are blazing fast because the python scripts are taking it upon themselves to perform outer joins in very slow pure python code rather than just getting the database engine to do it. Or maybe all the queries are happening implicitly through SQLAlchemy ORM and the entire analysis is a fractal mess of lazy n+1 select antipattern OO nonsense, and most of the running time is actually network latency between the machine where the python sits and the machine where the database lives. Maybe 4 of the 17 hours of running time is due to compute heavy hot loops in pure python code that can be sped up 1000x if someone is willing to roll up their sleeves and spend a week rewriting as C / C++ / Cython code that lets the CPU loose to crunch numbers in arrays without allocating or hashing or reference counting or waiting for the GIL. Or maybe the entire thing is relatively well engineered, given the physics of the computations involved, and 17 hours is pretty reasonable!

If no one knows yet what the bottlenecks are, maybe spend a few days profiling stuff and comparing it to theoretical estimates of the throughput or processing speed that the hardware is capable of, assuming the system was making optimal use of the hardware, and try to figure it out. It'd be a bit unfortunate to not understand the bottlenecks and migrate everything to pyspark and end up with something that runs slower than the original version.

I always try to follow the rule-of-thumb of "if it can be done in the analytical DB, it should be done in the analytical DB". In my experience Oracle is pretty well suited for all of the "sorting, aggregation, discarding, merging, and splitting tasks". With proper indexing/partitioning processing 81M records shouldn't take 17 hours. Pulling all the data into python and then fighting the lack of (out-of-the-box) multi-threaded data processing capabilities seems to be part of the problem than of the solution.

In my current job if I have to do some analytical heavy lifting I just write the data to AWS S3 (parquet) and read the query-results back through AWS Athena (Presto) into python.

Depending on what goes on in between the lines of all that "sorting, aggregation, discarding, merging, and splitting" -- the core guts of what you're doing might quite easily done within Postgres.

And 81 million rows? That will fit on your laptop, easy (especially if many are discarded in the early stages of processing).

Or it perhaps might not fit so easily. But the basic point I'm trying to make here is: don't be afraid of simplicity. All other unknowns being equal, it's as good a starting point as any.

We actually see a lot of what you are describing at my current company. All our data is stored in a database that was once very popular, but is old now and not cloud based.

We have a couple of challenges.

1) The data engineering team typically loads data (typically 100s of millions of rows) into the database before anybody really decides how it will be used. Typically we (Data Scientists) only get access to views. The views and underlying tables are not indexed or partitioned as we need, so almost any query takes forever to run since it almost always results in a full table scan of the raw source table.

2) The database team is caught in a budgeting trap. There is a long term-migration to another cloud based database, so both people and financial resources are focused on that. The end result is that no further scaling of the current database provider is possible, which means that every complex query we make on this database creates more load, which makes every other query run slower. This database has a lot of users, tables, and queries, which means that a lot of the available people spend their time just making sure the database maintains some basic standard of performance.

3) Since any complex query (e.g. even a basic date based aggregation) increases the load on the server, user queries should only fetch and filter data. Aggregations and anything complex (e.g. a string operation) should be done downstream.

Based on these constraints, we basically have adopted the following.

1) Once the users (e.g. us) have defined a use case for the data, the Data Engineers/Data Platform team will index and partition the data properly. This typically results in a huge performance increase. Recently we had one view that went from 5+ hours for a basic query to less than 5 minutes.

2) All aggregations and data transformations are done in Databricks/PySpark.

3) Typically, after 2) if we convert the Spark Dataframe to a Pandas dataframe, data dataset is small enough to run anywhere.

One of the things to keep in mind is the people supporting it after your contract is over. I think Databricks/PySpark and Dask are fairly common and well known in the data community. Snowflake can probably help speed up the SQL queries once the data is moved into that, but I don't think it can cover some of the analytical things you can do in Spark.

Arrow/VoltronData doesn't seem like a fit for the use case you are describing unless you have bunch of developers trying to develop their own data engine with Arrow behind it.