PostgreSql Introduction
- What PostgreSql is:
PostgreSQL is a popular object-relational database framework that is open source. It uses and expands the SQL language and incorporates several functions for the most complex data workloads to be stored and extended safely. As part of the University of California, Berkeley’s POSTGRES project, the roots of PostgreSQL can be traced back to 1986 and has been actively built on the core platform for more than 30 years.
For its proven architecture, durability, data integrity, powerful feature set, scalability and the commitment of the open source community behind the software to consistently deliver high-performance and groundbreaking solutions, PostgresSQL has received a good reputation. PostgreSQL The use of PostgreSQL on all major operating systems has never been simpler.
2. Why is PostgreSQL used?
PostgreSql offers several features to help developers construct applications, protect data integrity from administrators, create a fault-tolerant environment, and help you manage data, regardless of the data set size. Postgre SQL is also highly scalable, in addition to being free and open source. You can define your own data types, construct custom SQL functions, for example (this sql feature is addressed in another post: click here to jump), and even write codes from different programming languages without the database being recompiled.
PostgreSql aims to comply with the SQL standard, in which conventional features do not contradict this consistency or can lead to poor architectural decisions. Supports many of the functions required by the SQL standard, but sometimes there are slightly different syntax or functions. Over time, further progress towards consistency can be expected. PostgreSQL complies with at least 160 of the 179 required SQL:2011 core conformance features from version 11 launched in October 2018. Before that, this standard did not completely comply with any relational database.
3. An introduction to various functions in PostgreSQL is as follows:
Type of information:
Basic Types: Integer, Binary, Boolean, String
Form of structure: Date/Time, Array, Set, UUID Type:
Form of document: JSON/JSONB, XML, Key-value (Hstore)
Form of Geometry: Point, Circle, Line, Polygon
Custom Types: Composite, Customizable Types
Integrity of data
Singularity, not nullity.
Principal Key
Foreign Main Center
Constraint of Exclusion
Explicit lock, lock advisory
Concurrency, efficiency
Index:
Advanced Index
Complex Scheduling Period/Optimizer for Question
Interactive, Interactive
Regulation of multi-version concurrency (MVCC)
Parallelization of read queries and B-tree indexes building
Partition of Table
The degree of isolation of all items specified in the norm of Sql, including serializable
Compilation of Just-in-time Speech (JIT)
Reliability, recovery from catastrophe
Write the Log Ahead (WAL)
Replication: asynchronous, logical, synchronous
Recovery point-in-time (pitr), Active backup
Room of the Table
Security For Safety
Authentication: GSSAPI, SSPI, LDAP, certificate, SCRAM-SHA-256, etc.
Strong Method of Access Management
Security at column and row stage
Scaleability
The functions and procedures stored
Language Programming: PL/PGSQL, Perl, Python (more)
Foreign data wrapper: To link to other databases or streams, use the standard SQL interface.
Many extensions, including PostGIS, that provide additional functionality
Internationalization, Quest for Texts
Help global character sets, such as ICUU proofreading,
Check For Full Text
Mysql Compare:
PostgreSQL’s reliability is extremely powerful. In disaster scenarios such as crashes and power failures, engines such as Innodb have made great progress in anti-strike ability. Nonetheless, many Mysql users have experienced failure scenarios for server-level database — -Mysql framework The library is MyISAM. In contrast, in this regard, the PG database is stronger.
There’s an output limit on any device. PG performance indicators can still sustain a hyperbolic or even logarithmic curve under high concurrent reads and writes and the load is reaching the maximum, and no longer decline after the peak, whereas the MySQL information decline after a peak.
For many years, because it has a wealth of geometric forms, PG has been in a dominant position in the GIS region. They’re more than geometric forms, in truth. PG has a large number of types of details, including dictionaries, arrays, bitmaps, etc. MaySQL, by contrast, is even worse. The spatial database extension of POSTGIS is much better than my spatial MySQL and uses PGSQL. Insagram is because of PG.
The “no lock” function of PG is very popular, also including operations such as vacuum to organize data space relevant to PGSSQL’s MVCC implementation.
Functions and conditional indexes can be used by PG, which makes PG database tuning very versatile. This feature does not have MySQL. In web applications, conditional indexes are very important.
PG has extremely strong SQL programming features, rich statistical functions and support for statistical syntax, such as analytical functions (called Oracle, PG Window functions), and can write multi-language stored procedures as well. Help for R: Very good, too. MySQL is much worse at this point. There are several research functions not available. The internal data storage of Tencent is predominantly Mysql, but Hadoop+PGsql is the primary data review.
PG has several cluster architectures to choose from, plproxy can allow mirroring or sharding of the statement-level, slony can render field-level synchronization settings, standby can construct WAL file-level or stream read-write separation clusters, synchronization frequency and cluster strategy.
In general, the relational database character string has a restricted length of approximately 8k, and the infinite text type feature is limited and can only be accessed as external big data. SQL grammar has built-in regular expressions, can be indexed, and can be retrieved in full text, or can use xml xpath. The TEXT type of PG can be used directly. The paper database can be skipped if you use PG.
The characteristics of replication are very relevant for web applications. So far, Mysql is also an asynchronous replication. Synchronous, asynchronous, and semi-synchronous replication can be performed by pgsql. Furthermore, mysql synchronization, close to oracle golden gate, is focused on binlog replication, which is based on stream replication. Synchronization is difficult to accomplish. For remote replication, this approach is more fitting. Wal is based on pgsql replication, which can achieve synchronous replication. Pgsql also offers stream replication at the same time.
The support of Pgsql for numa architecture is stronger than mysql, and output reads better than mysql. The submission of pgsql may be fully asynchronous and the memory table of mysql is not realistic enough (table lock reason)
I like to learn new and better ways of doing things when working on a scale, and feel free to ask questions and make suggestions.
Also, check out another story on this.
Thanks for reading this.
