What is happening behind the scenes after we configured database credentials in database.yml
• Conduct the three-way handshake to establish a TCP connection to the server.
• Exchange preferences and requirements with the database software to establish the session parameters.
• Perform database authentication checks to establish the client's identity.
• PostgreSQL supports several authentication methods, such as passwords (plaintext or MD5),GSSAPI, SSPI, and more.
• The client (Rails, via ActiveRecord) can now send commands to the server. Commands are typically SQL statements. These statements are sent as simple text strings in the 'Query' message format.
How Number of connection affects the database server
• When the number of connections or pools to a server increases, CPU usage will rise. This increased demand for memory and CPU resources can affect other operations, such as transaction speeds. Managing many connections can also reduce the effectiveness of the database and decreasing overall system performance.
What will happen if we give more connections then we configured in PSQL?
• When that limit of a db is reached, additional connection requests are rejected.
How connections b/w rails and PSQL established and maintained?
• Connection pooling is a technique where database connections are reused for multiple requests instead of being closed after each query. This approach uses a connection pooler, a software that manages the connections between the database and its client applications, optimizing the use of resources and improving performance.
• Establishing the connections is a fairly long operation.
• Connection pooler is sitting b/w client and server. client connects to the connection pooler instead of directly to the database. Req is sent to pooler and the pooler interprets queries returns back the response from DB.
• Once the response is returned it closes the connection. But the thing here is opening and closing connection is not overhead comparatively with client and DB connections.
#orm #activerecord #psql #rails