Scaling PostgresML to 1M Requests per Second
We have used PostgresML to a great success albeit in a very limited setting.<p>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&#x27;s initially produced, et cetera. The very fact that we can now train and deploy regressions&#x2F;classifiers using nothing but a couple views and timely SQL calls is already a fairly attractive proposition, however I&#x27;m really looking towards further adoption, library support, case studies and stuff like that.<p>For example, I&#x27;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&#x27;ve reached out to Timescale Cloud on this very matter, and was sad to learn that they can&#x27;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&#x27;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.<p>There are limitations[3] to this approach, of course, however they can be circumvented.<p>If you&#x27;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&#x2F; supply chain approach. First, you get rid of ETLs as such by adopting PostgresML, and next you specify the “contract&quot; 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.<p>[1] <a href="https:&#x2F;&#x2F;docs.timescale.com&#x2F;timescaledb&#x2F;latest&#x2F;overview&#x2F;core-concepts&#x2F;" rel="nofollow">https:&#x2F;&#x2F;docs.timescale.com&#x2F;timescaledb&#x2F;latest&#x2F;overview&#x2F;core-...</a><p>[2] <a href="https:&#x2F;&#x2F;docs.timescale.com&#x2F;timescaledb&#x2F;latest&#x2F;how-to-guides&#x2F;continuous-aggregates&#x2F;about-continuous-aggregates&#x2F;" rel="nofollow">https:&#x2F;&#x2F;docs.timescale.com&#x2F;timescaledb&#x2F;latest&#x2F;how-to-guides&#x2F;...</a><p>[3] <a href="https:&#x2F;&#x2F;docs.timescale.com&#x2F;timescaledb&#x2F;latest&#x2F;overview&#x2F;limitations&#x2F;" rel="nofollow">https:&#x2F;&#x2F;docs.timescale.com&#x2F;timescaledb&#x2F;latest&#x2F;overview&#x2F;limit...</a><p>[4] <a href="https:&#x2F;&#x2F;docs.timescale.com&#x2F;api&#x2F;latest&#x2F;hyperfunctions&#x2F;histogram&#x2F;" rel="nofollow">https:&#x2F;&#x2F;docs.timescale.com&#x2F;api&#x2F;latest&#x2F;hyperfunctions&#x2F;histogr...</a>