Map of Amazing Postgres Extensions You Might Not Know About | Claire Giordano | PGConf NYC 2023

Slides from a talk about a "map" of amazing Postgres extensions at PGConf NYC 2023. Postgres extensions are everywhere—and are found in 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 6+ 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, PostGIS, postgres_fdw, and Citus—as well as lesser-known extensions that are super handy too.

You’ll get a detour into the history of Postgres extensions and how they came to be, and a glimpse into the future of Postgres 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. PGCONF NYC 2023 CLAIRE GIORDANO @clairegiordano@hachyderm.io • @clairegiordano • @AzureDBPostgres • @citusdata
2. Engineer Dev Manager PM Marketer Writer Open-source champion Community Lead @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 Podcast episode with Paul Ramsey & Regina Obe: “Why people care about PostGIS”
32. Citus Distributed PostgreSQL “Postgres at any scale” Sharding Led by Microsoft “Super extension” CATEGORY Distributed Scale
33. 33 Postgres extensions enable innovation
35. pgRouting adds routing network analysis Shortest Path Driving Distance Traveling Salesperson CATEGORY Geo
36. 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...”
37. 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()');
38. tdigest, hll, topn approximation/”sketch” algorithms t-digest: calculate Percentiles (histograms) hll = HyperLogLog, for COUNT DISTINCT Index CATEGORY
39. 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
40. RUM more powerful version of GIN index for text search (or your own custom types) CATEGORY Index
41. 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
42. 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
43. 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
44. plv8 procedural language lets you run JavaScript code stored procedures, triggers Language CATEGORY
45. plv8 procedural language lets you run JavaScript code stored procedures, triggers NEXT TALK: “Building a better JavaScript Postgres Language Extension” by Jerry Sievert Language CATEGORY
46. orafce makes it easier to migrate from Oracle to Postgres functions, data types... mysql_fdw Migration CATEGORY
47. “ —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
48. pg_prewarm CONTRIB useful to put pages back in cache after restarting Postgres maybe should have been in “Performance” category? Admin CATEGORY
49. pg_surgery to recover from corruption beware! “unsafe by design” CATEGORY Admin
50. amcheck CONTRIB checks internal consistency tables & indexes Admin CATEGORY
51. pg_partman CATEGORY Admin
52. pgsodium Security CATEGORY CATEGORY
53. Unaccent CATEGORY Text
54. fuzzystrmatch CONTRIB ”fuzzy string match” Source: pganalyze blog post: Similarity in Postgres and Rails using Trigrams Text CATEGORY pg_trgm CONTRIB “trigram”
55. 55 Where to get Postgres extensions? @clairegiordano@hachyderm.io
56. 56 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)
57. 57 Trust: Can I trust & rely on this extension? Trust @clairegiordano@hachyderm.io
58. 58 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.
59. 59 Will more PG extensions be written in Rust? Rust Trust
60. PGRX = Platform for building Rust extensions Created by Eric Ridge of ZomboDB... Used by 62 repos so far 2.7K stars
61. 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.
62. People to thank for inspiration & reviews Daniel Gustafsson Devrim Gündüz Dimitri Fontaine Jelte Fennema-Nio Marco Slot Melanie Plageman Nik Larin Robert Treat Samay Sharma Teresa Giacomini Thomas Munro
63. @clairegiordano @clairegiordano@hachyderm.io aka.ms/PathToCitusCon PGCONF NYC 2023 z