Scaling PostgresML to 1M Requests per Second | Hacker News Reader

Scaling PostgresML to 1M Requests per Second

postgresml.org

177 pointsbubblehack3r30 comments

What is a good algorithm-to-purpose map for ML beginners? Looking for something like "Algo X is good for making predictions when your data looks like Y," etc.

This maybe? https://scikit-learn.org/stable/tutorial/machine_learning_ma...

This is what I would have replied with too.

xgboost. always xgboost. it will scale all the way from college kaggle problems (where it is the top performer almost always) to cloud scale.

xgboost is one of the few frameworks supported by Sagemaker, etc

Vowpal Wabbit is not the best anymore, but it is incredibly simple. You train it by piping text files in, then pipe your input into it for predictions.

Tsk to whoever downvoted this. Simple linear models are indeed the right starting point for most new projects while you come to grips with your data.

In some cases you can stop there or apply a quick nonlinearization like Fastfood to get good, snappy, and generally debuggable results for very little RAM.

In other cases you move on to decision tree ensembles or neural networks, depending on whether you already have features or need those to be learned, too. Either way this ratchets up the complexity and resource requirements.

Decision trees in particular tend to have bloated implementations. I still use XGBoost or Scikit for training, but wrote my own library to translate the models into a more efficient format (~95% smaller than Scikit) and have thread-safe inference.

Thanks for the reply! What is Fastfood though? I can't find anything on Google.

Of course. The paper is at https://arxiv.org/abs/1408.3060.

> Our method applies to any translation invariant and any dot-product kernel, such as the popular RBF kernels and polynomial kernels. We prove that the approximation is unbiased and has low variance. Experiments show that we achieve similar accuracy to full kernel expansions and Random Kitchen Sinks while being 100x faster and using 1000x less memory. These improvements, especially in terms of memory usage, make kernel methods more practical for applications that have large training sets and/or require real-time prediction.

Sadly Fastfood didn't quite make it into Scikit[1], but did land in scikit-learn-extra[2].

1. https://github.com/scikit-learn/scikit-learn/pull/3665. A shame, Scikit's equivalents scale very poorly.

2. https://scikit-learn-extra.readthedocs.io/en/stable/generate...

4 load balancers with local caches + 5 replica databases.

I don’t want to discredit the achievement but the 1MM/sec seems less exciting when you learn they horizontally scaled the architecture.

Especially when SQLite, in a client/server config (BedrockDB), achieved 4MM/sec from a single server.

https://blog.expensify.com/2018/01/08/scaling-sqlite-to-4m-q...

I'm not sure the two are comparable—OP's article is about 1 million ML predictions per second, while your link is about 4 million queries per second.

Thanks for pointing out the difference.

Genuine question: is a ML prediction slower than to fetch a row from a database and/or more difficult to scale?

It's slower than a row lookup.

Specifically, this model is an ensemble of decision trees.

This involves

1. Row lookup

2. N (tree depth) inequality checks on fields in the row for M trees

3. Weighted sum over M trees

In this case, they are talking about xgboost inference, which can verge on the hungry side of things, but this is no match for deep learning, although word embeddings and transformers are supported[1] by PostgresML regardless. GPUs are supported[2] for Transformers as well as xgboost, so this approach is also kind of future-proof in the sense that you're not going to ever be limited by your compute, as well as the size of your replica set. But now, yes, to answer your original question— yes, for the most part typical regression-type task inference is marginally slower than fetching and involves crossing the process boundary, can't be easily parallelised, and so on. Hopefully, this is something that could be dealt with on PostgresML side, as Postgres exposes some neat parallelism primitives AFAIK. COMBINEFUNC is how you make your aggregates parallel and with SERIALFUNC potentially multinode-friendly, although here I'm not sure as I haven't maintained custom aggregates over a high-availability Postgres cluster... At any rate, this is a clear area of future research; now that there's inference, how to best parallelise it in the model of Postgres.

[1] https://postgresml.org/user_guides/transformers/setup/

[2] https://postgresml.org/user_guides/setup/gpu_support/

