liunx底下db数据库比较 SQLite vs MySQL vs PostgreSQL vs Mongodb

引言

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.

各种数据库总序

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.

Submitted by:  O.S. Tezer

Spin up an SSD cloud server in under a minute.

Simple setup. Full root access. Straightforward pricing.

DEPLOY SERVER

Related Tutorials

• How To Set Up Django with Postgres, Nginx, and Gunicorn on Ubuntu 16.04
• How To Use PostgreSQL with your Django Application on Ubuntu 16.04
• How To Install and Use PostgreSQL on Ubuntu 16.04
• How To Install and Secure phpMyAdmin on Ubuntu 16.04
• How To Install Linux, Nginx, MySQL, PHP (LEMP stack) in Ubuntu 16.04

33 Comments

• B
• I
• UL
• OL
• Link
• Code
• Highlight
• Table

 

Logged in as:

Comment

  Notify me of replies to my comment

• 

  adsfas  March 20, 2014

  nosql comparison link is broken

   
• 

  Foo  March 20, 2014

  I question the claim that MySQL is "The most popular and commonly used RDBMS". It depends on what you mean by "popular and commonly used", but SQLite is used almost everywhere these days. I honestly don't know how to do anything on my computer or phone today that doesn't use SQLite. It's in iOS and Android, it's in Firefox and Chrome, it's in OS X (and used by every OS X application, indirectly, and several of them directly) and Linux. It's used by Dropbox and Skype and Lightroom and Airbus and pretty much every major software company in the world. MySQL is very popular for web apps, but SQLite is very popular for basically everything else.

   
  • 

    divinity76  August 20, 2015

    Yes. SQLite is much more popular than any other database. every Mac OS X and iPhone and iPod and iPad and Android and Skype and Dropbox and Firefox and Chrome and Windows 10 installation comes with SQLite built in. and they try to say MySQL is more popular? MySQL is not even close. most people have several SQLite databases. MySQL by comparison, is just installed on a couple of servers. ;)

     
  • 

    mounir1003  November 15, 2015

    We're talking web apps here, MySql is a mix of complexity and speed, the best for web apps, not the fat slow complex pgsql and not the very simple/fast SQLite, but of corse it depends on apps.
    almost every website/CMS/Framework today uses MySql.

     
• 

  lucas586955  March 20, 2014

  I would like to point out that the JSON and hstore datatypes in PostgreSQL aren't mentioned, and are extremely useful.

   
• 

  anon587193  March 20, 2014

  Please rename "Glossary" to "Table of Contents"

  3

   
  • 

    msx  February 21, 2015

    Thank you for pointing that.

     
• 

  lauris  March 20, 2014

  There was an interesting poll recently. It appears that Postgres is currently more popular among developers than MySQL http://www.databasefriends.co/2014/03/favorite-relational-database.html

   
• 

  valdis.veidelis+digitalocean  March 20, 2014

  Postgres JSON data type is a huge advantage over MySQL.

  1

   
• 

  rickyseltzer  March 20, 2014

  The date-time type of MySQL doesn't store the time zone. In PostrgeSQL, it does.

  1

   
• 

  kiliankoe  March 20, 2014

  It seems you forgot the link where it says Note: To learn more about Database Management Systems, check out our article: [Understanding Databases](link?). Otherwise thanks for the great overview!

   
• 

  joe587723  March 20, 2014

  I'd love to see this comparison when using a hosted solution like Amazon's RDS. The reason I say this is that the complexity issues with postgres go away, and I believe you're left with all of the benefits of postgres, and very few of the downsides.

   
• 

  patrick.hetu  March 20, 2014

  "understanding databases" link is broken

   
• 

  milan  March 20, 2014

  Regarding PostgreSQL read performance, you just need to put some connection pooling on front, and you will solve that problem.

   
• 

  dghaegtrdgasf  March 20, 2014

  "Unless you require absolute data integrity, ACID compliance or complex designs, PostgreSQL can be an over-kill for simple set-ups." People who don't need those things probably have no business using a database at all. It's shocking how many people think that the average self-hosted Wordpress site is actually dynamic and thus requires a database. The vast majority of things out there using a database are static sites with a silly, over-engineered backend.

   
