Using PostgreSQL's Background Worker Processes For Fun and Profit

Transcript

2. Using PostgreSQL's Background Worker Processes For Fun and Pro fi t Alexey Palazhchenko, FerretDB Inc. Armenia PostgreSQL User Group meetup February 22, 2025
3. About me
4. About me • Developer since early childhood
5. About me • Developer since early childhood • Go developer since r60 (2011)
6. About me • Developer since early childhood • Go developer since r60 (2011) • PostgreSQL contributor since 2024 (by accident)
7. About me • Developer since early childhood • Go developer since r60 (2011) • PostgreSQL contributor since 2024 (by accident) • Qik->Skype->Microsoft, […], Percona, Sidero Labs
8. 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
9. FerretDB overview Any application Any MongoDB driver MongoDB protocol
 BSON M M MMM M M
10. FerretDB overview FerretDB Any application Any MongoDB driver MongoDB protocol
 BSON
11. FerretDB overview PostgreSQL + DocumentDB extension FerretDB Any application Any MongoDB driver MongoDB protocol
 BSON PostgreSQL protocol
 SQL
12. Why PostgreSQL?
13. Why PostgreSQL? • Very mature and reliable
14. Why PostgreSQL? • Very mature and reliable • Many companies run it (sometimes side-by-side with MongoDB)
15. Why PostgreSQL? • Very mature and reliable • Many companies run it (sometimes side-by-side with MongoDB) • Open-source, huge community
16. Why PostgreSQL? • Very mature and reliable • Many companies run it (sometimes side-by-side with MongoDB) • Open-source, huge community • Extensible
17. Why not extension?
18. Why not extension? • Di ff erent processes scale di ff erently
19. Why not extension? • Di ff erent processes scale di ff erently • Stateless
20. Why not extension? • Di ff erent processes scale di ff erently • Stateless • Better at handling MongoDB client connections
 (there are many of them!)
21. 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
22. But…
23. — Database administrator in a big company «It would be easier to sneak a PostgreSQL extension to production»
24. FerretDB extension PostgreSQL + DocumentDB extension FerretDB Any application Any MongoDB driver MongoDB protocol
 BSON PostgreSQL protocol
 SQL
25. The idea
26. The idea • Go (garbage-collected language with runtime, di ff erent ABI, etc)
27. The idea • Go (garbage-collected language with runtime, di ff erent ABI, etc) • cgo
28. The idea • Go (garbage-collected language with runtime, di ff erent ABI, etc) • cgo • FerretDB Go module
29. Go + cgo + duct tape in C
30. Background workers
31. 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.
32. 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.
33. 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.
34. Not extension
35. Not extension • No control fi les, no CREATE EXTENSION, no upgrades, etc
36. Not extension • No control fi les, no CREATE EXTENSION, no upgrades, etc • Just add .so fi le to shared_preload_libraries
37. Not extension • No control fi les, no CREATE EXTENSION, no upgrades, etc • Just add .so fi le to shared_preload_libraries • postmaster preloads it
38. 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
39. 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
40. Duct tape
41. Duct tape • _PG_init: calls RegisterBackgroundWorker for a C function
42. Duct tape • _PG_init: calls RegisterBackgroundWorker for a C function • C: sets up signal handlers, calls Go/cgo function
43. Duct tape • _PG_init: calls RegisterBackgroundWorker for a C function • C: sets up signal handlers, calls Go/cgo function • Go: starts FerretDB Go module
44. _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); }
45. 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); }
46. Go package main / * extern void BackgroundWorkerMain(); * / import "C" func main() { panic("not reached") } / / export BackgroundWorkerMain func BackgroundWorkerMain() { // starts FerretDB Go module }
47. 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)
48. Go + cgo + duct tape in C
49. Go + cgo + duct tape in C Drama
50. It doesn’t work
51. Not fun, no pro fi t :(
52. Not fun, no pro fi t :( • Di ff erent Go version?
53. Not fun, no pro fi t :( • Di ff erent Go version? • Di ff erent PostgreSQL version?
54. Not fun, no pro fi t :( • Di ff erent Go version? • Di ff erent PostgreSQL version? • Compiler/linker fl ags?
55. Not fun, no pro fi t :( • Di ff erent Go version? • Di ff erent PostgreSQL version? • Compiler/linker fl ags? • Signals handling?
56. 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?
57. 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
58. 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
59. 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
60. 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 • 💣
61. — 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.»
62. What can we do?
63. What can we do? • Wait for multi-threaded PostgreSQL:
 https://wiki.postgresql.org/wiki/Multithreading
64. 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
65. 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
66. Right?..
67. Preloading and loading
68. Preloading and loading • PostgreSQL:
69. Preloading and loading • PostgreSQL: • preloads shared library
 (dlopen)
70. Preloading and loading • PostgreSQL: • preloads shared library
 (dlopen) • registers background worker
 (dlsym _PG_init, function call)
71. Preloading and loading • PostgreSQL: • preloads shared library
 (dlopen) • registers background worker
 (dlsym _PG_init, function call) • starts background worker process (fork, function call)
72. Preloading and loading • PostgreSQL: • preloads shared library
 (dlopen) • registers background worker
 (dlsym _PG_init, function call) • starts background worker process (fork, function call) • FerretDBLoader:
73. 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)
74. 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)
75. 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)
76. Old background_main void background_main(Datum main_arg) { BackgroundWorkerMain(); proc_exit(0); }
77. 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); }
78. 🎉 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/
79. Pro fi t
80. Pro fi t • PostgreSQL background worker processes infrastructure is useful
81. Pro fi t • PostgreSQL background worker processes infrastructure is useful • It can be used to run any code
82. Pro fi t • PostgreSQL background worker processes infrastructure is useful • It can be used to run any code • Some duct tape might be required
83. The fun continues
84. The fun continues • https://github.com/FerretDB/FerretDB 🌟
85. The fun continues • https://github.com/FerretDB/FerretDB 🌟 • https://www.ferretdb.com/
86. The fun continues • https://github.com/FerretDB/FerretDB 🌟 • https://www.ferretdb.com/
87. The fun continues • https://github.com/FerretDB/FerretDB 🌟 • https://www.ferretdb.com/ • Questions?