MySQL vs PostgreSQL (Part 2) - Об ОС Windows - Системное администрирование - Каталог статей - Архив документации и мануалов для админов

Воскресенье, 11.12.2016, 13:51
Приветствую Вас Гость | RSS
Мой сайт
Форма входа

Меню сайта

Категории раздела
Об ОС Windows [137]
В категории размещаются статьи, касающщиеся операционных систем от Microsoft.
Об ОС *Nix [198]
В данной категории собраны статьи об ОС семейства Unix/Linux/FreeBSD/...
Справочные материалы [351]
Справка по всему разделу.
Виртуализация и Облака [46]
Networks & Routing [86]
DataBases [22]

Наш опрос
Оцените мой сайт
Всего ответов: 193


Онлайн всего: 1
Гостей: 1
Пользователей: 0

Главная » Статьи » Системное администрирование » Об ОС Windows

MySQL vs PostgreSQL (Part 2)

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.

  • 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.

Greg Sabino MullanePostgres is not for sale (reprint of original blog post)

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

[edit] Community

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.

[edit] Name

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]

[edit] Links

(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

Категория: Об ОС Windows | Добавил: admin (17.01.2013)
Просмотров: 1506 | Комментарии: 1 | Теги: postgresql, MySQL, db, Databases | Рейтинг: 0.0/0
Всего комментариев: 0
Имя *:
Email *:
Код *:

Друзья сайта
  • Официальный блог
  • Сообщество uCoz
  • FAQ по системе
  • Инструкции для uCoz

  • Copyright MyCorp © 2016