English | MP4 | AVC 1280×720 | AAC 44KHz 2ch | 80 lectures (3h 8m) | 1020 MB
Solutions for Scaling Postgres with Master-Slave Replication, PgBouncer, PgPool II, HAProxy, Partitioning, Sharding
In this course, we discussed the problem of building scalable solutions based on PostgreSQL utilizing the resources of several servers. There is a natural limitation for such systems—basically, there is always a compromise between performance, reliability, and consistency. It’s possible to improve one aspect, but others will suffer. In this course, we’ll see how to find the best match for our use-cases so that we know eactly which aspects need scaling, and avoid the common trade-offs of distributed systems.
Scaling PostgreSQL is a journey. You should come out of this course more prepared to assess your scaling needs and understand how to scale reads and how to scale writes.
Each of this solution presented in this course will improve some aspect of the scalability topic, but each of them will add some complexity, and maybe some limitation or constraint.
We have to ask the right questions to get the system requirements, and this why we dedicated an entire lecture, so that we examine what questions we have to put ourself, before starting the Scaling Journey.
After this course, we should come out more prepared and understand how to scale reads.
We have several options for replication, depending on wether we favor performance or flexibility.
Replication can be used as a backup or a standby solution that would take over in case the main server crashes.
Replication can also be used to improve the performance of a software system by making it possible to distribute the load on several database servers.
Then, if we have one sort of replication in place, we could ask ourself if we want to allow several computers to serve the same data.
To achieve this, we should have a mechanism to distribute the requests. We’ll see here two of the most popular options available.
Next, if the number of database connections is great, then we’ll probably want to use a connection pooler. Again, we’ll cover two options here.
We’ll also see, how to scale writes, and how to make your traffic growth more predictable by adding queuing to your architecture.
Then, we’ll check partitioning for those cases when we have to deal with big tables.
Also, we’ll check sharding to scale writes, and all the complex decisions that come with it.
Finally, we’ll see shortly the multi-master solution, which is a relatively new concept that seems to be promising.
If our goal is to achieve only High availability, or the ability to continue working even in the situation where one part of the cluster fails, we can check out only those solutions.
The pre-requirements for HA is to put in place a replication strategy.
Then, we can use tools to allow a second server to take over quickly, if the primary server fails.
What you’ll learn
- Assess your scaling needs
- How to scale reads using Replication and Load-Balancing
- Which is the best Replication solution for a certain use case
- How to manage database connections with PgBouncer connection pooler
- How to make use of multiple PostgreSQL instances in the cloud (Google Cloud)
- How to achieve High-Availability
- How to perform Automatic Failover using PgPool II
- How to scale writes using Partitioning and Sharding
Table of Contents
Scaling PostgreSQL
1 Why Scale PostgreSQL
2 Vertical Scaling
3 Horizontal Scaling
4 CAP Theorem Explained
5 PostgreSQL vs NoSQL
6 Use case Consistent and Available System
7 Use case Available and Partitiontolerant System
8 Read Versus Write Bound Workload
9 How statistics will answer to all questions
10 Enable Statistics
11 Replication
12 Load Balancing
13 Connection Pooling
14 Queuing
15 Partitioning
16 Sharding
17 Multimaster
Streaming Replication
18 What is Streaming Replication
19 Asynchronous vs Synchronous Replication
20 Handson Initialise Primary Database
21 Configuring the Primary for Replication
22 Configuring the Replica Instance
23 Testing Replication Setup
Logical Replication
24 What is Logical Replication in Postgres
25 Settingup Postgres Servers for Logical Replication
26 Selective Copy of the Data
27 Create the Publication
28 Create the Subscription
29 Limitations of Logical Replication
30 Monitoring Logical Replication
31 Best usecases for Logical Replication
PgBouncer
32 Introduction
33 Fundamental concepts of connection pooling
34 Building a PgBouncer Setup
35 Installing and Configure PgBouncer
36 Creating a basic configuration file for PgBouncer
37 Connecting to PgBouncer
38 Advanced Settings for Performance
39 Pool Modes
40 A simple benchmark
Scaling PostgreSQL with Google Cloud and HAProxy
41 Introduction
42 Key Components
43 Key Characteristics of the Architecture
44 Creating PostgreSQL Instances on Google Cloud
45 Creating a GCE for HAProxy
46 Configure HAProxy for LoadBalancing
47 Testing LoadBalancing
Partitioning
48 Introduction
49 Which Tables Need Partitioning
50 How should the Tables be Partitioned
51 Declarative vs Inheritance Partitioning
52 Creating a Partitioned Table
53 Partitioning Methods
Sharding
54 Introduction
55 Pain Points of Sharding
56 How to Partition Data in PostgreSQL
57 Second Level Sharding
58 Querying Across Shards
PostgreSQL High Availability
59 Why High Availability
60 Steps to achieve High Availability
61 Essential Questions to setup High Availability
62 LogShipping Replication
63 Streaming Replication and Logical Replication
64 Cascading Replication
65 Synchronous vs Asynchronous Replication
66 Automatic Failover and Alwayson Strategy
67 Simple HA Solution Example
68 Better HA Solution Example
PgPool II
69 Introduction
70 PgpoolII Features
71 Configure PgpoolII with Streaming Replication
72 Setting up Streaming Replication
73 Configuring PgpoolII for Load Balancing
74 Testing loadbalancing readwrite separation
75 Configure Pgpool for PostgreSQL HighAvailability
76 Configuring PostgreSQL Primary Server
77 Configuring PgpoolII Server
78 Configuring PostgreSQL Replica Server
79 Testing The Failover
80 Restoring failed nodes
Resolve the captcha to access the links!