Using PostgreSQL's Background Worker Processes For Fun and Profit

Slide 1

Slide 1 text

No content

Slide 2

Slide 2 text

Using PostgreSQL's Background Worker Processes For Fun and Pro fi t Alexey Palazhchenko, FerretDB Inc. Armenia PostgreSQL User Group meetup February 22, 2025

Slide 3

Slide 3 text

About me

Slide 4

Slide 4 text

About me • Developer since early childhood

Slide 5

Slide 5 text

About me • Developer since early childhood • Go developer since r60 (2011)

Slide 6

Slide 6 text

About me • Developer since early childhood • Go developer since r60 (2011) • PostgreSQL contributor since 2024 (by accident)

Slide 7

Slide 7 text

About me • Developer since early childhood • Go developer since r60 (2011) • PostgreSQL contributor since 2024 (by accident) • Qik->Skype->Microsoft, […], Percona, Sidero Labs

Slide 8

Slide 8 text

About me • Developer since early childhood • Go developer since r60 (2011) • PostgreSQL contributor since 2024 (by accident) • Qik->Skype->Microsoft, […], Percona, Sidero Labs • FerretDB Inc. co-founder (with other ex-Perconians) and CTO

Slide 9

Slide 9 text

FerretDB overview Any application Any MongoDB driver MongoDB protocol  BSON M M MMM M M

Slide 10

Slide 10 text

FerretDB overview FerretDB Any application Any MongoDB driver MongoDB protocol  BSON

Slide 11

Slide 11 text

FerretDB overview PostgreSQL + DocumentDB extension FerretDB Any application Any MongoDB driver MongoDB protocol  BSON PostgreSQL protocol  SQL

Slide 12

Slide 12 text

Why PostgreSQL?

Slide 13

Slide 13 text

Why PostgreSQL? • Very mature and reliable

Slide 14

Slide 14 text

Why PostgreSQL? • Very mature and reliable • Many companies run it (sometimes side-by-side with MongoDB)

Slide 15

Slide 15 text

Why PostgreSQL? • Very mature and reliable • Many companies run it (sometimes side-by-side with MongoDB) • Open-source, huge community

Slide 16

Slide 16 text

Why PostgreSQL? • Very mature and reliable • Many companies run it (sometimes side-by-side with MongoDB) • Open-source, huge community • Extensible

Slide 17

Slide 17 text

Why not extension?

Slide 18

Slide 18 text

Why not extension? • Di ff erent processes scale di ff erently

Slide 19

Slide 19 text

Why not extension? • Di ff erent processes scale di ff erently • Stateless

Slide 20

Slide 20 text

Why not extension? • Di ff erent processes scale di ff erently • Stateless • Better at handling MongoDB client connections  (there are many of them!)

Slide 21

Slide 21 text

Why not extension? • Di ff erent processes scale di ff erently • Stateless • Better at handling MongoDB client connections  (there are many of them!) • Some potential features would require GPUs

Slide 22

Slide 22 text

But…

Slide 23

Slide 23 text

— Database administrator in a big company «It would be easier to sneak a PostgreSQL extension to production»

Slide 24

Slide 24 text

FerretDB extension PostgreSQL + DocumentDB extension FerretDB Any application Any MongoDB driver MongoDB protocol  BSON PostgreSQL protocol  SQL

Slide 25

Slide 25 text

The idea

Slide 26

Slide 26 text

The idea • Go (garbage-collected language with runtime, di ff erent ABI, etc)

Slide 27

Slide 27 text

The idea • Go (garbage-collected language with runtime, di ff erent ABI, etc) • cgo

Slide 28

Slide 28 text

The idea • Go (garbage-collected language with runtime, di ff erent ABI, etc) • cgo • FerretDB Go module

Slide 29

Slide 29 text

Go + cgo + duct tape in C

Slide 30

Slide 30 text

Background workers

Slide 31

Slide 31 text

Slide 32

Slide 32 text

Background workers • PostgreSQL can be extended to run user-supplied code in separate processes. Such processes are started, stopped and monitored by postgres, which permits them to have a lifetime closely linked to the server's status. • These processes are attached to PostgreSQL's shared memory area and have the option to connect to databases internally; they can also run multiple transactions serially, just like a regular client-connected server process.

Slide 33

