Check out my udemy Introduction to Database Engineering course database.husseinnasser.com Learn the fundamentals of database systems to understand and build performant backend apps

If you show how to do it correctly, you should have wrap all code in try/finally. Connection should be released in any situation. Even when you do simple select, your database server may fail to process it, so it results in thrown exception and unreleased connection.

@hnasr

2 жыл бұрын

That is correct, good catch

@ayush.kumar.13907

2 жыл бұрын

@@hnasr good try-catch

@alb12345672

2 жыл бұрын

what if he did pool.connect().then((client)=>{......})

Hey Hussain loving the new art style for thumbnails

@marslogics

2 жыл бұрын

Lovely indeed

Your videos are getting so much better. I honestly didn't like those long long videos. Really appreciate the move to short, concise video with high quality audio

Very nice to go hands on and explain the intricacies instead of me bashing my head reading textbooks and documentations for hours only to be explained in 12 minutes.

Love this form of short videos of real world problems.

You’re the man. I was struggling with hanging queries for a week

Hey Hussein, i like the way you explain things. Next video, could you pls do step by step sizing for a web server hardware, and database. And a bit explanation on things to understand such as latency, troughput, bandwidth, TPS, etc

I just found your channel, subbed and rang the bell. Thanks for your amazing content!! I’m an aerospace/defense SWE (C, some C++) that is just starting their web dev (love backend) journey. Thinking of switching to it in the future. You and Amigoscode are my new favorite channels 😎. Cheers!!

@akramdahmani7214

2 жыл бұрын

you're following the GOATs good luck with your web dev journey

@nontraditionaltech2073

2 жыл бұрын

@@akramdahmani7214 thank you! GOATs for sure, best backend content on KZread.

Hussain , your videos are just fantastic ! Learn something cool in each and every video. Please keep the videos coming 👍🏼

in python we have context managers for that. like this async with connection as pool.acquire(): await connection.execute(...) and cuz of that "with ...", it automatically releases the connection as u r done w/ it 😇

source code updated with some important comments (thank you) The first is a typo I made while testing this new code I used Pool.query where it should have been client.query . Effectively what it was doing is getting a client and not using it at all and using a connection from the pool instead. The second one which is a nice guard With finally/try catch so it releases the client even in case of an error which I wasn’t accounting for. What a fantastic community I have over here. github.com/hnasr/javascript_playground/tree/master/database-leak

Get my Introduction to Database Engineering udemy course database.husseinnasser.com

Learning something new every day from these videos

Hi Hussain, I bought your course on Udemy to support you. As always, your content is simple and well explained, and pretty helpful.

@hnasr

2 жыл бұрын

Thank you ❤️

« You want things to be consistent ». 😅😅 Yes you do. Inconsistent problems are nightmares . Thanks for the explanation

We love you hussien!!

@hnasr

2 жыл бұрын

❤️

What if I use pool.query(begin) and write each line with pool.query. What happens in that case?

Between client.quety of begin and commit, you have used pool.query to actually fetch the data, so that will not gonna use your transaction that you created using client instance. The query in between should be executed using client.query instead pool.query to utilize the transaction your created.

@hnasr

2 жыл бұрын

Urgg good catch, must have been during preparation of the video forgot to change it back. It should be client.query as you mentioned. It still produces the same effect, but the query will not executed in the transaction that I prepared of course. (Dangerous )

@marslogics

2 жыл бұрын

@@hnasr Thank you for providing all the awesome details, really learning a lot from you. Lots of love from Pakistan.

isnt there a configuration at the server that you can limit from there your connections?

Great video, thanks Hussein

Very nice! I encountered a similar situation with Mlflow + Hyperopt (Data Science). Mlflow does not release connection automatically so after 100 or so model training, the optimization got stuck.

Hi Hussain, Thanks for awesome videos. How do we pick correct number for `max` connection pool in real world application? how do we scale it dynamically?

Did this bug reach production?

Depending on how your real code looked like, you have another bug lurking: You are mixing and matching client.query and pool.query. pool.query is a shorthand helper for getting a connection, running a client and returning the conneciton to the pool. While a client maintains a single connection until released manually. So in your example code you are creating a transaction via client.query("begin"). Than you ran the SELECT-Statement on a totally different connection via pool.query(). And then you commit an empty transaction on the first connection! That's also the reason why your backend is already stuck at request nr. 4 while you allow 4 connections (which would only block you at request 5). But on every request to try to get 2 connections and only return 1. Therefore on request 3 you try to get connection nr. 4 (working) and nr. 5 (blocking) and it's over.

