A New Era for Open Climate Fix’s Technical Infrastructure

The Classic Start-Up Stack

Since the start of Open Climate Fix, myself and other members of the technical team have worked across a large variety of projects, producing a number of exciting innovations that have had practical, real-world impact in accelerating the energy transition and reducing carbon emissions.
As with any start-up, the technical infrastructure behind these projects was a complex layer of the work of numerous colleagues. As a company grows, understanding of the domain deepens, and priorities change. The stack is then modified and shifted to support these changes. If it isn't built from the start with a layered approach using proper separation of concerns, adding these in can result in a convoluted mishmash of code.
As is common with software products, the core of the service was effectively a database with an application-specific model, and a suite of tooling on top of that to handle internal access and business logic. All fairly standard stuff, and a classic choice for a startup wanting to get from zero to production in as little time as possible.
Over the past six months, I set myself the task of dramatically improving our technical infrastructure; make it quicker, make it sturdier, make it easier to use: a backend fit for purpose. All this with the aim of speeding up efficiency across our backend, helping deliver our current clients improvements such as faster access to historic forecasts, more accurate historical data, richer forecast details and forecasts delivered sooner.

Improving Performance

Raw SQL is “ORMless”

I'll start with the Object-relational mapping (ORM). They are very helpful in certain scenarios: for instance, they can make building a database-backed app much easier for developers that don't have database or SQL experience; furthermore, they are useful in the instance where swapping out database types regularly is a requirement (say testing with SQLite, and deploying with Postgres).
However, the very abstractions that make these behaviours possible can, at a certain level of usage, serve to work against the ORM - especially when it comes to single-database usage.
Firstly, they can prevent the leveraging of some of the more powerful, highly specific functionality of the database in question, due to lack of support. As an example, COPY FROM - the fastest bulk insert method in Postgres - isn't natively supported in SQLAlchemy (at time of writing). Using the query language of the chosen database directly will most always allow for faster queries, due to this ability to exactingly employ the power of the database engine itself.
Secondly, the abstractions can reduce the cognitive load to such a degree that the writer is prevented from thinking about things that, in actuality, should be carefully considered; such as transaction and session management, query/index alignment, and even migrations. Relying on the "automagic" nature of an ORM to handle these concepts seems easier at first, but being thoughtful about them from the get go will save headaches later.
Thirdly, the abstractions can make it much more difficult to debug and analyze database performance. A raw SQL query can be examined easily with query analysers: both in-database, and via external visual tools; and in this manner performance can be easily monitored, queries tweaked, and indexes improved. With an ORM, the SQL being run is hidden from the developer, and it can be hard to understand from the ORM code exactly what the produced SQL will even be - it has to be extracted before it can be investigated.
Finally, raw SQL is highly readable, and very searchable for online help! Far from something that should be avoided due to any perceived complication: it is simple, explainable, and universal (database-specific flavours aside). In fact, I found during my rewrite that the PostgreSQL query for one specific use case was around fifty lines of code, whilst the SQLAlchemy code for the same functionality was six times that.

Python should Go

Aside from the ORM, there was a hit to performance arising from the usage of Python. Python is a widely used programming language which is normally pretty essential when machine learning is part of a product. Python wasn’t itself the whole issue: it can be written very well, and the new tooling coming out around it is really helping to normalize quality Python - plus, it's impressively fast for certain applications.
However, when Python isn't written with production in mind, it can serve to be a liability if thrust into that role. If an issue arose, it could have had a severe impact on the quality of the product and service. To a degree, this is the fault of Python: because it's so easy to produce, it's possible to write and deploy services without having to think about any of that. And don't get me wrong: that's great! It makes coding and deploying software accessible to a wider audience; to developers with experience in all different sectors. It also allows small, bootstrapping organisations to work on creative solutions with relative flexibility.
However, as our organisation grew, we moved from a more experimental phase into a successful, production focused company where crucial services became central to our core product. Python no longer served our needs or priorities in the place we had arrived at. I believe core services such as these should, wherever possible, be written in a statically typed, compiled language for the following reasons:
  1. They will almost always be faster than Python (or at least will have a higher speed ceiling),
  2. This type of language will eliminate a whole suite of bugs at compile time
  3. Stability
Not to mention, due to their more common usage as production languages, they might be easier to hire more specialised roles for (at least in my experience!). So, with the ease of understanding of a mostly Python development team in mind, and the knowledge of the ORMless capabilities of sqlc: Go seemed the obvious best choice for any rewrite. Interaction with existing Python services could be handled easily enough via a language-agnostic gRPC API.

Storage is key

One of the most impactful changes we made to ensure better efficiency and speed was reducing the storage footprint. A drop from 710 GB to 36 GB means that our new data platform in its entirety, is smaller than just the primary index of the old system - and that’s with more sites included, and a more detailed picture of the forecasts.
I achieved this storage reduction through a few different methods. Firstly, to help make Postgres better able to compress the forecast data, each forecast is now saved as a single array of values, rather than thousands of separate rows. Since the forecast values progress smoothly, this data compresses very effectively. Secondly, in the old system, around 53% of the storage was for the indexes (indexes are the lookup structures a database uses to find data quickly). I implemented a much tighter set of indexes, that fit an optimal set of redesigned queries, and removed all the old, bloated ones. Further to these improvements, our forecast values are now saved with reduced bit depth, to better reflect their precision, and these faster queries have removed the need for cacheing tables, again saving on storage.

Results & Impact

  1. Storage: 710 GB became 36 GB. The whole new platform is smaller than the old system’s index on its own (about 53% of the old system was index). It also holds more sites and more detail per forecast.
  2. Read speed:
    • Data older than a week: 5 min 25 s became 0.03 s, about 10,000× faster.
    • Last 7 days: 1.2× faster. That’s despite the old system carrying 12 GB of extra indexing built specifically to speed up recent lookups.
    • API and database caches are no longer needed.
  3. Write speed: 150× faster per forecast. Records written per day fell from about 16 million to about 158,000, at current volumes.
  4. What it means: lower running costs (one system instead of two), all history equally easy to query, more accurate answers, and room to grow, as we now have the infrastructure to expand our forecast 100,00s of homes across GB as well as support industry in a more secure, efficient and expansive way.

Join our mailing list to keep updated