PostgreSQL: From Idea to Database - Table of Contents

PostgreSQL: From Idea to Database is a series of blog posts written to teach practical database design. This series focuses on practical examples of working with Postgres to illustrate the why and how of operation.

Note: This series is an evolving and improving collection of blog posts. I regularly update this page as content is published and maintained.

Table of contents

The following listing includes links to the pages that are already published along with placeholders for planned future topics. Future topics (e.g. titles without links) are likely to change in title, exact content and scope.

General Postgres topics

• Introduction to PostgreSQL: From Idea to Database series (2018)
• PostgreSQL Crash Course (2018)
• Define, Design, Repeat (2018)
• Build your Data Dictionary in PostgreSQL (2018)
• Enhance your PostgreSQL Data Dictionary (2018)
• PostgreSQL Data Dictionary Extension (2019)
• PostgreSQL vs. MySQL: Why we use PostgreSQL (2016)
• Database Anti-patterns: Performance Killers (2018)
• Deploying PostgreSQL using Ansible and Sqitch (2019)
• Large Text in PostgreSQL: Performance and Storage (2020)
• Use PostgreSQL file_fdw to Access External Data (2020)
• Load and query Pi-hole data from Postgres (2021)
• Backup and Restore
• PostgreSQL Functions - Data as an API

Advanced Postgres topics

Performance

• PostgreSQL 10 Parallel Queries and Performance (2018)
• Testing PostgreSQL Unlogged Tables for Performance (2016)
• PostgreSQL performance on Raspberry Pi (2019)
• PostgreSQL performance on Raspberry Pi, Reporting edition (2019)
• PostgreSQL on Raspberry Pi 4: Initial Testing (2019)
• PostgreSQL Load Testing (2017)
• PostgreSQL: Integers, on-disk order, and performance (2020)
• PostgreSQL 13 B-Tree Deduplication (2020)

Spatial data (PostGIS)

• PostGIS Trajectory: Space plus Time (2020)
• Find your local SRID in PostGIS (2020)
• Create vector layers using QGIS and PostGIS(2019)
• PostGIS: Tame your spatial data (Part 1) (2018)
• PostGIS: Tame your spatial data (Part 2) (2018)
• Load OpenStreetMap data to PostGIS (2019)
• Updated for 2020: Load OpenStreetMap data to PostGIS (2020)
• Hands on with osm2pgsql's new Flex output (2020)
• Better OpenStreetMap places in PostGIS (2021)
• Scaling osm2pgsql: Process and costs (2019)
• osm2pgsql on a Raspberry Pi (2019)
• PgOSM: Transform OpenStreetMap data in PostGIS (2019)
• PgOSM Transformations explained (2019)
• FIXED! --> Gotcha restoring XML data from pg_dump (2019, 2018)

PostGIS recorded sessions

• Intro to PostGIS and OpenStreetMap (1/6) (Recorded December 2019)
• Load PostGIS with osm2pgsql (2/6) (Recorded January 2020)
• Exploring OpenStreetMap from PostGIS: Tools and Queries (3/6) (Recorded January 2020)
• PostGIS Queries and Performance (4/6) (Recorded February 2020)
• Postgres 12 and PostGIS 3 (5/6) (Recorded February 2020)
• PostGIS Advanced Features (6/6) (Recorded March 2020)

Administration and Security

• Exploring PgConfig comparison tool (2019)
• Installing PostgreSQL and PostGIS from source (2019)
• Prepare for Postgres 12: Configuration Changes (2019)

Get in touch

If you have questions or comments please Contact us.