SQLite vs MySQL vs PostgreSQL

SQLite vs MySQL vs PostgreSQL：关系型数据库比较

作者 孙镜涛 发布于 2014年4月1日 | 讨论

• 分享到： 微博 微信 Facebook Twitter 有道云笔记 邮件分享
• 稍后阅读
• 我的阅读清单

自1970年埃德加·科德提出关系模型之后，关系型数据库便开始出现，经过了40多年的演化，如今的关系型数据库种类繁多，功能强大，使用广泛。面对如此之多的关系型数据库，我们应该如何权衡找出适合自己应用场景的数据库系统呢？O.S. Tezer最近在DigitalOcean上发表了一篇博文，对比了SQLite、MySQL和PostgreSQL这三个常用的、流行的关系型数据库管理系统（RDBMS），希望能对你有所帮助。

O.S. Tezer分别从数据库支持的数据类型、优势、劣势、何时应该使用以及何时不应该使用该数据库这5个方面对SQLite、MySQL和PostgreSQL做了比较。

SQLite

SQLite是一款轻型数据库，它遵守ACID，能够嵌入到使用它的应用程序中。作为一个自包含的、基于文件的数据库，SQLite提供了非常出色的工具集能够处理所有类型的数据，与托管在服务器上基于进程的关系型数据库相比它的约束更少，也更易用。

当应用程序使用SQLite时，SQLite并非作为一个独立进程通过某种通信协议（例如socket）与应用程序通信，而是作为应用程序的一部分，应用程序通过调用SQLite的接口直接访问数据文件。感谢类库的底层技术，它让SQLite变得非常快速、高效并且十分强大。

SQLite支持的数据类型

SQLite支持的数据类型包括：NULL、INTEGER、REAL、TEXT、BLOB。

注意：如果你想了解与SQLite数据类型相关的更多内容，可以参阅官方文档。

SQLite的优点

• 基于文件

  整个数据库完全由磁盘上的一个文件构成，这使得它的可移植性非常好。

• 标准化

  尽管它看起来像一个“简化版”的数据库实现，但是SQLite确实支持SQL。它省略了一些功能（RIGHT OUTER JOIN和FOR EACH STATEMENT），但同时也增加了一些额外的功能。

• 非常适合开发甚至是测试

  在大多数应用程序的开发阶段，大部分开发人员可能都非常需要一个能够支持并发扩展的解决方案。SQLite 包含丰富的功能，所能提供的特性超乎开发所需，使用起来也非常简洁——只需要一个文件和一个C链接库。

SQLite的缺点

• 没有用户管理

  高级数据库都支持用户系统，例如管理连接对数据库和表的访问权限。鉴于SQLite的目的和性质（没有多客户端并发的高层设计），它并不包含这些功能。

• 缺少通过优化获得额外性能的空间

  还是由于设计方面的原因，无法通过优化SQLite获得大量的额外性能。这个类库非常容易调整、也非常容易使用。它并不复杂，所以从技术上无法让它变得更快，因为它已经很快了。

何时应该使用SQLite

• 嵌入式应用程序

  所有需要可移植性、不需要扩展的应用程序，例如单用户的本地应用、移动应用或者游戏。

• 替代磁盘访问

  在很多情况下，需要直接读写磁盘文件的应用程序可以切换到SQLite从而受益于SQLite提供的额外功能以及使用结构化查询语言（SQL）所带来的简便性。

• 测试

  对大部分应用程序而言没必要使用额外的进程测试业务逻辑（例如应用程序的主要目标：功能）。

何时不应该使用SQLite

• 多用户应用程序

  如果有多个客户端需要访问并使用同一个数据库，那么最好使用功能完整的关系型数据库（例如MySQL），而不是选择SQLite。

• 需要高写入量的应用程序

  写操作是SQLite的一个局限。该DBMS在同一时刻仅允许一个写操作，因而也限制了其吞吐量。

MySQL