Slide 33 text

Slide 34

Slide 34 text

Not extension

Slide 35

Slide 35 text

Not extension • No control fi les, no CREATE EXTENSION, no upgrades, etc

Slide 36

Slide 36 text

Not extension • No control fi les, no CREATE EXTENSION, no upgrades, etc • Just add .so fi le to shared_preload_libraries

Slide 37

Slide 37 text

Not extension • No control fi les, no CREATE EXTENSION, no upgrades, etc • Just add .so fi le to shared_preload_libraries • postmaster preloads it

Slide 38

Slide 38 text

Not extension • No control fi les, no CREATE EXTENSION, no upgrades, etc • Just add .so fi le to shared_preload_libraries • postmaster preloads it • registers background worker

Slide 39

Slide 39 text

Not extension • No control fi les, no CREATE EXTENSION, no upgrades, etc • Just add .so fi le to shared_preload_libraries • postmaster preloads it • registers background worker • then forks a background worker process

Slide 40

Slide 40 text

Duct tape

Slide 41

Slide 41 text

Duct tape • _PG_init: calls RegisterBackgroundWorker for a C function

Slide 42

Slide 42 text

Duct tape • _PG_init: calls RegisterBackgroundWorker for a C function • C: sets up signal handlers, calls Go/cgo function

Slide 43

Slide 43 text

Duct tape • _PG_init: calls RegisterBackgroundWorker for a C function • C: sets up signal handlers, calls Go/cgo function • Go: starts FerretDB Go module

Slide 44

Slide 44 text

_PG_init void _PG_init(void) { BackgroundWorker worker; MemSet(&worker, 0, sizeof(BackgroundWorker)); snprintf(worker.bgw_name, BGW_MAXLEN, "FerretDB"); worker.bgw_flags = BGWORKER_SHMEM_ACCESS; worker.bgw_start_time = BgWorkerStart_RecoveryFinished; worker.bgw_restart_time = BGW_NEVER_RESTART; snprintf(worker.bgw_library_name, BGW_MAXLEN, "ferretdb"); snprintf(worker.bgw_function_name, BGW_MAXLEN, "background_main"); RegisterBackgroundWorker(&worker); }

Slide 45

Slide 45 text

background_main #include "postgres.h" #include "postmaster/bgworker.h" #include "storage/ipc.h" #include "storage/proc.h" #include "_cgo_export.h" PG_MODULE_MAGIC; void background_main(Datum main_arg) { BackgroundWorkerUnblockSignals(); BackgroundWorkerMain(); proc_exit(0); }

Slide 46

Slide 46 text