Can you train xgboost on a GPU and use that model to make predications on the CPU?

Sure!

From the article:

> XGBoost cannot serve predictions concurrently because of internal data structure locks. This is common to many other machine learning algorithms as well, because making predictions can temporarily modify internal components of the model.

I wonder if finer-grained mutex wa or other concurrency protection types covering interior components would be an option.

Speaking in generalities rather than specifically about Postgres/PostgresML, this is how you should think about speccing hardware to solve these kinds of problems:

A DBMS that does mostly point-query reads — fetching single "cold" rows from a large storage, with high concurrency — is an IO-bottlenecked workload. Given a sufficiently mainframe-y "DMA from disk straight through to the NIC" architecture (which modern microcomputer servers are rapidly approaching), the CPU is just "guiding" that transfer — acting as a PCIe switchboard operator — rather than actually performing it. The only things you need to worry about are JBOD IO-queue depth and (multi-aggregated) NIC total ring-buffer size. This means that you can go very far with "vertical" scaling for these workloads, in the sense that you can shove more disks and NICs into the same machine and it'll be able to serve more RPS, without really increasing CPU load. For IO-bottlenecked workloads, you only start to suffer when your PCIe bandwidth is exhausted. This is why systems built for "SAN" or "DB" or "hyperconverged" use-cases like this always use dual-socket lower-cores-per-socket CPUs (lately: dual Epyc 7532s) rather than single higher-core-count CPUs — as each socket gets its own PCIe channels, so the lower-powered dual-socket CPUs actually maximize PCIe bandwidth per machine.

Meanwhile, any kind of ML prediction is going to be a CPU- (or maybe GPU-) bottlenecked workload. If you have 64 CPU cores in a machine, then you can only be doing 64 ML predictions at a time on that machine. You might be able to resolve any individual prediction "quickly"; but unlike an IO-bottlenecked workload, that "quickly" doesn't involve the worker thread ever blocking + yielding the CPU to other workloads while waiting on anything; it's solid 100%-pinned CPU-cycles on that core, all the way through the prediction. There is no advantage in oversubscribing the machine with workloads; running KN predictions across N cores makes each query take (slightly more than) K times as long to run. You may as well just make each machine the smallest size it can reasonably be to run the model (as each machine then gets its own IO memory and IO "breathing room", and you avoid the price-premium of using latest-gen parts), stamp out horizontal replicas of it, and load-balance between them.

This is why so many phones and other low-power devices are shipping with "AI/ML accelerators": putting ASICs in, tuned to run specific ML-model architectures faster, can vastly lower the power budget for the device as a whole, as running (concurrent) models on the device would otherwise have to ramp up the CPU to its highest frequency and pin it at 100% to do one of these predictions.

RAM and the Postgres shared buffers, as well OS page cache can also be important factors when there is an active working subset, e.g. the active sessions on a website might be reused hundreds of times per session, with only relatively small portion of them active at any time.

Shared RAM across all cores can be a reason to use fewer larger machines rather than more smaller ones in such a case. Postgres does give you options either way though.

Yup. In both cases, I was presuming a worst-case workload — e.g. an OLAP workload where you have to read all the data in the DB to answer a query, and "all the data" is far larger than RAM; or 10k concurrent queries from different tenants who all care about different things. That's really where bottlenecks will hit you. "More RAM" does help when your problems aren't worst-case problems. :)