MySQL是最受欢迎的一个大规模数据库服务器。它是一款功能丰富的开源产品，许多网站和在线应用程序都使用该数据库。MySQL的入门相对比较简单，开发者可以从Internet上获取到大量与该数据库相关的信息。

注意:鉴于该产品的受欢迎程度，使用该数据库可以让我们受益于大量第三方应用程序、工具以及集成类库。

尽管MySQL并没有尝试实现完整的SQL标准，但是它依然为用户提供了大量功能。作为一个独立的数据库服务器，应用程序需要与MySQL守护进程通信才能访问数据库——不同于SQLite。

MySQL支持的数据类型

MySQL支持的数据类型包括TINYINT、SMALLINT、MEDIUMINT、INT或INTEGER、BIGINT、FLOAT、DOUBLE、DOUBLE PRECISION、REAL、DECIMAL、NUMERIC、DATE、DATETIME、TIMESTAMP、TIME、YEAR、CHAR、VARCHAR、TINYBLOB, TINYTEXT、BLOB, TEXT、MEDIUMBLOB、MEDIUMTEXT、LONGBLOB, LONGTEXT、ENUM、SET。

MySQL的优点

• 易用

  很容易安装。第三方工具，包括可视化工具，让用户能够很容易入门。

• 功能丰富

  MySQL支持关系型数据库应该有的大部分功能——或者直接支持、或者间接支持。

• 安全

  支持很多安全特性，有些非常高级，并且是内置于MySQL中。

• 可扩展也非常强大

  MySQL能够处理大量数据，并且在需要的时候可以规模化使用。

• 快速

  放弃某些标准让MySQL能够非常高效、简捷地工作，因而速度更快。

MySQL的缺点

• 已知限制

  MySQL从一开始就没有打算做所有事情，因而它在功能方面有一定的局限性，并不能满足一些先进应用程序的要求。

• 可靠性问题

  MySQL对某些功能（例如引用、事务、审计等）的实现方式使得它与其他的关系型数据库相比缺少了一些可靠性。

• 开发停滞

  尽管MySQL依然是一款开源产品，但是自从它被收购之后人们就对其开发进展有很多抱怨。需要注意的是有一些基于MySQL的、完整集成的数据库在标准的MySQL之上附加了其他价值，例如MariaDB。

何时应该使用MySQL

• 分布式操作

  如果SQLite不能满足你的需求，那么将MySQL引入到开发栈中，就像任何其他独立的数据库服务器一样，它能够给你带来大量的操作自由度以及一些先进的功能。

• 高安全性

  MySQL的安全机制通过一种简单的方式为数据的访问和使用提供了可靠的保护。

• 网站和Web应用

  尽管有一些约束，但是绝大部分网站和Web应用都可以简单地运行在MySQL上。相关的灵活可扩展的工具非常易于使用和管理——事实证明这些工具在长期运行时非常有用。

• 定制解决方案

  MySQL有丰富的配置项和运行模式，如果你需要一个高度量身定制的解决方案，那么MySQL能够非常容易地尾随并执行你的规则。

何时不应该使用MySQL

• SQL遵从性

  因为MySQL并没有打算实现完整的SQL标准，所以它并不完全符合SQL。如果你可能需要与这样的关系型数据库集成，那么从MySQL切换过去可能并不容易。

• 并发性

  尽管MySQL和一些其他的存储引擎能够非常好地执行读操作，但是并发读写可能会有问题。

• 缺少功能

  MySQL缺少某些功能，例如全文本搜索。

PostgreSQL

PostgreSQL是一款先进的、开源的对象关系型数据库管理系统，它的主要目标是遵从标准和可扩展。PostgreSQL，或者说Postgres，试图将ANSI/ISO SQL标准及其修正结合起来。

与其他关系型数据库相比，PostgreSQL独特的地方是它支持高度需要的、完整的面向对象以及关系型数据库的功能，例如完全支持可靠性事务。

