Database

PreviousElastic Search NextSQL

Last updated 9 months ago

Was this helpful?

Database

Introduction

It is divided into 2 types, sql and no sql

SQL

There is database containing tables which contains numbers of row of record

It contains data schema, all the data in the table must fit the type
It is a data relations (foreign key and primary key)

NoSQL

There is a database containing different collections that contains numbers of document
It is used to store data in json format, so as to increase the flexibility of data format

The data will be duplicated, as there is no linkage between documents
The important data from other collections can be included into nested document, so that no need to look into other documents, to make fetching data faster and more efficient, but you will need to update the related documents manually
For the case that update frequently, it is recommended to store references into documents, and find the related information from other collections , and merge together

Comparison

SQL: used to for table that type is really important but not change and access data frequently, e.g : user data
NoSQL: used for collection that type is not really important but access and change frequently, as its speed is faster than due to no relation between collections, data are usually put together rather than join with different tables. Furthermore, the NOSQL database server can be divided into several partitions, find the value by key-value matching in order to faciltate the horizontal scaling

How does the query work

A SQL statement starts in a client program and gets sent over the network to the database server.
When the database server receives the SQL statement, the relational engine starts processing it. First the command parser checks that the statement is valid. Then it converts the statement into a query tree, which is an internal data structure.
The query optimizer looks at the query tree and figures out the most efficient way to execute the SQL statement, creating an execution plan.
The execution plan gets passed to the query executor, which uses it to coordinate retrieving or changing the data as directed by the SQL statement. The executor interacts with the storage engine to access the data.
The storage engine uses access methods, which are protocols for reading and writing data in the most efficient way for different operations.
For reading data, the buffer manager checks if the required data is cached in memory and fetches it from disk if needed. This speeds up subsequent access.
For writing data with inserts or updates, the transaction manager makes sure the changes happen atomically and maintain the database's integrity.
At the same time, the lock manager applies locks so multiple transactions can happen simultaneously without conflict. This maintains isolation and consistency.

Working together, these components process SQL statements reliably and efficiently in a database management system.

Database Connection Pool

Why need connection pool?

Here is the sequence of steps involved in a typical database connection life cycle
1. Opening a connection to the database using the database driver
3. Reading / writing data over the socket
4. Closing the connection
5. Closing the socket
For simple database operations, these steps are not expensive, but as an application scales up, the performance of the application will suffer as each connection is created and destroyed. One pattern for improving performance is a connection pool, a group of already configured and established network connections between the client and database that can be reused for data operations within an application.

Sizing

If we make the pool too small (i.e. choose too few connections), we’ll introduce latency, as operations have to wait for an available connection to open up before they can execute.
If we choose too many connections, that can also create latency for reasons that relate to how each processor core in a server typically executes threads
It is important to decide the max pool size and the minimum idle pool size based on :
Our application and how it interacts with the database
How often it interacts with the database (i.e. scale)
Our database technology of choice
The hardware specs of our database servers
Network latency

PreviousElastic Search NextSQL

Last updated 9 months ago

Was this helpful?

Introduction

It is divided into 2 types, sql and no sql

SQL

There is database containing tables which contains numbers of row of record

It contains data schema, all the data in the table must fit the type
It is a data relations (foreign key and primary key)

NoSQL

There is a database containing different collections that contains numbers of document
It is used to store data in json format, so as to increase the flexibility of data format

The data will be duplicated, as there is no linkage between documents
The important data from other collections can be included into nested document, so that no need to look into other documents, to make fetching data faster and more efficient, but you will need to update the related documents manually
For the case that update frequently, it is recommended to store references into documents, and find the related information from other collections , and merge together

Comparison

SQL: used to for table that type is really important but not change and access data frequently, e.g : user data
NoSQL: used for collection that type is not really important but access and change frequently, as its speed is faster than due to no relation between collections, data are usually put together rather than join with different tables. Furthermore, the NOSQL database server can be divided into several partitions, find the value by key-value matching in order to faciltate the horizontal scaling

How does the query work

A SQL statement starts in a client program and gets sent over the network to the database server.
When the database server receives the SQL statement, the relational engine starts processing it. First the command parser checks that the statement is valid. Then it converts the statement into a query tree, which is an internal data structure.
The query optimizer looks at the query tree and figures out the most efficient way to execute the SQL statement, creating an execution plan.
The execution plan gets passed to the query executor, which uses it to coordinate retrieving or changing the data as directed by the SQL statement. The executor interacts with the storage engine to access the data.
The storage engine uses access methods, which are protocols for reading and writing data in the most efficient way for different operations.
For reading data, the buffer manager checks if the required data is cached in memory and fetches it from disk if needed. This speeds up subsequent access.
For writing data with inserts or updates, the transaction manager makes sure the changes happen atomically and maintain the database's integrity.
At the same time, the lock manager applies locks so multiple transactions can happen simultaneously without conflict. This maintains isolation and consistency.

Working together, these components process SQL statements reliably and efficiently in a database management system.

Database Connection Pool

Why need connection pool?

Here is the sequence of steps involved in a typical database connection life cycle
1. Opening a connection to the database using the database driver
2. Opening a for reading/writing data
3. Reading / writing data over the socket
4. Closing the connection
5. Closing the socket
For simple database operations, these steps are not expensive, but as an application scales up, the performance of the application will suffer as each connection is created and destroyed. One pattern for improving performance is a connection pool, a group of already configured and established network connections between the client and database that can be reused for data operations within an application.

Sizing

If we make the pool too small (i.e. choose too few connections), we’ll introduce latency, as operations have to wait for an available connection to open up before they can execute.
If we choose too many connections, that can also create latency for reasons that relate to how each processor core in a server typically executes threads
It is important to decide the max pool size and the minimum idle pool size based on :
Our application and how it interacts with the database
How often it interacts with the database (i.e. scale)
Our database technology of choice
The hardware specs of our database servers
Network latency