Tweet
Tweet

Slide 1

Slide 1 text

PGCONF NYC 2023 CLAIRE GIORDANO @clairegiordano@hachyderm.io • @clairegiordano • @AzureDBPostgres • @citusdata

Slide 2

Slide 2 text

Engineer Dev Manager PM Marketer Writer Open-source champion Community Lead @clairegiordano @clairegiordano@hachyderm.io

Slide 3

Slide 3 text

Citus = Postgres open-source extension that first got me involved with PG

Slide 4

Slide 4 text

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

Slide 5

Slide 5 text

5 Search for a good analogy for Postgres Extensions plug & play beloved modular building toy

Slide 6

Slide 6 text

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?

Slide 7

Slide 7 text

7

Slide 8

Slide 8 text

8

Slide 9

Slide 9 text

9 Extensions support authored by Dimitri Fontaine in 2011, in Postgres 9.1

Slide 10

Slide 10 text

10 “Journey” for this talk about Postgres Extensions @clairegiordano@hachyderm.io

Slide 11

Slide 11 text

11 How to discover what Postgres extensions exist? @clairegiordano@hachyderm.io

Slide 12

Slide 12 text

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

Slide 13

Slide 13 text

But then I started to wonder... What categories is everyone else using for Postgres extensions? @clairegiordano@hachyderm.io

Slide 14

Slide 14 text

First place I looked for Categories: PGXN.org I found tags! Not categories. I could search! Not browse extensions.

Slide 15

Slide 15 text

15 Discovered “Trunk” via Samay Sharma @ Tembo pgt.dev

Slide 16

Slide 16 text

Trunk has categories for extensions! open source project pgt.dev

Slide 17

Slide 17 text

Supabase’s docs about PG extensions have categories too!

Slide 18

Slide 18 text

So does Crunchy Bridge!

Slide 19

Slide 19 text

Azure Cosmos DB for PostgreSQL has “categories” in docs too Citus Data types Full-text search Functions Index types Language Miscellaneous Pgvector PostGIS

Slide 20

Slide 20 text

Like a dog with a tennis ball I spent wayyyyyy too much time trying to come up with “right” set of categories

Slide 21

Slide 21 text

21 My search to find the “right” set of categories

Slide 22

Slide 22 text

@clairegiordano@hachyderm.io All models are wrong, but some are useful.” “ —George Box, statistician Dimitri Fontaine reminded me of this quote

Slide 23

Slide 23 text

So I promised you a “map” of Postgres extensions

Slide 24

Slide 24 text

Mind map of Categories of Postgres Extensions AI / Machine Learning Admin Migration @clairegiordano@hachyderm.io

Slide 25

Slide 25 text

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

Slide 26

Slide 26 text

26 “Journey” for this talk on Amazing PG Extensions

Slide 27

Slide 27 text

And will Postgres extensions save you time?

Slide 28

Slide 28 text

pg_stat_statements CONTRIB tracks statistics about SQL statements how long queries took? CATEGORY Performance

Slide 29

Slide 29 text

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

Slide 30

Slide 30 text

30 Concept of “Super extension” on Postgres.FM

Slide 31

Slide 31 text

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”

Slide 32

Slide 32 text

Citus Distributed PostgreSQL “Postgres at any scale” Sharding Led by Microsoft “Super extension” CATEGORY Distributed Scale

Slide 33

Slide 33 text

33 Postgres extensions enable innovation

Slide 34

Slide 34 text

No content

Slide 35

Slide 35 text

pgRouting adds routing network analysis Shortest Path Driving Distance Traveling Salesperson CATEGORY Geo

Slide 36

Slide 36 text

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...”

Slide 37

Slide 37 text

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()');

Slide 38

Slide 38 text

tdigest, hll, topn approximation/”sketch” algorithms t-digest: calculate Percentiles (histograms) hll = HyperLogLog, for COUNT DISTINCT Index CATEGORY

Slide 39

Slide 39 text

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

Slide 40

Slide 40 text

RUM more powerful version of GIN index for text search (or your own custom types) CATEGORY Index

Slide 41

Slide 41 text

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

Slide 42

Slide 42 text

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

Slide 43

Slide 43 text

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

Slide 44

Slide 44 text

plv8 procedural language lets you run JavaScript code stored procedures, triggers Language CATEGORY

Slide 45

Slide 45 text

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

Slide 46

Slide 46 text

orafce makes it easier to migrate from Oracle to Postgres functions, data types... mysql_fdw Migration CATEGORY

Slide 47

Slide 47 text

“ —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

Slide 48

Slide 48 text

pg_prewarm CONTRIB useful to put pages back in cache after restarting Postgres maybe should have been in “Performance” category? Admin CATEGORY

Slide 49

Slide 49 text

pg_surgery to recover from corruption beware! “unsafe by design” CATEGORY Admin

Slide 50

Slide 50 text

amcheck CONTRIB checks internal consistency tables & indexes Admin CATEGORY

Slide 51

Slide 51 text

pg_partman CATEGORY Admin

Slide 52

Slide 52 text

pgsodium Security CATEGORY CATEGORY

Slide 53

Slide 53 text

Unaccent CATEGORY Text

Slide 54

Slide 54 text

fuzzystrmatch CONTRIB ”fuzzy string match” Source: pganalyze blog post: Similarity in Postgres and Rails using Trigrams Text CATEGORY pg_trgm CONTRIB “trigram”

Slide 55

Slide 55 text

55 Where to get Postgres extensions? @clairegiordano@hachyderm.io

Slide 56

Slide 56 text

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)

Slide 57

Slide 57 text

57 Trust: Can I trust & rely on this extension? Trust @clairegiordano@hachyderm.io

Slide 58

Slide 58 text

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.

Slide 59

Slide 59 text

59 Will more PG extensions be written in Rust? Rust Trust

Slide 60

Slide 60 text

PGRX = Platform for building Rust extensions Created by Eric Ridge of ZomboDB... Used by 62 repos so far 2.7K stars

Slide 61

Slide 61 text

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.

Slide 62

Slide 62 text

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

Slide 63

Slide 63 text

@clairegiordano @clairegiordano@hachyderm.io aka.ms/PathToCitusCon PGCONF NYC 2023 z