Re: Need Some Recent Information on the Differences between Postgres and MySql

On Fri, Jun 25, 2010 at 1:44 AM, Dave Page <dpage(at)pgadmin(dot)org> wrote:
> On Fri, Jun 25, 2010 at 9:33 AM, Rob Wultsch <wultsch(at)gmail(dot)com> wrote:
>> MySQL has several full text search solutions. The built in MyISAM
>> solution is the best known, but there is also an engine for using
>> sphinx.
>>
>> ...
>>
>> And there are features that MySQL has that PG does not. Index only
>> queries is a massive feature. Pluggable backend storage engines are
>> another.
>
> Some might argue that is not a feature. Sure, it means you can have
> different types of storage, but it means the feature set gets
> fragmented - for example, if you want text search, you use MyISAM, but
> if you want relational integrity you have to use InnoDB or some other
> backend. You want both? Oh. Hmmm.
>
> It could also be argued that having a storage engine API means that
> the query planner/optimiser cannot have nearly as much knowledge about
> how the data is stored and what access characteristics it may have
> thus preventing it from being as well optimised as Postgres.
>
> --
> Dave Page
> EnterpriseDB UK: http://www.enterprisedb.com
> The Enterprise Postgres Company
>

In many cases this criticism would be correct with the current
interface. Drizzle has already changed the interface and is looking to
allow more knowledge to be passed back to planner. In a few years
MySQL like systems may have as much knowledge as PG does.

The freedom of the storage engine interface allows for much more
varied backend characteristics. Some examples:
- NDB: A GPL'ed distributed highly redundant transactional storage
engine for MySQL that can non-impactfully survive the lose of servers.
I know of no PG equivilant.
- TokuDB and Infobright: Data warehousing backends. The alternatives
data warehousing forks based on PG that I know of are true forks which
end up diverging significantly from the mainline. With MySQL the
esoteric backends can stay current with mainline easily. IIRC both
have incorporated new planner'ish features.
- Sphinx : Fulltext indexing in MySQL done right.
- CSV : A SQL interface to CRUD CSV. I know of no comparable in PG.
- Blitzdb :An interesting new non-transactional engine that has
recently been merged into Drizzle.
- Innodb : The primary transactional storage engine for MySQL. It does
not have all the features of PG (like check contraints), but it has
some features (like Compression!!!) which are *exceptionally* useful.
The backend being seperate from the core has in the last few years
allowed significant features additions/changes (thank you Oracle).
PostGIS is somewhat similar in how it is decoupled from core.
- Blackhole : A storage engine that does not actually store data. I
have used this for a variety of purposes including making ORM happy
and allowing the dropping of parts of an application backend without
breaking the application completely.

To some in the MySQL community much of the most interesting
development has happened outside of core. I guess the crux of my point
is that storage engine interface allows for many features that are not
found in PG.

--
Rob Wultsch
wultsch(at)gmail(dot)com