@hnasr

2 жыл бұрын

That is correct thanks! Its a typo I made while building this repro and testing different cases, I already fixed it as someone pointed it out earlier

I have finished this beginner playlist. This was the last video. Which playlist should I start watching next?

This problem can also happen with atomic database updates done in a transaction ... The atomic update never completes ... the connection is never released back to the pool. This continues until all connections in the pool are all used. Now, you can't collect another connection from the pool. Server restart necessary. You need to run select * from pg_stat_activity where datname = 'db_name'; to see the state of connections to postgres itself.

never thought that you would do this silly mistake 😅

Nice video as usual, what is your mac specs if do not mind to tell us 🙏🏻

Hello Hussein, do you have tutorial on how to setup psql on production? ex. tuning up and security. Thank you...

@HassanSelim0

2 жыл бұрын

Personally I always prefer to use managed database, whatever my cloud provider provides. Because I don't have the capacity to monitor the database to make sure things are fine and up to date all the time.

This was awesome. One question though. If max connections is 4, why does the failure happen after the 3rd refresh instead of the 4th?

@senpaker

2 жыл бұрын

there were 4 request made, 1. when he first open the page 2. 3 other request he made by refreshing

Hey what do you use for screen recording? How do you zoom in to your mouse like that?

@lakhveerchahal

2 жыл бұрын

It can be done in editing probably.

@PhongGT

2 жыл бұрын

@@lakhveerchahal I guess you can do it with tracking but I think there are screen recording apps that zoom in for you

Hi, I have a weird question. Assuming you have the code exactly as your code, but on the select query, you get an error, that then gets sent straight to catch instead of finishing with client.release, would it still hang the client, and if yes, what's the best way to release the connection even in the case of an error?

@redouane5626

2 жыл бұрын

call release in `finally` block

@astral_gaming_0956

2 жыл бұрын

@@redouane5626 But wouldn't I need to declare client outside the try/catch to be able to release it in either finally or catch? Or is holstering affecting try/catch blocks as well (as in once const client got called, it can be referenced in .catch as well) ?

@alexander_farkas

2 жыл бұрын

