Lisk-sdk: Drop PostgreSQL native bindings

[4miners]

Would be nice to drop pg-native, however research should be done how much it will affect performance.

[pcdinh]

What will it solve?

[4miners]

@pcdinh There is no real issue with it, but there are some disadvantages: https://github.com/brianc/node-postgres#native-bindings

[vitaly-t]

@4miners Oliver asked me to look into this.

The link you provided to the disadvantages is no longer good, and the only API link that I can find is this one: https://node-postgres.com/features/native

If you can recover the list of disadvantages, please post them here, and we will start from there :wink:

[nazarhussain]

I don't have exact information about using native bindings in javascript. But from a Ruby background I can say, native bindings are always more proficient. Using native bindings is a good to go approach in Ruby paradigm. Theoretically I can't see any disadvantage even in NodeJS.

[4miners]

@vitaly-t https://github.com/brianc/node-postgres/commit/0c24ff3c12a2e1b2dd6d16a8192c64fd34dcd1e7#diff-04c6e90faac2675aa89e2176d2eec7d8L167

[vitaly-t]

@4miners I am well aware of all the differences between the Native and JavaScript bindings. After all, I was the one who investigated and reported most of them. Here's just one example: https://github.com/brianc/node-postgres/issues/988

But none of those discrepancies affect us. And performance with the Native Bindings is a bit better ;)

[karmacoma]

We will stick with the native bindings until there is stronger evidence dropping them would be beneficial.

[vitaly-t]

@karmacoma Your wish may be coming true 😄

This morning myself and @diego-G were looking at some strange issue within 0.9.12: Error: Unable to set non-blocking to true, which was once reported here: https://github.com/brianc/node-pg-native/issues/34

---

It was following this issue: https://github.com/LiskHQ/lisk/issues/1149

---

From the pg-promise perspective it doesn't make any sense, because the issue is reported to only happen when trying to execute a query before the connection is fully open, which is impossible. BUT, since we are using pg-native, its internal C++ async management makes everything possible.

That's why I suggested Diego first to try and switch off pg-native, which solved the issue in 0.9.12.

I asked him to check that for 1.0.0, and if reproducible - we should definitely then throw away pg-native, which would require only two lines of changes:

• delete this line: https://github.com/LiskHQ/lisk/blob/development/db/index.js#L30
• remove pg-native dependency from package.json.

@diego-G Please follow up here in relation to version 1.0.0

[shuse2]

I think one of the drawback to use pg-native is the build flow.
In docker, You must have postgres client installed in the image same as the core.
Also, to install locally, we need install postgres first before npm install

[karmacoma]

Sounds like we can also simplify/update our build process in @LiskHQ/lisk-build.