Disclosure: when you buy through links on our site, we may earn an affiliate commission.

PostgreSQL Replication, High Availability HA and Scalability

Solutions for Scaling Postgres with Master-Slave Replication, PgBouncer, PgPool II, HAProxy, Partitioning, Sharding
4.7
4.7/5
(5 reviews)
237 students
Created by

9.4

CourseMarks Score®

10.0

Freshness

8.5

Feedback

9.2

Content

Platform: Udemy
Video: 3h 8m
Language: English
Next start: On Demand

Top PostgreSQL courses:

Detailed Analysis

CourseMarks Score®

9.4 / 10

CourseMarks Score® helps students to find the best classes. We aggregate 18 factors, including freshness, student feedback and content diversity.

Freshness Score

10.0 / 10
This course was last updated on 5/2022.

Course content can become outdated quite quickly. After analysing 71,530 courses, we found that the highest rated courses are updated every year. If a course has not been updated for more than 2 years, you should carefully evaluate the course before enrolling.

Student Feedback

8.5 / 10
We analyzed factors such as the rating (4.7/5) and the ratio between the number of reviews and the number of students, which is a great signal of student commitment.

New courses are hard to evaluate because there are no or just a few student ratings, but Student Feedback Score helps you find great courses even with fewer reviews.

Content Score

9.2 / 10
Video Score: 8.0 / 10
The course includes 3h 8m video content. Courses with more videos usually have a higher average rating. We have found that the sweet spot is 16 hours of video, which is long enough to teach a topic comprehensively, but not overwhelming. Courses over 16 hours of video gets the maximum score.
Detail Score: 10.0 / 10

The top online course contains a detailed description of the course, what you will learn and also a detailed description about the instructor.

Extra Content Score: 9.5 / 10

Tests, exercises, articles and other resources help students to better understand and deepen their understanding of the topic.

This course contains:

0 article.
1 resources.
0 exercise.
0 test.

Table of contents

Description

PostgreSQL is one of the most powerful and easy-to-use database management systems. It has strong support from the community and is being actively developed with a new release every year.
PostgreSQL supports the most advanced features included in SQL standards. It also provides NoSQL capabilities and very rich data types and extensions. All of this makes PostgreSQL a very attractive solution in software systems.

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.

Introduction to Scaling PostgreSQL
•Why scale PostgreSQL?
•What is Vertical Scaling?
•What is Horizontal Scaling?
•Read Versus Write Bound Workloads
•Why Statistics are essential?
•How to enable and make us of Statistics? (Hands-on)
•How to scale Postgres for Reads?
•How replication helps to scale out?
•What are the Load-Balancers?
•How to scale Postgres for Writes?
•How to make use of Queues?
•How could Partitioning and Sharding help in scaling out?
•What is the Multi-Master solution about?

Understanding the Limitations of Scaling out PostgreSQL
•CAP Theorem Explained
•PostgreSQL vs. Cassandra
•Use case: CA Systems
•Use case: AP Systems

How to use Streaming Replication?
•What is Streaming Replication?
•Asynchronous vs. Synchronous Replication
•How to Initialise Primary Database? (Hands-on)
•How to Configuring the Primary for Replication?  (Hands-on)
•How to Configuring the Replica Instance?  (Hands-on)
•Testing Replication Setup  (Hands-on)

How to use Logical Replication?
•What is Logical Replication in Postgres?
•Step by step Logical Replication setup
•How to setup the servers for Logical Replication? (Hands-on)
•How to make a selective Copy of the Data? (Hands-on)
•How to Create the Publication? (Hands-on)
•How to Create the Subscription? (Hands-on)
•Postgres Limitations of Logical Replication
•How to Monitoring Logical Replication? (Hands-on)
•Best use-cases for using Logical Replication

How to make use of PgBouncer?
•What is PgBouncer?
•Fundamental concepts of connection pooling
•How to build a PgBouncer Setup? (Hands-on)
•How to install and configure PgBouncer? (Hands-on)
•How to create a basic configuration file for PgBouncer? (Hands-on)
•How to connect to PgBouncer? (Hands-on)
•Explaining Advanced Settings for Performance
•Which are the available Pool Modes?
•Executing a benchmark with PgBouncer (Hands-on)

