Exploring the Limits of Row Insertion With Heroku
As seen in the above screenshot of an email that I received from Heroku, I had managed to insert 35,589 rows into my database over the course of the first 11 days in July. For a site that has multiple active users, I don’t think it’s too hard to imagine generating 10,000 rows over 11 days, but how is it possible that I was generating about 3200 rows per day when I am really the only user?
It all goes back to an automated process that I had set up using Heroku Scheduler. In order to accomplish my goal of tracking and saving exchange rate information for 249 countries, which between them utilized 168 unique currencies, I had a job scheduled to run 8 times per day. This job would fetch the exchange rates that I needed from fixer.io, and then take that information and create a few records, namely a Rate object with the current exchange rate for each of the 168 currencies that I was tracking, and then a joiner object that would connect the new rate object with the appropriate Country object. If it sounds confusing and inefficient, then I would have to agree, but for a time it worked. However, I was automatically creating at least (249 + 168) rows each time I ran the job, and so when running the job 8 times per day it comes out to 3,336 rows on a single day.
As I mentioned above, this did work for a time, and on my local machine there is certainly no set limit as to how many rows I can create in a month while developing, but once I deployed I reached the 10,000 row monthly limit in a few days, and because Heroku is kind enough to give a 7 day grace period to fix things once the row limit is met before they revoke the ability to insert new records into the database, I was able to surpass the limit by about 25,000 rows before my insert ability was gone, and my app was dead in the water.
So if I wanted my application to live on, I had to figure out a way to reduce the number of rows that I was regularly creating. I had a few ideas — first I thought of trying to transition out of storing any of the currency information, and simply querying the fixer.io API on the front end when needed, but doing so would limit my ability to obtain historical currency information that would be utilized for a graph that I was pretty attached to, and also there is a monthly limit to the number of API calls that can be made, so that method may have solved one problem while creating another. I also thought of abandoning the historical currency information altogether, but with that being a core part of the application, I felt that it was too big of a change to make. Ultimately, after a fair bit of research and hand wringing, I found my solution — and it all has to do with changing the data type that I was using to store the data.
For someone like myself, most of our data type needs can be met with just a few basic types — text, number, date, etc., but PostgreSQL, the database management system required by Heroku, also allows for a JSON data type. There are varying opinions [see resources below]on the practice of using this feature, but for my purposes I felt it was necessary. By creating a table called Rates, with an attribute called “rate list” where that list would be stored in JSON format, I would be able to store the result of my fetch to the exchange rate API as a single entry into the database — so effectively reducing the number of new records created every three hours from 417 all the way down to 1— a decrease of over 99%. From there, I am now able to query that Rates table and perform an object lookup with whatever currency code I need to get the newest Rate object.
Now obviously the problem was created by my own naiveté when I was setting up the whole thing to begin with, but I am satisfied with solution that I’ve reached. Feel free to check out the project on Github here: https://github.com/kennja05/trip-tracker-be, and the website here: https://trip-tracker.netlify.app/
Resources:
