Building a recommendation engine inside Postgres with Python and Pandas (2020) | Hacker News Reader

Building a recommendation engine inside Postgres with Python and Pandas (2020)

blog.crunchydata.com

173 pointsrbanffy43 comments

I guess I'm wondering what the point of pandas is here. And maybe in general? We hired a data engineer awhile ago and he kept complaining that the "python kernel" was crashing and he needed bigger instances to run his pandas scripts. I took a look and realized he was trying to pull the entire database of a nearly a billion rows into python to do some trivial joins, groupings, and filtering. I'm thinking, what are you doing man.

I think this is pretty common in the pandas world right

Pretty common and I think one of the main reasons why is that Pandas API has a very low entry barrier to get productive. It is very easy to do something without really knowing anything about computer science.

It's very true that pandas is in memory and so not good with large datasets... it is good for playing around with data and plotting / visualising. Scikitlearn, statsmodels are very powerful for this.

SQL is sometimes under appreciated as superior for grouping sorting and filtering... although with Bigquery and KSQL, SQL is definitely finding it's groove again.

Yikes, please tell me they were hired as a junior?

Obviously in your case some trivial SQL would have sufficed, which I feel like should have been caught in an interview but could be excused for a junior eng.

Also Pandas is not an amazing choice for data engineering tasks in general IMO, type conversion wonkiness is one reason I have questions about people who decide to use Pandas for legitimate data engineering tasks (as opposed to exploratory work or analysis).

The issue is a lot of the tools in the ecosystem are positioning themselves as “do everything” and the supporting marketing/services is all about increasing numbers of workarounds to enable this.

Using pandas but your data doesn’t fit in memory anymore? Here just use this fragile series of packages to distribute it across processors. Don’t reevaluate whether Pandas/Python is still the best choice, just layer more things in.

Run into issues with that approach? Here lift and shift your whole thing into Spark! Don’t ever optimise or actually engineer your process, just layer more things in!!!

So guilty of this, haha

Yeah me too, But there is a point where you have to stop. I also at some point switched from Excel/Origin to Pandas because of scaling, and now I'm making my first steps in to learning SQL. A point comes where you have to grab and learn a different tool. But it's a difficult to thing to do when you have to get stuff done!

I think one approach that can help you is to start writing your pandas logic using only apply/merge/groupby etc. I.e. no iterators like df.iterrows() and similar.

Once you have done that, the logic will be easily translatable to SQL, and your brain is prepared for the SQL/relational-algebra way of thinking.

And good luck! You are doing it right!

As of a few days ago (Apache Spark 3.2 release), you can use the pandas API on a Spark cluster: https://databricks.com/blog/2021/10/04/pandas-api-on-upcomin...

How the relevant lesson from the original post was “Actually, you can scale pandas these days” eludes me.

Since the original post criticizes the behavior of Panda runtime where it loads everything into memory before processing, I think replacing the underlying Panda runtime with Spark runtime is very much a valid solution.

You can keep using Panda code for exploratory work, then with a little effort move it to production with Spark runtime.

As an avid user of pandas: That solution is just kicking the can down the road.

And now you have a whole set of new problems ?

I like to think of Pandas as a replacement of Excel. I think where Pandas users go off the tracks is when you start getting to 100M rows and start having memory/kernel issues. For many of us, it is obvious that the next station is a database with the transformations necessary. But not many people ever get to this scale. It doesn't help that Pandas has a read_sql() function.

I’ve seen this and has fed into my hatred of pandas

“In the moment when I truly understand my enemy, understand him well enough to defeat him, then in that very moment I also love him. I think it’s impossible to really understand somebody, what they want, what they believe, and not love them the way they love themselves. And then, in that very moment when I love them.... I destroy them.”

― Orson Scott Card, Ender's Game

I'm betting you are not an avid Pandas user ;)

Yes and no. I am not a data scientist but there are quite a few python scripts where we use pandas. Initially, I thought it was really nice and convenient but the more I have used it the more I think it's bloated, a pain to use, and dumping the data into sqlite would be easier for a lot of my tasks.

The places where pandas blows SQL out of the water are in exploratory data analysis and visual output.

