RDS: Managed PostgreSQL

Concept

RDS (Relational Database Service) is an AWS service that runs databases such as PostgreSQL and MySQL for you. A relational database stores data in tables with rows and columns, and you ask it questions with SQL. With RDS you get a hostname, a port, a username and a password. You do not get a login to the machine underneath, because you do not need one. AWS looks after the server, the disk, the patches, the backups and the failover (switching to a spare copy when the main one fails).

For your application, it is an ordinary PostgreSQL database. It uses the same SQL, the same drivers and the same ORM (a library that lets code use a database through objects). The only change is that DATABASE_URL points to an AWS hostname instead of localhost.

Why not just install Postgres on the server

You can. Many small products do this, and it is fine for a hobby project. But then you own all of this:

• Backups. Someone must write the script, schedule it and store the files somewhere else. Then comes the step that everyone skips: test a restore. A backup that you never tested is only a hope.
• Data lives on the app server. If that machine dies, is terminated by mistake or is replaced during a deploy, your customers' data is lost with it.
• You cannot scale out. Auto Scaling (Lesson 12) creates and removes app servers freely. This only works if the servers hold no data. A database on the same server ties you to one machine.
• Resource fights. Postgres and Node share CPU and RAM. A slow query makes your website slow, and a busy website makes the database slow.
• Patching and upgrades are your job, on your schedule, with your downtime.

The diagram above (from Lesson 0) shows the goal: app servers that remember nothing and can be replaced at any time. This only works when the memory, which is the database, lives somewhere else. RDS is that place.

Who does what — you vs AWS

This split is called the shared responsibility model. It means that AWS looks after some parts and you look after the rest. It is a very common interview question about managed services. (A managed service is one where AWS runs the machines for you.) RDS moves the line up, but your half does not disappear.

Notice that a slow query, a missing index and a leaked password are still your job. An index is a lookup structure that makes searches fast. “Managed” does not mean that AWS fixes everything.

The choices you make: class, storage, Multi-AZ

Instance class

The instance class is the size of the database server. The names work like EC2 names, with a db. prefix. db.t4g.micro is burstable, uses an ARM Graviton CPU, and has 2 vCPU and 1 GB RAM. For learning and a low-traffic product, db.t4g.micro or db.t4g.small (2 GB) is enough. Move to db.m or db.r (r means memory-optimised) when your working set no longer fits in memory. The working set is the data you use most often. Postgres is fast when its indexes and busy rows are in RAM. It is slow when it must read from disk. So RAM is the number to watch.

Storage

• gp3 (a type of SSD disk) is the default choice. It includes 3,000 IOPS (disk reads and writes per second), and you pay per GB. Start at 20 GB.
• Turn on storage autoscaling (--max-allocated-storage), so a growing table never fills the disk and freezes the database. You can make storage bigger, but you can never make it smaller.
• Turn on encryption at rest (data is scrambled on the disk) when you create the database. You cannot turn it on later. You would have to copy a snapshot with encryption and restore from it.

Multi-AZ — high availability, not a backup

Multi-AZ means AWS keeps a second, hidden copy (the standby) of the database in another Availability Zone. Every write is copied to it at the same time (synchronously). If the main database fails, RDS moves the same hostname to the standby in about one to two minutes. You change no code. (This table describes the classic Multi-AZ instance setup. A newer “Multi-AZ DB cluster” option has standbys that you can read from.)

A read replica is a different thing. It is an extra copy that you can read from, to share the load. In one line: Multi-AZ is for staying online, a read replica is for speed.

In this course we run a single-AZ database to keep the bill small. For a real product with paying customers, turn Multi-AZ on before launch. It is one setting.

Backups, snapshots and point-in-time restore

A backup is a saved copy of your data that you can restore after a failure or a mistake. A snapshot is a copy of the whole database disk at one moment. RDS gives you two kinds, and they behave differently:

Two terms every engineer should be able to explain. RPO (Recovery Point Objective) is how much data you can afford to lose. With point-in-time restore it is about five minutes. RTO (Recovery Time Objective) is how long you can be down. A restore takes tens of minutes, and a Multi-AZ failover takes about two. In one line: RPO is about lost data, RTO is about lost time. Lesson 18 practises a real restore.

Build it: a private database

1. Create a subnet group — the list of subnets RDS may use

   A subnet group is a list of subnets where RDS is allowed to place your database. Go to RDS → Subnet groups → Create. Name it myapp-private, pick the myapp VPC and select only the two private subnets. (RDS needs two AZs, even for one database.) This step keeps the database off the internet. RDS can only place it in subnets that have no route to the Internet Gateway.

