Wouldn't using site-to-site VPN be adding a SPOF just as well, namely the VPN server (and, in addition to this, make the VPN server a bottleneck for transfer speed) ?

You can "easily" enough set up two VPN connections on separate pairs of machines and route to a virtual IP on each end and use ucarp or keepalived to have one or the other take over. It's not pretty, but it works.

And the VPN server may very well become a bottleneck for transfer speed at some point, but most of us won't ever need to deal with a level of bandwidth where that's an issue.

This was part of an attempt to improve our Django app, by using PgBouncer to do connection pooling with a Heroku database. Heroku requires SSL for all db connections.

Site-to-site VPN would not have been an option in this instance, and in general I have had poor experience with them - Flaky SPOF, poor throughput, and so on.