Implement table partitioning?

[vgeorge]

This relates to #47 and #66.

I believe there is a opportunity to improve response and ingest times by implementing table partitioning. PostgreSQL documentation describe it as "...splitting what is logically one large table into smaller physical pieces". As scenarios are independent from each other, partitioning of scenarios table could be done via scenarioId property.

To validate this approach, we would need to add migrations and update the CLI to create/remove scenario partitions after they are ingested/deleted. The API server wouldn't be affected because database queries would remain the same.

@danielfdsilva remembered that there is a lot of post-processing on the API server to calculate summaries after the data is fetched from the DB, so there might not be gain from this side. But I believe that the ingest process could benefit from it and the index size could be significantly smaller.

I'm tagging this as enhancement for a future development cycle, as it is not something straightforward to do now.

cc @olafveerman @EricSoroos

[EricSoroos]

I don't think that partitioning will help. The partitioned table approach is useful when you have a hot segment of a large table, or you have entries that rotate out on a schedule. (e.g., recent entries are heavily used, or time series with a specific retention period). You don't really have a natural partition key, as there are 9k scenarios, and you probably want O(10) partitions. You don't really have any way to use the additional tables to offload IO, because they're all going to be in the same tablespace/EBS drive.

This app is basically cold reads of a gigantic dataset. ( indexes are an order of magnitude greater than memory, the dataset is 2 OOM.) There will never be hot data, because we're caching the results.

There are two things we can do to improve retrieval speed -- make the data use less space, and make more index hits occupy the same page when reading. In the first case, each byte we save per record is 1GB of table space. We should convert the text numbers in json to 4byte real. (not numeric as I said before, that's still 25-300% more expensive than a real). We can get 4x there. That's less data to store, less to read from disk, and less to send to the client because it's post processing the data.

The other thing would be to cluster on scenario id, so that if we're reading one page, we probably want more records in the same page. It might help, it might already be that way due to the ingest. Clustering the table is an expensive operation, in part because it's going to take a ton of disk space and processing time, and we can't make the drive smaller again afterwards. We could test this, but I'd push that till after doing everything else possible.

I haven't looked at what's the bottleneck on ingest, but I'd put good money on being able to speed it up dramatically by using copy rather than insert. I'd also bet that it'll be way faster with the flattened storage, due to much less data going over the wire. But without looking at an ingest up close and seeing what the load is like, this is just a recommendation from a quick look at the code and a lot of PG experience.