• 

  trent.lloyd  March 21, 2014

  "Lack of features: Again, depending on the choice of the database-engine, MySQL can lack certain features, such as the full-text search." MySQL has supported Full-Text search since the dark ages in MyISAM, and full-text search is available for the much more commonly used InnoDB in the current MySQL 5.6 "GA"/stable version (First released 14 months ago in February 2013) http://dev.mysql.com/doc/refman/5.6/en/fulltext-search.html

   
• 

  kamaln7  March 30, 2014

  Thanks! I have updated the broken links.

   
• 

  jordan.burke  April 28, 2014

  As other's have said JSON support in PostgreSQL is a huge benefit over other RDBMS. The fact that I can throw in a simple JSON object and then query against its property fields using a SQL query makes PostgreSQL immensely powerful.

   
• 

  rbmovingforward  July 8, 2014

  Just joined D.O. because of this post. Great information for someone looking to get more familiar with databases. Thanks to everyone who commented as well.

   
• 

  barry244139  July 18, 2014

  There's a whole lot of things missing from the PostgreSQL benefits section. Just off the top of my head:

  Full Text Search

  PG has full text search capabilities that rival standalone dedicated search engines like Solr, Sphinx, and Elastic Search. The full text search that exists in MySQL is scary by comparison. If your are only searching data in the database, its the way do go. You don't have to setup and manage another system. You don't have to worry about data syncing issues either or encoding issues transitioning your data from PG to another search engine. It has multiple dictionaries that you can implement all over the place. You can even add your own, weight searchable parts, etc.

  Multi Index Queries

  This is big and ties into the previous one. By default, PostgreSQL does multi-index queries so when you're creating an index you just add them for individual columns that you will need to search on. You don't need to create an index over multiple fields unless it's there to maintain uniqueness across multiple fields.

  Combined with the built in full text search, that means that you can just add the full text search to any where clause and all of the indexes for other items will be hit as well.

  Custom data types

  PG is extensible and allows the creation of custom data types as well as having 2 types of indexes built for working with those custom data types. JSON and hstore were mentioned earlier and they are the result of this. There are many other custom data types (XML, ISBN, etc) to handle just about any need that you could have...and since PG handles multi-index queries...well, that's awesome.

  Functions are amazing

  Database functions are great for use in queries or manipulating data that comes back, but PostgreSQL takes them to another level. You can you create an index with the results of a function that will automatically get used when the matching function is used in a where clause. Things like a unique index on a lowercase username come to mind. This is extremely helpful if you're working with XML too because you can't index an XML file...but you can index the result of an XPATH function on the XML datatype.

  Wasted space is TOASTed

  TOAST is the compression layer behind PG's TEXT field types. It automatically zip's large data. I dropped a 2.2 mb XML file in and it stored in as 81 kb.

  Stored procedures aren't painful

  You can write stored procedures in other languages including Python and Javascript. This makes putting logic that belongs in your database in there a whole lot simpler.

  LISTEN/NOTIFY will change your life

  Never have a long running process poll for changes to data again. Long running server processes can now hold a connection and let the database tell them when a change happened. These remove your dependence on framework "AFTER SAVE" hooks, which really comes in handy when you need to connect to your database with another language. If it changes in the database, it changes everywhere.

  A few quick use cases for this:

  • A Postgres based queuing system that listens for new tasks. Heroku actually built one for Ruby called queue_classic that's available on Github (which they use in production). Also uses logic to find and update the record simultaneously to ensure no 2 workers grab the same job.
  • A cache updating system that listens for data changes and pushes them to memcached / redis
  • A process that listens for data changes and pushes them to a 3rd party service, statsd, or standalone search engine if needed

  PostGIS for Geospatial Data

  Adds a bunch of custom datatypes and logic for dealing with geospatial data. Because you can do that with PostgreSQL.

  Because of multi-index queries, this means that in one query you can search for basic where conditions, full text, and filter by geospatial parameters like distance. In ONE. DANG. QUERY.

  This is off the top of my head. There's a lot more.

数据库设计原理差异比较