How to scale PostgreSQL in Google Cloud?
•Introduction
•Key Components on Google Cloud
•Key Characteristics of the Architecture
•How to create PostgreSQL Instances on Google Cloud?  (Hands-on)
•How to create a Google Cloud Engine (GCE) for HAProxy? (Hands-on)
•How to configure HAProxy for Load-Balancing? (Hands-on)
•Testing Load-Balancing

How to make use of PostgreSQL Partitioning?
•What is Partitioning?
•Which Tables Need Partitioning?
•How should the Tables be Partitioned? 
•Declarative vs. Inheritance Partitioning
•How to create a Partitioned Table? (Hands-on)
•Partitioning Methods

How to Shard PostgreSQL?
•What is Sharding?
•Pain-Points of Sharding?
•What is Second Level Sharding?
•What is good Sharding?
•How to query across multiple Shards?

How to setup High Availability (HA) on PostgreSQL?
•Why High Availability?
•Steps to achieve High Availability
•Essential Questions to ask before setting-up High Availability
•Log-Shipping Replication
•Streaming Replication and Logical Replication
•Cascading Replication
•Synchronous vs. Asynchronous Replication
•Automatic Failover and Always-on Strategy
•Simple HA Solution Example
•Better HA Solution Example

How to make use of PgPool II?
•What is PgPool II?
•Pgpool-II Features
•How to Configure Pgpool-II with Streaming Replication? (Hands-on)
•How to setup Streaming Replication? (Hands-on)
•How to Configuring Pgpool-II for Load Balancing ? (Hands-on)
•Testing load-balancing & read/write separation (Hands-on)
•How to Configure Pgpool for PostgreSQL High-Availability? (Hands-on)
•How to Configure PostgreSQL Primary Server? (Hands-on)
•How to Configure Pgpool-II Server? (Hands-on)
•How to Configure PostgreSQL Replica Server? (Hands-on)
•Testing The Failover (Hands-on)
•How to restore failed nodes? (Hands-on)

You will 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

Requirements

• You need access to a Windows/Mac/Linux PC with 10GB of free disk space
• Basic familiarity with database objects such as tables and indexes is expected
• Some familiarity with Linux will be helpful

This course is for

• Software Engineers interested in designing Scalable and HA solutions on top of PostgreSQL
• Database Administrators
• Everyone interested in building better PostgreSQL applications

How much does the PostgreSQL Replication, High Availability HA and Scalability course cost? Is it worth it?

The course costs $14.99. And currently there is a 75% discount on the original price of the course, which was $49.99. So you save $35 if you enroll the course now.

Does the PostgreSQL Replication, High Availability HA and Scalability course have a money back guarantee or refund policy?

YES, PostgreSQL Replication, High Availability HA and Scalability has a 30-day money back guarantee. The 30-day refund policy is designed to allow students to study without risk.

Are there any SCHOLARSHIPS for this course?

Currently we could not find a scholarship for the PostgreSQL Replication, High Availability HA and Scalability course, but there is a $35 discount from the original price ($49.99). So the current price is just $14.99.

Who is the instructor? Is Lucian Oprea a SCAM or a TRUSTED instructor?

Lucian Oprea has created 6 courses that got 1,276 reviews which are generally positive. Lucian Oprea has taught 7,496 students and received a 4.4 average review out of 1,276 reviews. Depending on the information available, Lucian Oprea is a TRUSTED instructor.
Software Engineer
I’m a software engineer from Romania with a strong background in computer science and with more than 8 years of professional experience.
I’m passionate about developing products that are easy-to-use, intelligent, and well-architected. I’m an advocate of writing a well-crafted code that follows best design practices. I seek to bring the same level of passion and completeness to teaching.

9.4

CourseMarks Score®

10.0

Freshness

8.5

Feedback

9.2

Content

Platform: Udemy
Video: 3h 8m
Language: English
Next start: On Demand

Students are also interested in

Review widget (for course creators):

PostgreSQL Replication, High Availability HA and Scalability rating
Code for the widget (just copy and paste it to your site):
<a href="https://coursemarks.com/course/postgresql-replication-high-availability-ha-and-scalability/" target="_blank" title="PostgreSQL Replication, High Availability HA and Scalability on Coursemarks.com"><img border="0" src="https://coursemarks.com/widget/94.svg" width="200px" alt="PostgreSQL Replication, High Availability HA and Scalability rating"/></a>