Path: csiph.com!x330-a1.tempe.blueboxinc.net!newsfeed.hal-mli.net!feeder1.hal-mli.net!nx01.iad01.newshosting.com!newshosting.com!novia!news-out.readnews.com!news-xxxfer.readnews.com!news.misty.com!news.iecc.com!not-for-mail From: John Levine Newsgroups: comp.databases.mysql,comp.databases.postgresql Subject: Re: MySQL vs Postgres for a "data warehouse", 5-10 TB Date: Sat, 10 Sep 2011 19:47:49 +0000 (UTC) Organization: Taughannock Networks, Trumansburg NY Lines: 27 Message-ID: References: NNTP-Posting-Host: leila.iecc.com X-Trace: gal.iecc.com 1315684069 52610 64.57.183.34 (10 Sep 2011 19:47:49 GMT) X-Complaints-To: abuse@iecc.com NNTP-Posting-Date: Sat, 10 Sep 2011 19:47:49 +0000 (UTC) Cleverness: some X-Newsreader: trn 4.0-test77 (Sep 1, 2010) Originator: johnl@joyce.lan (John L) Xref: x330-a1.tempe.blueboxinc.net comp.databases.mysql:1393 comp.databases.postgresql:243 >For example, I remember the horrors and upsets of REPAIR TABLE >statements that took almost a day. That was with a MyISAM table around >40 gigs (I do not remember the size exactly). Yeah, they do. I'm most of the way through repairing a 57G table which takes about a day and a half. >This means that I cannot use MyISAM, period, end of story, for >something 100 times the size of the above. Hmmn. This suggests that you might want to reread the manual. A few moments spent looking at the MySQL manual reveals partitioned tables and MERGE tables, both of which are reasonable ways to divide one logical table into several physical ones. If your data just grows, but the oldest stuff never changes, merged tables may be appropriate, since you can make the older tables packed and read-only. Either way, the individual tables can be much smaller and more tractable. If your data naturally falls into slices (by numeric value in 5.1, number or string in 5.5), partitioned tables may work, particularly if you can construct your queries to take advantage of partition pruning in your queries. R's, John