官术网_书友最值得收藏!

Performance

PostgreSQL has a proven performance. It employs several techniques to improve concurrency and scalability, including the following:

  • PostgreSQL locking system: PostgreSQL provides several types of locks at the table and row levels. PostgreSQL is able to use more granular locks that prevent locking/blocking more than necessary; this increases concurrency and decreases the blocking time.
  • Indexes: PostgreSQL provides six types of indexes: B-Tree, hash, generalized inverted index (GIN), and the Generalized Search Tree (GiST) index, SP-GiST, and Block Range Indexes (BRIN). Each index type can be used for a certain scenario. For example, B-tree can be used for equality and range queries efficiently. GIST can be used for text search and for geospatial data. PostgreSQL supports partial, unique, and multicolumn indexes. It also supports indexes on expressions and operator classes.
  • Explain, analyze, vacuum, and cluster: PostgreSQL provides several commands to boost performance and provide transparency. The explain command shows the execution plan of an SQL statement. One can change some parameter settings such as memory settings, and then compare the execution plan before and after the change. The analyze command is used to collect the statistics on tables and columns. The vacuum command is used for garbage collection to reclaim unused hard disk space. The cluster command is used to arrange data physically on the hard disk. All these commands can be configured based on the database workload.
  • Table inheritance and constraint exclusion: Table inheritance allows the creation of tables with the same structure easily. Those tables are used to store subsets of data based on a certain criteria. This allows a very fast retrieval of information in certain scenarios, because only a subset of data is accessed when answering a query,
  • Very rich SQL constructs: PostgreSQL supports very rich SQL constructs. It supports correlated and uncorrelated subqueries. It supports common table expression (CTE), window functions, and recursive queries. Once developers have learned these SQL constructs, they will be able to write a crisp SQL code very quickly. Moreover, they will be able to write complex queries with minimal effort. The PostgreSQL community keeps adding new SQL features in each release; in release 9.6, three SQL clauses were added: GROUPING SETS, CUBE, and ROLLUP.
主站蜘蛛池模板: 吴忠市| 金山区| 万州区| 杭锦旗| 长治县| 隆子县| 湄潭县| 长宁区| 临猗县| 宁河县| 永善县| 龙泉市| 蓝山县| 密云县| 阆中市| 武城县| 绍兴县| 平安县| 启东市| 三穗县| 启东市| 平度市| 兴宁市| 贡嘎县| 南靖县| 犍为县| 平凉市| 正安县| 中西区| 永定县| 龙泉市| 隆化县| 余干县| 从化市| 九龙坡区| 绵阳市| 隆德县| 当阳市| 神农架林区| 翼城县| 辛集市|