Editorial information provided by DB-Engines
Name	MongoDB  X	PostgreSQL  X	SQLite  X
Description	One of the most popular document stores	Based on the object relational DBMS Postgres	Widely used in-process RDBMS
Database model	Document store	Relational DBMS	Relational DBMS
DB-Engines Ranking  Trend Chart	Score 320.22 Rank #4   Overall   #1   Document stores	Score 307.61 Rank #5   Overall   #4   Relational DBMS	Score 107.26 Rank #10   Overall   #7   Relational DBMS
Website	www.mongodb.org	www.postgresql.org	sqlite.org
Technical documentation	docs.mongodb.org/­manual	www.postgresql.org/­docs/­manuals	sqlite.org/­docs.html
Developer	MongoDB, Inc	PostgreSQL Global Development Group	Dwayne Richard Hipp
Initial release	2009	1989	2000
Current release	3.2.6, March 2016	9.5.3, May 2016	3.13.0, May 2016
License	Open Source	Open Source	Open Source
Database as a Service (DBaaS)	no	no	no
Implementation language	C++	C	C
Server operating systems	Linux OS X Solaris Windows	FreeBSD HP-UX Linux NetBSD OpenBSD OS X Solaris Unix Windows	server-less
Data scheme	schema-free	yes	yes
Typing	yes	yes	yes
XML support			no
Secondary indexes	yes	yes	yes
SQL	no	yes	yes
APIs and other access methods	proprietary protocol using JSON	native C library streaming API for large objects ADO.NET JDBC ODBC	ADO.NET  JDBC  ODBC
Supported programming languages	Actionscript  C C# C++ Clojure  ColdFusion  D  Dart  Delphi  Erlang Go  Groovy  Haskell Java JavaScript Lisp  Lua  MatLab  Perl PHP PowerShell  Prolog  Python R  Ruby Scala Smalltalk	.Net C C++ Java  Perl Python Tcl	Actionscript Ada Basic C C# C++ D Delphi Forth Fortran Haskell Java JavaScript Lisp Lua MatLab Objective-C OCaml Perl PHP PL/SQL Python R Ruby Scala Scheme Smalltalk Tcl
Server-side scripts	JavaScript	user defined functions	no
Triggers	no	yes	yes
Partitioning methods	Sharding	no, but can be realized using table inheritance	none
Replication methods	Master-slave replication	Master-slave replication	none
MapReduce	yes	no	no
Consistency concepts	Eventual Consistency Immediate Consistency	Immediate Consistency
Foreign keys	no	yes	yes
Transaction concepts	no	ACID	ACID
Concurrency	yes	yes	yes
Durability	yes	yes	yes
In-memory capabilities	yes	no	yes
User concepts	Access rights for users and roles	fine grained access rights according to SQL-standard	no
More information provided by the system vendor
	MongoDB	PostgreSQL	SQLite
Specific characteristics	MongoDB is the next-generation database that helps businesses transform their industries... » more
Competitive advantages	MongoDB combines the best of relational databases with the innovations of NoSQL technologies,... » more
Typical application scenarios	Internet of Things (Bosch), Mobile (The Weather Channel), Single View (MetLife),... » more
Key customers	ADP, Adobe, AstraZeneca, BBVA, Bosch, Cisco, CERN, Department of Veteran Affairs,... » more
Market metrics	10 million downloads (growing at thousands downloads per day). 2,000+ customers including... » more
Licensing and pricing models	MongoDB database server: Free Software Foundation’s GNU AGPL v3.0. Commercial licenses... » more
We invite representatives of system vendors to contact us for updating and extending the system information, and for displaying vendor-provided information such as key customers, competitive advantages and market metrics.
3rd party products and services
3rd parties	CData: Connect to Big Data & NoSQL through standard Drivers. » more
We invite representatives of 3rd party vendors to contact us for presenting information about their offerings here.
More resources
	MongoDB	PostgreSQL	SQLite
