Updating an extension/device generates 6 psql UPDATE queries per device.

M

mdoupe

Member
Mar 21, 2023
34
1
8
44
Hi all,

Whenever I click save on a device or an extension (even if the extension doesn't have a device associated) FusionPBX generates 6 update queries per device in the system. For small systems this isn't really an issue, but I have close to 2000 devices, so every time I click save, it's generating 12,000 update queries. Additionally, those updates are overwriting the update time, etc. for every single device whenever I click save. I'm on version 5.4.

I deleted every single extension and device out of my system, created two test extensions, and assigned them to two devices. Below is my PSQL log from that test:

Code: 
2025-08-08 15:01:25.996 MDT [12955] fusionpbx@fusionpbx LOG:  execute <unnamed>: update v_devices set device_provisioned_date = $1, device_provisioned_method = $2, device_provisioned_ip = $3, device_provisioned_agent = $4 where domain_uuid = $5 and device_address = $6
2025-08-08 15:01:25.996 MDT [12955] fusionpbx@fusionpbx DETAIL:  parameters: $1 = '2025-08-08 15:01:25.996154-06', $2 = 'https', $3 = '<my local ip>', $4 = '<my browser ua>', $5 = '6481d110-361b-4bf0-8fd1-075cb3fca32d', $6 = '000000000001'
2025-08-08 15:01:26.001 MDT [12955] fusionpbx@fusionpbx LOG:  execute <unnamed>: update v_devices set device_provisioned_date = $1, device_provisioned_method = $2, device_provisioned_ip = $3, device_provisioned_agent = $4 where domain_uuid = $5 and device_address = $6
2025-08-08 15:01:26.001 MDT [12955] fusionpbx@fusionpbx DETAIL:  parameters: $1 = '2025-08-08 15:01:26.001833-06', $2 = 'https', $3 = '<my local ip>', $4 = '<my browser ua>', $5 = '6481d110-361b-4bf0-8fd1-075cb3fca32d', $6 = '000000000001'
2025-08-08 15:01:26.004 MDT [12955] fusionpbx@fusionpbx LOG:  execute <unnamed>: update v_devices set device_provisioned_date = $1, device_provisioned_method = $2, device_provisioned_ip = $3, device_provisioned_agent = $4 where domain_uuid = $5 and device_address = $6
2025-08-08 15:01:26.004 MDT [12955] fusionpbx@fusionpbx DETAIL:  parameters: $1 = '2025-08-08 15:01:26.004831-06', $2 = 'https', $3 = '<my local ip>', $4 = '<my browser ua>', $5 = '6481d110-361b-4bf0-8fd1-075cb3fca32d', $6 = '000000000001'
2025-08-08 15:01:26.008 MDT [12955] fusionpbx@fusionpbx LOG:  execute <unnamed>: update v_devices set device_provisioned_date = $1, device_provisioned_method = $2, device_provisioned_ip = $3, device_provisioned_agent = $4 where domain_uuid = $5 and device_address = $6
2025-08-08 15:01:26.008 MDT [12955] fusionpbx@fusionpbx DETAIL:  parameters: $1 = '2025-08-08 15:01:26.008023-06', $2 = 'https', $3 = '<my local ip>', $4 = '<my browser ua>', $5 = '6481d110-361b-4bf0-8fd1-075cb3fca32d', $6 = '000000000001'
2025-08-08 15:01:26.010 MDT [12955] fusionpbx@fusionpbx LOG:  execute <unnamed>: update v_devices set device_provisioned_date = $1, device_provisioned_method = $2, device_provisioned_ip = $3, device_provisioned_agent = $4 where domain_uuid = $5 and device_address = $6
2025-08-08 15:01:26.010 MDT [12955] fusionpbx@fusionpbx DETAIL:  parameters: $1 = '2025-08-08 15:01:26.010857-06', $2 = 'https', $3 = '<my local ip>', $4 = '<my browser ua>', $5 = '6481d110-361b-4bf0-8fd1-075cb3fca32d', $6 = '000000000001'
2025-08-08 15:01:26.013 MDT [12955] fusionpbx@fusionpbx LOG:  execute <unnamed>: update v_devices set device_provisioned_date = $1, device_provisioned_method = $2, device_provisioned_ip = $3, device_provisioned_agent = $4 where domain_uuid = $5 and device_address = $6
2025-08-08 15:01:26.013 MDT [12955] fusionpbx@fusionpbx DETAIL:  parameters: $1 = '2025-08-08 15:01:26.013589-06', $2 = 'https', $3 = '<my local ip>', $4 = '<my browser ua>', $5 = '6481d110-361b-4bf0-8fd1-075cb3fca32d', $6 = '000000000001'
2025-08-08 15:01:26.016 MDT [12955] fusionpbx@fusionpbx LOG:  execute <unnamed>: update v_devices set device_provisioned_date = $1, device_provisioned_method = $2, device_provisioned_ip = $3, device_provisioned_agent = $4 where domain_uuid = $5 and device_address = $6
2025-08-08 15:01:26.016 MDT [12955] fusionpbx@fusionpbx DETAIL:  parameters: $1 = '2025-08-08 15:01:26.016789-06', $2 = 'https', $3 = '<my local ip>', $4 = '<my browser ua>', $5 = '6481d110-361b-4bf0-8fd1-075cb3fca32d', $6 = '000000000002'
2025-08-08 15:01:26.020 MDT [12955] fusionpbx@fusionpbx LOG:  execute <unnamed>: update v_devices set device_provisioned_date = $1, device_provisioned_method = $2, device_provisioned_ip = $3, device_provisioned_agent = $4 where domain_uuid = $5 and device_address = $6
2025-08-08 15:01:26.020 MDT [12955] fusionpbx@fusionpbx DETAIL:  parameters: $1 = '2025-08-08 15:01:26.019941-06', $2 = 'https', $3 = '<my local ip>', $4 = '<my browser ua>', $5 = '6481d110-361b-4bf0-8fd1-075cb3fca32d', $6 = '000000000002'
2025-08-08 15:01:26.022 MDT [12955] fusionpbx@fusionpbx LOG:  execute <unnamed>: update v_devices set device_provisioned_date = $1, device_provisioned_method = $2, device_provisioned_ip = $3, device_provisioned_agent = $4 where domain_uuid = $5 and device_address = $6
2025-08-08 15:01:26.022 MDT [12955] fusionpbx@fusionpbx DETAIL:  parameters: $1 = '2025-08-08 15:01:26.022862-06', $2 = 'https', $3 = '<my local ip>', $4 = '<my browser ua>', $5 = '6481d110-361b-4bf0-8fd1-075cb3fca32d', $6 = '000000000002'
2025-08-08 15:01:26.025 MDT [12955] fusionpbx@fusionpbx LOG:  execute <unnamed>: update v_devices set device_provisioned_date = $1, device_provisioned_method = $2, device_provisioned_ip = $3, device_provisioned_agent = $4 where domain_uuid = $5 and device_address = $6
2025-08-08 15:01:26.025 MDT [12955] fusionpbx@fusionpbx DETAIL:  parameters: $1 = '2025-08-08 15:01:26.025683-06', $2 = 'https', $3 = '<my local ip>', $4 = '<my browser ua>', $5 = '6481d110-361b-4bf0-8fd1-075cb3fca32d', $6 = '000000000002'
2025-08-08 15:01:26.028 MDT [12955] fusionpbx@fusionpbx LOG:  execute <unnamed>: update v_devices set device_provisioned_date = $1, device_provisioned_method = $2, device_provisioned_ip = $3, device_provisioned_agent = $4 where domain_uuid = $5 and device_address = $6
2025-08-08 15:01:26.028 MDT [12955] fusionpbx@fusionpbx DETAIL:  parameters: $1 = '2025-08-08 15:01:26.028627-06', $2 = 'https', $3 = '<my local ip>', $4 = '<my browser ua>', $5 = '6481d110-361b-4bf0-8fd1-075cb3fca32d', $6 = '000000000002'
2025-08-08 15:01:26.031 MDT [12955] fusionpbx@fusionpbx LOG:  execute <unnamed>: update v_devices set device_provisioned_date = $1, device_provisioned_method = $2, device_provisioned_ip = $3, device_provisioned_agent = $4 where domain_uuid = $5 and device_address = $6
2025-08-08 15:01:26.031 MDT [12955] fusionpbx@fusionpbx DETAIL:  parameters: $1 = '2025-08-08 15:01:26.031511-06', $2 = 'https', $3 = '<my local ip>', $4 = '<my browser ua>', $5 = '6481d110-361b-4bf0-8fd1-075cb3fca32d', $6 = '000000000002'
 
I’ve run into exactly the same issue before.
In my case, the constant extra queries — especially in provisioning and device update logic —and lack of proper queues for backend processes were one of several bottlenecks I found in FusionPBX. Many parts of the codebase trigger unnecessary updates for every device in the domain, even if nothing has changed, and the lack of eager loading can create big N+1 query problems. On large systems, that quickly becomes thousands of queries on a single save.

Dealing with these inefficiencies (and a few other quirks like loading thousands of unused variables into memory) is actually what pushed me to create a more optimized fork of FusionPBX. Since developers of FusionPBX didn't want to collaborate on adding these improvements into their system, that fork eventually became FS PBX, which I later released as open source so others could benefit from the performance improvements I’d made. We use it internally for our VoIP business in the USA.

If you’d like to stick with FusionPBX, I’d suggest bringing this up through their proper support channels — the developers there might be able to help you, but a lot of the system will have to be updated.
 
I was unable to reproduce this. The steps weren't detailed, so here is my attempt to reproduce this.
  • Enable logging all SQL Queries on a test system.
  • From an empty domain.
  • Created two extensions
  • Created two devices
  • Assigned both devices to both extensions
  • Extension Edit for a specific extension
  • Press the Save button
UPDATE v_extensions
UPDATE v_voicemails
INSERT into v_database_transactions
  • Went to Device Edit on one of the devices and pressed the SAVE button.
UPDATE v_devices
UPDATE v_device_lines
UPDATE v_device_lines
INSERT into v_database_transactions

The reason there are two updates for device_lines is that two extensions are saved to this device.
  • I didn't change any values.
  • I didn't add any device keys or device settings.
@mdoupe I wasn't able to reproduce what you reported. Am I missing any steps to reproduce this?

[Update] An efficiency improvement has been completed to SAVE only rows of data that have been modified.
  • Available now on the Master Branch today.
  • Will do more extensive testing and then will backport it to 5.4
  • Nice to finish something that was on my to-do list


@pbxgeek forked FusionPBX at version 5.1. He is responding to messages on this forum for advertising.

Here are the pull requests for nemerald-voip @pbxgeek
https://github.com/fusionpbx/fusionpbx/pulls?q=author:nemerald-voip+

Notice that the pull requests were merged.
 
Last edited:
I'm not sure what other information is needed to troubleshoot this. When I click save on one of my two test extensions, the regular queries (ie. updating the extension, device, voicemail) happen. That's not a problem. But then it seems to trigger a provisioning update (6 times?) on every single device in the domain.

First it runs this 6 times on the first extension/device:

Code: 
update v_devices set
device_provisioned_date = $1,
device_provisioned_method = $2,
device_provisioned_ip = $3,
device_provisioned_agent = $4
where
domain_uuid = $5 and
device_address = $6

parameters:
$1 = '2025-08-11 08:00:42.490835-06',
$2 = 'https',
$3 = '<my workstation ip>',
$4 = '<my user agent>',
$5 = '6481d110-361b-4bf0-8fd1-075cb3fca32d',
$6 = '000000000001'

and then it runs this 6 times on the second extension/device:

Code: 
update v_devices set
device_provisioned_date = $1,
device_provisioned_method = $2,
device_provisioned_ip = $3,
device_provisioned_agent = $4
where
domain_uuid = $5 and
device_address = $6

parameters:
$1 = '2025-08-11 08:00:42.513234-06',
$2 = 'https',
$3 = '<my workstation ip>',
$4 = '<my user agent>',
$5 = '6481d110-361b-4bf0-8fd1-075cb3fca32d',
$6 = '000000000002'

But I am only updating one extension and one device. So I am unsure why the second device is also being provisioned, and why it's updating device_provisioned_date, device_provisioned_method, device_provisioned_ip, and device_provisioned_agent when I didn't make any change to that device.

I've attached the full db log, although I only have mod logging on, so there are no selects. I can turn that on if it'll help.
 

Attachments

Ok, here's part of the puzzle. The devices I was updating are yealink t46u's. looking in the provisioning editor, that model has 6 files. I set device #2 to a cisco 79x1, which has 3 files, then I was getting 6 updates for device #1(yealink) and 3 updates for device #2(cisco). Then I changed device #1 to a vtech, which has 2 provisioning files. Now when I click save, I get 2 provisioning updates for device #1(vtech) and 3 for device #2(cisco).

So I think it's doing a DB write every time it provisions a file. Streamlining that to be one update per device instead of one update per file would be a good first step.

But I still don't know if there's a way to stop it from reprovisioning every device every time one device/extension gets saved.
 
@markjcrane,
I respect the work you've done, and I'm not going to share any private messages that could cause you embarrassment. You've created some excellent software, though, as with any project, there’s always room for refinement. I believe it could use a professional developer's touch to cover the above-described situations. My background isn't just in development—I'm also a real-world VoIP provider who deals with the practical challenges of the industry every day. I've been actively assisting others on this forum with FusionPBX, FreeSWITCH, and general VoIP issues.

@mdoupe,
I apologize for not being able to offer direct assistance on this particular issue. I only wanted to highlight that the situation you're encountering isn't uncommon within the current codebase. I'm also not fully satisfied with the current provisioning system and am in the process of rewriting it for optimization in my forked version. Mark has dedicated years to developing this provisioning server, and while it works well, there is always room for improvement. Hopefully, he'll be able to assist you with your concerns.
 
The FusionPBX support and developer team work with many clients who are primarily companies that do VoIP hosting. Our clients guide or development through their requests. FusionPBX is not a hosting provider, and for this reason we don't have a conflict of interest with our clients.

FusionPBX has multiple professional senior-level developers. FusionPBX code and features have always been evolving and will continue to do so. Our clients may encourage different things. FusionPBX now has OpenID support, Integration with RingoTel, Web Sockets, and more.

FusionPBX follows the KISS principle. This means we work to limit our dependencies and points of failure. This means the FusionPBX install is faster and has fewer points of failure. This image shows the FusionPBX design philosophy that we are striving to follow.

Space X Raptor iterations.jpeg

@mdoupe Phone provisioning. It won't take much effort to solve the issue you reported. You found exactly what is happening the phone is requesting each provisioning file and there is an UPDATE query for each one. I have a solution in my mind and I'll implement it this week.
 
@markjcrane I’d be interested to see if any examples exist in FusionPBX where eager loading is implemented to mitigate the N+1 query issue. If your senior development team can share such an example, I’d be glad to review it. Without that, it seems likely that the challenges mdoupe is encountering may be related to database handling optimization
 
You must log in or register to reply here.
Top Bottom