SVI/netbird
1
0
Fork 0
mirror of https://github.com/netbirdio/netbird.git synced 2026-05-17 21:52:24 -04:00
Code Issues 3.0k Packages Projects Releases 10 Wiki Activity

[PR #1065] Implement SQLite Store using gorm and relational approach #2987

New Issue
Open
opened 2025-11-20 08:04:36 -05:00 by saavagebueno · 0 comments
saavagebueno commented 2025-11-20 08:04:36 -05:00
Owner
Copy Link

Original Pull Request: https://github.com/netbirdio/netbird/pull/1065

State: closed
Merged: Yes

Describe your changes

This approach involves loading associated tables into slices and then converting them into maps. This technique is introducing additional fields prefixed with 'G' (shows Gorm relation), allowing for granular resource management without many changes to existing structures. While this approach introduces complexities, it serves the goal of achieving a functional implementation while minimizing disruptions to the current architecture.

One significant advantage is the elimination of the complexity associated with the lookup table used in the document-oriented approach. This simplification not only enhances query performance but also contributes to improved write speed, especially as the number of accounts grows.

Another key advantage introduced by the new relational approach is the ability to operate on separated data structures, such as accounts, users, and peers, independently. This level of separation empowers more granular and focused updates, ensuring that modifications to one resource do not require loading the entire account. But this may require additional changes in the Store interface and the Account Manager.

Benchmark Results:

$ go test  ./management/server -run Benchmark_ -bench=.  -benchmem
goos: darwin
goarch: amd64
pkg: github.com/netbirdio/netbird/management/server
cpu: Intel(R) Core(TM) i5-8259U CPU @ 2.30GHz
BenchmarkTest_StoreWrite/FileStore_Write_100-8         	     146	  14532698 ns/op	 6130954 B/op	    8549 allocs/op
BenchmarkTest_StoreWrite/SqliteStore_Write_100-8       	     278	   4623969 ns/op	  210247 B/op	    2714 allocs/op
BenchmarkTest_StoreWrite/FileStore_Write_500-8         	      44	  25448032 ns/op	13313935 B/op	   15779 allocs/op
BenchmarkTest_StoreWrite/SqliteStore_Write_500-8       	     242	   5269740 ns/op	  210193 B/op	    2714 allocs/op
BenchmarkTest_StoreWrite/FileStore_Write_1000-8        	      25	  45887216 ns/op	26595502 B/op	   30004 allocs/op
BenchmarkTest_StoreWrite/SqliteStore_Write_1000-8      	     205	   5795530 ns/op	  210193 B/op	    2715 allocs/op
BenchmarkTest_StoreWrite/FileStore_Write_2000-8        	      12	 100217523 ns/op	50800290 B/op	   58819 allocs/op
BenchmarkTest_StoreWrite/SqliteStore_Write_2000-8      	     178	   6024594 ns/op	  210188 B/op	    2715 allocs/op
BenchmarkTest_StoreRead/FileStore_Read_100-8           	  434073	      2602 ns/op	    3192 B/op	      27 allocs/op
BenchmarkTest_StoreRead/SqliteStore_Read_100-8         	    1483	    694025 ns/op	  118992 B/op	    2113 allocs/op
BenchmarkTest_StoreRead/FileStore_Read_500-8           	  412197	      2588 ns/op	    3192 B/op	      27 allocs/op
BenchmarkTest_StoreRead/SqliteStore_Read_500-8         	    1731	    676320 ns/op	  118894 B/op	    2113 allocs/op
BenchmarkTest_StoreRead/FileStore_Read_1000-8          	  439562	      2681 ns/op	    3192 B/op	      27 allocs/op
BenchmarkTest_StoreRead/SqliteStore_Read_1000-8        	    1508	    708517 ns/op	  118790 B/op	    2113 allocs/op

We can see that the write speed of the relational approach is better compared with the result shown in #1030.
Unfortunately, the reading speed is worse but this might be due to the amount of copying I have to do when loading an account. This can be improved.

ERD diagram:

image

Issue ticket number and link

Checklist

  • Is it a bug fix
  • Is a typo/documentation fix
  • Is a feature enhancement
  • It is a refactor
  • Created tests that fail without the change (if possible)
  • Extended the README / documentation, if necessary
**Original Pull Request:** https://github.com/netbirdio/netbird/pull/1065 **State:** closed **Merged:** Yes --- ## Describe your changes This approach involves loading associated tables into slices and then converting them into maps. This technique is introducing additional fields prefixed with 'G' (shows Gorm relation), allowing for granular resource management without many changes to existing structures. While this approach introduces complexities, it serves the goal of achieving a functional implementation while minimizing disruptions to the current architecture. One significant advantage is the elimination of the complexity associated with the lookup table used in the document-oriented approach. This simplification not only enhances query performance but also contributes to improved write speed, especially as the number of accounts grows. Another key advantage introduced by the new relational approach is the ability to operate on separated data structures, such as accounts, users, and peers, independently. This level of separation empowers more granular and focused updates, ensuring that modifications to one resource do not require loading the entire account. But this may require additional changes in the Store interface and the Account Manager. #### Benchmark Results: ``` $ go test ./management/server -run Benchmark_ -bench=. -benchmem goos: darwin goarch: amd64 pkg: github.com/netbirdio/netbird/management/server cpu: Intel(R) Core(TM) i5-8259U CPU @ 2.30GHz BenchmarkTest_StoreWrite/FileStore_Write_100-8 146 14532698 ns/op 6130954 B/op 8549 allocs/op BenchmarkTest_StoreWrite/SqliteStore_Write_100-8 278 4623969 ns/op 210247 B/op 2714 allocs/op BenchmarkTest_StoreWrite/FileStore_Write_500-8 44 25448032 ns/op 13313935 B/op 15779 allocs/op BenchmarkTest_StoreWrite/SqliteStore_Write_500-8 242 5269740 ns/op 210193 B/op 2714 allocs/op BenchmarkTest_StoreWrite/FileStore_Write_1000-8 25 45887216 ns/op 26595502 B/op 30004 allocs/op BenchmarkTest_StoreWrite/SqliteStore_Write_1000-8 205 5795530 ns/op 210193 B/op 2715 allocs/op BenchmarkTest_StoreWrite/FileStore_Write_2000-8 12 100217523 ns/op 50800290 B/op 58819 allocs/op BenchmarkTest_StoreWrite/SqliteStore_Write_2000-8 178 6024594 ns/op 210188 B/op 2715 allocs/op BenchmarkTest_StoreRead/FileStore_Read_100-8 434073 2602 ns/op 3192 B/op 27 allocs/op BenchmarkTest_StoreRead/SqliteStore_Read_100-8 1483 694025 ns/op 118992 B/op 2113 allocs/op BenchmarkTest_StoreRead/FileStore_Read_500-8 412197 2588 ns/op 3192 B/op 27 allocs/op BenchmarkTest_StoreRead/SqliteStore_Read_500-8 1731 676320 ns/op 118894 B/op 2113 allocs/op BenchmarkTest_StoreRead/FileStore_Read_1000-8 439562 2681 ns/op 3192 B/op 27 allocs/op BenchmarkTest_StoreRead/SqliteStore_Read_1000-8 1508 708517 ns/op 118790 B/op 2113 allocs/op ``` We can see that the write speed of the relational approach is better compared with the result shown in #1030. Unfortunately, the reading speed is worse but this might be due to the amount of copying I have to do when loading an account. This can be improved. #### ERD diagram: ![image](https://github.com/netbirdio/netbird/assets/708165/914e82ee-e7ad-43d6-a204-2575c4e81539) ## Issue ticket number and link ### Checklist - [ ] Is it a bug fix - [ ] Is a typo/documentation fix - [x] Is a feature enhancement - [ ] It is a refactor - [ ] Created tests that fail without the change (if possible) - [ ] Extended the README / documentation, if necessary
saavagebueno added the pull-request label 2025-11-20 08:04:36 -05:00
Sign in to join this conversation.
No Branch/Tag Specified
Branches Tags
Labels
Clear labels
2021 Q4
2022 Q1
2022 Q1
accessibility
acl
agent
agent
Android
Android
api
authentik
automation
azure
battery-usage
bug
cache
client
client-ui
cloud
cloud-only
cloudflare
community
compatibility
config-idp
config-issue
connection
contribution
coturn
cross-vpn
dashboard
data-usage
distribution
dns
docker
documentation
duplicate
enhancement
enhancement
event-stream
feature-request
freebsd
getting-started
go
good first issue
gui
help wanted
home-assistant
idp
inconsistency
integration
integrations
ios
ipv6
jwt
k8s
keycloak
linux
login
macos
management-service
missing-docs
mobile
moved-internal
needs-review
netbird-ui
networking
new-platform
nginx
notification
okta
openwrt
packaging
peer-management
peer-management
peer-management
performance
postgres
posture-checks
psk
pull-request
Mirrored from GitHub Pull Request
 question
refactor
relay
release
rfc
routes
security
security-related
self-hosting
server
signal
sleep-issue
ssh
ssl
status
store
synology
system-compatibility-issue
test-suite
third-party-integration
triage
triage-needed
troubleshooting
UX
waiting-feedback
windows
wontfix
zitadel
No Label pull-request
Milestone
No items
No Milestone
Projects
Clear projects
No project
Assignees
Clear assignees
saavagebueno
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: SVI/netbird#2987
Delete Branch "%!s()"

Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?