DB-Engines blog posts	Oracle is the DBMS of the Year 5 January 2016, Paul Andlinger, Matthias Gelbmann Winners, losers and an attractive newcomer in Novembers DB-Engines ranking 2 November 2015, Paul Andlinger MongoDB and PostgreSQL struggle for the lead of the chasing group in the popularity ranking 1 July 2015, Paul Andlinger show all	MySQL, PostgreSQL and Redis are the winners of the March ranking 2 March 2016, Paul Andlinger Big gains for Relational Database Management Systems in DB-Engines Ranking 2 February 2016, Matthias Gelbmann MongoDB and PostgreSQL struggle for the lead of the chasing group in the popularity ranking 1 July 2015, Paul Andlinger show all	Big gains for Relational Database Management Systems in DB-Engines Ranking 2 February 2016, Matthias Gelbmann show all
Conferences and events more DBMS events	MongoDB World 2016 New York, USA, 28-29 June 2016
Recent citations in the news	MongoDB on breaches: Software is secure, but some users are idiots 3 May 2016, The Register Secure Your MongoDB Installation 24 May 2016, DZone News NOW TV tackles personalisation and scalability challenges with MongoDB 13 May 2016, Diginomica Grant Right to Use $Eval on MongoDB 3.2 26 May 2016, DZone News Xenomorph Leads EDM with NoSQL Integration to MongoDB 16 May 2016, Business Wire (press release) provided by Google News	EnterpriseDB wraps PostgreSQL into an enterprise-grade suite to challenge Oracle 23 May 2016, V3.co.uk PostgreSQL 9.6 beta, SyntaxNet goes open source, and Mozilla files a motion with U.S. District Court—SD Times news ... 13 May 2016, SDTimes.com Mysql VS PostgreSQL 16 May 2016, TheSequitur.com 平安科技中国部署量最大的PostgreSQL用户 30 May 2016, 比特网 MySQL和PostgreSQL数据库安全配置 30 May 2016, 比特网 provided by Google News	SQLite to be recommended data access for Universal Windows Apps 3 May 2016, WinBeta Realm reaches 1B users,100k active developers, launches version 1.0 30 May 2016, InfoQ.com Open Source Realm Mobile Database Hits Version 1.0 25 May 2016, ADT Magazine Плагин rpg для css sqlite 28 May 2016, TransferNews.ru SQLite 3.9 Supports JSON, Indexes on Expressions and More 20 October 2015, InfoQ.com provided by Google News

数据库性能比较

Database Speed Comparison

Note: This document is very very old. It describes a speed comparison between archaic versions of SQLite, MySQL and PostgreSQL.

The numbers here have become meaningless. This page has been retained only as an historical artifact.

Executive Summary

A series of tests were run to measure the relative performance of SQLite 2.7.6, PostgreSQL 7.1.3, and MySQL 3.23.41. The following are general conclusions drawn from these experiments:

• SQLite 2.7.6 is significantly faster (sometimes as much as 10 or 20 times faster) than the default PostgreSQL 7.1.3 installation on RedHat 7.2 for most common operations.

• SQLite 2.7.6 is often faster (sometimes more than twice as fast) than MySQL 3.23.41 for most common operations.

• SQLite does not execute CREATE INDEX or DROP TABLE as fast as the other databases. But this is not seen as a problem because those are infrequent operations.

• SQLite works best if you group multiple operations together into a single transaction.

The results presented here come with the following caveats:

• These tests did not attempt to measure multi-user performance or optimization of complex queries involving multiple joins and subqueries.

• These tests are on a relatively small (approximately 14 megabyte) database. They do not measure how well the database engines scale to larger problems.

Test Environment

The platform used for these tests is a 1.6GHz Athlon with 1GB or memory and an IDE disk drive. The operating system is RedHat Linux 7.2 with a stock kernel.

The PostgreSQL and MySQL servers used were as delivered by default on RedHat 7.2. (PostgreSQL version 7.1.3 and MySQL version 3.23.41.) No effort was made to tune these engines. Note in particular the default MySQL configuration on RedHat 7.2 does not support transactions. Not having to support transactions gives MySQL a big speed advantage, but SQLite is still able to hold its own on most tests.

I am told that the default PostgreSQL configuration in RedHat 7.3 is unnecessarily conservative (it is designed to work on a machine with 8MB of RAM) and that PostgreSQL could be made to run a lot faster with some knowledgeable configuration tuning. Matt Sergeant reports that he has tuned his PostgreSQL installation and rerun the tests shown below. His results show that PostgreSQL and MySQL run at about the same speed. For Matt's results, visit

http://www.sergeant.org/sqlite_vs_pgsync.html

