Scaling PostgreSQL Instances Horizontally and Vertically

Question

Pulumi · Accepted Answer

Scaling PostgreSQL instances involves increasing the resources for a single instance (vertical scaling) or adding more instances (horizontal scaling). In the context of cloud services, vertical scaling typically means changing the instance type to a more powerful one with more CPU, memory, or disk resources. Horizontal scaling, on the other hand, often involves adding read replicas or setting up a database cluster to distribute the load across multiple instances.

Below is a TypeScript program that demonstrates how you can use Pulumi to scale PostgreSQL instances vertically and horizontally on AWS using the `aws.rds.Instance` resource for vertical scaling and `aws.rds.Cluster` resource for horizontal scaling with Aurora. Aurora is a MySQL and PostgreSQL-compatible relational database built for the cloud, that combines the performance and availability of high-end commercial databases with the simplicity and cost-effectiveness of open-source databases.

The program includes:

1. Scaling a Single PostgreSQL Instance Vertically: Increasing the instance class of an RDS instance to scale it vertically.
2. Adding Read Replicas to Scale Horizontally: Adding an Aurora cluster with a specified number of read replicas to distribute the load across instances.

### Vertical Scaling Example

```typescript
import * as pulumi from "@pulumi/pulumi";
import * as aws from "@pulumi/aws";

// Create a PostgreSQL instance and scale it vertically by increasing the instance class.

// Define the configuration for the RDS instance.
const dbConfig = {
    allocatedStorage: 20,
    engine: "postgres",
    engineVersion: "13.3",
    instanceClass: "db.t3.micro", // Starting with a small instance class for demo purposes.
    name: "mydb",
    parameterGroupName: "default.postgres13",
    password: "mysecretpassword",
    username: "postgres",
};

// Create a new PostgreSQL RDS instance.
const postgresInstance = new aws.rds.Instance("postgres-instance", dbConfig);

// Change the instance class for vertical scaling.
// Update this to a larger instance class e.g., "db.m5.large"
const scaledPostgresInstance = new aws.rds.Instance("scaled-postgres-instance", {
    ...dbConfig,
    instanceClass: "db.m5.large",
});

// Export the address of the PostgreSQL instance.
export const endpoint = scaledPostgresInstance.endpoint;
```

In the code above, we create an AWS RDS instance with a small instance class (`db.t3.micro`) and then create a new instance with a larger instance class (`db.m5.large`). This demonstrates how you can change the instance size to scale vertically.

### Horizontal Scaling Example

For horizontal scaling, Aurora provides an easy way to create read replicas. The following code snippet sets up an Aurora PostgreSQL-compatible cluster and specifies the number of read replicas.

```typescript
// Horizontal scaling: Setting up Aurora cluster and read replicas.

// Create an Aurora PostgreSQL-compatible cluster.
const auroraCluster = new aws.rds.Cluster("aurora-cluster", {
    engine: "aurora-postgresql",
    engineVersion: "13.4",
    masterUsername: "postgres",
    masterPassword: "mysecretpassword",
});

// Specify the number of read replicas for the Aurora cluster.
const numberOfReadReplicas = 2; // Scale this number according to your needs.

// Create the specified number of read replicas for the Aurora cluster.
for (let i = 0; i < numberOfReadReplicas; i++) {
    // Read replica instance.
    new aws.rds.ClusterInstance(`aurora-cluster-instance-${i}`, {
        clusterIdentifier: auroraCluster.id,
        instanceClass: "db.r5.large",
    });
}

// Export the cluster endpoint and read endpoints.
export const clusterEndpoint = auroraCluster.endpoint;
export const readerEndpoint = auroraCluster.readerEndpoint;
```

In this section, we start by creating an Aurora cluster with the `aws.rds.Cluster` resource and then create a number of read replicas by iterating and creating `aws.rds.ClusterInstance` resources. The number of replicas can be adjusted as needed for horizontal scaling.

In both vertical and horizontal scaling, you will need to manage stateful updates cautiously. Vertical scaling requires downtime as the instance is modified, while horizontal scaling typically doesn't affect the availability but requires consideration for data replication and consistency.

Note: Always ensure sensitive information, such as database passwords, is handled securely. In a real-world scenario, you would use a Pulumi configuration or a secret manager rather than hardcoding values.