For perspective, that single server is - 1TB of DDR4 RAM - 3TB of NVME SSD storage - 192 physical 2.7GHz cores (384 with hyperthreading

And AWS vCPUs = SMT threads, or Hyperthreads(tm) in Intel speak for anyone comparing to the machines described in the PostgresML post. (Which were answering ML ops, not simple DB queries)

Sql Server + R 6 years ago, scoring transactions at 1M/sec https://blog.revolutionanalytics.com/2016/09/fraud-detection...

Right, if we're allowed horizontal scaling, 1M instances, 1request/s would do the trick.

I haven't heard of PostgresML before today. How does it compare to Feast?

Feast, for the most part, is a feature store that serves up features to machine learning models for inference or training. PostgresML is closer to Big Query ML. They provide the framework to building, deployment, and inference of models from within the database - using extensions for PostgreSQL. This allows analysts and other users to build models off primarily tabular data without needing to figure out the whole deployment mess.

We have used PostgresML to a great success albeit in a very limited setting.

The whole philosophy of pushing Postgres to the absolute limit of what is possible is very attractive; think what Timescale[1] does for time series, and what PostgresML does for machine learning by eliminating the need to perform any kind of ETL jobs to cut down on the latencies as to how late the data is consumed after it's initially produced, et cetera. The very fact that we can now train and deploy regressions/classifiers using nothing but a couple views and timely SQL calls is already a fairly attractive proposition, however I'm really looking towards further adoption, library support, case studies and stuff like that.

For example, I've been considering how Timescale could be used together with PostgresML to provide a single way to treat data from the point of consumption. In fact, I've reached out to Timescale Cloud on this very matter, and was sad to learn that they can't support PostgresML as part of their Cloud offering due to some security considerations related to it being a Python extension. Self-hosted it is. At any rate, they have some time back introduced what they call continuous aggregates[2]; a materialised view on top of a hypertable that doesn't require explicit refreshing, and is in fact realtime-accurate due to some waterline logic; it performs a normal view-like query for the most recent bits while retaining the materialised component in the compressed partial form. (Normal time-based compression and retention policies apply like they would to any other hypertable which is how the continuous aggregate views are implemented under the hood.) The idea here is that you can potentially reduce hundreds of million data points to a set of particular continuous aggregates within respective time frames; downsampling of past data is something you get for free. This is where I think the value lies for PostgresML; using a continuous aggregates for continuous training and as a store of historical predictions that you would normally want to keep separate from the training set itself. This could prove a reliable way of feeding new data for re-training along with downsampled selection of past data, and comparing various predictions on similar data points over time to keep track of how it goes. The data is compressed away while readily available and as long as it doesn’t have to change (historical predictions apply) the data points in the materialised component are never going to be materialised more than once.

There are limitations[3] to this approach, of course, however they can be circumvented.

If you're interested in this, you should check out some of the “hyperfunctions” they have to offer such as histogram[4] which is what I’ve had the pleasure of using previously to distil some of the time series data into a fixed-size feature vector, and naturally this would be a good place to start if you’re ever going to seriously consider this as something that you yourself would want to use for similar purpose. To me this is a natural step forwards in the data lifecycle/ supply chain approach. First, you get rid of ETLs as such by adopting PostgresML, and next you specify the “contract" of how your data is going to be produced, reduced, distilled, modelled, sampled, and ultimately evaluated— over prolonged periods of time and multiple iterations of the implementation.

[1] https://docs.timescale.com/timescaledb/latest/overview/core-...

[2] https://docs.timescale.com/timescaledb/latest/how-to-guides/...

[3] https://docs.timescale.com/timescaledb/latest/overview/limit...

[4] https://docs.timescale.com/api/latest/hyperfunctions/histogr...

Fascinating. Have you written any more on this? I’ve started using timescale and am keen to push as much as possible into the database, and currently the approach to ML requires us to extract huge amounts from the DB and is a significant bottleneck.

Not really, we've applied PostgresML in the charity space (we're the first data-driven charity in Ukraine) and we have been using Timescale for other things, however we haven't yet figured out how to connect all the dots. I'm going to talk to Timescale's technical team as well as PostgresML people on this subject next week. Hopefully, we can build some understanding as to how much this can be pushed as the use-case is simply too attractive to let pass; at least whenever you have the luxury to engineer the data flow bottom-up... If you're interested, we can stay in touch, or do a group/mailing list to explore this further. I have a feeling that we're sitting on a proverbial gold deposit here, in data integration terms :-)

Strongly agree, there is a compelling collection of technologies there, and I'd be very keen to keep in touch. I'm hoping to spend December working on how these could fit together for a very different use case (I'm in a research institution in Australia looking at energy and renewables). My email is ben dot weise at gmail dot com, feel free to hit me up :).

Reply on news.ycombinator.com