back
▲ 444 points

Show HN: PostgresML, now with analytics and project management

postgresml.org
by levkk·4y ago·70 comments·view on hn ↗
We've been hard at work for a few weeks and thought it's time for another update.

In case you missed our first post, PostgresML is an end-to-end machine learning solution, running alongside your favorite database.

This time we have more of a suite offering: project management, visibility into the datasets and the deployment pipeline decision making.

Let us know what you think!

Demo link is on the page, and also here: https://demo.postgresml.org

70 comments
Seems like a great idea. When you look at many ML frameworks half the code and learning overhead is data schlepping code and table like structures that "reinvent" the schema that already exists inside a database. Not to mention, there can be security concerns from dumping large amounts of data out of the primary store (how are you going to GDPR delete that stuff later on?). So why not use it natively where the data already is?

For anything substantive it seems like a bad idea to run this on your primary store since the last thing you want to do is eat up precious CPU and RAM needed by your OLTP database. But in a data warehouse or similar replicated setup, it seems like a really neat idea.

Yeah — seems like all you need is Snowflake-esque separation of storage and compute and bob’s your uncle.
It looks like this is running Python to do the actual ML?

There’s nothing stopping you from reading database table structures directly into memory in Python or R now. You don’t need an intermediate data store.

I agree that running training on production instances would be a bad idea. First, you need to denormalise data for ML, and secondly you typically don’t want your training data to be constantly changing.

This is really cool, running ML workloads on top of SQL is a very practical way of doing ML for a lot of businesses. Many companies don't have the fancy ML workloads like you see at OpenAI, they just have a SQL database with some data that could greatly help their business with some simple ML models trained on it. This looks like a nice way to do it. A slightly different approach that I've been working on involves hooking data warehouses up to Pachyderm [0] so you can do offline training on it. Not as good for online stuff as this, but for longer running batch style jobs it works really well.

[0] http://github.com/pachyderm/pachyderm

Can this be used to deploy an "active learning" model that learns from fresh data and model auto-updates?
That's exactly the target use case. Models make online predictions as part of Postgres queries, and can be periodically retrained in a cadence that makes sense for the particular data set. In my experience the real value of retraining at a fixed cadence is so that you can learn when your data set changes, and have fewer changes to work through when there is some data bug/anomaly introduced into the eco system. Models that aren't routinely retrained tend to die in a catastrophic manner when business logic changes, and their ingestion pipeline hasn't been updated since they were originally created.
Do you plan on adding support for managed PostgreSQL services like RDS in the future?
We can’t control the extensions RDS allows to be installed, and they are historically conservative. Lev and I do have some fairly extensive experience with replication patterns to Postgres instances running in EC2. Foreign data wrappers are also an option, and depending on workload may be a good horizontal scaling strategy in addition.
Amazon Aurora already has ML extensions for connecting to AWS SageMaker and AWS Comprehend. I doubt they'd add another, especially one they didn't write and isn't integrated with their existing lineup. https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide...
How do you deal with different dataset train/validation/test? How do you measure the degradation of the model? Is there any way to select the metric you target (accuracy, f1-score or any other)?
The data split technique is one of the optional parameters for the call to ‘train’. Model degradation is a really interesting topic, that is hopefully made less difficult when retraining is trivialized, but we also want to add deeper analytics into individual model predictions, as well as better model explanations with tools like shap. We haven’t exposed custom performance metrics in the API yet, but we’re computing a few right now and can add more. The next thing we may build could be a configuration wizard to help make these decisions easy based on some guided data analysis.
This is great! FYI for those who haven't seen, BigQuery can also run statistical learning methods directly on your data as part of the query. Really cool to see ML going this direction.
Hello really nice !

Can you explane the differences with https://madlib.apache.org/ ? Wouldnt an OLAP db better suited than pg for this kind of workload ?

Does being a postgreSQL module make it compatible with citus, greemplum or timescale ?

OLAP vs OLTP will depend on your ML use case. Online predictions will likely be better served by an OLTP vs offline batch predictions being better served by OLAP.

OLAP use cases often involve a lot of extra complexity out of the gate, and something we're targeting is to help startups maintain the simplest possible tech stack early on while they are still growing and exploring PMF. At a high enough level, it should just work with any database that supports Postgres extensions, since it's all just tables going into algos, but the devil in big data is always in evaluating the performance tradeoffs for the different workloads. Maybe we'll eventually need an "enterprise" edition.

Can we offload model train to a different server? It can be parallelized? Anyway, nice API and a promising project.
This can be done "manually" by configuring Postgres replication and/or foreign data wrappers. We don't have a magic button for that, but if we have a few examples in the wild we can establish best practices and then put those into code. I say this with some optimism that we may be able to see more targeted ML specific scalability use cases that can be solved more completely than general database scalability.
Congratulations on the launch!

This is the most exciting ML related project I've seen in a while, Mainly because the barrier for entry seems low as anyone with PG database could apply a model on them using PostgresML if I understood the premise correctly.

