Download - Scaling postgres
/
Scaling Postgres
Denish PatelDatabase Architect
https://twitter.com/DenishPatel
Wednesday, September 18, 13
1Wednesday, September 18, 13
OmniTI
1Wednesday, September 18, 13
OmniTI
1
• Helping customersnavigate explosive growthwith technology.
Wednesday, September 18, 13
OmniTI
1
• Helping customersnavigate explosive growthwith technology.
100MM+ users
$1B+ gross online sales
Wednesday, September 18, 13
OmniTI
1
• Helping customersnavigate explosive growthwith technology.
100MM+ users
$1B+ gross online sales
Open and closed source thought leaders, experts and authors
Wednesday, September 18, 13
OmniTI
1
• Helping customersnavigate explosive growthwith technology.
100MM+ users
$1B+ gross online sales
Open and closed source thought leaders, experts and authors
Wednesday, September 18, 13
2Wednesday, September 18, 13
Talk Outline
2Wednesday, September 18, 13
Talk Outline
• Scalability
• Database Scaling needs, costs, methods
• Scaling Postgres
• Vertically
• Horizontally
• Obstacles to Scalability
• Beyond Postgres
2Wednesday, September 18, 13
3Wednesday, September 18, 13
What is Scalability?
3Wednesday, September 18, 13
What is Scalability?
3
A service is said to be scalable if when we increase the resources in a system, it results in
increased performance in a manner proportional to resources added.
Wednesday, September 18, 13
4Wednesday, September 18, 13
Why to scale databases?
4Wednesday, September 18, 13
Why to scale databases?
• Support a higher volume of users
4Wednesday, September 18, 13
Why to scale databases?
• Support a higher volume of users
4Wednesday, September 18, 13
Why to scale databases?
• Support a higher volume of users
4Wednesday, September 18, 13
Why to scale databases?
• Support a higher volume of users
4Wednesday, September 18, 13
Why to scale databases?
• Provide better performance for existing users
• Support a higher volume of users
4Wednesday, September 18, 13
Why to scale databases?
• Provide better performance for existing users
• Store a larger volume of data
• Support a higher volume of users
4Wednesday, September 18, 13
Why to scale databases?
• Provide better performance for existing users
• Store a larger volume of data
• Improve system availability
• Support a higher volume of users
4Wednesday, September 18, 13
Why to scale databases?
• Provide better performance for existing users
• Store a larger volume of data
• Improve system availability
• Geographic dispersion
• Support a higher volume of users
4Wednesday, September 18, 13
5Wednesday, September 18, 13
Why is database scalability so hard?
5Wednesday, September 18, 13
Why is database scalability so hard?
5Wednesday, September 18, 13
Why is database scalability so hard?
• Search
5Wednesday, September 18, 13
Why is database scalability so hard?
• Search
• Concurrency
5Wednesday, September 18, 13
Why is database scalability so hard?
• Search
• Concurrency
• Consistency
5Wednesday, September 18, 13
Why is database scalability so hard?
• Search
• Concurrency
• Consistency
• Speed
5Wednesday, September 18, 13
Why is database scalability so hard?
• Search
• Concurrency
• Consistency
• Speed
• Cost
5Wednesday, September 18, 13
Why is database scalability so hard?
• Search
• Concurrency
• Consistency
• Speed
• Cost
• Cost of hardware
5Wednesday, September 18, 13
Why is database scalability so hard?
• Search
• Concurrency
• Consistency
• Speed
• Cost
• Cost of hardware
• Cost deployment effort
5Wednesday, September 18, 13
Why is database scalability so hard?
• Search
• Concurrency
• Consistency
• Speed
• Cost
• Cost of hardware
• Cost deployment effort
• Cost Ongoing maintenance
5Wednesday, September 18, 13
6Wednesday, September 18, 13
Scaling Needs & Methods
6Wednesday, September 18, 13
Scaling Needs & Methods
6Wednesday, September 18, 13
Scaling Needs & Methods
• Data growth
6Wednesday, September 18, 13
Scaling Needs & Methods
• Data growth
• Read requests
6Wednesday, September 18, 13
Scaling Needs & Methods
• Data growth
• Read requests
• Write requests
6Wednesday, September 18, 13
Scaling Needs & Methods
• Data growth
• Read requests
• Write requests
• Vertical Scaling
• Horizontal Scaling
6Wednesday, September 18, 13
Scaling Needs & Methods
• Data growth
• Read requests
• Write requests
• Vertical Scaling
• Horizontal Scaling
6Wednesday, September 18, 13
Scaling Needs & Methods
• Data growth
• Read requests
• Write requests
• Vertical Scaling
• Horizontal Scaling
6Wednesday, September 18, 13
7Wednesday, September 18, 13
Vertical Scaling (Scale up)
7Wednesday, September 18, 13
Vertical Scaling (Scale up)
7Wednesday, September 18, 13
Vertical Scaling (Scale up)
• Pros
7Wednesday, September 18, 13
Vertical Scaling (Scale up)
• Pros
• Simple to implement
7Wednesday, September 18, 13
Vertical Scaling (Scale up)
• Pros
• Simple to implement
• Ease of maintenance
7Wednesday, September 18, 13
Vertical Scaling (Scale up)
• Pros
• Simple to implement
• Ease of maintenance
• Cons
7Wednesday, September 18, 13
Vertical Scaling (Scale up)
• Pros
• Simple to implement
• Ease of maintenance
• Cons
• Cost of hardware
7Wednesday, September 18, 13
Vertical Scaling (Scale up)
• Pros
• Simple to implement
• Ease of maintenance
• Cons
• Cost of hardware
• Sometimes SPOFs
7Wednesday, September 18, 13
8Wednesday, September 18, 13
Horizontal Scaling (Scale Out)
8Wednesday, September 18, 13
Horizontal Scaling (Scale Out)
8Wednesday, September 18, 13
Horizontal Scaling (Scale Out)
8Wednesday, September 18, 13
Horizontal Scaling (Scale Out)
• Pros
8Wednesday, September 18, 13
Horizontal Scaling (Scale Out)
• Pros
• Cheaper in hardware cost
8Wednesday, September 18, 13
Horizontal Scaling (Scale Out)
• Pros
• Cheaper in hardware cost
• Flexibility
8Wednesday, September 18, 13
Horizontal Scaling (Scale Out)
• Pros
• Cheaper in hardware cost
• Flexibility
• Higher fault tolerance
8Wednesday, September 18, 13
Horizontal Scaling (Scale Out)
• Pros
• Cheaper in hardware cost
• Flexibility
• Higher fault tolerance
• Cons
8Wednesday, September 18, 13
Horizontal Scaling (Scale Out)
• Pros
• Cheaper in hardware cost
• Flexibility
• Higher fault tolerance
• Cons
• Complex to implement
8Wednesday, September 18, 13
Horizontal Scaling (Scale Out)
• Pros
• Cheaper in hardware cost
• Flexibility
• Higher fault tolerance
• Cons
• Complex to implement
• Expensive to maintain
8Wednesday, September 18, 13
Horizontal Scaling (Scale Out)
• Pros
• Cheaper in hardware cost
• Flexibility
• Higher fault tolerance
• Cons
• Complex to implement
• Expensive to maintain
• Bigger footprint in the Data Center
8Wednesday, September 18, 13
Horizontal Scaling (Scale Out)
• Pros
• Cheaper in hardware cost
• Flexibility
• Higher fault tolerance
• Cons
• Complex to implement
• Expensive to maintain
• Bigger footprint in the Data Center
• No built in support in databases
8Wednesday, September 18, 13
9Wednesday, September 18, 13
Spec’ing Hardware
9Wednesday, September 18, 13
Spec’ing Hardware
• CPU
9Wednesday, September 18, 13
Spec’ing Hardware
• CPU
• 8+ cores
9Wednesday, September 18, 13
Spec’ing Hardware
• CPU
• 8+ cores
• RAM
9Wednesday, September 18, 13
Spec’ing Hardware
• CPU
• 8+ cores
• RAM
• 64GB+
9Wednesday, September 18, 13
Spec’ing Hardware
• CPU
• 8+ cores
• RAM
• 64GB+
• Disks
9Wednesday, September 18, 13
Spec’ing Hardware
• CPU
• 8+ cores
• RAM
• 64GB+
• Disks
• SSDs
9Wednesday, September 18, 13
Spec’ing Hardware
• CPU
• 8+ cores
• RAM
• 64GB+
• Disks
• SSDs
• RAID 10
9Wednesday, September 18, 13
Spec’ing Hardware
• CPU
• 8+ cores
• RAM
• 64GB+
• Disks
• SSDs
• RAID 10
• Network
9Wednesday, September 18, 13
Spec’ing Hardware
• CPU
• 8+ cores
• RAM
• 64GB+
• Disks
• SSDs
• RAID 10
• Network
• min Gigbit, 10Gigbit
9Wednesday, September 18, 13
10Wednesday, September 18, 13
Tune Postgres/memory parameters
10Wednesday, September 18, 13
Tune Postgres/memory parameters
• shared_buffers
10Wednesday, September 18, 13
Tune Postgres/memory parameters
• shared_buffers
• effective_cache_size
10Wednesday, September 18, 13
Tune Postgres/memory parameters
• shared_buffers
• effective_cache_size
• checkpoint_completion_target
10Wednesday, September 18, 13
Tune Postgres/memory parameters
• shared_buffers
• effective_cache_size
• checkpoint_completion_target
• checkpoint_segments
10Wednesday, September 18, 13
Tune Postgres/memory parameters
• shared_buffers
• effective_cache_size
• checkpoint_completion_target
• checkpoint_segments
• max_connections
10Wednesday, September 18, 13
Tune Postgres/memory parameters
• shared_buffers
• effective_cache_size
• checkpoint_completion_target
• checkpoint_segments
• max_connections
• work_mem
10Wednesday, September 18, 13
Tune Postgres/memory parameters
• shared_buffers
• effective_cache_size
• checkpoint_completion_target
• checkpoint_segments
• max_connections
• work_mem
• maintenance_work_mem
10Wednesday, September 18, 13
Tune Postgres/memory parameters
• shared_buffers
• effective_cache_size
• checkpoint_completion_target
• checkpoint_segments
• max_connections
• work_mem
• maintenance_work_mem
http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server
10Wednesday, September 18, 13
11Wednesday, September 18, 13
Tune Postgres/logging parameters
11Wednesday, September 18, 13
Tune Postgres/logging parameters
11Wednesday, September 18, 13
Tune Postgres/logging parameters
11Wednesday, September 18, 13
Tune Postgres/logging parameters
• logging_collector => 'on'
11Wednesday, September 18, 13
Tune Postgres/logging parameters
• logging_collector => 'on'
• log_destination => 'stderr'
11Wednesday, September 18, 13
Tune Postgres/logging parameters
• logging_collector => 'on'
• log_destination => 'stderr'
• log_filename => 'postgresql-%Y-%m-%d_%H%M%S.log'
11Wednesday, September 18, 13
Tune Postgres/logging parameters
• logging_collector => 'on'
• log_destination => 'stderr'
• log_filename => 'postgresql-%Y-%m-%d_%H%M%S.log'
• log_line_prefix => '%m [%r] [%p]: [%l-1] user=%u,db=%d,e=%e '
11Wednesday, September 18, 13
Tune Postgres/logging parameters
• logging_collector => 'on'
• log_destination => 'stderr'
• log_filename => 'postgresql-%Y-%m-%d_%H%M%S.log'
• log_line_prefix => '%m [%r] [%p]: [%l-1] user=%u,db=%d,e=%e '
• log_min_duration_statement => 1000ms
11Wednesday, September 18, 13
Tune Postgres/logging parameters
• logging_collector => 'on'
• log_destination => 'stderr'
• log_filename => 'postgresql-%Y-%m-%d_%H%M%S.log'
• log_line_prefix => '%m [%r] [%p]: [%l-1] user=%u,db=%d,e=%e '
• log_min_duration_statement => 1000ms
• log_autovacuum_min_duration => '0'
11Wednesday, September 18, 13
12Wednesday, September 18, 13
Tune Postgres/logging parameters
12Wednesday, September 18, 13
Tune Postgres/logging parameters
12Wednesday, September 18, 13
Tune Postgres/logging parameters
12Wednesday, September 18, 13
Tune Postgres/logging parameters
• log_lock_waits => 'on'
12Wednesday, September 18, 13
Tune Postgres/logging parameters
• log_lock_waits => 'on'
• log_temp_files => '0'
12Wednesday, September 18, 13
Tune Postgres/logging parameters
• log_lock_waits => 'on'
• log_temp_files => '0'
• log_checkpoints => 'on'
12Wednesday, September 18, 13
Tune Postgres/logging parameters
• log_lock_waits => 'on'
• log_temp_files => '0'
• log_checkpoints => 'on'
• log_connections => 'on'
12Wednesday, September 18, 13
Tune Postgres/logging parameters
• log_lock_waits => 'on'
• log_temp_files => '0'
• log_checkpoints => 'on'
• log_connections => 'on'
• log_disconnections => 'on'
12Wednesday, September 18, 13
Tune Postgres/logging parameters
• log_lock_waits => 'on'
• log_temp_files => '0'
• log_checkpoints => 'on'
• log_connections => 'on'
• log_disconnections => 'on'
• log_min_error_statement => 'warning'
12Wednesday, September 18, 13
Tune Postgres/logging parameters
• log_lock_waits => 'on'
• log_temp_files => '0'
• log_checkpoints => 'on'
• log_connections => 'on'
• log_disconnections => 'on'
• log_min_error_statement => 'warning'
• log_min_messages => 'warning'
12Wednesday, September 18, 13
Tune Postgres/logging parameters
• log_lock_waits => 'on'
• log_temp_files => '0'
• log_checkpoints => 'on'
• log_connections => 'on'
• log_disconnections => 'on'
• log_min_error_statement => 'warning'
• log_min_messages => 'warning'
• log_statement => 'ddl'
12Wednesday, September 18, 13
13Wednesday, September 18, 13
Optimize Queries/report slow queries
13Wednesday, September 18, 13
Optimize Queries/report slow queries
13Wednesday, September 18, 13
Optimize Queries/report slow queries
13Wednesday, September 18, 13
14Wednesday, September 18, 13
Optimize Queries/Extensive monitoring
14Wednesday, September 18, 13
Optimize Queries/Extensive monitoring
14Wednesday, September 18, 13
15Wednesday, September 18, 13
Optimize Queries /Explain Analyze
15Wednesday, September 18, 13
Optimize Queries /Explain Analyze
explain (analyze,buffers) select col1,col2 from demo_ios where col2 between 0.01 and 0.02; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------- Index Only Scan using idx_demo_ios on demo_ios (cost=0.00..35330.93 rows=993633 width=16) (actual time=58.100..3250.589 rows=1000392 loops=1) Index Cond: ((col2 >= 0.01::double precision) AND (col2 <= 0.02::double precision)) Heap Fetches: 0 Buffers: shared hit=923073 read=3848 Total runtime: 4297.405 ms
15Wednesday, September 18, 13
Optimize Queries /Explain Analyze
explain (analyze,buffers) select col1,col2 from demo_ios where col2 between 0.01 and 0.02; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------- Index Only Scan using idx_demo_ios on demo_ios (cost=0.00..35330.93 rows=993633 width=16) (actual time=58.100..3250.589 rows=1000392 loops=1) Index Cond: ((col2 >= 0.01::double precision) AND (col2 <= 0.02::double precision)) Heap Fetches: 0 Buffers: shared hit=923073 read=3848 Total runtime: 4297.405 ms
15Wednesday, September 18, 13
16Wednesday, September 18, 13
Optimize Queries /track functions
16Wednesday, September 18, 13
Optimize Queries /track functions
16Wednesday, September 18, 13
Optimize Queries /track functions
• track_functions = pl # none, pl, all
16Wednesday, September 18, 13
Optimize Queries /track functions
• track_functions = pl # none, pl, all
• reload online
16Wednesday, September 18, 13
Optimize Queries /track functions
• track_functions = pl # none, pl, all
• reload online
• select * from pg_stat_user_functions;
16Wednesday, September 18, 13
Optimize Queries /track functions
• track_functions = pl # none, pl, all
• reload online
• select * from pg_stat_user_functions;
16Wednesday, September 18, 13
Optimize Queries /track functions
• track_functions = pl # none, pl, all
• reload online
• select * from pg_stat_user_functions;
funcid | schemaname | funcname | calls | total_time | self_time
16Wednesday, September 18, 13
Optimize Queries /track functions
• track_functions = pl # none, pl, all
• reload online
• select * from pg_stat_user_functions;
funcid | schemaname | funcname | calls | total_time | self_time
16Wednesday, September 18, 13
17Wednesday, September 18, 13
Partitioning
17Wednesday, September 18, 13
Partitioning
• As table size grows, queries eventually slows down, even with indexing
17Wednesday, September 18, 13
Partitioning
• As table size grows, queries eventually slows down, even with indexing
• Allows data added, removed and queried fast
17Wednesday, September 18, 13
Partitioning
• As table size grows, queries eventually slows down, even with indexing
• Allows data added, removed and queried fast
• Partitioning pruning queries
17Wednesday, September 18, 13
Partitioning
• As table size grows, queries eventually slows down, even with indexing
• Allows data added, removed and queried fast
• Partitioning pruning queries
• Manage partitions
17Wednesday, September 18, 13
18Wednesday, September 18, 13
Partitioning /Postgres
18Wednesday, September 18, 13
Partitioning /Postgres
18Wednesday, September 18, 13
Partitioning /Postgres
• Postgres partitioning
18Wednesday, September 18, 13
Partitioning /Postgres
• Postgres partitioning
• Trigger based
18Wednesday, September 18, 13
Partitioning /Postgres
• Postgres partitioning
• Trigger based
• Rule based
18Wednesday, September 18, 13
Partitioning /Postgres
• Postgres partitioning
• Trigger based
• Rule based
• Lack of built-in Postgres partition management
18Wednesday, September 18, 13
Partitioning /Postgres
• Postgres partitioning
• Trigger based
• Rule based
• Lack of built-in Postgres partition management
• Postgres partition management extension: pg_partman
18Wednesday, September 18, 13
Partitioning /Postgres
• Postgres partitioning
• Trigger based
• Rule based
• Lack of built-in Postgres partition management
• Postgres partition management extension: pg_partman
• http://pgxn.org/dist/pg_partman/doc/pg_partman.html
18Wednesday, September 18, 13
Partitioning /Postgres
• Postgres partitioning
• Trigger based
• Rule based
• Lack of built-in Postgres partition management
• Postgres partition management extension: pg_partman
• http://pgxn.org/dist/pg_partman/doc/pg_partman.html
• Tomorrow’s session at 11:30AM (When Postgres can’t....)
18Wednesday, September 18, 13
19Wednesday, September 18, 13
Partitioning /functional
19Wednesday, September 18, 13
Partitioning /functional
Configuration Data Transaction data Session data
Configuration Tools Reporting Tools Monitoring Tools
Web Applications Other Applications
19Wednesday, September 18, 13
Partitioning /functional
Configuration Data Transaction data Session data
Configuration Tools Reporting Tools Monitoring Tools
Web Applications Other Applications
• Partition data based on functionality
19Wednesday, September 18, 13
Partitioning /functional
Configuration Data Transaction data Session data
Configuration Tools Reporting Tools Monitoring Tools
Web Applications Other Applications
• Partition data based on functionality
• Separate Postgres clusters
19Wednesday, September 18, 13
Partitioning /functional
Configuration Data Transaction data Session data
Configuration Tools Reporting Tools Monitoring Tools
Web Applications Other Applications
• Partition data based on functionality
• Separate Postgres clusters
• Start with Separate schemas
19Wednesday, September 18, 13
Partitioning /functional
Configuration Data Transaction data Session data
Configuration Tools Reporting Tools Monitoring Tools
Web Applications Other Applications
• Partition data based on functionality
• Separate Postgres clusters
• Start with Separate schemas
• No relationship between data
19Wednesday, September 18, 13
Partitioning /functional
Configuration Data Transaction data Session data
Configuration Tools Reporting Tools Monitoring Tools
Web Applications Other Applications
• Partition data based on functionality
• Separate Postgres clusters
• Start with Separate schemas
• No relationship between data
• Help to spread the load across server
19Wednesday, September 18, 13
Partitioning /functional
Configuration Data Transaction data Session data
Configuration Tools Reporting Tools Monitoring Tools
Web Applications Other Applications
• Partition data based on functionality
• Separate Postgres clusters
• Start with Separate schemas
• No relationship between data
• Help to spread the load across server
• Less complex compare to sharding!
19Wednesday, September 18, 13
20Wednesday, September 18, 13
pgbouncer
20Wednesday, September 18, 13
pgbouncer
20Wednesday, September 18, 13
pgbouncer
20Wednesday, September 18, 13
pgbouncer
20Wednesday, September 18, 13
pgbouncer
• A lightweight connection pooler
20Wednesday, September 18, 13
pgbouncer
• A lightweight connection pooler
• Helps to reduce # of newly created connections on DB server
20Wednesday, September 18, 13
pgbouncer
• A lightweight connection pooler
• Helps to reduce # of newly created connections on DB server
• Abstracts DBs from App
20Wednesday, September 18, 13
pgbouncer
• A lightweight connection pooler
• Helps to reduce # of newly created connections on DB server
• Abstracts DBs from App
• Helps to instrument smooth and easy failover
20Wednesday, September 18, 13
pgbouncer
• A lightweight connection pooler
• Helps to reduce # of newly created connections on DB server
• Abstracts DBs from App
• Helps to instrument smooth and easy failover
• Connection pooling Options
20Wednesday, September 18, 13
pgbouncer
• A lightweight connection pooler
• Helps to reduce # of newly created connections on DB server
• Abstracts DBs from App
• Helps to instrument smooth and easy failover
• Connection pooling Options
• Session, Transaction, Statement pooling options
20Wednesday, September 18, 13
pgbouncer
• A lightweight connection pooler
• Helps to reduce # of newly created connections on DB server
• Abstracts DBs from App
• Helps to instrument smooth and easy failover
• Connection pooling Options
• Session, Transaction, Statement pooling options
• Beware! Transaction pooling doesn’t support prepared transactions
20Wednesday, September 18, 13
21Wednesday, September 18, 13
Caching
21Wednesday, September 18, 13
Caching
21Wednesday, September 18, 13
Caching
• Memcached
21Wednesday, September 18, 13
Caching
• Memcached
• Open source, High-performance distributed memory object caching system
21Wednesday, September 18, 13
Caching
• Memcached
• Open source, High-performance distributed memory object caching system
• Speeds up dynamic web applications by alleviating database load.
21Wednesday, September 18, 13
Caching
• Memcached
• Open source, High-performance distributed memory object caching system
• Speeds up dynamic web applications by alleviating database load.
• An in-memory key-value store for small chunks of arbitrary data
21Wednesday, September 18, 13
Caching
• Memcached
• Open source, High-performance distributed memory object caching system
• Speeds up dynamic web applications by alleviating database load.
• An in-memory key-value store for small chunks of arbitrary data
• Redis
21Wednesday, September 18, 13
Caching
• Memcached
• Open source, High-performance distributed memory object caching system
• Speeds up dynamic web applications by alleviating database load.
• An in-memory key-value store for small chunks of arbitrary data
• Redis
• Open source, advanced key-value store.
21Wednesday, September 18, 13
Caching
• Memcached
• Open source, High-performance distributed memory object caching system
• Speeds up dynamic web applications by alleviating database load.
• An in-memory key-value store for small chunks of arbitrary data
• Redis
• Open source, advanced key-value store.
• Works with an in-memory & persistent dataset
21Wednesday, September 18, 13
22Wednesday, September 18, 13
Replication /built-in
22Wednesday, September 18, 13
Replication /built-in
• Cluster Level Replication (Binary)
22Wednesday, September 18, 13
Replication /built-in
• Cluster Level Replication (Binary)
• Streaming Replication
22Wednesday, September 18, 13
Replication /built-in
• Cluster Level Replication (Binary)
• Streaming Replication
• WAL-only replication
22Wednesday, September 18, 13
Replication /built-in
• Cluster Level Replication (Binary)
• Streaming Replication
• WAL-only replication
• Hybrid replication
22Wednesday, September 18, 13
Replication /built-in
Master
• Cluster Level Replication (Binary)
• Streaming Replication
• WAL-only replication
• Hybrid replication
22Wednesday, September 18, 13
Replication /built-in
Master
Failover • Cluster Level Replication (Binary)
• Streaming Replication
• WAL-only replication
• Hybrid replication
22Wednesday, September 18, 13
Replication /built-in
Master
Failover
Read Salve 1
• Cluster Level Replication (Binary)
• Streaming Replication
• WAL-only replication
• Hybrid replication
22Wednesday, September 18, 13
Replication /built-in
Master
Failover
Read Salve 1
• Cluster Level Replication (Binary)
• Streaming Replication
• WAL-only replication
• Hybrid replication
22Wednesday, September 18, 13
Replication /built-in
Master
Failover
Read Salve 1
• Cluster Level Replication (Binary)
• Streaming Replication
• WAL-only replication
• Hybrid replication
22Wednesday, September 18, 13
Replication /built-in
Master
Failover
Read Salve 1
Streaming Replication
• Cluster Level Replication (Binary)
• Streaming Replication
• WAL-only replication
• Hybrid replication
22Wednesday, September 18, 13
Replication /built-in
Master
Failover
Read Salve 1
PITR!
Streaming Replication
• Cluster Level Replication (Binary)
• Streaming Replication
• WAL-only replication
• Hybrid replication
22Wednesday, September 18, 13
Replication /built-in
Master
Failover
Read Salve 1
PITR!
PITR!
Streaming Replication
• Cluster Level Replication (Binary)
• Streaming Replication
• WAL-only replication
• Hybrid replication
22Wednesday, September 18, 13
Replication /built-in
Master
Failover
Read Salve 1
PITR!
PITR!
PITR!Streaming Replication
• Cluster Level Replication (Binary)
• Streaming Replication
• WAL-only replication
• Hybrid replication
22Wednesday, September 18, 13
Replication /built-in
Master
Failover
Read Salve 1
PITR!
PITR!
PITR!Streaming Replication
• Cluster Level Replication (Binary)
• Streaming Replication
• WAL-only replication
• Hybrid replication
22Wednesday, September 18, 13
Replication /built-in
• Pros:
Master
Failover
Read Salve 1
PITR!
PITR!
PITR!Streaming Replication
• Cluster Level Replication (Binary)
• Streaming Replication
• WAL-only replication
• Hybrid replication
22Wednesday, September 18, 13
Replication /built-in
• Pros:
• Built-in
Master
Failover
Read Salve 1
PITR!
PITR!
PITR!Streaming Replication
• Cluster Level Replication (Binary)
• Streaming Replication
• WAL-only replication
• Hybrid replication
22Wednesday, September 18, 13
Replication /built-in
• Pros:
• Built-in
• Allows to open replicated database in read-only mode
Master
Failover
Read Salve 1
PITR!
PITR!
PITR!Streaming Replication
• Cluster Level Replication (Binary)
• Streaming Replication
• WAL-only replication
• Hybrid replication
22Wednesday, September 18, 13
Replication /built-in
• Pros:
• Built-in
• Allows to open replicated database in read-only mode
• Cons:
Master
Failover
Read Salve 1
PITR!
PITR!
PITR!Streaming Replication
• Cluster Level Replication (Binary)
• Streaming Replication
• WAL-only replication
• Hybrid replication
22Wednesday, September 18, 13
Replication /built-in
• Pros:
• Built-in
• Allows to open replicated database in read-only mode
• Cons:
• All or none
Master
Failover
Read Salve 1
PITR!
PITR!
PITR!Streaming Replication
• Cluster Level Replication (Binary)
• Streaming Replication
• WAL-only replication
• Hybrid replication
22Wednesday, September 18, 13
Replication /built-in
• Pros:
• Built-in
• Allows to open replicated database in read-only mode
• Cons:
• All or none
• Doesn’t allow write on replicated database
Master
Failover
Read Salve 1
PITR!
PITR!
PITR!Streaming Replication
• Cluster Level Replication (Binary)
• Streaming Replication
• WAL-only replication
• Hybrid replication
22Wednesday, September 18, 13
Replication /built-in
• Pros:
• Built-in
• Allows to open replicated database in read-only mode
• Cons:
• All or none
• Doesn’t allow write on replicated database
• Doesn’t work across major version
Master
Failover
Read Salve 1
PITR!
PITR!
PITR!Streaming Replication
• Cluster Level Replication (Binary)
• Streaming Replication
• WAL-only replication
• Hybrid replication
22Wednesday, September 18, 13
Replication /built-in
• Pros:
• Built-in
• Allows to open replicated database in read-only mode
• Cons:
• All or none
• Doesn’t allow write on replicated database
• Doesn’t work across major version
• Postgres 9.2 (primary) does NOT replicate to 9.3 (secondary)
Master
Failover
Read Salve 1
PITR!
PITR!
PITR!Streaming Replication
• Cluster Level Replication (Binary)
• Streaming Replication
• WAL-only replication
• Hybrid replication
22Wednesday, September 18, 13
Replication /built-in
• Pros:
• Built-in
• Allows to open replicated database in read-only mode
• Cons:
• All or none
• Doesn’t allow write on replicated database
• Doesn’t work across major version
• Postgres 9.2 (primary) does NOT replicate to 9.3 (secondary)
• Postgres 9.2.1 (primary) can replicate to 9.2.4 (secondary)
Master
Failover
Read Salve 1
PITR!
PITR!
PITR!Streaming Replication
• Cluster Level Replication (Binary)
• Streaming Replication
• WAL-only replication
• Hybrid replication
22Wednesday, September 18, 13
Replication /built-in
• Pros:
• Built-in
• Allows to open replicated database in read-only mode
• Cons:
• All or none
• Doesn’t allow write on replicated database
• Doesn’t work across major version
• Postgres 9.2 (primary) does NOT replicate to 9.3 (secondary)
• Postgres 9.2.1 (primary) can replicate to 9.2.4 (secondary)
Master
Failover
Read Salve 1
PITR!
PITR!
PITR!Streaming Replication
https://wiki.postgresql.org/wiki/Binary_Replication_Tools
• Cluster Level Replication (Binary)
• Streaming Replication
• WAL-only replication
• Hybrid replication
22Wednesday, September 18, 13
23Wednesday, September 18, 13
Replication /built-in
23Wednesday, September 18, 13
Replication /built-in
Read Salve 1
23Wednesday, September 18, 13
Replication /built-in
Read Salve 1
DW System
23Wednesday, September 18, 13
Replication /built-in
Read Salve 1
DW System
23Wednesday, September 18, 13
Replication /built-in
Read Salve 1
DW System
postgres_fdw
23Wednesday, September 18, 13
Replication /built-in
Read Salve 1
DW System
postgres_fdw
23Wednesday, September 18, 13
Replication /built-in
Read Salve 1
DW System
postgres_fdw
23Wednesday, September 18, 13
Replication /built-in
• postgres_fdwRead
Salve 1
DW System
postgres_fdw
23Wednesday, September 18, 13
Replication /built-in
• postgres_fdw
• Postgres 9.3 feature
Read Salve 1
DW System
postgres_fdw
23Wednesday, September 18, 13
Replication /built-in
• postgres_fdw
• Postgres 9.3 feature
• Allows to access data stored in external PostgreSQL servers
Read Salve 1
DW System
postgres_fdw
23Wednesday, September 18, 13
Replication /built-in
• postgres_fdw
• Postgres 9.3 feature
• Allows to access data stored in external PostgreSQL servers
• cross version queries
Read Salve 1
DW System
postgres_fdw
23Wednesday, September 18, 13
Replication /built-in
• postgres_fdw
• Postgres 9.3 feature
• Allows to access data stored in external PostgreSQL servers
• cross version queries
• Postgres 9.3 could query Postgres 9.1
Read Salve 1
DW System
postgres_fdw
23Wednesday, September 18, 13
Replication /built-in
• postgres_fdw
• Postgres 9.3 feature
• Allows to access data stored in external PostgreSQL servers
• cross version queries
• Postgres 9.3 could query Postgres 9.1
• Application
Read Salve 1
DW System
postgres_fdw
23Wednesday, September 18, 13
Replication /built-in
• postgres_fdw
• Postgres 9.3 feature
• Allows to access data stored in external PostgreSQL servers
• cross version queries
• Postgres 9.3 could query Postgres 9.1
• Application
• Run query remotely on slave db
Read Salve 1
DW System
postgres_fdw
23Wednesday, September 18, 13
Replication /built-in
• postgres_fdw
• Postgres 9.3 feature
• Allows to access data stored in external PostgreSQL servers
• cross version queries
• Postgres 9.3 could query Postgres 9.1
• Application
• Run query remotely on slave db
• Data warehouse data refreshes
Read Salve 1
DW System
postgres_fdw
23Wednesday, September 18, 13
24Wednesday, September 18, 13
Replication /third-party tools
24Wednesday, September 18, 13
Replication /third-party tools
24Wednesday, September 18, 13
Replication /third-party tools
24Wednesday, September 18, 13
Replication /third-party tools
• Table level Replication Tools (Trigger based)
24Wednesday, September 18, 13
Replication /third-party tools
• Table level Replication Tools (Trigger based)
• Slony
24Wednesday, September 18, 13
Replication /third-party tools
• Table level Replication Tools (Trigger based)
• Slony
• Bucardo
24Wednesday, September 18, 13
Replication /third-party tools
• Table level Replication Tools (Trigger based)
• Slony
• Bucardo
• Mimeo: http://pgxn.org/dist/mimeo/
24Wednesday, September 18, 13
Replication /third-party tools
• Table level Replication Tools (Trigger based)
• Slony
• Bucardo
• Mimeo: http://pgxn.org/dist/mimeo/
• Pros:
24Wednesday, September 18, 13
Replication /third-party tools
• Table level Replication Tools (Trigger based)
• Slony
• Bucardo
• Mimeo: http://pgxn.org/dist/mimeo/
• Pros:
• Allows to open replicated database in read-write mode
24Wednesday, September 18, 13
Replication /third-party tools
• Table level Replication Tools (Trigger based)
• Slony
• Bucardo
• Mimeo: http://pgxn.org/dist/mimeo/
• Pros:
• Allows to open replicated database in read-write mode
• Allows table/database level replication
24Wednesday, September 18, 13
Replication /third-party tools
• Table level Replication Tools (Trigger based)
• Slony
• Bucardo
• Mimeo: http://pgxn.org/dist/mimeo/
• Pros:
• Allows to open replicated database in read-write mode
• Allows table/database level replication
• Allows rolling upgrade
24Wednesday, September 18, 13
Replication /third-party tools
• Table level Replication Tools (Trigger based)
• Slony
• Bucardo
• Mimeo: http://pgxn.org/dist/mimeo/
• Pros:
• Allows to open replicated database in read-write mode
• Allows table/database level replication
• Allows rolling upgrade
• cross version replication is allowed
24Wednesday, September 18, 13
Replication /third-party tools
• Table level Replication Tools (Trigger based)
• Slony
• Bucardo
• Mimeo: http://pgxn.org/dist/mimeo/
• Pros:
• Allows to open replicated database in read-write mode
• Allows table/database level replication
• Allows rolling upgrade
• cross version replication is allowed
• Multi-master replication
24Wednesday, September 18, 13
Replication /third-party tools
• Table level Replication Tools (Trigger based)
• Slony
• Bucardo
• Mimeo: http://pgxn.org/dist/mimeo/
• Pros:
• Allows to open replicated database in read-write mode
• Allows table/database level replication
• Allows rolling upgrade
• cross version replication is allowed
• Multi-master replication
• Cons:
24Wednesday, September 18, 13
Replication /third-party tools
• Table level Replication Tools (Trigger based)
• Slony
• Bucardo
• Mimeo: http://pgxn.org/dist/mimeo/
• Pros:
• Allows to open replicated database in read-write mode
• Allows table/database level replication
• Allows rolling upgrade
• cross version replication is allowed
• Multi-master replication
• Cons:
• Complicated to setup
24Wednesday, September 18, 13
Replication /third-party tools
• Table level Replication Tools (Trigger based)
• Slony
• Bucardo
• Mimeo: http://pgxn.org/dist/mimeo/
• Pros:
• Allows to open replicated database in read-write mode
• Allows table/database level replication
• Allows rolling upgrade
• cross version replication is allowed
• Multi-master replication
• Cons:
• Complicated to setup
• Unknown territory
24Wednesday, September 18, 13
25Wednesday, September 18, 13
Sharding
25Wednesday, September 18, 13
Sharding
25Wednesday, September 18, 13
Sharding
25Wednesday, September 18, 13
Sharding
• Sharding is the process of splitting up your data so it resides in different tables or often different physical databases.
25Wednesday, September 18, 13
Sharding
• Sharding is the process of splitting up your data so it resides in different tables or often different physical databases.
• Application aware sharding
25Wednesday, September 18, 13
Sharding
• Sharding is the process of splitting up your data so it resides in different tables or often different physical databases.
• Application aware sharding
• Application transparent sharding
25Wednesday, September 18, 13
26Wednesday, September 18, 13
Application aware sharding
26Wednesday, September 18, 13
Application aware sharding
26Wednesday, September 18, 13
Application aware sharding
26Wednesday, September 18, 13
Application aware sharding
• http://instagram-engineering.tumblr.com/post/10853187575/sharding-ids-at-instagram
26Wednesday, September 18, 13
Application aware sharding
• http://instagram-engineering.tumblr.com/post/10853187575/sharding-ids-at-instagram
• Postgres allows “logical” shards through Schema
26Wednesday, September 18, 13
Application aware sharding
• http://instagram-engineering.tumblr.com/post/10853187575/sharding-ids-at-instagram
• Postgres allows “logical” shards through Schema
• Easy to move to “physical” shard later
26Wednesday, September 18, 13
27Wednesday, September 18, 13
Application transparent sharding
27Wednesday, September 18, 13
Application transparent sharding
27Wednesday, September 18, 13
28Wednesday, September 18, 13
Sharding Challenges
28Wednesday, September 18, 13
Sharding Challenges
• Reliability
28Wednesday, September 18, 13
Sharding Challenges
• Reliability
• Distributed queries
28Wednesday, September 18, 13
Sharding Challenges
• Reliability
• Distributed queries
• Cross-shard join
28Wednesday, September 18, 13
Sharding Challenges
• Reliability
• Distributed queries
• Cross-shard join
• Auto-increment key management
28Wednesday, September 18, 13
Sharding Challenges
• Reliability
• Distributed queries
• Cross-shard join
• Auto-increment key management
• Choosing shard key
28Wednesday, September 18, 13
Sharding Challenges
• Reliability
• Distributed queries
• Cross-shard join
• Auto-increment key management
• Choosing shard key
• Shard schemes
28Wednesday, September 18, 13
29Wednesday, September 18, 13
Obstacles for Scaling Postgres
29Wednesday, September 18, 13
Obstacles for Scaling Postgres
29Wednesday, September 18, 13
Obstacles for Scaling Postgres
• Postgres table bloat
29Wednesday, September 18, 13
Obstacles for Scaling Postgres
• Postgres table bloat
• FKs relationships
29Wednesday, September 18, 13
Obstacles for Scaling Postgres
• Postgres table bloat
• FKs relationships
• Insufficient logging
29Wednesday, September 18, 13
Obstacles for Scaling Postgres
• Postgres table bloat
• FKs relationships
• Insufficient logging
• Insufficient Caching
29Wednesday, September 18, 13
Obstacles for Scaling Postgres
• Postgres table bloat
• FKs relationships
• Insufficient logging
• Insufficient Caching
• Insufficient Monitoring and Metrics
29Wednesday, September 18, 13
Obstacles for Scaling Postgres
• Postgres table bloat
• FKs relationships
• Insufficient logging
• Insufficient Caching
• Insufficient Monitoring and Metrics
• ORMs
29Wednesday, September 18, 13
Obstacles for Scaling Postgres
• Postgres table bloat
• FKs relationships
• Insufficient logging
• Insufficient Caching
• Insufficient Monitoring and Metrics
• ORMs
• Single Point of Failure
29Wednesday, September 18, 13
Obstacles for Scaling Postgres
• Postgres table bloat
• FKs relationships
• Insufficient logging
• Insufficient Caching
• Insufficient Monitoring and Metrics
• ORMs
• Single Point of Failure
• Lack of communications between teams
29Wednesday, September 18, 13
30Wednesday, September 18, 13
Beyond Postgres
30Wednesday, September 18, 13
Beyond Postgres
30Wednesday, September 18, 13
Beyond Postgres
• Avoid serialization in application code
30Wednesday, September 18, 13
Beyond Postgres
• Avoid serialization in application code
• Feature Flags
30Wednesday, September 18, 13
Beyond Postgres
• Avoid serialization in application code
• Feature Flags
• Browse only mode (Read only mode)
30Wednesday, September 18, 13
Beyond Postgres
• Avoid serialization in application code
• Feature Flags
• Browse only mode (Read only mode)
• Don’t use database for Queuing
30Wednesday, September 18, 13
Beyond Postgres
• Avoid serialization in application code
• Feature Flags
• Browse only mode (Read only mode)
• Don’t use database for Queuing
• RabbitMQ
30Wednesday, September 18, 13
Beyond Postgres
• Avoid serialization in application code
• Feature Flags
• Browse only mode (Read only mode)
• Don’t use database for Queuing
• RabbitMQ
• Reconsider options for Full Text Search
30Wednesday, September 18, 13
Beyond Postgres
• Avoid serialization in application code
• Feature Flags
• Browse only mode (Read only mode)
• Don’t use database for Queuing
• RabbitMQ
• Reconsider options for Full Text Search
• tsearch provided by Postgres
30Wednesday, September 18, 13
Beyond Postgres
• Avoid serialization in application code
• Feature Flags
• Browse only mode (Read only mode)
• Don’t use database for Queuing
• RabbitMQ
• Reconsider options for Full Text Search
• tsearch provided by Postgres
• Solr, Lucene
30Wednesday, September 18, 13
31Wednesday, September 18, 13
Further reading . . .
31Wednesday, September 18, 13
Further reading . . .
• Scalable Internet Architectures - Theo Schlossnagle
• Web Operations: Keeping the Data On Time - John Allspaw , Jesse Robbins
• PostgreSQL 9.0 High Performance - Greg Smith
31Wednesday, September 18, 13
32Wednesday, September 18, 13
References
32Wednesday, September 18, 13
References
32Wednesday, September 18, 13
References
• http://www.postgresql.org/docs/
• http://www.circonus.com
• http://pgbouncer.projects.pgfoundry.org/doc/usage.html
• http://pgxn.org/dist/pg_partman/doc/pg_partman.html
• http://dalibo.github.io/pgbadger/
• https://github.com/omniti-labs/omnipitr
• http://instagram-engineering.tumblr.com/post/10853187575/sharding-ids-at-instagram
• http://memcached.org
• http://redis.io
• http://postgres-xc.sourceforge.net/docs/1_1/
32Wednesday, September 18, 13
Thanks
• PostgresOpen Conference Committee
• OmniTI
• You!!
33Wednesday, September 18, 13