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

Using PostgreSQL's Background Worker Processes ...

Using PostgreSQL's Background Worker Processes For Fun and Profit

Alexey Palazhchenko

February 22, 2025
Tweet

More Decks by Alexey Palazhchenko

Other Decks in Programming

Transcript

  1. Using PostgreSQL's Background Worker Processes For Fun and Pro fi

    t Alexey Palazhchenko, FerretDB Inc. Armenia PostgreSQL User Group meetup February 22, 2025
  2. About me • Developer since early childhood • Go developer

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

    since r60 (2011) • PostgreSQL contributor since 2024 (by accident) • Qik->Skype->Microsoft, […], Percona, Sidero Labs
  4. 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
  5. FerretDB overview PostgreSQL + DocumentDB extension FerretDB Any application Any

    MongoDB driver MongoDB protocol
 BSON PostgreSQL protocol
 SQL
  6. Why PostgreSQL? • Very mature and reliable • Many companies

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

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

    run it (sometimes side-by-side with MongoDB) • Open-source, huge community • Extensible
  9. Why not extension? • Di ff erent processes scale di

    ff erently • Stateless • Better at handling MongoDB client connections
 (there are many of them!)
  10. 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
  11. — Database administrator in a big company «It would be

    easier to sneak a PostgreSQL extension to production»
  12. FerretDB extension PostgreSQL + DocumentDB extension FerretDB Any application Any

    MongoDB driver MongoDB protocol
 BSON PostgreSQL protocol
 SQL
  13. The idea • Go (garbage-collected language with runtime, di ff

    erent ABI, etc) • cgo • FerretDB Go module
  14. 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.
  15. 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.
  16. 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. • Also, by linking to libpq they can connect to the server and behave like a regular client application.
  17. Not extension • No control fi les, no CREATE EXTENSION,

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

    no upgrades, etc • Just add .so fi le to shared_preload_libraries • postmaster preloads it
  19. 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
  20. 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
  21. Duct tape • _PG_init: calls RegisterBackgroundWorker for a C function

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

    • C: sets up signal handlers, calls Go/cgo function • Go: starts FerretDB Go module
  23. _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); }
  24. 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); }
  25. Go package main / * extern void BackgroundWorkerMain(); * /

    import "C" func main() { panic("not reached") } / / export BackgroundWorkerMain func BackgroundWorkerMain() { // starts FerretDB Go module }
  26. 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)
  27. Not fun, no pro fi t :( • Di ff

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

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

    erent Go version? • Di ff erent PostgreSQL version? • Compiler/linker fl ags? • Signals handling?
  30. 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?
  31. 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
  32. 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
  33. 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 • PostgreSQL forks the process with threads
  34. 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 • PostgreSQL forks the process with threads • 💣
  35. — 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.»
  36. 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
  37. 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
  38. Preloading and loading • PostgreSQL: • preloads shared library
 (dlopen)

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

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

    • registers background worker
 (dlsym _PG_init, function call) • starts background worker process (fork, function call) • FerretDBLoader:
  41. 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)
  42. 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) • fi nds entry point
 (dlsym BackgroundWorkerMain)
  43. 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) • fi nds entry point
 (dlsym BackgroundWorkerMain) • calls entry point
 (function call)
  44. 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); }
  45. 🎉 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/
  46. Pro fi t • PostgreSQL background worker processes infrastructure is

    useful • It can be used to run any code • Some duct tape might be required