- Learning PostgreSQL 11
- Salahaldin Juba Andrey Volkov
- 351字
- 2021-07-02 13:11:44
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: btree, hash, Generalized Inverted Index (GIN), 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, btree can be used for efficient equality and range queries. 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 a SQL statement. You 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 us to easily create tables with the same structure. Those tables are used to store subsets of data based on 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.
推薦閱讀
- 程序設(shè)計(jì)與實(shí)踐(VB.NET)
- oreilly精品圖書:軟件開發(fā)者路線圖叢書(共8冊(cè))
- TypeScript圖形渲染實(shí)戰(zhàn):基于WebGL的3D架構(gòu)與實(shí)現(xiàn)
- Learning Three.js:The JavaScript 3D Library for WebGL
- IBM Cognos Business Intelligence 10.1 Dashboarding cookbook
- Simulation for Data Science with R
- MySQL數(shù)據(jù)庫應(yīng)用實(shí)戰(zhàn)教程(慕課版)
- 寫給青少年的人工智能(Python版·微課視頻版)
- PhantomJS Cookbook
- Web前端開發(fā)精品課:HTML5 Canvas開發(fā)詳解
- MonoTouch應(yīng)用開發(fā)實(shí)踐指南:使用C#和.NET開發(fā)iOS應(yīng)用
- Learning Scrapy
- Expert Angular
- Selenium WebDriver自動(dòng)化測(cè)試完全指南
- Python深度學(xué)習(xí)實(shí)戰(zhàn):基于TensorFlow和Keras的聊天機(jī)器人以及人臉、物體和語音識(shí)別