Map of Amazing Postgres Extensions You Might Not Know About | Claire Giordano | PGDay Chicago 2024

Slides from a Postgres extensions talk I gave at PGDay Chicago 2024. With an awesome mind map for data viz purposes.

Abstract: Postgres extensions are everywhere—and are found in oh so many Postgres deployments. You might even say that PG extensions are the tools that make your Postgres toolbox complete.

But navigating the world of PG extensions can be a challenge. Where do you find all the extensions? Which Postgres extensions can make your life better and save you time? Which ones should you consider using? Can you trust them?

This talk walks you through the set of amazing Postgres extensions I’ve learned about in my ~7 years working in the world of Postgres: what they are, why care, and where to find them. You’ll learn about popular extensions like pg_stat_statements, "super extensions" such as PostGIS and Citus—as well as 25 lesser-known extensions that are quite handy, too.

This talk includes a brief detour into the history of Postgres extensions, plus a glimpse into the future of extensions (spoiler: will they be built with Rust?) By the end of this talk, you’ll have a “map” of the world of amazing Postgres extensions—plus a legend and compass for figuring out which extensions can help you and your application.

Transcript

1. CLAIRE GIORDANO @clairegiordano • @pg_at_msft • @AzureDBPostgres • @citusdata • @PosetteConf PGDAY CHICAGO 2024 MAP OF AMAZING POSTGRES YOU MIGHT NOT KNOW ABOUT EXTENSIONS MAP OF AMAZING YOU MIGHT NOT KNOW ABOUT
2. Engineer Dev Manager PM Marketer Writer Open-source champion Community Lead Podcast Host @clairegiordano @clairegiordano@hachyderm.io
3. Citus = Postgres open-source extension that first got me involved with PG
4. 4 What is a Postgres extension?  Adds functionality to Postgres (hence: “extends” Postgres)  Not a fork  To use—install Postgres & the PG extension(s)  To enable (or disable), get familiar with CREATE EXTENSION ALTER EXTENSION DROP EXTENSION
5. 5 Search for a good analogy for Postgres Extensions plug & play beloved modular building toy
6. 6 Search for a good analogy for Postgres Extensions can you add on specialized tools to Swiss Army Knife? are extensions similar to Apps in an App Store?
9. 9 Extensions support authored by Dimitri Fontaine in 2011, in Postgres 9.1
10. 10 “Journey” for this talk about Postgres Extensions @clairegiordano@hachyderm.io
11. 11 How to discover what Postgres extensions exist? @clairegiordano@hachyderm.io
12. To understand what exists, needed “mental map” of different types of PG extensions In 1<>1 with Daniel Gustafsson, explored this 1st draft set of “categories” 1. Monitoring 2. Indexing 3. Data types 4. Geospatial 5. Statistical Analysis 6. Distributed Scale 7. Connectors 8. Storage Optimizations
13. But then I started to wonder... What categories is everyone else using for Postgres extensions? @clairegiordano@hachyderm.io
14. First place I looked for Categories: PGXN.org I found tags! Not categories. I could search! Not browse extensions.
15. 15 Discovered “Trunk” via Samay Sharma @ Tembo pgt.dev
16. Trunk has categories for extensions! open source project pgt.dev
17. Supabase’s docs about PG extensions have categories too!
18. So does Crunchy Bridge!
19. Azure Cosmos DB for PostgreSQL has “categories” in docs too Citus Data types Full-text search Functions Index types Language Miscellaneous Pgvector PostGIS
20. Like a dog with a tennis ball I spent wayyyyyy too much time trying to come up with “right” set of categories
21. 21 My search to find the “right” set of categories
22. @clairegiordano@hachyderm.io All models are wrong, but some are useful.” “ —George Box, statistician Dimitri Fontaine reminded me of this quote
23. So I promised you a “map” of Postgres extensions
24. Mind map of Categories of Postgres Extensions AI / Machine Learning Admin Migration @clairegiordano@hachyderm.io
25. 25 Mind map of Postgres Extensions (by category) pg_cron pgtap amcheck pg_partman pgvector postgresml HLL topn postgres_fdw Citus multicorn postgis pgrouting hypopg RUM plv8 orafce pg_stat_statements pg_hint_plan pgSentinel fuzzystrmatch AI / Machine Learning Admin Migration tdigest pg_prewarm pg_surgery @clairegiordano@hachyderm.io
26. 26 “Journey” for this talk on Amazing PG Extensions
27. And will Postgres extensions save you time?
28. pg_stat_statements CONTRIB tracks statistics about SQL statements how long queries took? CATEGORY Performance
29. postgres_fdw CONTRIB fdw = “Foreign Data Wrapper” lots and lots of foreign data wrappers (just a few listed here) mysql_fdw mongo_fdw postgres_fdw duckdb_fdw tds_fdw oracle_fdw kafka_fdw Connectors CATEGORY mysql_fdw
30. 30 Concept of “Super extension” on Postgres.FM
31. PostGIS store, index, & query geographic data points, lines, polygons “Super extension” CATEGORY Geo
32. PostGIS CATEGORY Geo EP07: “Why people care about PostGIS” LISTEN TO THE PODCAST
33. Citus Distributed PostgreSQL “Postgres at any scale” Sharding Led by Microsoft “Super extension” CATEGORY Distributed Scale
34. TimescaleDB CATEGORY Timeseries
35. 35 Postgres extensions enable innovation
37. pgRouting adds network routing graph analysis Shortest Path Driving Distance Traveling Salesperson CATEGORY Geo
38. pgvector open source vector similarity search has added HNSW as new index type HNSW = Hierarchical Navigable Small World (HNSW) CATEGORY AI / Machine Learning Source: Photograph by Claire Giordano from Andy Pavlo’s Keynote at PGConf NYC 2023, titled “What Goes Around Comes Around... and Around...”
39. pg_cron Created/maintained by Marco Slot CATEGORY Admin “pg_cron: probably the best way to schedule jobs within PostgreSQL database” —FatDBA - - vacuum my table every night at 3am (GMT) SELECT cron.schedule( '0 3 * * *’, 'VACUUM my_table’ ); - - call a procedure every minute SELECT cron.schedule('process-new-events’, '* * * * *', 'CALL process_new_events()');
40. tdigest, hll, topn approximation/”sketch” algorithms t-digest: calculate Percentiles (histograms) hll = HyperLogLog, for COUNT DISTINCT Analytics CATEGORY
41. RUM more powerful version of GIN index CATEGORY Index
42. RUM more powerful version of GIN index for text search (or your own custom types) stores additional info per entry, e.g. timestamp CATEGORY Index
43. CATEGORY Index hypopg hypothetical indexing try out an index for free: doesn’t cost CPU, disk, resources visible to query planner helps you find an index for slow queries
44. pg_hint_plan “ —Rob Treat, statistician It’s an excellent learning tool, really good for deep dive performance work. Why? Because it gives you ability to pick and choose between different indexes—and get the execution plan without having to rebuild those indexes... The larger the data set the more valuable it is.” CATEGORY Performance
45. pg_hint_plan “ —Rob Treat, statistician It’s an excellent learning tool, really good for deep dive performance work. Why? Because it gives you ability to pick and choose between different indexes—and get the execution plan without having to rebuild those indexes... The larger the data set the more valuable it is.” CATEGORY Performance EP10: “My Journey into Postgres Monitoring” LISTEN TO THE PODCAST
46. pgsentinel a way to get what’s going on in the database over time you can look at “right now” with pg_stat_activity, but maybe something important happened 5 minutes ago? or maybe you need statistics about “what’s been happening in my database” created/maintained by Bertrand Drouvot CATEGORY Performance
47. plv8 procedural language lets you run JavaScript code stored procedures, triggers Language CATEGORY
48. plv8 procedural language lets you run JavaScript code stored procedures, triggers plv8 documentation link from Jerry Sievert Language CATEGORY https://plv8.github.io/
49. orafce makes it easier to migrate from Oracle to Postgres functions, data types... mysql_fdw Migration CATEGORY
50. “ —Daniel Gustafsson, Postgres hacker at Microsoft The TAP in pgTAP stands for “test anything protocol.” pgTAP enables you to write unit tests for your database—for objects, tables, & views. Developers will tell you it’s really really cool.” pgTAP CATEGORY Admin
51. pg_prewarm CONTRIB useful to put pages back in cache after restarting Postgres maybe should have been in “Performance” category? Admin CATEGORY
52. pg_surgery to recover from corruption beware! “unsafe by design” CATEGORY Admin
53. amcheck CONTRIB checks internal consistency tables & indexes Admin CATEGORY
54. pg_partman CATEGORY Admin
55. pgsodium Security CATEGORY CATEGORY
56. Unaccent CATEGORY Text
57. fuzzystrmatch CONTRIB ”fuzzy string match” Source: pganalyze blog post: Similarity in Postgres and Rails using Trigrams Text CATEGORY pg_trgm CONTRIB “trigram”
58. 58 Where to get Postgres extensions? @clairegiordano@hachyderm.io
59. 59 4 common sources for Postgres extensions... +1 to Christoph Berg, Devrim Gündüz, David Wheeler, & all PG extension creators! RPM & Debian “Contrib” (pre-bundled) RPM, Debian, etc. package PGXN Client Cloud provider (if managed service)
60. PGXN v2 improvement project community project led by David Wheeler from Tembo 60 Want to get involved or provide input? Future PGXN-v2 Architecture Diagram (Apr 2024) Source: David Wheeler of Tembo: https://gist.github.com/theory/1de9633f57976e778b46b013867c3147 pgtreats.info/slack-invite Join #extensions channel on Postgres Slack
61. 61 Trust: Can I trust & rely on this extension? Trust @clairegiordano@hachyderm.io
62. 62 Source: cs.cmu.edu. Copyright 1984 ACM. Originally published in Communications of the ACM, August 1984, Volume 27, Number 8. Copy by permission of the Association of Computing Machinery.
63. 63 Will more PG extensions be written in Rust? Rust Trust
64. PGRX = Platform for building Rust extensions Created by Eric Ridge of ZomboDB... Used by 62 repos so far 2.7K stars
65. Not only do developers love Rust—extensions that are built in Rust will benefit users too Source: Jelte Fennema talk “Postgres without SQL: Natural language queries using GPT-3 & Rust” at Citus Con: An Event for Postgres 2023. Published on YouTube.
66. People to thank for inspiration & reviews Daniel Gustafsson David Wheeler Devrim Gündüz Dimitri Fontaine Jelte Fennema-Nio Jerry Sievert Marco Slot Melanie Plageman Nik Larin Robert Treat Samay Sharma Teresa Giacomini Thomas Munro
67. Have you heard of this podcast for developers who love Postgres?  Monthly  14+ episodes  A-ma-zing guests aka.ms/PathToCitusCon Hosted by Claire Giordano
68. aka.ms/PathToCitusCon A podcast for developers who love Postgres
69. with topics like....  Becoming expert in PostgreSQL  From dev to Postgres specialist  Journey into Performance benchmarking  My Journey into Postgres monitoring  How I got started as a developer  Why people care about PostGIS aka.ms/PathToCitusCon Hosted by Claire Giordano
70. 70 Save the Date aka.ms/posette-cal (formerly Citus Con)
71. @clairegiordano @clairegiordano@hachyderm.io aka.ms/PathToCitusCon z aka.ms/posette PGDAY CHICAGO 2024