Download - Postgres Scaling Opportunities and Options
![Page 1: Postgres Scaling Opportunities and Options](https://reader033.vdocuments.us/reader033/viewer/2022060205/55a20a9a1a28ab9b368b46c2/html5/thumbnails/1.jpg)
Postgres Scaling Opportunities
BRUCE MOMJIAN
December, 2014
Configuring Postgres for heavy workloads can take many forms.This talk explores available Postgres scaling options.Creative Commons Attribution License http://momjian.us/presentations
1 / 37
![Page 2: Postgres Scaling Opportunities and Options](https://reader033.vdocuments.us/reader033/viewer/2022060205/55a20a9a1a28ab9b368b46c2/html5/thumbnails/2.jpg)
PostgreSQL the database…
◮ Open Source Object Relational DBMS since 1996
◮ Distributed under the PostgreSQL License
◮ Similar technical heritage as Oracle, SQL Server & DB2
◮ However, a strong adherence to standards (ANSI-SQL 2008)
◮ Highly extensible and adaptable design
◮ Languages, indexing, data types, etc.◮ E.g. PostGIS, JSONB, SQL/MED
◮ Extensive use throughout the world for applications andorganizations of all types
◮ Bundled into Red Hat Enterprise Linux, Ubuntu, CentOSand Amazon Linux
Postgres Scaling Opportunities 2 / 37
![Page 3: Postgres Scaling Opportunities and Options](https://reader033.vdocuments.us/reader033/viewer/2022060205/55a20a9a1a28ab9b368b46c2/html5/thumbnails/3.jpg)
PostgreSQL the community…
◮ Independent community led by a Core Team of six
◮ Large, active and vibrant community
◮ www.postgresql.org
◮ Downloads, Mailing lists, Documentation
◮ Sponsors sampler:
◮ Google, Red Hat, VMWare, Skype, Salesforce, HP andEnterpriseDB
◮ http://www.postgresql.org/community/
Postgres Scaling Opportunities 3 / 37
![Page 4: Postgres Scaling Opportunities and Options](https://reader033.vdocuments.us/reader033/viewer/2022060205/55a20a9a1a28ab9b368b46c2/html5/thumbnails/4.jpg)
EnterpriseDB the company…
◮ The worldwide leader of Postgres based products andservices founded in 2004
◮ Customers include 50 of the Fortune 500 and 98 of theForbes Global 2000
◮ Enterprise offerings:
◮ PostgreSQL Support, Services and Training◮ Postgres Plus Advanced Server with Oracle Compatibility◮ Tools for Monitoring, Replication, HA, Backup & Recovery
Community
◮ Citizenship
◮ Contributor of key features: Materialized Views, JSON, &more
◮ Nine community members on staff
Postgres Scaling Opportunities 4 / 37
![Page 5: Postgres Scaling Opportunities and Options](https://reader033.vdocuments.us/reader033/viewer/2022060205/55a20a9a1a28ab9b368b46c2/html5/thumbnails/5.jpg)
EnterpriseDB the company…
Postgres Scaling Opportunities 5 / 37
![Page 6: Postgres Scaling Opportunities and Options](https://reader033.vdocuments.us/reader033/viewer/2022060205/55a20a9a1a28ab9b368b46c2/html5/thumbnails/6.jpg)
Scaling
Database scaling is the ability to increase database throughput byutilizing additional resources such as I/O, memory, CPU, oradditional computers.However, the high concurrency and write requirements ofdatabase servers make scaling a challenge. Sometimes scaling isonly possible with multiple sessions, while other options requiredata model adjustments or server configuration changes.
Postgres Scaling Opportunities 6 / 37
![Page 7: Postgres Scaling Opportunities and Options](https://reader033.vdocuments.us/reader033/viewer/2022060205/55a20a9a1a28ab9b368b46c2/html5/thumbnails/7.jpg)
Outline
Postgres scaling opportunities:
1. Multi-session
2. Single-session
3. Multi-host
Postgres Scaling Opportunities 7 / 37
![Page 8: Postgres Scaling Opportunities and Options](https://reader033.vdocuments.us/reader033/viewer/2022060205/55a20a9a1a28ab9b368b46c2/html5/thumbnails/8.jpg)
Vertical/Horizontal Scaling
���������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������
���������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������
Horizontal
Vertical
������������������������������������������������������������������������������
������������������������������������������������������������������������������
������������������������������������������������������������������������
������������������������������������������������������������������������
Postgres Scaling Opportunities 8 / 37
![Page 9: Postgres Scaling Opportunities and Options](https://reader033.vdocuments.us/reader033/viewer/2022060205/55a20a9a1a28ab9b368b46c2/html5/thumbnails/9.jpg)
Examples
Vertical scaling examples:
◮ More and faster CPUs
◮ More memory
◮ More and faster storage
◮ Battery-backed cache (BBU)
Horizontal scaling involves adding servers.
Postgres Scaling Opportunities 9 / 37
![Page 10: Postgres Scaling Opportunities and Options](https://reader033.vdocuments.us/reader033/viewer/2022060205/55a20a9a1a28ab9b368b46c2/html5/thumbnails/10.jpg)
Hardware Components
I/O
CPU
Memory
Postgres Scaling Opportunities 10 / 37
![Page 11: Postgres Scaling Opportunities and Options](https://reader033.vdocuments.us/reader033/viewer/2022060205/55a20a9a1a28ab9b368b46c2/html5/thumbnails/11.jpg)
1. Multi-Session
Clients
Database
CPU
I/O
Memory
Postgres Scaling Opportunities 11 / 37
![Page 12: Postgres Scaling Opportunities and Options](https://reader033.vdocuments.us/reader033/viewer/2022060205/55a20a9a1a28ab9b368b46c2/html5/thumbnails/12.jpg)
I/O Spreading Using Tablespaces
Clients
Database
CPU
Memory
Tablespaces
Requires tables & indexes to be spread across tablespacesTablespaces should be on different storage devices
Postgres Scaling Opportunities 12 / 37
![Page 13: Postgres Scaling Opportunities and Options](https://reader033.vdocuments.us/reader033/viewer/2022060205/55a20a9a1a28ab9b368b46c2/html5/thumbnails/13.jpg)
I/O Spreading Using Hash Partitions
�����������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������
�����������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������
Clients
Database
CPU
Memory
Hash Partitions
Requires rows to be hashed by key; one partition per device
Postgres Scaling Opportunities 13 / 37
![Page 14: Postgres Scaling Opportunities and Options](https://reader033.vdocuments.us/reader033/viewer/2022060205/55a20a9a1a28ab9b368b46c2/html5/thumbnails/14.jpg)
I/O Spreading Using RAID 0
�����������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������
�����������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������
Clients
Database
CPU
Memory
RAID 0
Auto-hashed by storage block number
Postgres Scaling Opportunities 14 / 37
![Page 15: Postgres Scaling Opportunities and Options](https://reader033.vdocuments.us/reader033/viewer/2022060205/55a20a9a1a28ab9b368b46c2/html5/thumbnails/15.jpg)
Write Spreading Using WAL Relocation
Clients
Database
CPU
Memory
WAL Tables and Indexes
Separates WAL writes from table & index I/O
Postgres Scaling Opportunities 15 / 37
![Page 16: Postgres Scaling Opportunities and Options](https://reader033.vdocuments.us/reader033/viewer/2022060205/55a20a9a1a28ab9b368b46c2/html5/thumbnails/16.jpg)
Read Reduction via Increased Memory
Clients
Database
CPU
I/O
Memory
Additional memory caching reduces read requirements
Postgres Scaling Opportunities 16 / 37
![Page 17: Postgres Scaling Opportunities and Options](https://reader033.vdocuments.us/reader033/viewer/2022060205/55a20a9a1a28ab9b368b46c2/html5/thumbnails/17.jpg)
Scaling Connections Using a Pooler
Clients
Database
Connection Pooler
CPU
I/O
Memory
Fewer idle connections reduces resource usage
Postgres Scaling Opportunities 17 / 37
![Page 18: Postgres Scaling Opportunities and Options](https://reader033.vdocuments.us/reader033/viewer/2022060205/55a20a9a1a28ab9b368b46c2/html5/thumbnails/18.jpg)
Multi-Session CPU Scaling
Clients
Database
I/O
Memory
CPU
Multiple sessions spread across available CPUs
Postgres Scaling Opportunities 18 / 37
![Page 19: Postgres Scaling Opportunities and Options](https://reader033.vdocuments.us/reader033/viewer/2022060205/55a20a9a1a28ab9b368b46c2/html5/thumbnails/19.jpg)
2. Single-Session
Client
Database
CPU
I/O
Memory
Postgres Scaling Opportunities 19 / 37
![Page 20: Postgres Scaling Opportunities and Options](https://reader033.vdocuments.us/reader033/viewer/2022060205/55a20a9a1a28ab9b368b46c2/html5/thumbnails/20.jpg)
Read Parallelism Using effective_io_concurrency
Client
Database
CPU
Memory
Table
Index
Used during bitmap heap scansAssumes table is hashed across multiple devices
Postgres Scaling Opportunities 20 / 37
![Page 21: Postgres Scaling Opportunities and Options](https://reader033.vdocuments.us/reader033/viewer/2022060205/55a20a9a1a28ab9b368b46c2/html5/thumbnails/21.jpg)
I/O Scaling via Parallelism (not implemented)
Client
Database
CPU
Memory
I/O
Involves parallel index, heap, partition, and tablespace access
Postgres Scaling Opportunities 21 / 37
![Page 22: Postgres Scaling Opportunities and Options](https://reader033.vdocuments.us/reader033/viewer/2022060205/55a20a9a1a28ab9b368b46c2/html5/thumbnails/22.jpg)
CPU Scaling via Parallelism (not implemented)Database
Client
I/O
Memory
CPU
Involves parallel sorts, joins, and function execution
Postgres Scaling Opportunities 22 / 37
![Page 23: Postgres Scaling Opportunities and Options](https://reader033.vdocuments.us/reader033/viewer/2022060205/55a20a9a1a28ab9b368b46c2/html5/thumbnails/23.jpg)
Sort I/O Reduction Using Work_mem
Client
Database
CPU
I/O
Memory Sorts/Hashes
Reduces temporary result reads & writes
Postgres Scaling Opportunities 23 / 37
![Page 24: Postgres Scaling Opportunities and Options](https://reader033.vdocuments.us/reader033/viewer/2022060205/55a20a9a1a28ab9b368b46c2/html5/thumbnails/24.jpg)
Logical Dump Parallelism
CPU
I/O
Memory
pg_dump
Dumps tables using concurrent database connections
Postgres Scaling Opportunities 24 / 37
![Page 25: Postgres Scaling Opportunities and Options](https://reader033.vdocuments.us/reader033/viewer/2022060205/55a20a9a1a28ab9b368b46c2/html5/thumbnails/25.jpg)
Logical Restore Parallelism
CPU
I/O
Memory
pg_restore
Loads tables and creates indexes using concurrent databaseconnections
Postgres Scaling Opportunities 25 / 37
![Page 26: Postgres Scaling Opportunities and Options](https://reader033.vdocuments.us/reader033/viewer/2022060205/55a20a9a1a28ab9b368b46c2/html5/thumbnails/26.jpg)
3. Multi-Host
������������������������������������������������������������������������������
������������������������������������������������������������������������������
������������������������������������������������������������������������������
������������������������������������������������������������������������������
Postgres Scaling Opportunities 26 / 37
![Page 27: Postgres Scaling Opportunities and Options](https://reader033.vdocuments.us/reader033/viewer/2022060205/55a20a9a1a28ab9b368b46c2/html5/thumbnails/27.jpg)
Read Scaling Using Pgpool & Streaming Replication
SELECTINSERT, UPDATE,
DELETE to master
host
Slave SlaveMasterreplication
to any host
replicationstreaming
������������������������������������������������������������������������������������������������
������������������������������������������������������������������������������������������������
�������������������������������������������������������������������������������������������
�������������������������������������������������������������������������������������������
pgpool
A full copy of the data exists on every node.
Postgres Scaling Opportunities 27 / 37
![Page 28: Postgres Scaling Opportunities and Options](https://reader033.vdocuments.us/reader033/viewer/2022060205/55a20a9a1a28ab9b368b46c2/html5/thumbnails/28.jpg)
CPU/Memory Scaling With AsynchronousMulti-Master
SQL Queries SQL Queries
Asynchronous
CPU
Memory
CPU
Memory
Tables and Indexes Tables and Indexes
A full copy of the data exists on every node; requires conflictresolution. The asynchronous delay allows write-load buffering.
Postgres Scaling Opportunities 28 / 37
![Page 29: Postgres Scaling Opportunities and Options](https://reader033.vdocuments.us/reader033/viewer/2022060205/55a20a9a1a28ab9b368b46c2/html5/thumbnails/29.jpg)
Oracle Real Application Clusters (RAC)
Synchronization
SQL Queries SQL Queries
CPU
Memory
CPU
Memory
CPU
Memory
WALWAL1 WAL2Tables and Indexes
Tables and indexes on shared storage; inter-node synchronizationrequired for cache consistency
Postgres Scaling Opportunities 29 / 37
![Page 30: Postgres Scaling Opportunities and Options](https://reader033.vdocuments.us/reader033/viewer/2022060205/55a20a9a1a28ab9b368b46c2/html5/thumbnails/30.jpg)
I/O Scaling with Sharding: Challenges
◮ Multi-host write queries require two-phase commit (exceptXC)
◮ Multi-host visibility snapshots are not supported (except XC)
◮ Sharding benefits are only possible with a shardableworkload
◮ Changing the sharding layout can cause downtime
◮ Additional hosts reduce reliability; additional standbyservers might be required
Postgres Scaling Opportunities 30 / 37
![Page 31: Postgres Scaling Opportunities and Options](https://reader033.vdocuments.us/reader033/viewer/2022060205/55a20a9a1a28ab9b368b46c2/html5/thumbnails/31.jpg)
Application-Based Sharding
������������������������������������������������������������������������������������������������������������������������������������������������������������������������
������������������������������������������������������������������������������������������������������������������������������������������������������������������������
�����������������������������������������������������������������������������������������������������������������������������������������������������������������
�����������������������������������������������������������������������������������������������������������������������������������������������������������������
������������������������������������������������������������������������������������������������������������������������������������������������������������������������
������������������������������������������������������������������������������������������������������������������������������������������������������������������������
Applications
SQL Queries
Data Node Data Node Data Node
Applications send queries based on the sharding layout.
Postgres Scaling Opportunities 31 / 37
![Page 32: Postgres Scaling Opportunities and Options](https://reader033.vdocuments.us/reader033/viewer/2022060205/55a20a9a1a28ab9b368b46c2/html5/thumbnails/32.jpg)
Sharding Using PL/Proxy
������������������������������������������������������������������������������������������������������������������������������������������������
������������������������������������������������������������������������������������������������������������������������������������������������
������������������������������������������������������������������������������������������������������������������������������������������������
������������������������������������������������������������������������������������������������������������������������������������������������
������������������������������������������������������������������������������������������������������������������������������������������������
������������������������������������������������������������������������������������������������������������������������������������������������
Function Calls
SQL Queries
PL/Proxy
Data Node Data Node Data Node
Requires rows to be hashed by key, supports parallel-node queryexecution
Postgres Scaling Opportunities 32 / 37
![Page 33: Postgres Scaling Opportunities and Options](https://reader033.vdocuments.us/reader033/viewer/2022060205/55a20a9a1a28ab9b368b46c2/html5/thumbnails/33.jpg)
Sharding Using Postgres-XC
�����������������������������������������������������������������������������������������������������������������������������������������������������������������
�����������������������������������������������������������������������������������������������������������������������������������������������������������������
������������������������������������������������������������������������������������������������������������������������������������������������������������������������
������������������������������������������������������������������������������������������������������������������������������������������������������������������������
������������������������������������������������������������������������������������������������������������������������������������������������������������������������
������������������������������������������������������������������������������������������������������������������������������������������������������������������������
SQL Queries
XC Coordinator
SQL Queries
Data NodeData Node Data Node
Joins, sorts, aggregates
ManagerGlobal Transaction
Enables hashing of large tables, replication of others; supportsparallel-node consistent transactions and DDL
Postgres Scaling Opportunities 33 / 37
![Page 34: Postgres Scaling Opportunities and Options](https://reader033.vdocuments.us/reader033/viewer/2022060205/55a20a9a1a28ab9b368b46c2/html5/thumbnails/34.jpg)
Scaling Using Foreign Data Wrappers
������������������������������������������������������������������������������������������������������������������������������������������������������������������������
������������������������������������������������������������������������������������������������������������������������������������������������������������������������
�����������������������������������������������������������������������������������������������������������������������������������������������������������������
�����������������������������������������������������������������������������������������������������������������������������������������������������������������
�����������������������������������������������������������������������������������������������������������������������������������������������������������������
�����������������������������������������������������������������������������������������������������������������������������������������������������������������
SQL Queries
Foreign Server Foreign Server Foreign Server
SQL Queries
PG FDW
No joins, sorts, aggregates
Requires rows to be hashed by key
Postgres Scaling Opportunities 34 / 37
![Page 35: Postgres Scaling Opportunities and Options](https://reader033.vdocuments.us/reader033/viewer/2022060205/55a20a9a1a28ab9b368b46c2/html5/thumbnails/35.jpg)
Bulk Data Scaling Using Hadoop
������������������������������������������������������������������������������������������������������������������������������������������������
������������������������������������������������������������������������������������������������������������������������������������������������
������������������������������������������������������������������������������������������������������������������������������������������
������������������������������������������������������������������������������������������������������������������������������������������
������������������������������������������������������������������������������������������������������������������������������������������
������������������������������������������������������������������������������������������������������������������������������������������
Map/Reduce Jobs
Postgres Server
Hadoop
Postgres Server Postgres Server
Postgres Scaling Opportunities 35 / 37
![Page 36: Postgres Scaling Opportunities and Options](https://reader033.vdocuments.us/reader033/viewer/2022060205/55a20a9a1a28ab9b368b46c2/html5/thumbnails/36.jpg)
Additional Resources…
◮ Postgres Downloads:
◮ www.enterprisedb.com/downloads
◮ Product and Services information:
Postgres Scaling Opportunities 36 / 37
![Page 37: Postgres Scaling Opportunities and Options](https://reader033.vdocuments.us/reader033/viewer/2022060205/55a20a9a1a28ab9b368b46c2/html5/thumbnails/37.jpg)
Conclusion
http://momjian.us/presentations thttps://www.flickr.com/photos/blieusong/
Postgres Scaling Opportunities 37 / 37