Proper Connection Pool Use

Question

I'm confused as to how to properly interact with my Postgres database throughout the typical user experience on my web app. I'm seeking clarification on the most efficient method of communicating with the database, without over-communicating (spamming).

Currently I use org.apache.commons.dbcp.BasicDataSource to connect to my database whenever needed, and I always close the connection when finished. It makes total sense to me when I'm doing a one-time query.

But what if the query can be performed multiple times at the discretion of the user?

Here are 2 specific occurrences in my app:

• If a user types in their credentials and then clicks "Login" on the login page, the database is queried to validate the credentials. What if the user clicks the button 100 times? This means they could potentially spam the button, causing multiple queries to be sent to my database. We could impose a manual limit (like 5 clicks per minute) but where do we draw the line?
• Once logged in, their user profile must be filled and they can 'Save' the changes after any field changes are triggered. So it would be very easy to maliciously spam the save button after a new character is typed.

I understand a new connection is not created each time getConnection() is called but I don't understand if the inner mechanics of BasicDataSource handle this potential spamming.

Once a user clicks 'Save' it's important that the changes are accessible to all other users. For example, User A could click 'Make Visible to Other Users' and then click 'Save'. User B should now be able to find User A in our app.

Do you recommend I use Connection Pool, Hibernate, Redis, cache2K or some other tool/framework? Or is it sufficient to query the database each and every time since pool will optimize it on the back-end?

Thanks so much. I'm using Java 8 + Vaadin 8.

Answer 1

If a user types in their credentials and then clicks "Login" on the login page, the database is queried to validate the credentials. What if the user clicks the button 100 times?

There are a number of ways to address this. I wonder though if this is something you should really be concerned about. 100 requests isn't a whole lot in the scheme of things. Ignoring connections for a moment, if you run the same query 100 times in a row, it's typically a lot less work for the DB than running 100 queries for different data due to caching of pages, paths, etc. optimizations.

Once logged in, their user profile must be filled and they can 'Save' the changes after any field changes are triggered. So it would be very easy to maliciously spam the save button after a new character is typed

Saves are a little more costly but again, is this really going to matter? I'd be more concerned about possible inconsistencies in state than the stress on the DB. Unless you are running the DB on some really constrained infrastructure, I doubt you'll notice one user doing this.

For both of these, an automated process overwhelming the system with many thousands of requests per second, that could be something you might want to worry about. A user mashing a mouse is moving in extreme slow motion for systems that execute billions of instructions per second.

Do you recommend I use Connection Pool, Hibernate, Redis, cache2K or some other tool/framework? Or is it sufficient to query the database each and every time since pool will optimize it on the back-end?

I'm a little confused by your mention of connection pool here since you seem to imply elsewhere in your question that you are already using a pool. That would be the only reason that you wouldn't create a new connection on each request even though you close the connection after every request.

When you call close on the datasource using pooling, it's generally taking the connection you were using and returning it to the pool. This prevents reconnecting on every request. This is important because for most sensibly written queries, the time it takes to establish a connections is far more expensive than executing the query and will take a lot longer. If you aren't pooling, then the user clicking the mouse really fast could cause issues.

As far as caching and the other things you mention, it seems like overkill for what you are talking about. The first thing I would add is a simple in-memory cache. If that's not sufficient, you add in a distributed cache. But I wouldn't do any of that unless you know specifically what problems those solve for you. Otherwise you are probably just creating new issues to deal with.

Answer 2

I don't work in Java much and am not familiar with the tooling available, but in most modern platforms (which does include Java), the data access layer/object will have connection pooling, which handles this for you.

While writing code, the general approach is indeed to open when you need, query/execute, then close. However, those commands are passed to the connection manager in some way or another, which is generally responsible for deciding the details of whether it will really close that connection or not. The pool manager may well keep the connection open and hold it until another request is made, thus re-using the open connection and avoiding the overhead of re-opening it. This is an abstraction detail that you don't generally have to be concerned about (although, as ever, it's good to know how the abstraction works, and why, because there may be corner cases where you want to bypass the abstraction, but it's rare in the case of connection pooling, for most applications anyway).

And finally, for concerns of "spamming" the database, this is usually a non-issue as well. Databases often handle thousands of requests per second. Anything a user can manually throw at it is likely to be churned up without any issue. A related concept would be a DoS/DDoS attack: where someone writes code that will try to flood your system with requests and thus block others from using it. However, this is a much different beast, and tends to affect the web hosting server much sooner than it will cause problems for the database.

All in all, it's good to be concerned about such things, but experience will eventually lead you to the realization that there's much more pertinent things to be concerned with :)

Answer 3

But what if the query can be performed multiple times at the discretion of the user?

You can start by debouncing the submit function on your client, don't allow it to resend another request until a certain amount of time elapsed or at least just disable the button until after you get a response (or timeout). Then do the same at the server side.

Once a user clicks 'Save' it's important that the changes are accessible to all other users. For example, User A could click 'Make Visible to Other Users' and then click 'Save'. User B should now be able to find User A in our app.

Any change would become apparent once you run your query and make a commit

Do you recommend I use Connection Pool

Normally you would use a connection pool in a situation where you would expect multiple transactions occurring concurrently (transaction is a blocking operation) and you don't want everyone waiting on that one connection object (starvation).