Most of the comments here seems to regarding separating the compute from the database machine which it seems isn't possible right now with PostgresML, But the GitHub reads at the start:

> The system runs Postgres with the pgml-extension installed on port 5433 by default, *just in case you happen to be running Postgres already*:

  $ psql -U postgres -h 127.0.0.1 -p 5433 -d pgml_development
I think the second part needs to be clarified better, Is it installing PGML extension on a machine running a existing PG database and connecting to it (or) does it mean just starting the postgres session of the PGML docker package?
Great idea! I see this is implemented using the Python language interface supported by PostgreSQL and importing sklearn models. I always wonder how scalable this is considering the serialization-deserialization overhead between Postgres' core and Python. Do you see any significant performance difference between this and training the sklearn models directly on something like Dataframes?
This is an interesting benchmark I'll try to code up. Although, it seems a bit like an apples/oranges comparison, since a Dataframe in memory had to come from somewhere, either a CSV or database like Postgres, in which case I have my money on Postgres outcompeting the standalone process parsing CSV.

In the end though, it'll be important to have benchmarks for all the key steps in the process, both in terms of memory and compute. Off a hunch, I think the memory inefficiency involved in high level pandas operations is more likely to be a driving force to move operations into lower layers, than CPU runtime.

This looks amazing!

The animated GIF on your homepage moves a little bit too fast for me to follow.

Interesting concept, but I think Big Query ML [1] has been providing similar features for years now. Curious to learn what are the differences, other than offering this as a Postgres plugin.

[1] https://cloud.google.com/bigquery-ml/docs/introduction

I don't understand 5he example on the homepage. How does the extension know what is "buy it again"?
"Buy it again" is simply a name for the PostgresML project that the model is being trained for.

There is deeper explanation in the README: https://github.com/postgresml/postgresml

never mind. figured it out from github. this is cool.
Reminds me of https://riverml.xyz/latest/ (which is awesome) but the idea is even better because it skips all the copying and preprocessing yak shaving. Can't wait to kick the tires!
Cool approach. This nicely fits in the trend of SQL-as-much-as-possible because that makes it just a tiny bit more accessible. Definitely going to play with this in the next few days. (edit:) Being able to get training data from a SQL view is by far the nicest. Keep it up!
I feel like a lot of issues out of ML systems came from the fact that some person got a CSV dump of the data and then iterated for a month to build a fantastic model, which nobody knows how to integrate with the DB.

So, this is why I really like this idea and about 3 years ago I seriously thought about starting this thing as well. I went ahead and built a specific data company (so not a tooling one) and now I don't like this idea anymore.

To me this is a lot like proposing: "lets get rid of Rest Apis and Graphql and connect the frontend directly to the DB". (ignoring security issues for a bit).

In frontend: The view you like to display your data is a different one than how it should be saved. Exactly the same in ML, the view your data can be trained / predicted on is a very different than it should be stored.

They are connected, but IMO there always has to be a transformation layer. (and Python is just a much better way to do that transformation, but that's an other story)

Neat project. Any roadmap for cross-validation support (GridSearchCV and friends)?
Yep! traditional hypertuning techniques and automated broader surveys across multiple algos are both on the roadmap for "soon".
Landed a PR to enable GridSearchCV and RandomSearchCV. https://github.com/postgresml/postgresml/pull/83
Sql to rule the world! Now just Sql to create GUI and websites and I'm
This is awesome. I’m guessing the models are executed on the database server and not a separate cluster? What about GPU training? How is that handled? I’d love to see more docs.
This is great - will be experimenting with one weekend soon...
I wonder if/how a PostgreSQL plug-in can provide an optimal mix of computing and storage resources for varying machine learning workloads.
Stateful services like Postgres cannot be rapidly scaled up/down to adjust to daily loads, even when those loads are very predictably busy at noon, but often sit idle overnight. Scheduling ML jobs while the db is typically wasting resources might be an efficient strategy.
Anyone care to reframe the question or improve it?
How this compares to https://mindsdb.com/
Is this a competitor to bigquery autoML or to something like kubeflow?
This looks awesome! I’m not an expert but wouldn’t the typical database hardware not be really optimal for running ML? Is this meant to run on a replica (which is quite straightforward to setup) that has ML optimised hardware?
It’s probably a stretch to run GPT-3 inside a db, but most of the “deep learning” models I’ve run in more traditional environments are a few megabytes. That’s millions of params, but Morre’s law has been generous enough to us over the decades that I think there is a good case to spend a few megabytes of DB ram on ML. I would think this idea has really landed though, when we start hearing about Postgres deployments with GPUs on board though :)
Very cool! Will probably use it soon.
Thanks! We’d still consider this an early stage project and would love your feedback for which features to prioritize. Our roadmap is only getting longer…
is it possible, or how hard is it, to plug in custom proprietary models?
Do I need to know about ML/statistics to interpret the results?
WHY!?
What affiliation does this have with PostgreSQL?