Is it constraint on database client-server deployment (Firebird, Postgresql, Oracle) that it should not be made over WiFi (due to unreliability)?

Question

Database client-server connectivity can sometimes happen. I mean: there is client side dll that sits on the client computer (device, it can be mobile phone as well). For the Windows OS based clients:

• fbclient.dll, gds32.dll - Firebird
• libpq.dll - Postgresql
• oci.dll - Oracle

(of course, these dll can have dependencies). And the usual scenarios are: the application (it may be fat client of the client-server application) loads DB client library (fbclient.dll, oci.dll, etc.) in its process space and calls dll commands. dll - in turn - establishes connection with the server process (e.g. Firebird server process that runs on 3050 port on the server machine, Postgresql server process that runs no 5432 port on the server machine, Oracle TNS Listener on 1521 port) and keeps this connection open for the duration of the db client-server session.

E.g. user can authenticat and start connection and issue multiple SQL commands over this connection.

I understand that today the client-server DB communication usually hapens in the wired environment exclusively. E.g. either DB server and Web/Application server sit on the same machine or either they are in the different machines that are connected by very fast and reliable wired network.

It was differently in the early days of IT. That was the time when client-server architecture was quite common - client machines had fat clients of the application and they used database dlls on their machine and these dlls communicated with the DB services on the server machine.

Such applications are still in use and maybe there are some other scenarios where client-server DB connectivity can happen even today.

So - all that was background. My question is - can database client-server connectivity be deployed over WiFi networks (or over networks which has WiFi link, e.g. employee laptop can connect to the WiFi Access Point then then this access point has wired connection the database server - this can be pretty common setting both in the work office and home office situations) or is it common DBA understanding that no client-server connectivity can involve WiFi link.

My collegues adheres to the quite strict rules that there can be no deployment of our solutions which involve client-server DB connectivity over WiFi. I have seen, that such deploymen is possible though. I can not believe that such constraint can really be in force? Are WiFi networs really so unreliable although they are quite popular?

Answer 1

It's a question of choosing the Right Tool for the Right Job.

A database connection is a very simple beast.
The client opens the connection (usually through TCP/IP sockets) to the database and, commonly, holds that connection throughout its execution. This is because opening a new connection is generally "expensive" and clients don't generally run Connection Pooling.
The client uses that connection, pushing SQL into it and receiving data back.
Finally, the client closes the connection, usually when the Application is shut down.

The Trouble starts if anything happens to that connection.

The client now has a broken connection that it can no longer use.
The database has a "hanging" connection - it has no idea what's happened to the client - so it will maintain that connection for "some time" after which it will forcibly close the connection and roll back any work that had been done (and not committed).

Sure, the client can reconnect and it will get a fresh connection but it will not be able to see any of the [uncommitted] changes that it made on the previous connection, because those are still "hidden away" in that "hanging" connection. Also, any changes it tries to make might be "blocked" by that "hanging" connection as well.

All in all, not a good User Experience.

Contrast that with a Web Application.
The web browser makes an HTTP request to a web server.
That web server connects to the database (over that nice, reliable, wired network and using efficient things like Connection Pooling), pulls the data and closes the connection. It then sends the HTTP response back to the client's web browser.
The web browser and server are much, much better suited to handling this intermittent connection scenario (they're actually built for it, with HTTP itself being completely stateless).

Answer 2

From Radio Network Engineering perspective , you could have WiFi connections with pretty good reliability in radio. Usually, the WiFi Network try to get the highest possible modulation scheme, which allow the user to get maximum throughput, based on radio conditions. Also, some wired connections finally goes through Microwaves or other wireless systems or may go through internet provider congested trunks that loss packets in peak hours.

Now the behavior of TCP/IP protocol used by SQL apps on layer 3, auto regulate the quantity of packets without acknowledge, in this way it reach the maximum packet rate according to the packet loss and delay seen.

Application layer just see delay in transfer and packet rate coming. The impact of delay and low throughput for applications in general are higher session times per user and more users queued simultaneously, application timeouts and so on. Keeping in mind that databases reach maximum users easier than web services, maybe convenient to have intermediate backend that free up quickly the Disk database resources which are the bottleneck of many applications.

In conclusion valuable and limited resources in databases as I/O disk capacity, max number of simultaneous sessions, tables locked by reading among others, are impacted by low performance users. Backend systems can buffer the sessions of users with delay in transfer.

However, if the application is simple with few users the effort to make web servers and apis could be greater than the benefit.