The exploratory data analysis part is mostly a product of composability and verbosity. Composability is hard to define succinctly, but an easy example of it would just be the ability to easily define a filter as a variable and then reference that same filter in later code. Python provides a better interface for defining higher-order operations than pgPL/SQL, hands down. As for verbosity, I get data requests at work that require a lot of chained transformations - where I’d have to write 5-10 CTEs and a bunch of GROUP BYs and window functions that would total ~300 lines of SQL. Pandas equivalent would be <50 lines, and also easier to iterate on due to the composability factor. Other “killer-feature” examples of pandas terseness: https://pandas.pydata.org/pandas-docs/stable/reference/api/p..., https://pandas.pydata.org/pandas-docs/stable/reference/api/p....

Feature parity varies by db, but pandas is generally much more expressive and capable than SQL for string munging and time-series stuff.

This is before getting to Jupyter, which blows every SQL interface I’ve ever seen out of the water. One-liner plotting with Pandas is a complete game changer for investigating any unfamiliar dataset; the first class plotting integration is really hard to beat.

All that said, can’t comment on your coworker’s situation without knowing the context. A billion time series datapoints might be fine, but if it’s a wide table using 100s of gigs, probably not smart. I wouldn’t use pandas in a large ETL pipeline without a very good reason, nor would I ever use it for serious joining. If he is just straight up trying trying to functionally replace SQL with pandas code, he’s definitely not doing it right.

(Am data engineer, have been working professionally w/ SQL+Pandas on a near-daily basis for over 3 years.)

> what the point of pandas is here. And maybe in general?

In general, if you are comparing to an SQL database, the most important is complex anaylsis/manipulation. Of course SQL is great for database level analysis - sorting/filtering/etc. But SQL queries for domain-specific analysis can become very opaque quickly and debugging of complex queries is a mess. Pandas is great for more complex manipulation as it keeps available the wider python ecosystem, allows for simpler debugging of complex processes through a REPL loop, links easily to visualisation, is pretty fast for some things through leveraging things like numpy, in-memory avoids multiple database calls, etc.

Obviously pandas isn't a great choice for persistent large datasets (maybe around the 0.1-1TB mark?) though, simply because it is memory stored.

The use cases definitely overlap significantly, but run into problems (typically ridiculous SQL queries on one side, giant data sets on the other) and you really need to stop and think about the tooling.

Sounds like an inexperienced data engineer. One of the first things you learn is to read tabular data into a DataFrame, like pd.read_csv. So, there’s a natural progression to pd.read_sql [0], which default behavior loads SELECT * FROM t without any filters into a DataFrame. But, it also accepts a raw sql query or SQLAlchemy selectable, which should be used to join/filter data before loading into python.

[0]https://pandas.pydata.org/docs/reference/api/pandas.read_sql...

It's interesting to me to see pandas used in this application. I'd be curious to see a more fully featured implementation.

I'm a scientist by profession and I've been working on building out several different generalized data processing pipelines for some specific problems in my sub-field, to make gathering and formatting in-situ data easier and more standardized/open/version-controlled. It's going great, worlds better than the smattering of matlab code strewn across the hard drives in the lab written in a non-collaborative manner and shared by email...

... but. I'll admit, I've run into a lot of footguns in the pandas API in terms of efficiency. You'll do something it what seems to be the logical way, or in a way that the API funnels you towards (like the groupby calls in the OP), and you'll quickly realize that if you're working on large-ish tables (>10Gb in memory) that it was the stupid way to do things. In terms of readable code to share with colleagues, pandas can't be beat, but things get wonky when you reach significant complexity and I would be surprised if it made any sense to use in a 'real' recommendation engine when considering developer productivity.

Most of my workin pandas is with small to medium size < 1g tables what constraints do you run into with the larger data sets you are talking about?

Look into Dask if you are attempting to process an entire data table that's larger than memory.

For your last paragraph, you're conflating the need to share code with the need to build a robust scalable service. Most research code are only needed for the paper and rarely touched again.

Enjoyed that read but to be honest I always had mixed feelings about PSQL Extensions. Logic in a database is wrong

It’s so amazing to have the ability to enhance your database with custom methods. Keeps your data model really well organized across your infrastructure