@@astral_gaming_0956 you should define variable outside of try-block. ''' let client; try { client = await connect() ... } finally { client?.release() }

@astral_gaming_0956

2 жыл бұрын

@@alexander_farkas that makes sense, thanks for the explanation. I had a bit of a brainlag and thought that it might become a weird security vulnerability if you could crash the backend before the connection got released, thus after doing it X (max) number of times, no other user could connect to the database.

You are a legend

I mean, you shouldn't be creating db connection inside your handler. You should open one or few and pass service in that use these connections into handlers using DI.

Hi, Hussein I am a bootcamp level programmer (javascript)trying to understand stuff I read an article about golang and they were saying that shifting from node to golang reduced their server cost by xyz% which was on the cloud obviously . I just know that golang is a low level language so therefore it can handle more requests but I am just quercous about how does it reduces the cost ? tried thinking about it but I have gaps In my knowledge I don't know about cloud could you please take some time to help me understand this

@vamshikrishnareddy76

Жыл бұрын

lets say we built the backend in nodejs and deployed it to cloud technically we are renting the servers and then what ? will we choose the number of servers that we want to rent? does the backend language matter to choose the number of servers we want to rent?

Hey Hussein which playlist will this video lie in

@hnasr

2 жыл бұрын

backend engineering beginners and Postgres and database Engineering

anybody has any idea how hikari handles this ?

So wanted to understand this why don't you have the connection assigned to a global variable and then use it in each api reuqest?

@hnasr

2 жыл бұрын

This is a bad practice, when two different HTTP requests come and you only have 1 shared connection, they will served on the same connection as two different queries. If query 2 finished before query 1 than the result of query 2 might be returned to the first HTTP request which is a huge security concern. Unless you can guarantee the database supports pipelining (requests/responses are returned in the order they were received. Pooling is used to circumvent this by marking a connection is busy when a query is executing on it. Plus you can scale.

@9709923699

2 жыл бұрын

@@hnasr oh wow thanks a lot for the reply never knew about this. So wanted to get suggestion how do I learn all these things? Does your database engineering course will help in this?

Wait a second Hussain - do we really need to begin a transaction/commit when running a SELECT?

@hnasr

2 жыл бұрын

No you don’t need to, the database will start one for you always. And yes you can use transactions that purely read data to ensure consistency and isolation. I talk about that on my course

Awesome

I have mongo db pool connection leak issue

@clinton11994

2 жыл бұрын

I resolved it by updating nodejs and mongodb lib to latest version.

Hussein why don't you use ORMs ?

@hnasr

2 жыл бұрын

ORM does many things behind the scene and result in many leaky abstractions. SQL is leaky as is, add ORM on top and you have a disaster. I like raw SQL where I have more control

Well explained! Two questions: 1. Does every user has up to 4 connections or are those total connections which would be ridiculous. 2. Is release() the same as disconnect()? Or does the pool belong to a connection until you disconnect that's why you would need release?

@hnasr

2 жыл бұрын

1) there isn’t a concept of users in HTTP, every request will consume one connection while it is executing. One user can make those 4 requests 2) no, release returns the connection to the pool so it can be reused. Disconnect destroys it so one else can use it. Good question

@supportic

2 жыл бұрын

​@@hnasr Ok I got the first point, thanks. So is it bad to terminate every DB connection when the transaction is done? Say we have a user display a table with data from the DB. The initial connection was made with a query for the data which means 1 open connection and 1 of 4 pool connections being used which get released after the transaction. Do I keep the DB connection open or do I close it? Maybe the users wants to query more information.

@lakhveerchahal

2 жыл бұрын

@@supportic I think stating max 4 connections means that you can have at max 4 connections in PARALLEL, if there's a 5th parallel connection made it'll go in pending and will be served only after one of the previous 4 connections are done and released. Also, pool is created only once when the server starts. It shouldn't be terminated or recreated again and again otherwise it defeats the purpose of having a pool.

Follow up questions... 1) So obviously pool works for all connection spawning from the same machine. Two seperate machines can't share a pool, correct? 2) I read that pg db by default can accept 100 connections, but in pool you're hard coding to a number, i.e. 4, is there a way to make it auto scale and not hardcode max. 3) when you define max does pool automatically opens that much new connections? or only opens a new connection if all existing connections are under use and new connection request comes in? 4) how would you modify your code so even if db/pool/etc is hung up you can return meaningful http error 5) you said pool queries aren't atomic as pool.query commits right away... so what's the point of lines pool.query('begin')....pool.query('commit') doesn't that make it an atomic transaction?

@jackedelic9188

2 жыл бұрын

for pt 5, the second pool.query could be a different client. For the rest im also curious to know 😃

@troooooper100

2 жыл бұрын

@@jackedelic9188 Yea, but I'm wondering if I define pool max to be 1000, will it take memory from the get go, or expand only when necessary. And what can I do to make it expand until it hits bottleneck from db. i.e. DB says connection failed. Ideally pool should not need max, it should expand as much the upstream allow.

The pg library should do it automatically with commit, we can ask for this feature

@hnasr

2 жыл бұрын

Nope, you could have multiple transactions in the same connection.

Hey Hussein, can you also upload your podcast episodes on Amazon music 😅 Would love to subscribe your channel there 🙌

@hnasr

2 жыл бұрын

can I really do that? 😅 didn’t know amazon music has podcast

@lakhveerchahal

2 жыл бұрын

They just recently added the podcast feature, earlier it wasn't there.

Wait your “back end” is “leaking”? lol jk

Twitch engineers are taking notes.

Black belt.

What can this be an example of? Starvation? You managed to starve a thread in a single threaded language! JK. Nice video.

@rxnniiee

2 жыл бұрын

the rest of the application would still work, it only hangs at that single request endpoint since it's always going to be waiting on the exhausted connection pool

so RAII is a neat concept in c++ which ties the lifetime of an object to the scope. using that pattern in cpp to acquire connections would lead to automatic release of connections while exiting. but, that is not available in garbage collected languages.

@he2he

2 жыл бұрын

It is possible in garbage collected languages. For example in c# you would write "using var client = ...". The using would cause the method Dispose to be called at end of the scope.

100M