由于其强大的底层技术，PostgreSQL能够非常高效地处理很多任务。得益于多版本并发控制（MVCC），它能够在没有读锁的情况下实现并发并保证ACID。

PostgreSQL是高度可编程的，因此扩展性非常好，它支持称为“存储过程”的自定义程序。用户可以创建这种函数简化重复的、复杂的以及经常需要的数据库操作的执行。

尽管该数据库非常强大，但是它却没有像MySQL那么流行，即便如此依然有很多优秀的第三方工具和类库可以让我们更容易地使用它。

PostgreSQL支持的数据类型

PostgreSQL支持的数据类型包括：bigint、bigserial、bit [(n)]、bit varying [(n)]、boolean、box、bytea、character varying [(n)]、character [(n)]、cidr、circle、date、double precision、inet、integer、interval [fields] [(p)]、line、lseg、macaddr、money、numeric [(p,s)]、path、point、polygon、real、smallint、serial、text、time、timestamp、tsquery、tsvector、txid_snapshot、uuid、xml

PostgreSQL的优点

• 开源且遵从SQL标准

  PostgreSQL是一款开源的、免费的、功能非常强大的关系型数据库。

• 强大的社区

  由一个忠实的、经验丰富的社区支持，用户可以通过知识库和Q&A网站获得全天候的免费服务。

• 强有力的第三方支持

  除了非常先进的特性之外，PostgreSQL还有很多优秀的、开源的第三方工具可以辅助系统的设计、管理和使用。

• 可扩展

  可以通过存储过程扩展PostgreSQL的功能。

• 面向对象

  PostgreSQL不仅是一个关系型数据库，它还是一个面向对象的数据库——支持嵌套等功能。

PostgreSQL的缺点

• 性能:

  对于简单繁重的读取操作，使用PostgreSQL可能有点小题大做，同时性能也比MySQL这样的同类产品要差。

• 流行程度

  尽管有大量的部署，但是鉴于该数据库的性质，它的受欢迎程序并不高。

• 托管

  由于上面提到的几点，很难找到提供托管PostgreSQL实例的主机或者服务提供商。

何时应该使用PostgreSQL

• 数据完整性

  当绝对需要可靠性和数据完整性的时候，PostgreSQL是更好的选择。

• 复杂的定制程序

  如果需要数据库执行定制程序，那么可扩展的PostgreSQL是更好的选择。

• 集成:

  如果将来可能需要将整个数据库迁移到其他合适的解决方案上（例如Oracle），那么PostgreSQL可能兼容性最好也更容易切换。

• 复杂的设计

  与其他开源且免费的数据库相比，对于复杂的数据库设计PostgreSQL在功能方面最全面，潜力最大，不需要你放弃其他有价值的资产。

何时不应该使用PostgreSQL

• 速度

  如果你只需要快速读取操作，那么PostgreSQL并不合适。

• 简单

  除非你需要绝对的数据完整性，ACID遵从性或者设计复杂，否则PostgreSQL对于简单的场景而言有点多余。