SQLite was tested in the same configuration that it appears on the website. It was compiled with -O6 optimization and with the -DNDEBUG=1 switch which disables the many "assert()" statements in the SQLite code. The -DNDEBUG=1 compiler option roughly doubles the speed of SQLite.

All tests are conducted on an otherwise quiescent machine. A simple Tcl script was used to generate and run all the tests. A copy of this Tcl script can be found in the SQLite source tree in the file tools/speedtest.tcl.

The times reported on all tests represent wall-clock time in seconds. Two separate time values are reported for SQLite. The first value is for SQLite in its default configuration with full disk synchronization turned on. With synchronization turned on, SQLite executes an fsync() system call (or the equivalent) at key points to make certain that critical data has actually been written to the disk drive surface. Synchronization is necessary to guarantee the integrity of the database if the operating system crashes or the computer powers down unexpectedly in the middle of a database update. The second time reported for SQLite is when synchronization is turned off. With synchronization off, SQLite is sometimes much faster, but there is a risk that an operating system crash or an unexpected power failure could damage the database. Generally speaking, the synchronous SQLite times are for comparison against PostgreSQL (which is also synchronous) and the asynchronous SQLite times are for comparison against the asynchronous MySQL engine.

Test 1: 1000 INSERTs

CREATE TABLE t1(a INTEGER, b INTEGER, c VARCHAR(100));
INSERT INTO t1 VALUES(1,13153,'thirteen thousand one hundred fifty three');
INSERT INTO t1 VALUES(2,75560,'seventy five thousand five hundred sixty');
... 995 lines omitted
INSERT INTO t1 VALUES(998,66289,'sixty six thousand two hundred eighty nine');
INSERT INTO t1 VALUES(999,24322,'twenty four thousand three hundred twenty two');
INSERT INTO t1 VALUES(1000,94142,'ninety four thousand one hundred forty two');

PostgreSQL:	4.373
MySQL:	0.114
SQLite 2.7.6:	13.061
SQLite 2.7.6 (nosync):	0.223

Because it does not have a central server to coordinate access, SQLite must close and reopen the database file, and thus invalidate its cache, for each transaction. In this test, each SQL statement is a separate transaction so the database file must be opened and closed and the cache must be flushed 1000 times. In spite of this, the asynchronous version of SQLite is still nearly as fast as MySQL. Notice how much slower the synchronous version is, however. SQLite calls fsync() after each synchronous transaction to make sure that all data is safely on the disk surface before continuing. For most of the 13 seconds in the synchronous test, SQLite was sitting idle waiting on disk I/O to complete.

Test 2: 25000 INSERTs in a transaction

BEGIN;
CREATE TABLE t2(a INTEGER, b INTEGER, c VARCHAR(100));
INSERT INTO t2 VALUES(1,59672,'fifty nine thousand six hundred seventy two');
... 24997 lines omitted
INSERT INTO t2 VALUES(24999,89569,'eighty nine thousand five hundred sixty nine');
INSERT INTO t2 VALUES(25000,94666,'ninety four thousand six hundred sixty six');
COMMIT;

PostgreSQL:	4.900
MySQL:	2.184
SQLite 2.7.6:	0.914
SQLite 2.7.6 (nosync):	0.757

When all the INSERTs are put in a transaction, SQLite no longer has to close and reopen the database or invalidate its cache between each statement. It also does not have to do any fsync()s until the very end. When unshackled in this way, SQLite is much faster than either PostgreSQL and MySQL.

Test 3: 25000 INSERTs into an indexed table

BEGIN;
CREATE TABLE t3(a INTEGER, b INTEGER, c VARCHAR(100));
CREATE INDEX i3 ON t3(c);
... 24998 lines omitted
INSERT INTO t3 VALUES(24999,88509,'eighty eight thousand five hundred nine');
INSERT INTO t3 VALUES(25000,84791,'eighty four thousand seven hundred ninety one');
COMMIT;

PostgreSQL:	8.175
MySQL:	3.197
SQLite 2.7.6:	1.555
SQLite 2.7.6 (nosync):	1.402

There were reports that SQLite did not perform as well on an indexed table. This test was recently added to disprove those rumors. It is true that SQLite is not as fast at creating new index entries as the other engines (see Test 6 below) but its overall speed is still better.

Test 4: 100 SELECTs without an index

