Skip to content
learn
Global menu

PostgreSQL and Amazon Aurora — Mission

Why I’m learning this

Section titled “Why I’m learning this”

I have spent years on SQL Server: T-SQL, SSMS, execution plans, IDENTITY columns, Entity Framework migrations. More and more of the projects I meet start on PostgreSQL instead, often on a managed service such as Amazon Aurora, and I noticed that I was writing SQL Server in PostgreSQL’s syntax. It works, until a column name has a capital letter, a DateTime comes back three hours off, or a pool of a hundred connections meets a server that allows a hundred.

I want to know PostgreSQL on its own terms: its types, its SQL, how it runs transactions and uses indexes, how it replicates and how it is backed up. Then I want to know what Aurora changes: what AWS manages for me, what I lose, and what it costs.

Who this course is for

Section titled “Who this course is for”

You are a C# or Java developer. You know SQL from SQL Server or another database: joins, grouping, indexes, transactions. You have run a container before. You don’t need to know PostgreSQL. The Java side assumes the level of Java for C# developers; lesson 4 sends Spring Data questions to Spring Boot, Spring Cloud and Reactor for C# developers, and lesson 1 sends container questions to WSL containers. The DuckDB course queries the same CI data with an analytical engine: it’s a good comparison, not a prerequisite.

SQL Server, PostgreSQL and Aurora in one table

Section titled “SQL Server, PostgreSQL and Aurora in one table”
SQL Server PostgreSQL Aurora PostgreSQL
Licence commercial (Express and Developer editions free) PostgreSQL License, open source a managed AWS service, billed per instance or capacity unit, storage and I/O
Server one process, threads one process per connection PostgreSQL’s engine on AWS’s distributed storage
Top level instance → databases → schemas cluster → databases → schemas DB cluster: a writer, readers, one shared storage volume
Admin account sa, sysadmin postgres, a superuser a member of rds_superuser, never a real superuser
Client tool sqlcmd, SSMS psql, pgAdmin psql and the AWS console
From .NET Microsoft.Data.SqlClient Npgsql Npgsql
From Java Microsoft JDBC driver PostgreSQL JDBC driver PostgreSQL JDBC driver, or the AWS Advanced JDBC Wrapper

The data

Section titled “The data”

The course reuses data this site already publishes, rather than an invented shop or library:

Lesson 2 builds the model that every later lesson loads from sql/schema.sql:

erDiagram
    RUNS ||--o{ JOBS : "has"
    JOBS ||--o{ STEPS : "has"
    PROJECTS ||--o{ PROJECT_REFS : "references"
    PROJECTS ||--o{ PACKAGE_REFS : "uses"
    RUNS {
        bigint run_id PK
        text workflow_name
        conclusion conclusion
        timestamptz started_at
    }
    JOBS {
        bigint job_id PK
        text_array labels
        interval duration "generated"
    }
    STEPS {
        smallint number PK
        tstzrange ran "generated"
    }
    PROJECTS {
        text path PK
        text_array frameworks
        boolean in_solution
    }
    ICONIC_CHORDS {
        integer chord_id PK
        pitch_class_array pitch_classes
        smallint_array guitar_voicing
    }

Schema ci holds the first three tables, schema ga the others.

By the end of this course, I will be able to

Section titled “By the end of this course, I will be able to”
  • run PostgreSQL in a container and work in psql, with databases, schemas, roles and privileges;
  • choose PostgreSQL’s types on purpose: text, numeric, timestamptz, uuid, jsonb, arrays and ranges, with constraints, domains and generated columns;
  • write the SQL that PostgreSQL does better than T-SQL: recursive CTEs, window functions, LATERAL, DISTINCT ON, RETURNING, upserts and MERGE;
  • use PostgreSQL from C# with Npgsql and EF Core, and from Java with JDBC and HikariCP, including pools, prepared statements and COPY;
  • read a query plan and pick the right index: B-tree, GIN, GiST, BRIN, partial and expression indexes;
  • explain MVCC, isolation levels, locks and VACUUM, and reproduce a deadlock;
  • store and search JSON and text, and write functions, triggers and extensions;
  • partition large tables, replicate them, back them up, restore them and upgrade them;
  • run the same database on Amazon Aurora PostgreSQL, knowing what differs, and estimate what it costs, including a migration from SQL Server.

Outline

Section titled “Outline”
# Lesson If you know SQL Server
1 Getting started: a container, psql, databases, schemas and roles sqlcmd, logins and users, dbo, TOP, IDENTITY
2 Types and modeling datetimeoffset, nvarchar, computed columns, unique indexes and NULL
3 Queries: CTEs, windows, LATERAL, upserts and MERGE CROSS APPLY, OUTPUT inserted.*, MERGE
4 PostgreSQL from C# and Java SqlConnection pooling, SqlBulkCopy, EF Core providers
5 Indexes and plans: B-tree, GIN, BRIN, EXPLAIN (ANALYZE, BUFFERS) execution plans, included columns, filtered indexes
6 Transactions and MVCC: isolation levels, locks, VACUUM, bloat, deadlocks READ_COMMITTED_SNAPSHOT, the version store
7 JSON and search: jsonb operators and indexes, full-text search, pg_trgm OPENJSON, full-text catalogs
8 Functions and extensions: PL/pgSQL, triggers, pgvector T-SQL procedures, CLR
9 Partitioning and large tables partition functions and schemes, SWITCH
10 Replication and high availability: WAL, physical and logical replication Always On availability groups, transactional replication
11 Backup, restore and upgrades: pg_dump, point-in-time recovery, pg_upgrade BACKUP, RESTORE … STOPAT, in-place upgrades
12 Amazon Aurora PostgreSQL: storage, replicas and failover, endpoints, Aurora Serverless, Global Database, RDS Proxy, IAM authentication Azure SQL Database, Hyperscale
13 Migrating from SQL Server, and costs: a migration by hand, by program and with pgloader, AWS DMS, Babelfish, Aurora prices the Data Migration Assistant, SqlBulkCopy

Lessons 1 to 11 each end with a short section on what changes on Aurora, from AWS’s documentation; lesson 12 is about Aurora itself, with a failover reproduced locally; lesson 13 moves a SQL Server database to PostgreSQL, then describes AWS DMS, Babelfish and Aurora’s prices. None of it runs on AWS: the course creates no AWS resource, so everything about Aurora is marked to verify.

Journal: what I tried, what surprised me, what I still need to verify.

Resources

Section titled “Resources”