I’m participating in nuggets Creators Camp # 4, click here to learn more and learn together!
PostgreSQL series of articles
preface
This article is about PostgreSQL
Tip: The following is the body of this article, the following cases for reference
What is PostgreSQL?
PostgreSQL (pronounced post-Gres-QL) is a powerful open source object-relational database system originally conceived in 1986 as the Berkley Postgres Project. The project was evolving and changing until 1994, when developers Andrew Yu and Jolly Chen added a Structured Query Language (SQL) translator to Postgres, The release, called Postgres95, is distributed to the open source community.
In 1996, Postgres95 was again significantly changed and released as PostgresSQL version 6.0. This version of Postgres improves the speed of the back end, including the enhanced SQL92 standard and important back end features, including subselections, defaults, constraints, and triggers.
After more than 30 years of active development, it has earned a reputation for reliability, functional robustness and performance. It is more popular in foreign countries than in China, but it has become popular in China in recent years. And because it’s a free license, anyone can use, modify, and distribute PostgreSQL for any purpose, free of charge, whether private, commercial, or academic.
Simple use of PostgreSQL
2.1 Installing PostgreSQL
This article will not do too much introduction to the installation process, because there are too many relevant information, the following is a good nanny installation tutorial, recommended to you:
Developer.aliyun.com/article/745… The author Yunxi. D
Through this detailed tutorial, you can easily install successful.
How does PostgreSQL compare to other databases?
The author mainly introduces the function features, performance, reliability, whether there is technical support and whether open source free of charge
3.1 Functions and Features
PostgreSQL has most of the features found in large databases, such as transactions, subqueries, triggers, views, foreign key references, table scalability, and locks. There are also certain features that large databases don’t have, such as user-defined types, inheritance, rules, and multi-version concurrency control to reduce lock contention.
3.2 performance
PostgreSQL’s performance is comparable to open source databases, with pros and cons.
3.3 reliability
After more than 30 years of active development, each version has at least one month of beta testing, and the official website also has a release history. Through checking, stable and reliable versions can be provided for production use. Compared with some databases, the author thinks it has advantages in this aspect.
3.4 Whether Technical support is available
3.5 Whether open source is free
Truly free and open source, PostgreSQL’s open source protocol is similar to BSD or MIT and can be distributed, closed source or open source for any purpose.
License address: tldrlegal.com/license/pos…
Note:
The GNU General Public License (GNU GPL or GPL) is a widely used free software License originally written by Richard Stallman for the GNU Project. The latest version of this license agreement is “Version 3” (V3), released June 29, 2007. The GNU Lesser General Public License (LGPL) is a modified version of the GNU Lesser General Public License intended for use in some software libraries. The GPL gives the definition of free software for computer programs and uses a “Copyleft” to ensure that the program’s freedom is fully preserved. This agreement has evolved over time, and its content has become more complex, which has greatly made MySQL less open source.
Here are the specific constraints in MySQL:
(1) It is not allowed to patent changes made in MySQL; MySQL changes need to be made public and owned by Oracle. ③ Source code modification for purely academic and practice purposes is also GPL compliant; (4) Oracle MySQL Enterprise edition or advanced features will involve a fee, and Oracle does not allow other closed source products based on MySQL.
PostgreSQL vs. MySQL
| features | Postgresql | MySQL |
|---|---|---|
| describe | The world’s most advanced open source database | The world’s most popular open source database |
| The development of | Is a pure open source project | I don’t think it’s a pure open source project |
| Implementation language | C | C, C + + |
| Graphic tool | PgAdmin | MySQL Workbench |
| ACID | Yes | Yes |
| The storage engine | A single storage | Multiple storage engines, of which InnoDB and MyISAM are used the most |
| The full text retrieval | Yes | Yes |
| Delete temporary table | Deleted when the database connection is disconnected | Need to manually delete |
| Delete table | Cascading operations: that is, updating and deleting parent tables synchronously updates and deletes child tables. The reverse is the same | Cascading operations are not supported |
| Clear the table | Delete can be used to remove data from a table. However, for a large table, truncate is more efficient because the truncate deletes all rows without sweeping the entire table | Permanently deleted and cannot be undone |
| On the column | serial | Automatic incremental |
| Analytic functions | Yes | No |
| The data type | Supports multiple advanced types such as array, hStore, and User-defined Type. | SQL standard types |
| Unsigned integer | No | Yes |
| Boolean type | Yes | Internally, TINYINT(1) is used to represent Boolean values |
| IP address data type | Yes | No |
| Set column defaults | Both constants and function calls are supported | Must be a constant or CURRENT_TIMESTAMP TIMESTAMP or DATETIME column |
| Common table expressions | Yes | No |
| EXPLAIN detail output | A more detailed | It’s less detailed |
| Materialized views | Yes (Postgresql takes the concept of views to the next level by allowing views to physically store data. We call these views materialized views, which cache complex query results and then allow them to be refreshed periodically) | No |
| The check constraint | Yes | No (MySQL ignores CHECK constraint) |
| Table inheritance | Yes | No |
| A programming language for stored procedures | Ruby, Perl, Python, TCL, PL/pgSQL, SQL, JavaScript, etc. | SQL:2003 syntax for stored procedures |
| Full outer join | Yes | No |
| Intersects the operator | Yes (Postgresql’s INTERSECT operator consolidates the result sets of two or more SELECT statements into a single result set) | No |
| Except the operator | Yes (Except operator returns rows that exist in the first query clause but not in the second query clause by comparing the result sets of two or more Quires) | No |
| Part of the index | Yes | No |
| Bitmap indexes | Yes | No |
| Expression index | Yes | NO |
| Cover index | Yes (since version 9.2) | Yes (MySQL supports overwriting indexes, allowing data to be retrieved only by scanning indexes, without reference to table data. Great for large tables with millions of rows.) |
| The trigger | Supports triggers that can be fired on most command types, except for those that affect the database globally, such as roles and table Spaces | Limited to a few commands |
| partition | RANGE, LIST | RANGE, LIST, HASH, KEY, and composite partitions that use RANGE or LIST combined with HASH or KEY subpartitions |
| Task time | pgAgent | Scheduled event |
| The connection size | Each new connection is an operating system process | Each new connection is an operating system thread |
| SQL compatibility | Compatibility with most SQL | MySQL is partially SQL compatible. For example, check constraints are not supported |
| applicability | You can use PostgreSQL to perform complex queries with outstanding performance | MySQL performs well on OLAP and OLTP systems when only read speed is required. |
| Support JSON | Support for JSON and other NoSQL features such as native XML support. It also allows you to index JSON data for faster access. | MySQL supports JSON data types, but does not support any other NoSQL features. |
| The default value | The default value can only be changed at the system level | Default values can be overridden at both the session and statement levels |
| B-tree indexes | Runtime merge dynamic transformations | Two or more b-tree indexes can be used when appropriate |
| The object data | Very good object statistics | Pretty good object statistics |
Note if there is insufficient follow-up supplement, you are welcome to point out the deficiencies
conclusion
That’s my brief introduction to PostgreSQL.
Copyright belongs to NoLongerConfused. For commercial reprint please contact NoLongerConfused for authorization. For non-commercial reprint please indicate the source.