Thread: Understanding PostgreSQL Storage Engines

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.

Thanks for any help...

-Carlos

Hello

2010/10/8 Carlos Mennens <carlos.mennens@gmail.com>:
> 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.
>

PostgreSQL supports and uses just only one storage engine - PostgreSQL.

Regards

Pavel Stehule

> Thanks for any help...
>
> -Carlos
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

On Friday 08 October 2010 2:30:40 pm 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.
>
> Thanks for any help...
>
> -Carlos

Postgres only has one storage engine. Sort of simplifies things.

--
Adrian Klaver
adrian.klaver@gmail.com

On 10/08/2010 03:39 PM, Adrian Klaver wrote:
> On Friday 08 October 2010 2:30:40 pm 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.
>>
>> Thanks for any help...
>>
>> -Carlos
>
> Postgres only has one storage engine. Sort of simplifies things.
>
My guess is the OP wants to know that Postgres uses tactically in its
engine: B(+)trees (or whatever it does actually use) versus Indexed
Sequential Access Method (judging by the name). No?

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.

--
Craig Ringer

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... :).

Link.

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.

Pavel Stehule wrote:
> 2010/10/8 Carlos Mennens <carlos.mennens@gmail.com>:
>> I know that MySQL uses MyISAM storage engine by default... what
>> storage engine does PostgreSQL use by default ...
>
> PostgreSQL supports and uses just only one storage engine - PostgreSQL.

That said, ISTM one of Postgres's bigger strengths commercially seems
to be that vendors can reasonably easily plug in different storage engines.

Isn't the biggest SQL database in the world basically postgres using a
non-default different storage engine[note 1 below]?  Heck, companies have
even made FPGA/hardware-accelerated storage engines for postgres[2].
Bigger IT companies than Oracle have sold PostgreSQL using
different storage engines[3].

Couldn't one almost say that one of the big differences between
MySQL and Postgres is that MySQL only offers a couple storage
engines, while Postgres has many vendors offering engines?

[ 1/2 :-) ]

   Ron

[1]
http://www.computerworld.com/s/article/9087918/Size_matters_Yahoo_claims_2_petabyte_database_is_world_s_biggest_busiest
"Yahoo brought the database in-house and continued to enhance
it, including tighter data compression, more parallel data
processing and more optimized queries. The top layer remains PostgreSQL"

[2] http://www.dbms2.com/2007/09/27/the-netezza-developer-network/
"My understanding is that they started with PostgreSQL and then
rewrote the back-end to embed in the FPGA. Query processing on
a SPU is split between the general purpose CPU and the FPGA,
with the latter mostly responsible for restricting rows and
projecting columns."

[3] http://www.fujitsu.com/downloads/MAG/vol40-1/paper15.pdf
"Fujitsu loaded the storage management mechanism of Symfoware
Server into PostgreSQL. "

On Wed, Oct 13, 2010 at 12:29 PM, Ron Mayer
<rm_pg@cheapcomplexdevices.com> wrote:
> Pavel Stehule wrote:
>> 2010/10/8 Carlos Mennens <carlos.mennens@gmail.com>:
>>> I know that MySQL uses MyISAM storage engine by default... what
>>> storage engine does PostgreSQL use by default ...
>>
>> PostgreSQL supports and uses just only one storage engine - PostgreSQL.
>
> That said, ISTM one of Postgres's bigger strengths commercially seems
> to be that vendors can reasonably easily plug in different storage engines.

That depends on how you define "reasonably easily". It's not even
remotely close to the ease with which you can plugin a different
storage engine in MySQL, and would take a significant amount of
engineering expertise and effort.

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 13 October 2010 12:35, Dave Page <dpage@pgadmin.org> wrote:
> On Wed, Oct 13, 2010 at 12:29 PM, Ron Mayer
> <rm_pg@cheapcomplexdevices.com> wrote:
>> Pavel Stehule wrote:
>>> 2010/10/8 Carlos Mennens <carlos.mennens@gmail.com>:
>>>> I know that MySQL uses MyISAM storage engine by default... what
>>>> storage engine does PostgreSQL use by default ...
>>>
>>> PostgreSQL supports and uses just only one storage engine - PostgreSQL.
>>
>> That said, ISTM one of Postgres's bigger strengths commercially seems
>> to be that vendors can reasonably easily plug in different storage engines.
>
> That depends on how you define "reasonably easily". It's not even
> remotely close to the ease with which you can plugin a different
> storage engine in MySQL, and would take a significant amount of
> engineering expertise and effort.