BEGIN;
SELECT count(*), avg(b) FROM t2 WHERE b>=0 AND b<1000;
SELECT count(*), avg(b) FROM t2 WHERE b>=100 AND b<1100;
... 96 lines omitted
SELECT count(*), avg(b) FROM t2 WHERE b>=9800 AND b<10800;
SELECT count(*), avg(b) FROM t2 WHERE b>=9900 AND b<10900;
COMMIT;

PostgreSQL:	3.629
MySQL:	2.760
SQLite 2.7.6:	2.494
SQLite 2.7.6 (nosync):	2.526

This test does 100 queries on a 25000 entry table without an index, thus requiring a full table scan. Prior versions of SQLite used to be slower than PostgreSQL and MySQL on this test, but recent performance enhancements have increased its speed so that it is now the fastest of the group.

Test 5: 100 SELECTs on a string comparison

BEGIN;
SELECT count(*), avg(b) FROM t2 WHERE c LIKE '%one%';
SELECT count(*), avg(b) FROM t2 WHERE c LIKE '%two%';
... 96 lines omitted
SELECT count(*), avg(b) FROM t2 WHERE c LIKE '%ninety nine%';
SELECT count(*), avg(b) FROM t2 WHERE c LIKE '%one hundred%';
COMMIT;

PostgreSQL:	13.409
MySQL:	4.640
SQLite 2.7.6:	3.362
SQLite 2.7.6 (nosync):	3.372

This test still does 100 full table scans but it uses uses string comparisons instead of numerical comparisons. SQLite is over three times faster than PostgreSQL here and about 30% faster than MySQL.

Test 6: Creating an index

CREATE INDEX i2a ON t2(a);
CREATE INDEX i2b ON t2(b);

PostgreSQL:	0.381
MySQL:	0.318
SQLite 2.7.6:	0.777
SQLite 2.7.6 (nosync):	0.659

SQLite is slower at creating new indices. This is not a huge problem (since new indices are not created very often) but it is something that is being worked on. Hopefully, future versions of SQLite will do better here.

Test 7: 5000 SELECTs with an index

SELECT count(*), avg(b) FROM t2 WHERE b>=0 AND b<100;
SELECT count(*), avg(b) FROM t2 WHERE b>=100 AND b<200;
SELECT count(*), avg(b) FROM t2 WHERE b>=200 AND b<300;
... 4994 lines omitted
SELECT count(*), avg(b) FROM t2 WHERE b>=499700 AND b<499800;
SELECT count(*), avg(b) FROM t2 WHERE b>=499800 AND b<499900;
SELECT count(*), avg(b) FROM t2 WHERE b>=499900 AND b<500000;

PostgreSQL:	4.614
MySQL:	1.270
SQLite 2.7.6:	1.121
SQLite 2.7.6 (nosync):	1.162

All three database engines run faster when they have indices to work with. But SQLite is still the fastest.

Test 8: 1000 UPDATEs without an index

BEGIN;
UPDATE t1 SET b=b*2 WHERE a>=0 AND a<10;
UPDATE t1 SET b=b*2 WHERE a>=10 AND a<20;
... 996 lines omitted
UPDATE t1 SET b=b*2 WHERE a>=9980 AND a<9990;
UPDATE t1 SET b=b*2 WHERE a>=9990 AND a<10000;
COMMIT;

PostgreSQL:	1.739
MySQL:	8.410
SQLite 2.7.6:	0.637
SQLite 2.7.6 (nosync):	0.638

For this particular UPDATE test, MySQL is consistently five or ten times slower than PostgreSQL and SQLite. I do not know why. MySQL is normally a very fast engine. Perhaps this problem has been addressed in later versions of MySQL.

Test 9: 25000 UPDATEs with an index

BEGIN;
UPDATE t2 SET b=468026 WHERE a=1;
UPDATE t2 SET b=121928 WHERE a=2;
... 24996 lines omitted
UPDATE t2 SET b=35065 WHERE a=24999;
UPDATE t2 SET b=347393 WHERE a=25000;
COMMIT;

PostgreSQL:	18.797
MySQL:	8.134
SQLite 2.7.6:	3.520
SQLite 2.7.6 (nosync):	3.104

As recently as version 2.7.0, SQLite ran at about the same speed as MySQL on this test. But recent optimizations to SQLite have more than doubled speed of UPDATEs.

