Need help deciding best MariaDB storage engine for our use case and server hardware limitations [closed]

Solution 1:

Engine: InnoDB. Period. (Sure, 1% of use cases are better off with something else, but yours does not seem to indicate the need for a different engine.)

Snowflake: Terrible, especially if you need to search on a "range". Please provide the schema (preferrable via SHOW CREATE TABLE); I will be more specific. (Then I may agree that Snowflake is good, but I doubt it.)

Star schema -- Good. Normalizing common strings: good. Normalizing 'continuous' values (dates, ints, floats): bad. But the purpose is to save disk space, hence speed up some queries.

10GB/year -- that sounds like a "few" rows per second on average. Heavy, but not terribly heavy. That is, the ETL processing does not sound like you need help.

Data Warehousing -- http://mysql.rjweb.org/doc.php/datawarehouse

Purge old data -- This is one of the few uses for PARTITIONing. http://mysql.rjweb.org/doc.php/partitionmaint

Splitting into separate tables that are kept online -- likely to be a hassle but with very little benefit.

Costly reports --> Summary tables http://mysql.rjweb.org/doc.php/summarytables Summary tables are much smaller than a Fact table; it is even acceptable to denormalize.

Columnstore -- One big plus is the significant compression it gives. But I don't see your 50GB as being very big. Another benefit of CS is automatic "indexing" of every column. However, only one column can be used for the two-level efficiency of lookup.

4 cores -- plenty for InnoDB; more cores would be useful for CS.

32GB RAM -- With only 50GB of data and 10GB/year -- If all you do is look at the latest year's data, 32GB is more than enough. If you frequently scan all 50GB, then there will be a lot of I/O. If you implement Summary Tables, then 32GB is overkill for most activities. (The Summary Tables might be under 10GB and go back to the beginning of the data; hence very cacheable.)

32GB + CS -- Your 50GB will become about 5GB. (But I don't know if the 32 will be overkill.)

HDD vs. SSD -- SSD is noticeably faster.

Bottom line (and budget) -- The techniques mentioned above can keep InnoDB on 32GB humming along fine for several years.