How to improve ingest process?

[vgeorge]

I've finished ingesting Malawi final results. The process was not as smooth as expected, I only managed to complete it after three attempts. The first two were interrupted after hours of processing, then I had to restart from scratch.

The errors were probably caused by a network failure or because my computer hibernated, there was no specific error message. The process was through after I setup the computer to stay awake on system preferences. Total time of import was approximately six hours.

I think we might need to refine the ingest process for the next country imports, as it takes several hours and is very susceptible to connection problems and other issues. Maybe having an intermediate table for scenario results, or to pre-process source files into PostgreSQL-ready CSV files to be ingested with COPY instruction.

[vgeorge]

Now that we have received new (and larger) country results it seems that PostgreSQL may not be the right database for GEP. Uncompressed scenario files for Mozambique have approximately 1 GB each. Importing a model with 150 GB in size is impractical in the current way the CLI works.

A CLI refactor improve something, but I suggest migrating from a monolithic (PostgreSQL) to a NoSQL database (Bigtable, DynamoDB, MongoDB), which allows horizontal growth and have more efficient ways of storaging documents with varying structure, such as model results.

[vgeorge]

We discussed and decided to optimize PostgreSQL usage instead of migrating to another DB. Ingesting a very large dataset with the current CLI was feasible (thanks to @danielfdsilva) by setting up a ECS instance to avoid common interference of desktop computers.

Mozambique is up and a scenario takes about 30-50 seconds to load, sometimes incurring in timeouts. Checking the docs I've found out that JSONB property types can be indexed:

https://www.postgresql.org/docs/9.6/datatype-json.html

JSONB has a higher conversion overhead, so ingest time will increase.

The filterValues property are already JSONB, but summary don't. As every scenario query applies a filter by summary, I suspect we can improve response times considerably by using indexes.

What we need to do here:

• Create migration to convert summary from json to jsonb;
• Create migration to add a GIN index and update WHERE clause expression if needed (see docs for caveats);
• Test and deploy.