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

On 16 August 2010 23:59, Andy <angelflow(at)yahoo(dot)com> wrote:
> For the same data set, with mostly text data, how does the data (table + index) size of Postgresql compared to that of MySQL?
>
> In this presentation, the largest blog site in Japan talked about their migration from Postgresql to MySQL. One of their reasons for moving away from Postgresql was that data size in Postgresql was too large (p. 12 & p. 41). Specifically they talked about index being 40% of total data size:
>
> 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.
>
> Does anyone have any actual experience about how the data sizes of Postgresql & MySQL compare to each other?
>
> The company in the presentation used Postgresql 8.1. Has there been any significant changes in data size between 8.1 and 8.4/9.0?
>
> Thanks.
>

This is quite a crude and unrealistic test (as you'd need a set of
real-world data), but just did a quick test using PostgreSQL 9.0 alpha
4 and MySQL . I created a new database in both PostgreSQL and MySQL.
Created the same table in both, and loaded identical data (50
paragraphs of lorem ipsum) and got it to insert the table's contents
back into itself until both reached 65,536 rows. I also did a VACUUM
in PostgreSQL and an OPTIMIZE TABLE in MySQL.

PostgreSQL's table size shows 867 MB
MySQL's table size as MyISAM shows 2,542 MB
MySQL's table size as InnoDB shows: 3,576 MB

Also bear in mind that MySQL's InnoDB engine doesn't support full text
indexes, and when you can apply full text indexes, it only returns a
result if it matches less than 50% of the total rows in the table.

PostgreSQL provides GIN and GiST types of index which are used for
full text searches, but off the top of my head I don't know if either
is actually equivalent to MySQL's implementation. I suspect they're
quite different. Hopefully someone more familiar with both system's
full text search features can answer that.

--
Thom Brown
Registered Linux user: #516935