Ruby for All

The Database Wizard with Andrew Atkinson

Jan 19, 2023

A database specialist discusses his journey, the pronunciation of SQL, PostgreSQL vs MySQL performance, transactions and locking in Rails, choosing PostgreSQL over MySQL, the use of pgbouncer, and join tables vs Array or JSON columns for modeling relationships.

28:38

Creator website

AI Summary

AI Chapters

Episode notes

Podcast summary created with Snipd AI

Quick takeaways

Benchmarking databases for specific use cases can provide a more accurate performance comparison between MySQL and Postgres.

Using PG Bouncer as a connection pooler can optimize connection usage and improve performance in scalable applications with high numbers of concurrent connections.

Deep dives

Using Postgres for web development and focusing on administration and education

Andy Atkinson discusses his experience working with Postgres for web development and how he has shifted his focus to the administration side and educating other developers about the capabilities of Postgres.

Introduction

3min

Comparing MySQL and Postgres

6min

Understanding Concurrency and Locking in Database Engines

4min

Understanding Table Locking and Concurrent Access in Databases

4min

Choosing PostgresQL over MySQL and Scaling with PG Bouncer

6min

Exploring Join Tables vs Array Columns for Modeling Relationships in a Database

6min

Timestamps

[01:19] Andy tells us a bit about himself, and how his journey led him to becoming a database specialist.

[03:17] How do you pronounce SQL? Andy explains why he pronounces it the way he does and what SQL actually stands for.

[04:38] Have you ever wondered if PostgreSQL is faster than MySQL? What goes into measuring database performance?

[10:19] Andrew asks Andy to dive deeper into transactions and locking in Rails which leads into a longer discussion around what locks are.

[17:08] Julie asks Andy why he prefers PostgreSQL over MySQL and how he arrived at that position.

[20:46] Andrew asks Andy what pgbouncer is and why you may want to add it to your application and when.

[22:51] Julie asks Andy a database archictecture question about when you should use join tables instead of Array or JSON columns.

[27:18] As the show winds down, Andrew asks Andy to come back on the show soon so they can keep discussing this topic.

Sponsor

Links

- MariaDB

- MySql

- Rails Pessimistic Locking

- Rails Optimistic Locking

- Heroku

- Fly.io

Remember Everything You Learn from Podcasts

Save insights instantly, chat with episodes, and build lasting knowledge - all powered by AI.

Ruby for All

The Database Wizard with Andrew Atkinson

Podcast summary created with Snipd AI

Quick takeaways

Deep dives

Using Postgres for web development and focusing on administration and education

Differences between MySQL and Postgres in terms of speed and performance

PG Bouncer as a connection pooler for Postgres

Remember Everything You Learn from Podcasts