Test 10: 25000 text UPDATEs with an index

BEGIN;
UPDATE t2 SET c='one hundred forty eight thousand three hundred eighty two' WHERE a=1;
UPDATE t2 SET c='three hundred sixty six thousand five hundred two' WHERE a=2;
... 24996 lines omitted
UPDATE t2 SET c='three hundred eighty three thousand ninety nine' WHERE a=24999;
UPDATE t2 SET c='two hundred fifty six thousand eight hundred thirty' WHERE a=25000;
COMMIT;

PostgreSQL:	48.133
MySQL:	6.982
SQLite 2.7.6:	2.408
SQLite 2.7.6 (nosync):	1.725

Here again, version 2.7.0 of SQLite used to run at about the same speed as MySQL. But now version 2.7.6 is over two times faster than MySQL and over twenty times faster than PostgreSQL.

In fairness to PostgreSQL, it started thrashing on this test. A knowledgeable administrator might be able to get PostgreSQL to run a lot faster here by tweaking and tuning the server a little.

Test 11: INSERTs from a SELECT

BEGIN;
INSERT INTO t1 SELECT b,a,c FROM t2;
INSERT INTO t2 SELECT b,a,c FROM t1;
COMMIT;

PostgreSQL:	61.364
MySQL:	1.537
SQLite 2.7.6:	2.787
SQLite 2.7.6 (nosync):	1.599

The asynchronous SQLite is just a shade slower than MySQL on this test. (MySQL seems to be especially adept at INSERT...SELECT statements.) The PostgreSQL engine is still thrashing - most of the 61 seconds it used were spent waiting on disk I/O.

Test 12: DELETE without an index

DELETE FROM t2 WHERE c LIKE '%fifty%';

PostgreSQL:	1.509
MySQL:	0.975
SQLite 2.7.6:	4.004
SQLite 2.7.6 (nosync):	0.560

The synchronous version of SQLite is the slowest of the group in this test, but the asynchronous version is the fastest. The difference is the extra time needed to execute fsync().

Test 13: DELETE with an index

DELETE FROM t2 WHERE a>10 AND a<20000;

PostgreSQL:	1.316
MySQL:	2.262
SQLite 2.7.6:	2.068
SQLite 2.7.6 (nosync):	0.752

This test is significant because it is one of the few where PostgreSQL is faster than MySQL. The asynchronous SQLite is, however, faster then both the other two.

Test 14: A big INSERT after a big DELETE

INSERT INTO t2 SELECT * FROM t1;

PostgreSQL:	13.168
MySQL:	1.815
SQLite 2.7.6:	3.210
SQLite 2.7.6 (nosync):	1.485

Some older versions of SQLite (prior to version 2.4.0) would show decreasing performance after a sequence of DELETEs followed by new INSERTs. As this test shows, the problem has now been resolved.

Test 15: A big DELETE followed by many small INSERTs

BEGIN;
DELETE FROM t1;
INSERT INTO t1 VALUES(1,10719,'ten thousand seven hundred nineteen');
... 11997 lines omitted
INSERT INTO t1 VALUES(11999,72836,'seventy two thousand eight hundred thirty six');
INSERT INTO t1 VALUES(12000,64231,'sixty four thousand two hundred thirty one');
COMMIT;

PostgreSQL:	4.556
MySQL:	1.704
SQLite 2.7.6:	0.618
SQLite 2.7.6 (nosync):	0.406

SQLite is very good at doing INSERTs within a transaction, which probably explains why it is so much faster than the other databases at this test.

Test 16: DROP TABLE

DROP TABLE t1;
DROP TABLE t2;
DROP TABLE t3;

PostgreSQL:	0.135
MySQL:	0.015
SQLite 2.7.6:	0.939
SQLite 2.7.6 (nosync):	0.254

SQLite is slower than the other databases when it comes to dropping tables. This probably is because when SQLite drops a table, it has to go through and erase the records in the database file that deal with that table. MySQL and PostgreSQL, on the other hand, use separate files to represent each table so they can drop a table simply by deleting a file, which is much faster.

On the other hand, dropping tables is not a very common operation so if SQLite takes a little longer, that is not seen as a big problem.