Map of Amazing Postgres Extensions You Might Not Know About | Claire Giordano | PASS Data Summit 2024

Slides from a conference presentation at PASS Data Summit 2024 in Seattle. Abstract: Postgres extensions are everywhere—and are found in lots of 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. If you’re new to Postgres, you might be wondering “What is a Postgres extension?” If you’re familiar with Postgres, you might still wonder: “Where do I find all the extensions? Can I trust them? Which Postgres extensions will make my life better and save me time?”

In this updated version of a talk I first gave at PGConf NYC, I’ll walk you through the set of amazing Postgres extensions I’ve learned about in my ~8 years working in the world of Postgres: what they are, why care about them, 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 over 20 lesser-known (or favorite) extensions which are quite handy, too.

This talk includes a brief detour into the history of Postgres extensions, and 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. @clairegiordano • @PosetteConf • @AzureDBPostgres MAP OF AMAZING YOU MIGHT NOT KNOW ABOUT
2. Head of Postgres open-source community initiatives at Microsoft linkedin.com/in/claireg @clairegiordano @clairegiordano@hachyderm.io she/her Giordano Engineer Dev Manager PM Marketer Writer Open-source champion Community Lead Podcaster
3. Quite a lot of Postgres work @ Microsoft Azure Database for PostgreSQL MANAGED DATABASE SERVICE fully-managed database service for Postgres NEW CAPABILITIES in FLEXIBLE SERVER “Flexible Server” aka.ms/blog-pg-at-microsoft OPEN SOURCE WORK PostgreSQL core Contribute to PG open source (and review patches on many other people's work!) Citus Open Source Citus open-source extension to Postgres gives you Postgres at any scale. (Think: distributed Postgres.) PG Ecosystem Postgres extension & tooling our PG team at Microsoft maintains or contributed to in last ~8 months PG Community Contribute to growth & knowledge of the PostgreSQL open-source developer & user communities. @clairegiordano
4. Citus = open-source extension that first got me involved with Postgres
5. 5 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
6. 6 Search for a good analogy for Postgres Extensions plug & play beloved modular building toy
7. 7 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?
10. 10 Extensions support authored by Dimitri Fontaine in 2011, in Postgres 9.1
11. 11 “Journey” for this talk about Postgres Extensions @clairegiordano
12. 12 How to discover what Postgres extensions exist? @clairegiordano
13. 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
14. But then I started to wonder... What categories is everyone else using for Postgres extensions? @clairegiordano
15. First place I looked for Categories: PGXN.org I found tags! Not categories. I could search! Not browse extensions.
16. 16 Discovered “Trunk” via Samay Sharma @ Tembo pgt.dev
17. Trunk has categories for extensions! open source project pgt.dev
18. Supabase’s docs about PG extensions have categories too!
19. So does Crunchy Bridge!
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. All models are wrong, but some are useful.” “ — George Box, statistician Dimitri Fontaine reminded me of this quote @clairegiordano
23. So I promised you a “map” of Postgres extensions
24. Mind map of Categories of Postgres Extensions AI / Machine Learning Admin Migration @clairegiordano
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 azure_ai
26. 26 “Journey” for this talk on Amazing PG Extensions
27. And will Postgres extensions save you time? @clairegiordano
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 TalkingPostgres.com
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 CATEGORY AI / Machine Learning
39. azure_ai Not open source (yet!) Integrated with Azure OpenAI service Enables you to create text embeddings within your Azure Database for PostgreSQL database CATEGORY AI / Machine Learning Source: Pic by Tonie Huizer on LinkedIn of Microsoft PASS 2024 keynote
40. pg_cron Created/maintained by Marco Slot CATEGORY Admin “pg_cron: probably the best way to schedule jobs within PostgreSQL database” —Prashant @ FatDBA.com - - 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()');
41. t-digest, hll, topn approximation/”sketch” algorithms t-digest: calculate Percentiles (histograms) hll = HyperLogLog, for COUNT DISTINCT Analytics CATEGORY
42. RUM more powerful version of GIN index CATEGORY Index
43. 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
44. 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
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
46. 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” TalkingPostgres.com LISTEN TO THE PODCAST
47. 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
48. plv8 procedural language lets you run JavaScript code stored procedures, triggers Language CATEGORY
49. plv8 procedural language lets you run JavaScript code stored procedures, triggers plv8 documentation link from Jerry Sievert Language CATEGORY https://plv8.github.io/
50. orafce makes it easier to migrate from Oracle to Postgres functions, data types... mysql_fdw Migration CATEGORY
51. “ —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
52. pg_prewarm CONTRIB useful to put pages back in cache after restarting Postgres maybe should have been in “Performance” category? Admin CATEGORY
53. pg_surgery to recover from corruption beware! “unsafe by design” CATEGORY Admin
54. amcheck CONTRIB checks internal consistency tables & indexes Admin CATEGORY
55. pg_partman Admin CATEGORY
56. pgsodium Security CATEGORY CATEGORY
57. Unaccent CATEGORY Text
58. fuzzystrmatch CONTRIB ”fuzzy string match” Source: pganalyze blog post: Similarity in Postgres and Rails using Trigrams Text CATEGORY pg_trgm CONTRIB “trigram”
59. 59 Where to get Postgres extensions? @clairegiordano@hachyderm.io
60. 60 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)
61. PGXN v2 improvement project community project led by David Wheeler from Tembo 61 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
62. 62 Trust: Can I trust & rely on this extension? Trust @clairegiordano
63. 63 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.
64. 64 Will more PG extensions be written in Rust? Rust Trust @clairegiordano
65. PGRX = for building Rust extensions Created by Eric Ridge of ZomboDB... Used by 228 repos so far (last time I gave this talk it was 62 repos!) 3.7K stars
66. 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.
67. 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
68. 68 Favors to ask you!
69. Our Talking Postgres podcast about human side of PG Recent guests: David Rowley, Melanie Plageman, & Tom Lane…
70. Save the date! aka.ms/posette-subscribe Subscribe to news  Brought to you by
71. @clairegiordano @clairegiordano@hachyderm.io TalkingPostgres.com z z linkedin.com/in/claireg •Thank you