2. Create the instance

   RDS → Create database → Standard create, PostgreSQL. The fields that matter:

   Field	Set it to	Why
   Engine version	a current major version (16 or newer)	It is supported for years and gets security patches
   Template	Free tier / Dev-Test	Single-AZ is fine while learning
   Master username	dbadmin	For you only — never for the app
   Password	Auto-generate, or a long random one	Store it in your password manager straight away
   Instance class	db.t4g.micro	Smallest size; about 100 connections at most (see below)
   Storage	20 GB gp3, autoscaling to 100	Grows on its own instead of filling up
   Connectivity	myapp VPC, subnet group myapp-private	The private subnets from step 1
   Public access	No	No public IP at all — it exists only inside the VPC
   Security group	db-sg (remove default)	Accepts 5432 only from web-sg (Lesson 6)
   Backups	7 days retention	Point-in-time restore to any second in that window
   Encryption	On	Free, and can't be added later
   Deletion protection	On	AWS refuses to delete the database until you switch this off. It prevents expensive accidents

   Creating the database takes 5–15 minutes. When it is ready, copy the endpoint (the hostname of the database), which looks like myapp-db.abc123xyz.ap-south-1.rds.amazonaws.com. If you look it up from your laptop, it gives a private 10.0.x.x address. This shows that the internet cannot reach it.

Connect from the app server

The database only accepts connections from web-sg, so you must test from your EC2 instance. Log in with SSH, install the Postgres command-line client (the package postgresql-client, which gives you the psql command), and connect:

Do not run your app as the admin

dbadmin can drop every database on the server. Suppose your web app connects as admin, and it has a SQL-injection bug (a flaw that lets an attacker run their own SQL) or a leaked .env. Then the attacker owns everything. Use the admin account only to create a limited user for the app. This is least privilege, the same idea as in Lesson 5.

CREATE USER appuser WITH PASSWORD 'A-LONG-RANDOM-PASSWORD';
GRANT appuser TO dbadmin;                 -- RDS needs this before dbadmin can create a database owned by appuser
CREATE DATABASE myapp OWNER appuser;      -- appuser owns this database and can only use it
REVOKE ALL ON DATABASE myapp FROM PUBLIC; -- nobody else may even connect to it

On RDS, the admin user is not a full superuser. So it must be a member of appuser (the GRANT line) before it can create a database owned by appuser. Without that line you get “must be member of role”. Make the second password a long random string without symbols, and store it in your password manager. Now you have two identities: dbadmin for you and appuser for the app.

Connect the Next.js app

Add the URL to the server's .env (still chmod 600):

DATABASE_URL=postgresql://appuser:THE-APP-PASSWORD@myapp-db.abc123xyz.ap-south-1.rds.amazonaws.com:5432/myapp?sslmode=require

A connection string is one line that tells your app how to reach the database. It has five parts: user : password @ host : port / database. If the password contains @ : / # ?, you must URL-encode it (write each of these symbols as a % code). This is why we made passwords without symbols.

Migrations — creating the tables

A migration is a small file of changes to the database structure, such as “add a table” or “add a column”. A migration tool applies these files in order. Whatever tool you use (Prisma, Drizzle, Knex), production has one rule: apply migrations with a deploy-only command that asks no questions. Never use the development command there.

Add the migration step to your deploy loop from Lesson 7, before the reload. Make it backward-compatible. The old version of the app is still running while the migration runs. So never rename or drop a column in the same release that stops using it. First add the new column. Then deploy code that works with both columns. Drop the old column in a later release. This “expand then contract” pattern lets teams change the database with no downtime.

TLS to the database

TLS is the encryption that protects data while it travels over a network. On PostgreSQL 15 and later, RDS requires TLS by default, so sslmode=require is needed. This setting encrypts the traffic, but it does not check who the server is. A convincing fake server would still be accepted.

