On Sat, Oct 9, 2010 at 11:19 AM, Lincoln Yeoh <lyeoh@pop.jaring.my> wrote:
> At 11:32 AM 10/9/2010, Craig Ringer wrote:
>>
>> On 10/09/2010 05:30 AM, Carlos Mennens wrote:
>>>
>>> I know that MySQL uses MyISAM storage engine by default and was just
>>> trying to look on Google to try and see if I could understand what
>>> storage engine does PostgreSQL use by default when I generate a
>>> database / table. Is there some way someone (me) who knows nothing
>>> about how a ORDBMS works understand the difference between all storage
>>> engine options and which does PostgreSQL use by default.
>>
>> In MySQL terms, PostgreSQL's one and only storage engine is much more like
>> InnoDB than MyISAM. That's not to say it's particularly like MySQL+InnoDB in
>> behaviour, only much *more* like InnoDB than MyISAM. It's an MVCC design
>> with proper transaction support (like any real database) with minimal
>> locking and a focus on concurrency, data integrity and correctness.
>>
>> If you're used to MySQL, you'll want to read this:
>>
>> http://wiki.postgresql.org/wiki/Slow_Counting
>>
>> as it bites MySQL people all the time.
>
> It also affects MySQL users shifting from MyISAM to InnoDB:
> http://mysqlha.blogspot.com/2009/08/fast-count-for-innodb.html
>
> So select count isn't always fast on MySQL.
>
> MySQL has a long feature list which is nice for fooling the ignorant. But
> you can't use many of them at the same time. Fast count? Use MyISAM. Full
> text index? MyISAM. Transactions, use InnoDB. Fast concurrent inserts, use
> InnoDB. So on and so forth.
>
> Some call it flexibility and choice... :).
That's why I have a lot of respect for the Drizzle guys. Their first
step was to abandon myisam completely. It might take them a while to
get a finished product, but they are headed in the right direction.