• 复制

  对于缺少数据库和系统管理经验的人而言使用MySQL实现复制要更简单，除非你愿意花费时间、精力和资源。

  
  

  
  

  SQLite vs MySQL vs PostgreSQL: A Comparison Of Relational Database Management Systems

  Posted Feb 21, 2014  645kviews  MySQL PostgreSQL

  Introduction

  Relational databases have been in use for a long time. They became popular thanks to management systems that implement the relational model extremely well, which has proven to be a great way to work with data [especially for mission-critical applications].

  In this DigitalOcean article, we are going to try to understand the core differences of some of the most commonly used and popular relational database management systems (RDBMS). We will explore their fundamental differences in terms of features and functionality, how they work, and when one excels over the other in order to help developers with choosing a RDBMS.

  Glossary

  1. Database Management Systems

  1. Relational Database Management Systems
  2. Relations And Data Types
  3. Popular And Important Relational Databases

  2. SQLite

  1. SQLite's Supported Data Types
  2. Advantages of SQLite
  3. Disadvantages of SQLite
  4. When To Use SQLite
  5. When Not To Use SQLite

  3. MySQL

  1. MySQL's Supported Data Types
  2. Advantages of MySQL
  3. Disadvantages of MySQL
  4. When To Use MySQL
  5. When Not To Use MySQL

  4. PostgreSQL

  1. PostgreSQL's Supported Data Types
  2. Advantages of PostgreSQL
  3. Disadvantages of PostgreSQL
  4. When To Use PostgreSQL
  5. When Not To Use PostgreSQL

  Database Management Systems

  Databases are logically modelled storage spaces for all kinds of different information (data). Each database, other than schema-less ones, have a model, which provide structure for the data being dealt with. Database management systems are applications (or libraries) which manage databases of various shapes, sizes, and sorts.

  Note: To learn more about Database Management Systems, check out our article: Understanding Databases.

  Relational Database Management Systems

  Relational Database Systems implement the relational model to work with the data. Relational model shapes whatever information to be stored by defining them as related entities with attributes across tables (i.e. schemas).

  These type of database management systems require structures (e.g. a table) to be defined in order to contain and work with the data. With tables, each column (e.g. attribute) holds a different type (e.g. data type) of information. Each record in the database, uniquely identified with keys, translates to a row that belongs to a table, with each row's series of attributes being represented as the columns of a table -- all related together, as defined within the relational model.

  Relations And Data Types

  Relations can be considered as mathematical sets that contain series of attributes which collectively represent the database and information being kept. This type of identification and collection method allow relational databases to work the way they do.

  When defining a table to insert records, each element forming a record (i.e. attribute) must match the defined data type (e.g. an integer, a date etc.). Different relational database management systems implement different data types -- which are not always directly interchangeable.

  Working with and through constraints, like the one we have just explained, is common with relational databases. In fact, constraints form the core of the relations.

  Note: If you need to work with truly unrelated, randomly represented information (e.g. a document), you might be interested in using a NoSQL (schema-less database). If you would like to learn more about them, check out our article A Comparison Of NoSQL Database Management Systems.

  Popular And Important Relational Databases

  In this article, we are going to introduce three major and important open-source relational database management systems that have helped to shape the world of application development.

  • SQLite:
    

  A very powerful, embedded relational database management system. 

  • MySQL:
    

  The most popular and commonly used RDBMS.

  • PostgreSQL:
    

  The most advanced, SQL-compliant and open-source objective-RDBMS.

  Note: Open-source applications almost always come with the freedom to use any way desired. Most of the time freedom to fork the project (therefore use the code) to create something new is also permitted. If you are interested in DBMSs, you might want to check out some forked projects, based on these popular ones, such as the MariaDB.

  SQLite

  SQLite is an amazing library that gets embedded inside the application that makes use of. As a self-contained, file-based database, SQLite offers an amazing set of tools to handle all sorts of data with much less constraint and ease compared to hosted, process based (server) relational databases.

  When an application uses SQLite, the integration works with functional and direct calls made to a file holding the data (i.e. SQLite database) instead of communicating through an interface of sorts (i.e. ports, sockets). This makes SQLite extremely fast and efficient, and also powerful thanks to the library's underlying technology.

  SQLite's Supported Data Types

  • NULL:
    

  NULL value.

  • INTEGER:
    

  Signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value.

  • REAL:
    

  Floating point value, stored as an 8-byte IEEE floating point number.

  • TEXT:
    

  Text string, stored using the database encoding (UTF-8, UTF-16BE or UTF-16LE).

  • BLOB:
    

  A blob of data, stored exactly as it was input.

  Note: To learn more about SQLite's data types and SQLite type affinity, check out the official documentation on the subject.

  Advantages of SQLite

  • File based:
    

  The entire database consists of a single file on the disk, which makes it extremely portable.

  • Standards-aware:
    

  Although it might appear like a "simple" DB implementation, SQLite uses SQL. It has some features omitted (RIGHT OUTER JOIN or FOR EACH STATEMENT), however, some additional ones are baked in.

  • Great for developing and even testing:
    

  During the development phase of most applications, for a majority of people it is extremely likely to need a solution that can scale for concurrency. SQLite, with its rich feature base, can offer more than what is needed for development with the simplicity of working with a single file and a linked C based library.

  Disadvantages of SQLite

  • No user management:
    

  Advanced databases come with the support for users, i.e. managed connections with set access privileges to the database and tables. Given the purpose and nature of SQLite (no higher-levels of multi-client concurrency), this feature does not exist.

  • Lack of possibility to tinker with for additional performance:
    

  Again by design, SQLite is not possible to tinker with to obtain a great deal of additional performance. The library is simple to tune and simple to use. Since it is not complicated, it is technically not possible to make it more performant than it already, amazingly is.

  When To Use SQLite

  • Embedded applications:
    

  All applications that need portability, that do not require expansion, e.g. single-user local applications, mobile applications or games.

  • Disk access replacement:
    

  In many cases, applications that need to read/write files to disk directly can benefit from switching to SQLite for additional functionality and simplicity that comes from using the Structured Query Language(SQL).

  • Testing:
    

  It is an overkill for a large portion of applications to use an additional process for testing the business-logic (i.e. the application's main purpose: functionality). 

  When Not To Use SQLite

  • Multi-user applications:
    

  If you are working on an application whereby multiple clients need to access and use the same database, a fully-featured RDBM (e.g. MySQL) is probably better to choose over SQLite.

  • Applications requiring high write volumes:
    

  One of the limitations of SQLite is the write operations. This DBMS allows only one single write*operating to take place at any given time, hence allowing a limited throughput.

  MySQL

  MySQL is the most popular one of all the large-scale database servers. It is a feature rich, open-source product that powers a lot of web-sites and applications online. Getting started with MySQL is relatively easy and developers have access to a massive array of information regarding the database on the internet.

  Note: It should be stated that given the popularity of the product, there are a lot of third-party applications, tools and integrated libraries which help greatly with many aspects of working with this RDBMS.

  Despite not trying to implement the full SQL standard, MySQL offers a lot of functionality to the users. As a stand-alone database server, applications talk to MySQL daemon process to access the database itself -- unlike SQLite. 

  MySQL's Supported Data Types

  • TINYINT:
    

  A very small integer.

  • SMALLINT:
    

  A small integer.

  • MEDIUMINT:
    

  A medium-size integer.

  • INT or INTEGER:
    

  A normal-size integer.

  • BIGINT:
    

  A large integer.

  • FLOAT:
    

  A small (single-precision) floating-point number. Cannot be unsigned.

  • DOUBLE, DOUBLE PRECISION, REAL:
    

  A normal-size (double-precision) floating-point number. Cannot be unsigned.

  • DECIMAL, NUMERIC:
    

  An unpacked floating-point number. Cannot be unsigned.

  • DATE:
    

  A date.

  • DATETIME:
    

  A date and time combination.

  • TIMESTAMP:
    

  A timestamp.

  • TIME:
    

  A time.

  • YEAR:
    

  A year in 2- or 4- digit formats (default is 4-digit).

  • CHAR:
    

  A fixed-length string that is always right-padded with spaces to the specified length when stored.

  • VARCHAR:
    

  A variable-length string.

  • TINYBLOB, TINYTEXT:
    

  A BLOB or TEXT column with a maximum length of 255 (2^8 - 1) characters.

  • BLOB, TEXT:
    

  A BLOB or TEXT column with a maximum length of 65535 (2^16 - 1) characters.

  • MEDIUMBLOB, MEDIUMTEXT:
    

  A BLOB or TEXT column with a maximum length of 16777215 (2^24 - 1) characters.

  • LONGBLOB, LONGTEXT:
    

  A BLOB or TEXT column with a maximum length of 4294967295 (2^32 - 1) characters.

  • ENUM:
    

  An enumeration.

  • SET:
    

  A set.

  Advantages of MySQL

  • Easy to work with:
    

  MySQL can be installed very easily. Third-party tools, including visual ones (i.e. GUIs) make it extremely simple to get started with the database.

  • Feature rich:
    

  MySQL supports a lot of the SQL functionality that is expected from a RDBMS -- either directly or indirectly.

  • Secure:
    

  A lot of security features, some rather advanced, are built in MySQL.

  • Scalable and powerful:
    

  MySQL can handle a lot of data and furthermore it can be used "at scale", if needed be.

  • Speedy:
    

  Giving up some standards allows MySQL to work very efficiently and cut corners, thus providing speed gains.

  Disadvantages of MySQL

  • Known limitations:
    

  By design, MySQL does not intend to do everything and it comes with functional limitations that some state-of-the-art applications might require.

  • Reliability issues:
    

  The way certain functionality gets handled with MySQL (e.g. references, transactions, auditing etc.) renders it a little-less reliable compared to some other RDBMSs.

  • Stagnated development:
    

  Although MySQL is still technical an open-source product, there are complaints regarding the development process since its acquisition. However, it should be noted that there are some MySQL-based, fully-integrated databases that add value on top of the standard MySQL installations (e.g. MariaDB).

  When To Use MySQL

  • Distributed operations:
    

  When you need more than what SQLite can offer, including MySQL to your deployment stack, just like any stand-alone database server, brings a lot of operational freedom together with some advanced features.

  • High security:
    

  MySQL's security features provide reliable protection for data-access (and use) in a simple way.

  • Web-sites and web-applications:
    

  A great majority of web-sites (and web-applications) can simply work on MySQL despite the constraints. This flexible and somewhat scalable tool is easy to use and easy to manage -- which proves very helpful in the long run.

  • Custom solutions:
    

  If you are working on a highly specific and extremely custom solution, MySQL can tag along easily and go by your rules thanks to its rich configuration settings and operation modes.

  When Not To Use MySQL

  • SQL compliance:
    

  Since MySQL does not [try to] implement the full SQL standard, this tool is not completely SQL compliant. If you might need integration with such RDBMSs, switching from MySQL will not be easy.

  • Concurrency:
    

  Even though MySQL and some storage engines perform really well with read operations, concurrent read-writes can be problematic.

  • Lack of features:
    

  Again, depending on the choice of the database-engine, MySQL can lack certain features, such as the full-text search. 

  PostgreSQL

  PostgreSQL is the advanced, open-source [object]-relational database management system which has the main goal of being standards-compliant and extensible. PostgreSQL, or Postgres, tries to adopt the ANSI/ISO SQL standards together with the revisions. 

  Compared to other RDBMSs, PostgreSQL differs itself with its support for highly required and integral object-oriented and/or relational database functionality, such as the complete support for reliable transactions, i.e. Atomicity, Consistency, Isolation, Durability (ACID).

  Due to the powerful underlying technology, Postgres is extremely capable of handling many tasks very efficiently. Support for concurrency is achieved without read locks thanks to the implementation of Multiversion Concurrency Control (MVCC), which also ensures the ACID compliance.

  PostgreSQL is highly programmable, and therefore extendible, with custom procedures that are called "stored procedures". These functions can be created to simplify the execution of repeated, complex and often required database operations.

  Although this DBMS does not have the popularity of MySQL, there are many amazing third-party tools and libraries that are designed to make working with PostgreSQL simple, despite this database's powerful nature. Nowadays it is possible to get PostgreSQL as an application package through many operating-system's default package manager with ease.

  PostgreSQL's Supported Data Types

  • bigint:
    

  signed eight-byte integer

  • bigserial:
    

  autoincrementing eight-byte integer

  • bit [(n)]:
    

  fixed-length bit string

  • bit varying [(n)]:
    

  variable-length bit string

  • boolean:
    

  logical Boolean (true/false)

  • box:
    

  rectangular box on a plane

  • bytea:
    

  binary data ("byte array")

  • character varying [(n)]:
    

  variable-length character string

  • character [(n)]:
    

  fixed-length character string

  • cidr:
    

  IPv4 or IPv6 network address

  • circle:
    

  circle on a plane

  • date:
    

  calendar date (year, month, day)

  • double precision:
    

  double precision floating-point number (8 bytes)

  • inet:
    

  IPv4 or IPv6 host address

  • integer:
    

  signed four-byte integer

  • interval [fields] [(p)]:
    

  time span

  • line:
    

  infinite line on a plane

  • lseg:
    

  line segment on a plane

  • macaddr:
    

  MAC (Media Access Control) address

  • money:
    

  currency amount

  • numeric [(p, s)]:
    

  exact numeric of selectable precision

  • path:
    

  geometric path on a plane

  • point:
    

  geometric point on a plane

  • polygon:
    

  closed geometric path on a plane

  • real:
    

  single precision floating-point number (4 bytes)

  • smallint:
    

  signed two-byte integer

  • serial:
    

  autoincrementing four-byte integer

  • text:
    

  variable-length character string

  • time [(p)] [without time zone]:
    

  time of day (no time zone)

  • time [(p)] with time zone:
    

  time of day, including time zone

  • timestamp [(p)] [without time zone]:
    

  date and time (no time zone)

  • timestamp [(p)] with time zone:
    

  date and time, including time zone

  • tsquery:
    

  text search query

  • tsvector:
    

  text search document

  • txid_snapshot:
    

  user-level transaction ID snapshot

  • uuid:
    

  universally unique identifier

  • xml:
    

  XML data

  Advantages of PostgreSQL

  • An open-source SQL standard compliant RDBMS:
    

  PostgreSQL is open-source and free, yet a very powerful relational database management system.

  • Strong community:
    

  PostgreSQL is supported by a devoted and experienced community which can be accessed through knowledge-bases and Q&A sites 24/7 for free.

  • Strong third-party support:
    

  Regardless of the extremely advanced features, PostgreSQL is adorned with many great and open-source third-party tools for designing, managing and using the management system.

  • Extensible:
    

  It is possible to extend PostgreSQL programmatically with stored procedures, like an advanced RDBMS should be.

  • Objective:
    

  PostgreSQL is not just a relational database management system but an objective one - with support for nesting, and more.

  Disadvantages of PostgreSQL

  • Performance:
    

  For simple read-heavy operations, PostgreSQL can be an over-kill and might appear less performant than the counterparts, such as MySQL.

  • Popularity:
    

  Given the nature of this tool, it lacks behind in terms of popularity, despite the very large amount of deployments - which might affect how easy it might be possible to get support.

  • Hosting:
    

  Due to above mentioned factors, it is harder to come by hosts or service providers that offer managed PostgreSQL instances. 

  When To Use PostgreSQL

  • Data integrity:
    

  When reliability and data integrity are an absolute necessity without excuses, PostgreSQL is the better choice.

  • Complex, custom procedures:
    

  If you require your database to perform custom procedures, PostgreSQL, being extensible, is the better choice.

  • Integration:
    

  In the future, if there is a chance of necessity arising for migrating the entire database system to a propriety (e.g. Oracle) solution, PostgreSQL will be the most compliant and easy to handle base for the switch. 

  • Complex designs:
    

  Compared to other open-source and free RDBMS implementations, for complex database designs, PostgreSQL offers the most in terms of functionality and possibilities without giving up on other valuable assets.

  When Not To Use PostgreSQL

  • Speed:
    

  If all you require is fast read operations, PostgreSQL is not the tool to go for.

  • Simple set ups:
    

  Unless you require absolute data integrity, ACID compliance or complex designs, PostgreSQL can be an over-kill for simple set-ups.

  • Replication:
    

  Unless you are willing to spend the time, energy and resources, achieving replication with MySQL might be simpler for those who lack the database and system administration experience.