Upgrade to Pro — share decks privately, control downloads, hide ads and more …

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

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.

Claire Giordano

October 06, 2023

More Decks by Claire Giordano

Other Decks in Technology


  1. 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
  2. 5 Search for a good analogy for Postgres Extensions plug

    & play beloved modular building toy
  3. 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?
  4. 7

  5. 8

  6. 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
  7. First place I looked for Categories: PGXN.org I found tags!

    Not categories. I could search! Not browse extensions.
  8. Azure Cosmos DB for PostgreSQL has “categories” in docs too

    Citus Data types Full-text search Functions Index types Language Miscellaneous Pgvector PostGIS
  9. Like a dog with a tennis ball I spent wayyyyyy

    too much time trying to come up with “right” set of categories
  10. @[email protected] All models are wrong, but some are useful.” “

    —George Box, statistician Dimitri Fontaine reminded me of this quote
  11. 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 @[email protected]
  12. 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
  13. 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”
  14. Citus Distributed PostgreSQL “Postgres at any scale” Sharding Led by

    Microsoft “Super extension” CATEGORY Distributed Scale
  15. 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...”
  16. 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()');
  17. 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
  18. RUM more powerful version of GIN index for text search

    (or your own custom types) CATEGORY Index
  19. 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
  20. 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
  21. 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
  22. 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
  23. orafce makes it easier to migrate from Oracle to Postgres

    functions, data types... mysql_fdw Migration CATEGORY
  24. “ —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
  25. pg_prewarm CONTRIB useful to put pages back in cache after

    restarting Postgres maybe should have been in “Performance” category? Admin CATEGORY
  26. fuzzystrmatch CONTRIB ”fuzzy string match” Source: pganalyze blog post: Similarity

    in Postgres and Rails using Trigrams Text CATEGORY pg_trgm CONTRIB “trigram”
  27. 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)
  28. 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.
  29. PGRX = Platform for building Rust extensions Created by Eric

    Ridge of ZomboDB... Used by 62 repos so far 2.7K stars
  30. 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.
  31. 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