And I don't think other storage engines bring anything but unnecessary
code maintenance overhead and complexity.  Plus, reading MySQL's
documentation, you can see notes scattered everywhere about how
features behave differently, or aren't compatible with certain storage
engines.  This not only increases the number of gotchas, but also
means supporting all these engines requires an extra level of
knowledge.

I think focus on a single storage engine means it's extremely mature,
predictable and stable... IMHO.

--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

On Wed, Oct 13, 2010 at 1:11 PM, Thom Brown <thom@linux.com> wrote:
> On 13 October 2010 12:35, Dave Page <dpage@pgadmin.org> wrote:
>> On Wed, Oct 13, 2010 at 12:29 PM, Ron Mayer
>> <rm_pg@cheapcomplexdevices.com> wrote:
>>> Pavel Stehule wrote:
>>>> 2010/10/8 Carlos Mennens <carlos.mennens@gmail.com>:
>>>>> I know that MySQL uses MyISAM storage engine by default... what
>>>>> storage engine does PostgreSQL use by default ...
>>>>
>>>> PostgreSQL supports and uses just only one storage engine - PostgreSQL.
>>>
>>> That said, ISTM one of Postgres's bigger strengths commercially seems
>>> to be that vendors can reasonably easily plug in different storage engines.
>>
>> That depends on how you define "reasonably easily". It's not even
>> remotely close to the ease with which you can plugin a different
>> storage engine in MySQL, and would take a significant amount of
>> engineering expertise and effort.
>
> And I don't think other storage engines bring anything but unnecessary
> code maintenance overhead and complexity.  Plus, reading MySQL's
> documentation, you can see notes scattered everywhere about how
> features behave differently, or aren't compatible with certain storage
> engines.  This not only increases the number of gotchas, but also
> means supporting all these engines requires an extra level of
> knowledge.
>
> I think focus on a single storage engine means it's extremely mature,
> predictable and stable... IMHO.

And allows extremely tight integration with the rest of the system -
something I've heard the MySQL engine vendors all complain about (the
rigidity of being behind a defined API that doesn't meet everyones
needs).


--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Fri, Oct 8, 2010 at 5:30 PM, Carlos Mennens <carlos.mennens@gmail.com> 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.

You could make  a pretty strong case that temp tables are a different
'storage engine', since they interact differently with the storage
system.  for example, they are not wal logged and this is one of the
things that makes them faster than regular tables.  This illustrates a
wider philosophy of the databases in terms of features.  Postgresql
went a different route than mysql.

We do expose various knobs that allow you to control specific
mechanics of how data is store in a table.  For example, using table
storage parameters, you can enable/disable compression for large
datums.  Many of the other things you would like do do with storage
engines, like not sync, or custom data organization, are
correspondingly exposed via the mvcc engine or the type system.  It's
much cleaner conceptually (IMNSHO) to deal with these things on the
SQL level vs  storage engines with their arcane tradeoffs.

There are of course some cool things we can't do that mysql can, but
the postgresql database behaves in much more regular fashion.  Imagine
debugging data corruption on a third party engine -- this is a problem
we don't want.

merlin

Ron Mayer <rm_pg@cheapcomplexdevices.com> writes:
>> PostgreSQL supports and uses just only one storage engine - PostgreSQL.

> That said, ISTM one of Postgres's bigger strengths commercially seems
> to be that vendors can reasonably easily plug in different storage engines.

> Isn't the biggest SQL database in the world basically postgres using a
> non-default different storage engine[note 1 below]?  Heck, companies have
> even made FPGA/hardware-accelerated storage engines for postgres[2].
> Bigger IT companies than Oracle have sold PostgreSQL using
> different storage engines[3].

> Couldn't one almost say that one of the big differences between
> MySQL and Postgres is that MySQL only offers a couple storage
> engines, while Postgres has many vendors offering engines?

Actually, that doesn't speak to storage engines at all.  What that
speaks to is having a well-engineered, understandable code base that
people can modify easily.  Those folk aren't "plugging in" anything,
they're just modifying what's there.

In theory one can also modify the MySQL code, but have you ever looked
at it?  Man is it ugly, and AFAICT almost completely lacking in internal
documentation.

Another reason why commercial companies are forking Postgres rather
than MySQL is that our license lets them do that for free.  MySQL they'd
have to pay money for (and these days, with Oracle owning the rights,
you'd be lucky if they'd allow you to sell a competing product at *any*
price).

            regards, tom lane