Understanding Advanced Datatypes in PostgreSQL
summary
Advocates defaulting to bigserial or uuid for IDs and using text over varchar; recommends timestamp with time zone always and jsonb instead of json. Describes arrays, gin-indexed ts_vectors, and range types with exclusion constraints for fast temporal queries. Notes TOAST pushes values out of line around ~2KB, and points to extensions: email/uri types, PostGIS, ip4r and a physical units type. Gives concrete examples and tradeoffs for storage and indexing.
AI-written summary · about
chapters (33)
- 0:00Intro
- 1:24Basic types surrogate keys
- 5:53Text: Use indexes for pattern- matching An index supports prefixes (LIKE 'Peters)
- 11:22Time travel: interval
- 12:07Boolean values
- 12:32Binary data
- 13:20Don't use
- 16:00Internals: What's a type?
- 17:12Internals: How does it work?
- 19:36Internals: TOAST is great
- 22:26Internals: Make TOAST faster
- 23:59Advanced types greatest hits
- 24:23Array: What?
- 29:18JSON(b): jsonb vs json
- 30:50JSON(b): Anti-patterns
- 33:32Enums: How?
- 34:24Ranges: What?
- 35:18Ranges: How? (Basics) Ranges can be created mid-query
- 36:21Ranges: How? (Window functions)
- 38:25Email Addresses
- 38:54emailaddr
- 40:07Skip the migration!
- 40:24Geometry: What?
- 40:42PostGIS: What?
- 41:54Network addresses
- 42:15Physical Units
- 45:05Domains: Examples
- 45:23Domains & Arrays
- 45:42Composite Types: How? (1/2)
- 46:19DIY functions
- 46:44DIY Operators
- 47:41Images
- 48:36Basic data types to use
related talks
45mAdvanced Data Types in PostgreSQL
25mJSON in PostgreSQL - evil data type or just needs to be tamed?
43mAdvanced Data Types in PostgreSQL
46mDon't do that!
19mNO ARRAY OF SUNSHINE: an introduction to data types from a data quality perspective
05mPostgreSQL JSON JSONB Making the Right Choice for Your Application
49mShould I use JSON in PostgreSQL?
42mPostgreSQL Tips & Tricks For App Devs