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

Search Discussions

  • Pavel Stehule at Oct 8, 2010 at 9:36 pm
    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
  • Dmitriy Igrishin at Oct 8, 2010 at 9:39 pm
    Hey,

    2010/10/9 Pavel Stehule <pavel.stehule@gmail.com>
    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.

    ... Which has a looooong history. 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
    --
    Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
    To make changes to your subscription:
    http://www.postgresql.org/mailpref/pgsql-general


    --
    // Dmitriy.
  • Ron Mayer at Oct 13, 2010 at 11:29 am

    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. "
  • Dave Page at Oct 13, 2010 at 11:36 am

    On Wed, Oct 13, 2010 at 12:29 PM, Ron Mayer 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
  • Thom Brown at Oct 13, 2010 at 12:12 pm

    On 13 October 2010 12:35, Dave Page wrote:
    On Wed, Oct 13, 2010 at 12:29 PM, Ron Mayer
    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
  • Dave Page at Oct 13, 2010 at 12:18 pm

    On Wed, Oct 13, 2010 at 1:11 PM, Thom Brown wrote:
    On 13 October 2010 12:35, Dave Page wrote:
    On Wed, Oct 13, 2010 at 12:29 PM, Ron Mayer
    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
  • Tom Lane at Oct 13, 2010 at 2:39 pm

    Ron Mayer 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
  • Adrian Klaver at Oct 8, 2010 at 9:39 pm

    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
  • Rob Sargent at Oct 9, 2010 at 12:00 am

    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?
  • Craig Ringer at Oct 9, 2010 at 3:33 am

    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
  • Lincoln Yeoh at Oct 9, 2010 at 5:20 pm

    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.
  • Scott Marlowe at Oct 9, 2010 at 7:19 pm

    On Sat, Oct 9, 2010 at 11:19 AM, Lincoln Yeoh 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.
  • Merlin Moncure at Oct 13, 2010 at 1:21 pm

    On Fri, Oct 8, 2010 at 5:30 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.
    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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-general @
categoriespostgresql
postedOct 8, '10 at 9:30p
activeOct 13, '10 at 2:39p
posts14
users13
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2021 Grokbase