Investigate Options to Reduce Hosting Costs

[entrotech]

Overview

We need to investigate ways to reduce the recurring monthly expenses for the Food Oasis project, since it currently about $120/mo, and absorbed by personal in-kind contributions from Bonnie Wolfe and John Darragh.

Action Items

• [x] The bills for Dev 2023 are shown below.
• [x] On 1/24/24, John Darragh dropped the AWS database that we hosted in the AWS East region. This appeared to be left over from either earlier experiments with the devops Community of Practice looking to hosting all of Food Oasis on AWS, or perhaps from moving the development server to the US West 2 region. This should reduce the AWS bill by about $18/month, starting in Feb 2024.
• [x] On 1/24/24, John Darragh also released (i.e., deleted) the Elastic IP Address service in the Ohio region, which was part of of the devops COP experimentation, which should further reduce the AWS bill by another $7.44/mo.
• [x] The above two measures should reduce AWS total charges from $80 / mo to about $54 / mo.
• [ ] The $13.80/mo Amazon Relational Database Storage charge is for disk memory to support the databases, which are set to the minimums for the respective database instances, 100 GB for the prod database and 20 GB for the dev database.
• [ ] Heroku hosts the docker containers for both DEV and PROD web applications. At $7/mo, for each environment, this is already the lowest paid tier. We have tried the free tier, but as I recall, the free tier uses dynos that "sleep" after a period of inactivity, and take 20 seconds or more to wake up when a request is made. This is not acceptable for a production environment, since users will typically abandon a request if the first response takes longer than a few seconds. I believe we started out with the free tier for the development environment, but found it too flaky to support development (but we could revisit this if we want).

AWS database charges are the largest cost. Some ways to reduce costs are:

1. Host both PROD and DEV databases on a single RDS instance. this will reduce charges to the current cost for the PROD database (eliminate the db.t3.micro instance charge, and about $2 of the database storage cost). This should reduce total AWS charges by $15 / mo to $39 / month.
2. The above measure would result in a single database server db.t2.small instance, for $26.78 / mo. This is an On-Demand instance, which is paid monthly. By paying for this as a Reserved instance instead, this price can be reduced by about 30% by committing to one year at a time, or 50% by paying for three years at a time, reducing the total database instance cost by about $13.50/ and make the total AWS cost about $26 per month.
3. All this gets the total AWS cost down to about $26/ mo, which is pretty reasonable, but we might be able to use a "micro" instance to host both database, which would further reduce the database instance and storage costs by another 50% or so to about $10-12/mo, which is the absolute cheapest database AWS offers.

@Bonnie pointed out that we could split the client application into two separate React apps for the Food Seekers and Volunteers, respectively. There are a few good reasons to consider this, one of which is to reduce the load on the database, allowing us to use a smaller database instance. We should at least make the changes 1 and 2 mentioned above, bringing our total AWS costs down to about $26 / month, so splitting the app might save us as much as $13 /month.

Resources/Instructions

Monthly AWS cost: ($83/mo)

The database cost further breaks down by instance and storage charge (December shown here)

Heroku ($14 /mo)

SendGrid ($20/mo) billing_invoices_8a1287778c190525018c26923be75ec4.pdf

[entrotech]

I moved the DEV database on to the same server as the PROD database. This has some downsides:

1. To isolate DEV database logins from having access to the PROD database (and conversely), I had to create separate database user accounts with a fairly complicated setup per this script:

REVOKE ALL ON DATABASE foodoasisdev from public;
REVOKE ALL ON DATABASE foodoasisprod from public;

CREATE USER devuser with encrypted password '<redacted>';
GRANT USAGE on SCHEMA public TO devuser;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO devuser;
ALTER DEFAULT PRIVILEGES IN SCHEMA public 
	GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO devuser;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO devuser;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE ON SEQUENCES TO devuser;
REVOKE ALL ON DATABASE foodoasisprod from devuser;
GRANT ALL ON DATABASE foodoasisdev TO devuser;

CREATE USER produser with encrypted password '<redacted>';
GRANT USAGE on SCHEMA public TO produser;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO produser;
ALTER DEFAULT PRIVILEGES IN SCHEMA public 
	GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO produser;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO produser;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE ON SEQUENCES TO produser;
REVOKE ALL ON DATABASE foodoasisdev from produser;
GRANT ALL ON DATABASE foodoasisprod TO produser;

This was very difficult to figure out and likely to cause confusion for anyone attempting to maintain the databases in the future.

2. Backups are performed at an instance level, so both databases are backed up to a single backup file. If it proves necessary to restored from backup, we don't have the option of restoring one of the databases easily. Instead, you would need to restore both to a new temporary instance, create a snapshot of the one you want to restore, restore that to the permanent instance and then delete the temporary instance. Not a big deal.
3. Performance of either database can be affected by the other, since they are no longer on isolated instances (separate virtual machines), so share memory and CPU resources.

[entrotech]

Also, I upgraded the Postgres version of both databases from 11.22 to 15.5 (required since support for ver 11.22 is being retired), and changed the database instance from a db.t2.small (which does not support Postgres versions newer than 12.*) to db.4g.small), which is slightly less expensive at $0.034 / hr vs the t2.small instance price of $0.036. After the partial month of Feb 2024, the monthly charges should be less than $39/mo.