@schana 0.5TB of disk for a single VM is a very large storage request. This looks like it is about 2x your expected need based on the data sizes you have described.

I'm also wondering if you have to have Postgres or could use MariaDB instead? We have shared database servers that may be able to hold your dataset. We actually have a shared Postgres instance on bare metal as well that is less promoted.

@bd808 This is not a "forever" project; it's more to surface intermediary results while developing the recommendation algorithm. The plan is to eventually have the dataset be accessible through production infrastructure.

The size of the request may be able to be trimmed, but I'm not confident the data will fit in 256GB. Some additional space is needed for holding the compressed dataset to load into postgres.

I'm not sure what using MariaDB entails, but there is likely to be a fair amount of re-loading and re-indexing occurring during development. There's nothing particularly special about the dataset that necessitates postgres.

If this is ultimately going to be offered as a shared data source, MariaDB is a much more likely backend than Postgres. We have bare metal MySQL/MariaDB databases in Cloud Services that can be used by Toolforge tools and other VPS projects. The "ToolsDB" server is meant for end-user data like this that is not a wiki replica. Reindexing frequency shouldn't be too much of a problem there although the db server is a shared resource so pounding it incredibly hard can cause issues.

@bd808 I created a database s53132__trex_p, but it seems that tables are limited to 64 indexes. Is there a way around this limitation besides exploding the data into its relational equivalent?

@schana That would be a question for the DBAs I guess. My first reaction is to wonder if you really need more than 64 distinct indexes on a table. That's a awful lot of indexing.

@bd808 Yes, it's a lot of indexing, but the table has records for every wikidata item and columns for every wiki, with the values being predictions that a particular item should exist in a given wiki. For performance reasons, sorting is necessary on every column. I could expand the data to be relational with tables per wiki and do joins, but that complicates the ingestion that will be somewhat frequent during the development of the algorithm.

64 secondary indexes per table is a MySQL InnoDB limit -- https://dev.mysql.com/doc/refman/5.7/en/innodb-restrictions.html

@schana Are you still blocked by the InnoDB index limit? Do you want to re-examine the need for a custom instance to host your own DB server? Disk is the most heavily over subscribed resource in the Cloud VPS environment, so figuring out how to get your total requested disk down would be very helpful.

@bd808 I'm moving this to paused for now, as there aren't available resources to drive the task. For the future, if we still want to load the data into MySQL, it can be restructured to avoid the index limit.