Advanced Indexing
Advanced indexing methods allow database systems to optimize queries to achieve greater performance.
| Index Type
|
MySQL
|
PostgreSQL
|
| Hash indexes
|
InnoDB, NDB and MEMORY engines support Hash indexes
|
PostgreSQL supports Hash indexes, though as of 8.1 they are never faster than b-tree indexes [13]
|
| Multiple Indexes
|
MySQL supports multiple indexes per table and can use one for each
alias of a table; since 5.0 it will also use index merge to use multiple
indexes for a single alias.
|
PostgreSQL supports multiple indexes per query.
|
| Full-Text Indexes
|
MySQL comes with full-text search for InnoDB and MyISAM storage
engines. Prior to version 5.6 only the MyISAM storage engine supported
this feature. [14]
A 3rd party add-on to MySQL, Sphinx Fulltext Search Engine allows it to support full-text searches on storage engines which do not natively support it.
|
PostgreSQL 8.2 has full text search in the tsearch2 module.
PostgreSQL 8.3 integrates tsearch2 into the core: "TSearch2, our
cutting-edge full text search tool, has been fully integrated into the
core code, and also has a cleaner API." [15]
|
| Partial Indexes
|
MySQL does not support partial indexes.
|
PostgreSQL supports partial indexes:
A partial index is an index built over a subset of a table; the
subset is defined by a conditional expression (called the predicate of
the partial index). The index contains entries for only those table rows
that satisfy the predicate. Partial indexes are a specialized feature,
but there are several situations in which they are useful.
One major reason for using a partial index is to avoid indexing
common values. Since a query searching for a common value (one that
accounts for more than a few percent of all the table rows) will not use
the index anyway, there is no point in keeping those rows in the index
at all. This reduces the size of the index, which will speed up queries
that do use the index. It will also speed up many table update
operations because the index does not need to be updated in all cases.
|
| Prefix Indexes
|
MySQL supports prefix indexes. Prefix indexes cover the first N
characters of a string column, making the index much smaller than one
that covers the entire width of the column, yet still provide good
performance characteristics.
|
With PostgreSQL, prefix indexes are a particular case of Expression Indexes (see below).
|
| Multi-column Indexes
|
MySQL is limited to 16 columns per index. [16] And not all storage engines provide multi-column indexes.
|
PostgreSQL is limited to 32 columns per index. [17]
|
| Bitmap Indexes
|
MySQL has no bitmap indexes but achieves similar functionality using its "index_merge" feature.
|
PostgreSQL supports the ability to combine multiple indexes at query time using bitmap indexes.
|
| Expression Indexes
|
Expression Indexes can be emulated in MySQL by adding a precomputed column and using a trigger to maintain it.
|
PostgreSQL allows you to create indexes based on expressions (which
may include calls to immutable functions). This is very handy in case
there is a table with relatively stable data (not a lot of inserts /
updates) and will often be running a query which involves an expensive
calculation - the expression itself can be indexed thus eliminating the
need of computing it at query runtime.
|
| Non-blocking CREATE INDEX
|
Dependent on the storage engine. Some engines (such as NDB Cluster
and InnoDB Plugin) support online add/drop index (no locks taken). If
the engine doesn't support online add/drop index, a write exclusive lock
is required and the table copied.
|
PostgreSQL supports the ability to create indexes without locking the table for writes.
|
| Covering Indexes
|
MySQL supports covering indexes, which allow data to be selected by
scanning the index alone without touching the table data. This is
advantageous with large tables that have many millions of rows.
|
Covering indexes were added to PostgreSQL 9.2
|
[edit] Partitioning
MySQL Supports several forms of horizontal partitioning.
- RANGE
- LIST
- HASH
- KEY
- Composite partitioning using a combination of RANGE or LIST with HASH or KEY subpartitions
- MySQL supports a total of 1024 partitions + subpartitions per table.
PostgreSQL only supports RANGE and LIST partitioning[18].
- HASH partitioning is supported via immutable functions.
- Composite partitioning is also supported.
- PostgreSQL partitions are tables that inherit from a master table.
- I have (thus far) been unable to find a specific technical or
practical upper limit to the number of partitions supported in
PostgreSQL. Anecdotally, the practical limit is less than the technical
limit.
[edit] Other features
In PostgreSQL, there is no built-in mechanism for limiting database size, mostly due to the risk it implies. This is another reason, after popularity, why the most of the web hosting companies are using MySQL[citation needed].
Also, PgAgent a scheduling agent for PostgreSQL allows for scheduled processes.
[edit] Licensing
PostgreSQL comes with an MIT-style license, which fits the Free Software Definition and Open Source Definition, and conforms to both the Debian Free Software Guidelines and the Copyfree Standard.
MySQL's source code is available under terms of the GNU General
Public License, which also fits the Free Software and Open Source
definitions and conforms to the Debian Free Software Guidelines (but not
to the Copyfree Standard). It is also available under a proprietary
license agreement, which is typically intended for use by those who wish
to release software incorporating MySQL code without having to release
the source code for the entire application. In practical terms, this
means that MySQL can be distributed with or without source code, as can
PostgreSQL, but to distribute without source code in the case of MySQL
requires paying Oracle for a MySQL Commercial License.
Even the MySQL client library is GPL (not LGPL: see GPL vs LGPL
for more discussion of these licenses), which means that to use (and
therefore link to) the MySQL client library the program must either
itself be GPL, must use one of a broad range of FOSS licenses including BSD and LGPL, or must have a commercial license from Oracle.
See Copyfree vs Copyleft for more about the differences in licensing styles.
[edit] Development
MySQL is owned and sponsored by a single for-profit firm, Oracle.
MySQL AB holds copyrights to most of the codebase. MySQL's corporate
management has drawn criticism for mismanagement of its development.
By contrast, PostgreSQL is not controlled by any single company,
but relies on a global community of developers and companies to develop
it. It does, however, enjoy both software development help and resource
contributions from businesses who make use of PostgreSQL database
technologies, such as EnterpriseDB. Corporate sponsors are considered
contributors roughly like any other, however, within PostgreSQL's
community-driven development model.
MySQL is an open-source PRODUCT.
Postgres is an open-source PROJECT.
One criticism of the MySQL development model has been the historical
reluctance of its corporate development team to accept patches from
external sources. This has prompted some to say MySQL is not a "true"
open source project. Nontrivial improvements from Google and Percona
have been accepted into the main codebase recently, though how
significant a change in external development policy this represents is
yet to be seen.
Furthermore, PostgreSQL's development team is much more accessible than
that of MySQL, and they will go as far as to provide you with a patch if
there really is a problem with the engine.
On the other hand, the MySQL team will routinely degrade bugs (to
"not a bug") without providing any solution, and accept the fact that
there's a problem and not fix it for 5 years, etc[citation needed].
[edit] Culture
MySQL's community is supported in part by the company's Community Relations Team. MySQL AB has sponsored an annual User's Conference and Expo since 2003.
PostgreSQL is a fully community supported open source project,
with no singular corporate sponsorship. Instead, companies whose
business models depend on PostgreSQL are accepted as members of the
community, and code from corporate contributors is accepted under the
same terms as from any other external contributor.
Both also have large numbers of enthusiastic supporters who are willing to assist on a voluntary basis.
[edit] Support Services
As a business product, MySQL's corporate sponsor provides its own official support
for the server, and there are independent support providers available
as well. A MySQL blog consolidator aggregates information about
independent support providers, and many are invited to MySQL's Users
Conference. One even holds its own sub-conference in association with
the main event.
PostgreSQL is a project with many sponsors and developers, and is
not controlled by any one company. A realistic choice of support is
available from a range of Professional Support Companies.
When the ANSI SQL standard was written, its author explained that the
official standards-compliant pronunciation of SQL is "ess queue ell".
The names of both MySQL and PostgreSQL reflect the pronunciation
specified by the SQL standard's author.
MySQL is officially pronounced "my ess queue ell", though those
unfamiliar with this often call it "my sequel" instead -- especially if
their previous DBMS experience centered around Microsoft SQL Server
(pronounced either "sequel server" or "ess queue ell server").
Because MySQL is a corporate software product, MySQL AB has
complete control over the name of the project. As a result of this, and
the desire for a consistent brand identity, the MySQL name is likely to
remain static.
PostgreSQL is pronounced "post gress queue ell", formed by
combining Postgres (the name of the original database management system
from which PostgreSQL is descended) with SQL. PostgreSQL is a true
portmanteau, in that it not only combines the spellings and
pronunciations of two words, but also their meanings: it is the Postgres
DBMS updated to use SQL. Some people refer it as "pgsql". The
mispronunciation "post gree sequel", and related abbreviation "post
gree", may be largely due to MS SQL Server DBA influence as well, though
it is a very rare error amongst PostgreSQL users.
There has been talk of going back to the original Postgres name,
though how much traction the idea has had is debatable. The PostgreSQL
wiki provides an overview of the debate, including pros and cons for such a name change and alternatives.
[edit] Popularity
MySQL is widely popular among various open-source web development
packages. The MyISAM engine is often the only database engine offered by
webhosting providers. Many web developers use MySQL.
Thus, MySQL became widely popular in web development, and MySQL calls
itself "The world's most popular open source database," a grounded
claim.
Part of the reason for this popularity is a common perception
that MySQL is "easier" to use than other databases -- particularly
PostgreSQL. That perception arose years ago, and has fed itself by word
of mouth, such that whether it is still true or not is likely to have
little or nothing to do with MySQL's current reputation for being
comparatively easy to use. In fact, in recent years PostgreSQL has made
significant changes[citation needed]
that have now caused the perception that they have "closed the gap",
and may even have improved its ease of use beyond that of MySQL, though
the validity of such claims is as open to question as those of MySQL.
PostgreSQL was not available on Windows. First windows native version was 8.0. This was an advantage for MySQL.
PostgreSQL has a good reputation with Oracle developers, which is
most certainly related to its much stricter and reliable approach, like
that of Oracle. The difficulties and controversy around MariaDB,
Drizzle and corporate takeovers have left many people re-considering
their database strategy.[citation needed]
(Note that many of these links are rather old and may not be accurate)
[edit] Pro PostgreSQL
[edit] Pro MySQL
See Also the Following Articles
Источник: http://www.wikivs.com/wiki/MySQL_vs_PostgreSQL | 