That's the job of an API IMO.

An API makes sense for this case when you won't be throwing away a lot of the raw data in favor of the processed/transformed data.

But doing a lot of these operations on-server makes sense when there's a significant volume of highly parallelizable transformations which need to be done on the data before it's usable.

Of course the best solution is likely to be a happy medium between the two, where simple low-level transformations are done on-server and the rest of the data preparation is done as the data is transferred to the client.

Not in data processing. There are cases where you have so much data that is better to bring computation to data rather than the other way around.

Having dealt with the fallout of a database that’s had hundreds of stored procedures and custom code stapled into it: never again.

It was so hard to debug, so hard to safely maintain these procs, and a general nightmare

Can you expand on why you think it is wrong or problematic?

Database is the last thing that scales usually so if you put a bunch of computational load on it besides queries, you've set yourself up for scaling/sharding sooner than later.

Not always! If computation involves math over set of records, then Postgres is great for that. Have the operation inside the db reduce connection pooling on the application level.

I remember a friend telling me in a previous job me they loaded the database with stored procedures because it ran on the most powerful server when the product launched. And then the database server hit its capacity very quickly.

Reminds me of why I learned to program in PostScript - the PostScript printer was, by far, the fastest computer I had access to - also it had a ton of memory and its very own SCSI hard disk.

Plotting a Mandelbrot on the Mac would take a lot longer, even in C, than just making the printer do it.

Usually overnight, because the program took a couple hours to run most of the time.

> you've set yourself up for scaling/sharding sooner than later.

OTOH, if you can pre-process the data (or shards) in the server (or servers), you won't need to move it across the network for processing.

Hard problems are hard and one of the reasons databases outlive their systems and developers is because moving that data is a Royal Pain.

Data and its structure outlives the developer, and often the application.

Tightly coupling a lot of the application-specific compute in with how the day is stored and accessed says you up for even more difficulties when you need to debug, scale, migrate storage/compute or evolve your application faster or more radically than your data organisation.

Do what thou wilt shall be the whole of the Law

I see some python / pandas hate here and I have to defend it a bit: We all agree a query like: tb_1 = pd.read_sql(big_table_1) tb_2 = pd.read_sql(big_table_2) table_i_want = pd.merge(tb_1, tb_2) table_i_want =table_i_want[table_i_want.my_id.isin([1,2,3])] is completely stupid, put that in the DB! And a lot of (very very) unexperienced people will write such a query, since they dont know anyhting about DBs and SQl.

However, pandas and python is here for what comes after that! Like a very complicated groupby logic, or invoking some apis to enhance data or complicated string processing or some kind of error measure or invoking some ML models or sending a slack message or building features for ranking or whatever.

I know a lot of people here love their SQL but queries like mentioned above are either impossible or become horrible horibly complicated and impossible to debug.

EDIT: And I want to add one extremely important thing. Whenever you can put stress away from your DB it is a good thing. Since your DB has to run all of the time and will be already stressed from Frontend requests. Combined with the fact that we are often talking about non-time sensitive work wouldn't it be better if your data scientists work on a database dump instead of continously hitting the DB?

You setup a read-replica so that the production database isn't hit by analytical queries.

And then ingest that read-replica to a column store data warehouse so that you don't have to think about reliability until someone starts yelling about speed or cost.

It recommends products that were most frequently included in the same order. So it’s not recommending similar or related products, but products frequently purchased with the product in question, so the wine use-case wouldn’t recommend wine, but it would recommend cheese, bottle openers, chocolate, etc.

This makes sense in the context of the example the author copied from [0], where the dataset is sports equipment. Often purchased in bundles of related products for a specific sport.

[0]https://github.com/scottfitzcodes/rec-engine-example

Seems like a nice approach, I used to try things like this with postgres (embedding sklearn).

Though it can be slow, harder to deal with memory usage issues, harder to debug in general, harder to extend/generalise.

And for me postgres is now one of multiple datastores so doing this is not as helpful.

Nice, now expand that to be a simple rolling trending algorithm and you've reinvented most of the now common place web tech

Isn't it better to write a postgres extension instead of running python inside?

Reply on news.ycombinator.com