For full safety, download the AWS RDS certificate bundle onto the server once. Give it to your Postgres driver as the trusted CA (certificate authority, the body that vouches for the server's identity), and turn on certificate checking. The driver settings are also where you set the pool size. The next section is about that number.

Connection limits — the outage that surprises everyone

A connection is one open line between your app and the database. A connection pool is a small set of connections that the app keeps open and reuses. Every PostgreSQL connection is a separate operating-system process that uses memory (a few MB each). So the database has a hard limit called max_connections. On RDS this limit is worked out from the instance's RAM. A db.t4g.micro allows roughly 100 connections (a little less in practice).

The failure looks like this:

1. You run 4 app servers, each with a connection pool of 20 → 80 connections. Fine.
2. Traffic grows and Auto Scaling starts 4 more servers → 160 connections wanted.
3. The database refuses the extra ones: FATAL: remaining connection slots are reserved. The site shows errors exactly when it is busiest.

The fixes, in order of effort

• Set a sensible pool size in the app (max: 5–10), not the library default.
• Use a bigger instance: more RAM gives a higher limit and more spare room.
• Put a pooler in front. A pooler is a middle service that holds a small number of real database connections and shares them among thousands of app connections. Choose RDS Proxy (managed by AWS, about $0.015 per vCPU per hour of your database, with a minimum of 2 vCPUs, so roughly $22 a month at least) or run PgBouncer (a free open-source pooler) yourself. A pooler is very important with serverless functions, because each call would otherwise open its own connection. This is the usual reason that Vercel + Postgres setups hit limits.

Reaching the database from your laptop, safely

You will want to look at data with a GUI tool (TablePlus, DBeaver, pgAdmin) or run a script. The database is private. The wrong fix is to make it public. The right fix is an SSH tunnel. This is an encrypted path that goes through the app server, which is allowed to reach the database:

The traffic travels inside the encrypted SSH connection. It comes out on the server (inside the VPC) and reaches the database. Nothing is opened to the internet.

Day-two operations

Maintenance window

AWS applies operating-system patches and minor-version updates during a weekly window that you choose. Pick your quietest hour (for example Sunday 03:00 IST). Patching a single-AZ database causes a short outage of a few minutes. With Multi-AZ, the outage shrinks to one failover. In the console: Modify → Maintenance.

Parameter groups

You cannot edit postgresql.conf (the Postgres settings file) on RDS. Instead you attach a parameter group, which is a named set of settings. The default group is read-only. To change anything, such as logging slow queries, make your own group. Set log_min_duration_statement = 500 (milliseconds), attach the group, and reboot if the setting is static (a static setting needs a reboot to apply).

What to watch

Lesson 17 turns these into alarms. Also turn on Performance Insights (free for seven days of history). It shows which SQL statements use most of the database time, so it answers “why is it slow?” on one screen. AWS is moving this view into CloudWatch Database Insights, so the console may show that name. The idea is the same.

Take a snapshot, and prove it restores

Before any risky change, take a manual snapshot (RDS → your database → Actions → Take snapshot). It stays until you delete it. Once, as a practice drill, use Restore to point in time to make a copy from ten minutes ago. It always restores into a new instance with a new hostname. That copy costs money until you delete it.

Troubleshooting table

What this costs

Interview corner

Practice task before Lesson 9

1. Build the database as above. From your laptop, check that the hostname gives a 10.0.x.x address and that psql hangs (it must, because the database is private). Then connect from the EC2 server. This must work.
2. Create appuser and the myapp database. Connect as appuser and try CREATE DATABASE other;. It should be denied. Why is that good?
3. Add DATABASE_URL to the server's .env, run your ORM's deploy migration, and load a page that reads from the database.
4. Open an SSH tunnel and connect with a GUI client from your laptop. Then close the tunnel and confirm the connection fails.
5. Take a manual snapshot. In the console, restore it to a new instance called myapp-db-drill, note how long it takes and that it has a different hostname, then delete the drill instance.
6. Run SHOW max_connections;. With your app's pool size, how many app servers can you run before you hit ~80% of it?

Conclusion

RDS is PostgreSQL where AWS handles the boring and risky parts: patching, backups, failover and disks. The important parts are still yours: access, schema and sizing.

• Private by construction: private subnets, a subnet group, no public IP, and a Security Group that trusts only the app's group.
• Two identities: an admin for you and a limited user for the app. Use generated passwords and never type them into shell history.
• Multi-AZ is for uptime, backups are for mistakes. Know which problem each solves, and remember that restores create a new instance.
• Watch connections: servers × pool size must stay under the limit. Otherwise, adding servers breaks the site.
• Reach it through a tunnel, never by opening it to the internet.

The app now has a real database, but the server is still built by hand. Next we package the app so that it runs the same everywhere: Docker.

End of Lesson 8. Next: Lesson 9 — Docker: Containerize Your Next.js and Node App.