Re: Postgresql's table & index compared to that of MySQL

On 17/08/10 06:59, Andy wrote:

> http://www.scribd.com/doc/2569473/Migrating-from-PostgreSQL-to-MySQL-at-Cocolog-Japans-Largest-Blog-Community
>
> Are there any reasons why table & index sizes of Postgresql should be larger than MySQL? Postgresql uses MVCC while InnoDB does not use "full" MVCC, so perhaps that's a factor there.

I can't help but wonder if they were doing regular VACUUM FULL
"maintenance" and being bitten by issues with index bloat as a
consequence. Though after reading the document linked, it's hard to
imagine they would be falling for a mistake that basic.

OTOH, a key clue there is:

We don't use Auto VACUUM since we are worried about latent
response time.

ie they wanted to avoid response latency spikes and thought/found they'd
be caused by autovacuum. Given that their discussion is of 7.4 to 8.1,
that's not unreasonable.

It looks to me like their *real* issue was just that they had problems
scaling a single PostgreSQL instance up to meet their needs, and were
having issues with vacuum under continuous high loads. They decided to
move to a sharded system, and moved over to MySQL at that time. That's
not necessarily a bad decision, as MySQL is more widely used in sharded
setups and there are some good tools to help with that sort of use.

It's pretty funny that they had issues with charset handling under Pg,
though, as most people seem to complain that Pg is too strict. Any ideas
what they might be talking about with:

"PostgreSQL allow the out of boundary UTF-8 japanese extended
character sets and multi bytes character sets which should
normally come back with an error - instead of accepting them"

... and whether it's related to existing or past issues? Or might they
have just been using SQL_ASCII?

There are also some clues that the way they were doing things may not
have been ideal. For example, they mention as a "problem" with MySQL
that you get a "different sort order without order by clause". I
struggle to interpret that in any way but that they were relying on the
undefined ordering of records in a query without an ORDER BY, which is
just silly.

--
Craig Ringer