I have been a MySQL user for years, including owning a few multi-gigabyte databases for my websites, and using it to host algebra.com (about 12 GB database).
I have had my ups and downs with MySQL. The ups were ease of use and decent performance for small databases such as algebra.com. The downs were things like twenty hour REPAIR TABLE operations on a 35 GB table, etc. Right now I have a personal (one user) project to create a 5-10 Terabyte data warehouse. The largest table will consume the most space and will take, perhaps, 200,000,000 rows. I want to use it to obtain valuable business intelligence and to make money. I expect it to grow, never shrink, and to be accessed via batch queries. I do not care for batch queries to be super fast, for example an hour per query would be just fine. However, while an hour is fine, two weeks per query is NOT fine. I have a server with about 18 TB of storage and 48 GB of RAM, and 12 CPU cores. My initial plan was to use MySQL, InnoDB, and deal with problems as they arise. Perhaps, say, I would implement my own joining procedures. After reading some disparaging stuff about InnoDB performance on large datasets, however, I am getting cold feet. I have a general feeling that, perhaps, I will not be able to succeed with MySQL, or, perhaps, with either MySQL and Postgres. I do not know much about Postgres, but I am very eager to learn and see if I can use it for my purposes more effectively than MySQL. I cannot shell out $47,000 per CPU for Oracle for this project. To be more specific, the batch queries that I would do, I hope, would either use small JOINS of a small dataset to a large dataset, or just SELECTS from one big table. So... Can Postgres support a 5-10 TB database with the use pattern stated above? Thanks! i
