MySQL vs Postgres for a "data warehouse", 5-10 TB - 09-10-2011 , 12:14 AM
First of all, this is not an attempt to create a flamewar of any
kind. 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
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
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
My initial plan was to use MySQL, InnoDB, and deal with problems as
they arise. Perhaps, say, I would implement my own joining
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
Re: MySQL vs Postgres for a "data warehouse", 5-10 TB - 09-10-2011 , 03:13 AM
On 10-09-2011 08:30, Michael Vilain wrote:
Re: MySQL vs Postgres for a "data warehouse", 5-10 TB - 09-10-2011 , 02:47 PM
takes about a day and a half.
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
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.
Re: MySQL vs Postgres for a "data warehouse", 5-10 TB - 09-10-2011 , 04:02 PM
On 2011-09-10, John Levine <johnl (AT) iecc (DOT) com> wrote:
very well. I had NO idea that it was possible. I thought that I can,
essentially, split my data by month.
Then I realized that I perform my updates for the more recent items by
means of INSERT... ON DUPLICATE KEY UPDATE, and it would not work with
``Because the MERGE engine cannot enforce uniqueness over the set of
numeric ID. (I get the numeric ID of my items with the items, they
already have a natural unique key)
The more I think about the problem, the more I have a bad feeling
Re: MySQL vs Postgres for a "data warehouse", 5-10 TB - 09-11-2011 , 04:24 AM
Ignoramus5263 <ignoramus5263 (AT) NOSPAM (DOT) 5263.invalid> wrote:
pgsql-performance mailing list; it's the sort of question they're used
Re: MySQL vs Postgres for a "data warehouse", 5-10 TB - 09-11-2011 , 07:35 AM
On 2011-09-11, Matthew Woodcraft <mattheww (AT) chiark (DOT) greenend.org.uk> wrote:
Re: MySQL vs Postgres for a "data warehouse", 5-10 TB - 09-11-2011 , 09:40 AM
Re: MySQL vs Postgres for a "data warehouse", 5-10 TB - 09-11-2011 , 11:11 AM
Ignoramus13208 <ignoramus13208 (AT) NOSPAM (DOT) 13208.invalid> wrote:
partitioned tables. And since you claimed partitions would not fit,
MERGE won't too.
Also the MERGE engine is notoriously unstable. I expect to see
support for MERGE tables being dropped soon.
Re: MySQL vs Postgres for a "data warehouse", 5-10 TB - 09-11-2011 , 12:07 PM
On 2011-09-11, Axel Schwenke <axel.schwenke (AT) gmx (DOT) de> wrote:
Re: MySQL vs Postgres for a "data warehouse", 5-10 TB - 09-23-2011 , 06:01 AM
We run a 250m rows beast here unpartitioned.
Updated in real time.
BTW select count(*) needs 5 minutes under full load and 2 minutes on a smaller but less busy box.
MySQL Innobase 5.5 and 5.1