Skip to content
This repository has been archived by the owner on Jun 4, 2024. It is now read-only.

Need an option to disable server-side prepares #86

Open
vitalif opened this issue Nov 17, 2019 · 2 comments
Open

Need an option to disable server-side prepares #86

vitalif opened this issue Nov 17, 2019 · 2 comments

Comments

@vitalif
Copy link

vitalif commented Nov 17, 2019

Hi!
Recently, when testing an application that does huge INSERTs (~60 mb in a single query) I discovered that these queries were much slower with nodejs pg-native driver than with just plain psql < file.sql. By "much slower" I mean 7-11 seconds with psql and 60-90 seconds with nodejs. It doesn't reproduce with all server configurations - I assume it reproduces better when the server has limited RAM and slow disks (HDDs).
After some digging I finally captured network traffic between the client and postgresql server and I discovered that nodejs and psql really send queries in different ways!

nodejs pg-native:
photo_2019-11-18_01-14-30

psql:
photo_2019-11-18_01-14-46

This test was done with pg-native, but the same also happens with pg.
'Q' is the "query" message and 'P' is the "parse" message. This means that nodejs seems to send a prepare+execute pair instead of just query.

So I obviously want to disable this behaviour - can you please add an option for it?
(PDO in PHP has one, it's called PDO::ATTR_EMULATE_PREPARES)

UPD: Rechecked with pg. It's OK, it was slow in my case because I actually had some bind variables there. With pg-native, however, the case is valid - prepare emerges out of nowhere :).

@charmander
Copy link
Collaborator

You should use UNNEST or COPY instead of building a dynamic query.

@vitalif
Copy link
Author

vitalif commented Nov 18, 2019

I'm fine with INSERT. I just need the correct behavior from the driver.

Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.
Labels
None yet
Projects
None yet
Development

No branches or pull requests

2 participants