Go package main / * extern void BackgroundWorkerMain(); * / import "C" func main() { panic("not reached") } / / export BackgroundWorkerMain func BackgroundWorkerMain() { // starts FerretDB Go module }

Slide 47

Slide 47 text

Make fi le build: env CGO_CFLAGS="-I $(shell pg_config -- includedir-server)" \ CGO_LDFLAGS="-shared" \ go build -v -buildmode=c-shared -o ferretdb.so cp ferretdb.so $(shell pg_config - - pkglibdir)

Slide 48

Slide 48 text

Go + cgo + duct tape in C

Slide 49

Slide 49 text

Go + cgo + duct tape in C Drama

Slide 50

Slide 50 text

It doesn’t work

Slide 51

Slide 51 text

Not fun, no pro fi t :(

Slide 52

Slide 52 text

Not fun, no pro fi t :( • Di ff erent Go version?

Slide 53

Slide 53 text

Not fun, no pro fi t :( • Di ff erent Go version? • Di ff erent PostgreSQL version?

Slide 54

Slide 54 text

Not fun, no pro fi t :( • Di ff erent Go version? • Di ff erent PostgreSQL version? • Compiler/linker fl ags?

Slide 55

Slide 55 text

Not fun, no pro fi t :( • Di ff erent Go version? • Di ff erent PostgreSQL version? • Compiler/linker fl ags? • Signals handling?

Slide 56

Slide 56 text

Not fun, no pro fi t :( • Di ff erent Go version? • Di ff erent PostgreSQL version? • Compiler/linker fl ags? • Signals handling? • PID 1 problem?

Slide 57

Slide 57 text

Not fun, no pro fi t :( • Di ff erent Go version? • Di ff erent PostgreSQL version? • Compiler/linker fl ags? • Signals handling? • PID 1 problem? • PostgreSQL preloads .so

Slide 58

Slide 58 text

Not fun, no pro fi t :( • Di ff erent Go version? • Di ff erent PostgreSQL version? • Compiler/linker fl ags? • Signals handling? • PID 1 problem? • PostgreSQL preloads .so • .so starts the Go runtime thread

Slide 59

Slide 59 text

Slide 60

Slide 60 text

Slide 61

Slide 61 text

— Ian Lance Taylor  https://github.com/golang/go/issues/15538#issuecomment-216916265 «A multi-threaded program can not safely call fork; this is a general rule, not speci fi c to Go. A single-threaded program that dlopen's a Go shared library becomes a multi-threaded program. At that point, it can not call fork.»

Slide 62

Slide 62 text

What can we do?

Slide 63

Slide 63 text

What can we do? • Wait for multi-threaded PostgreSQL:  https://wiki.postgresql.org/wiki/Multithreading

Slide 64

Slide 64 text

What can we do? • Wait for multi-threaded PostgreSQL:  https://wiki.postgresql.org/wiki/Multithreading • Make the PostgreSQL fork process fi rst, then load a shared library

Slide 65

Slide 65 text

What can we do? • Wait for multi-threaded PostgreSQL:  https://wiki.postgresql.org/wiki/Multithreading • Make the PostgreSQL fork process fi rst, then load a shared library • No other way

Slide 66

Slide 66 text

Right?..

Slide 67

Slide 67 text

Preloading and loading

Slide 68

Slide 68 text

Preloading and loading • PostgreSQL:

Slide 69

Slide 69 text

Preloading and loading • PostgreSQL: • preloads shared library  (dlopen)

Slide 70

Slide 70 text

Preloading and loading • PostgreSQL: • preloads shared library  (dlopen) • registers background worker  (dlsym _PG_init, function call)

Slide 71

Slide 71 text

Slide 72

Slide 72 text

Slide 73

Slide 73 text

Preloading and loading • PostgreSQL: • preloads shared library  (dlopen) • registers background worker  (dlsym _PG_init, function call) • starts background worker process (fork, function call) • FerretDBLoader: • loads FerretDB shared library  (dlopen)

Slide 74

Slide 74 text

Slide 75

Slide 75 text

Slide 76

Slide 76 text

Old background_main void background_main(Datum main_arg) { BackgroundWorkerMain(); proc_exit(0); }

Slide 77

Slide 77 text

New background_main void background_main(Datum main_arg) { h = dlopen(path, RTLD_NOW | RTLD_GLOBAL); f = (bgworker_main_type)dlsym(h, "BackgroundWorkerMain"); f(main_arg); dlclose(h); proc_exit(0); }

Slide 78

Slide 78 text

🎉 DEBUG: starting background worker process "FerretDBLoader"  LOG: database system is ready to accept connections  DEBUG: ferretdb_loader: loading go shared lib "/usr/lib/postgresql/16/lib/ferretdb.so"  2025/02/22 09:31:22 FerretDB is starting  2025/02/22 09:31:22 FerretDB is running at mongodb://127.0.0.1:27027/

Slide 79

Slide 79 text

Pro fi t

Slide 80

Slide 80 text

Pro fi t • PostgreSQL background worker processes infrastructure is useful

Slide 81

Slide 81 text

Pro fi t • PostgreSQL background worker processes infrastructure is useful • It can be used to run any code

Slide 82

Slide 82 text

Pro fi t • PostgreSQL background worker processes infrastructure is useful • It can be used to run any code • Some duct tape might be required

Slide 83

Slide 83 text

The fun continues

Slide 84

Slide 84 text

The fun continues • https://github.com/FerretDB/FerretDB 🌟

Slide 85

Slide 85 text

The fun continues • https://github.com/FerretDB/FerretDB 🌟 • https://www.ferretdb.com/

Slide 86

Slide 86 text

The fun continues • https://github.com/FerretDB/FerretDB 🌟 • https://www.ferretdb.com/

Slide 87

Slide 87 text

The fun continues • https://github.com/FerretDB/FerretDB 🌟 • https://www.ferretdb.com/ • Questions?

Slide 88

Slide 88 text

No content