Strategies/Tips for applications interacting with SQL Databases

Overview

This article shares essential strategies that application developers can use when interacting with SQL databases. These are some strategies that I learned over the years as an application developer. These tips aim to help improve performance, scalability and reliability of any application.

In this article, I would cover some common challenges faced by every developer, such as how to achieve data isolation without affecting performance, how to improve scalability by splitting traffic amongst master and slave instances, why EXPLAIN plan is your friend and why implementing a connection pool is beneficial.?

Ultimately, these strategies would help you maximize your system’s potential and provide a seamless user experience.?

Splitting traffic between read and write DB instances.

Splitting traffic between read and write database instances can significantly improve a system’s performance and scalability. However, to implement this effectively, it’s essential to identify whether the application is read-heavy, write-heavy, or both.

Understanding Read and Write Traffic

Here are definitions for read and write requests:

Read Requests: If the requests made to any service is entirely for the purpose of retrieving data then it is considered as read traffic. For instance, fetching a list of employees, retrieving an account balance or getting user details.

Write Requests: On the other hand, if the requests made to a service is entirely for the purpose of modifying the data then it is considered as write traffic. Examples include, adding a new employee to the database, updating an account balance, inserting metadata into a database, deleting a tenant from the database.

Identifying Application Load Characteristics

Now that we know the nature of application requests, let's shift focus to understanding an application’s workload. Specifically, we can classify an application’s workload into three categories:

Read-Heavy Applications: An application is considered as read heavy when the number of read requests outnumber the number of write requests. For example, social media platforms, search engines and ecommerce websites.

Write-Heavy Applications: An application is considered as write heavy when the number of write requests outnumber the number of read requests. Examples of such applications include archival systems, logging platforms.

Balanced Applications: An application can be considered balanced if the read and write requests are approximately equal.?

One can determine the workload of any application by observing the number of requests on a monthly or quarterly basis.

Managing Read-Heavy applications

Most of the read heavy SQL backed applications have a master-slave architecture where the master is the primary DB and the slave is the replica or secondary DB. In such cases, we can follow the following strategies to achieve better scalability and avoid bottlenecks and strain on primary DB.

Direct Read Traffic to Replicas: It’s recommended to route all read traffic to the replica database instead of the primary, reducing strain on the primary DB and enhancing scalability. That said, it is important to note that there might be a slight lag between the primary DB and secondary DB which might result in eventual consistency of data. Nevertheless, the modern cloud service providers like AWS ensure this lag is negligible close to milliseconds or nanoseconds.??

Autoscale Read Replicas: Additionally, nowadays cloud providers like AWS can provide additional read replicas which can be scaled based on the traffic. This means it is easy to scale up and down the read replicas based on DB cpu/memory usage. This is a configurable feature that can be used by any application developer.?

Managing Write-Heavy applications

Applications that are write heavy should mostly choose a high write throughput database like Cassandra and MongoDB. However, if the design requires you to choose an SQL DB then the following tips might be beneficial to some extent.

Asynchronous Processing: One way to improve performance is to process the write requests asynchronously. For instance,? publishing multiple messages to a queue and using multiple workers to process these write requests. Moreover, one can auto-scale these workers based on workload. This strategy would help minimize latency and improve overall throughput.?

Sharding: Sharding is another common strategy used to improve write throughput. In essence, sharding involves splitting data into smaller chunks and storing it in multiple DB instances. By doing this, it avoids load on a single DB instance and helps distribute write requests amongst multiple instances. However, sharding should be done carefully to avoid complications later. For example, if your tables have foreign key relationships then all data related to a particular key should be stored in the same shard. Fetching FK from multiple shards would lead to complicated joins that can degrade performance. Additionally, it is also important to design the system to avoid hot spots. A large number of requests sent to a particular shard create hot spots. Selecting a proper shard key based on the application use case and workload can prevent hot spots.

Partitioning: Partitioning is another useful strategy to distribute the write workload. Depending on your use case, you can choose an appropriate partition. For instance, in a multi-tenant environment, you can maybe partition based on the tenant name so that the traffic for a particular tenant goes to a particular partition and doesn’t affect the rest of the partitions. This kind of partitioning is known as list partitioning. Alternatively, you can also partition based on range or hash partitioning. Range partitioning divides data based on a particular range like date range or number of records. In contrast, a generated hash key directs data to a particular hash partition in hash partitioning. For example, you can have all customer data sent to a particular partition by generating a hash key using the customer ID.?

Connection Pooling

Connection pooling is a great tool to improve any application’s performance. It is a set of predefined connections that can be reused by the application every time. Thus this helps with improving overall performance, scalability and latency of an application.?

Benefits

Here are some benefits of using connection pooling.?

Reduced Overhead: Without connection pool an application would have to establish a new connection every time a request is made. This adds to the latency and results in additional overhead as creating a new connection every time requires an initial handshake. Connection pool helps with this overhead and improves efficiency.?

Handling Burst Traffic: During burst traffic, the number of connections made to the DB can spike without a connection pool. As every request makes a new connection. A connection pool would mitigate this issue by having a limit on the max/min number of connections to the DB.

Efficient Resource Management: Having a connection pool ensures that every application has a limited number of connections to the DB. It also has a cap on the maximum number of connections that an application can establish with the DB. This ensures that no application hoards on all connections to the DB.

Configuring a Connection Pool

It is important that the connection pool is configured properly. A minimum number of connections must always be set to the DB. This helps to always maintain some minimum connections in the pool ready for use. Similarly, maximum number of connections should also be defined. Maximum number helps the pool to put an upper limit to the number of connections created during peak traffic. An application should define these two numbers based on their usage, machine configuration and the threads used. Usually twice the number of CPU cores is an ideal number for maximum connections. However, it is also important that these numbers are not set to very large numbers as it may result in performance degradation.?

Minimum idle connections is another important parameter that must be configured correctly. This is the minimum number of idle connections maintained by the pool when the application is idle. You want the application to not hold a lot of connections when it is not working. Therefore it is important to set this parameter.?

Idle timeout and connection timeout are another two important parameters that one should set. Idle timeout ensures that the idle connections are freed after a certain amount of time thus avoiding resource wastage. Connection timeout ensures that the connection in use is released back to the pool after a certain amount of time. This ensures that a request cannot hold a DB connection for a long time. Therefore a request can retry in case the request errors out due to connection timeout. This not only improves overall application responsiveness but also enhances the user experience.?

By properly configuring the connection pool the application will be able to handle traffic spikes effectively and optimize the database resource utilization.?

Indexing

Indexing is an important technique that helps with faster data retrieval. It is important to properly index a table to avoid full table scans. Indexing helps to locate the data quickly thus improving the efficiency of database operations.?

There are two types of indexes one can create in sql. One is the primary key index and another is a composite index. Primary key index is usually a unique id in the table. It is unique and can be used to identify each record. Composite index is an index created by combining multiple columns. This type of index is useful when having multiple fields in the query’s where clause.

Choosing the Right Index

Here are some tips you can follow while creating an index.

Analyze Query Patterns: It is important to analyze the queries used by any application while creating an index. A field that would remain unique should be selected while creating a unique key/primary key index. While creating a composite index all fields in the where clause should be considered. It’s good to use EXPLAIN or EXPLAIN ANALYZE to understand the query’s data retrieval pattern and accordingly update the indexes on a table if required.?

Balance Indexing Needs: While indexes help with speeding up the read operations. It can negatively impact INSERT, DELETE and UPDATE operations. Therefore it is important to avoid over-indexing of any table.

Avoid Redundant Indexes: It is also important to ensure that there aren’t any duplicate or redundant indexes. For instance, you might have created a composite index on tenant_id and name and another one on tenant_id, name and job_id. The EXPLAIN planner might just be using one of the two based on your query. You should analyze that and get rid of the one that is not in use.

Index frequently used fields: Always index fields that are frequently used in WHERE, JOIN, GROUP BY and ORDER BY clauses. This would help improving the query efficiency,

Why Explain plan is your friend

Explain plan helps you optimize and improve SQL query performance. It provides detailed information about a query like query costs, execution time, type of indexing used and a few other metrics which can prove to be beneficial to any application developer or database administrator.?

Key Metrics

Some of the key metrics provided by the plan are:

• Query Costs which talks about both startup and total costs of a query.
• Execution Time: It provides an estimate of how long the query will take to execute.
• Filters and Scans: It specifies the type of filters applied and the type of scans used, such as sequential scans, index scans, bitmap scans, or function scans.
• Indexes: It talks about any indexes used during query execution.
• Rows Processed: It gives an estimate of the number of rows involved in each operation.

Let’s dive deeper into these metrics. EXPLAIN results are usually represented in a tree format, with -> symbol pointing to the child node. Each node provides details on

Startup Cost: This is the time needed to fetch the first row. This cost can vary depending upon the type of query operation. For instance; a sequential scan often has a very low startup cost because it can begin returning rows immediately. However, a sort operation has a higher startup cost, as it requires a lot of processing before any rows are returned.

Total Cost: This is the total cost to retrieve all rows for a specific operation. This gives insights into the overall query performance. Also, it is important to note that these cost estimates are not tied to any specific units. They are just a relative measure that gives you a sense of how expensive a query can be.

Type of Scan: The type of scan used is one of the crucial factors in determining a query’s performance. There are multiple types of scans used by the EXPLAIN planner and I have mentioned a few mostly used scans below.

• Sequential Scan: This type of scan as the name suggests, sequentially scans the entire table. Though it may seem less efficient, sequential scans can be used when the selectivity is low i.e. almost all records are fetched.
• Index Scan: This scan fetches rows using an index, which is generally faster and more efficient for indexed columns.?

Using EXPLAIN ANALYZE

EXPLAIN ANALYZE actually runs the entire query and provides cost estimates similar to EXPLAIN, along with the actual query execution time and the number of rows that must be returned. It is important to maintain caution while using EXPLAIN ANALYZE when executing it for UPDATE/DELETE queries as it will modify the data before returning the query plan results.?

Advantages of Using EXPLAIN Plan

There are many benefits of using a EXPLAIN PLAN like

Identifying Costly Operations: By analyzing query costs, it is easy to pinpoint operations that are inefficient such as full table scans or expensive joins and focus on optimizations of such operations.

Improving Execution Time: By using EXPLAIN plan it's easier to gain insights into the execution steps and times of a query, one can identify bottlenecks and explore ways to improve the overall execution time such as adding indexes, restructuring queries, or introducing caching mechanisms.

Ensuring Efficient Index Usage: It helps to understand the indexes used and the time taken by every index. This data can help us evaluate multiple indexes and pick the right one.?

Optimizing Complex Queries: It’s also easy to optimize complex queries using EXPLAIN plan. It gives a clear picture of the methods used for instance nested loops or hash joins in resolving a query which can help optimise complex queries with JOINS, aggregations or sorting.

Visualizing Data Flow: The tree structure helps a developer visualize the data movement through the query, from child nodes to the parent node and thus helps them in refining the query.

It is better to use an EXPLAIN plan during the development and testing phase to catch performance issues early and optimize the query.

Isolation Levels in Databases

Isolation levels define how data would be isolated when two concurrent transactions are executed together. This helps to achieve data consistency. Higher the isolation level better the consistency. However, higher the isolation, slower the performance. It is important to choose the right isolation level for your use case rather than choosing the strictest. Let’s look at some of the DB isolation levels and when you can use them.

Read Uncommitted: This is the lowest form of isolation. At this level transactions can read uncommitted data(dirty reads) by other transactions. This can result in invalid commits in case of rollbacks. For example, transaction 1 has updated the field to “1” and transaction 2 reads this uncommitted data and decides to add “1” to it resulting in output of “2”. However transaction 1 rolls back, in this case transaction 2 still updates the data as “2” when it might have been just “1” because of “dirty read” earlier. This kind of isolation should be used when not much data accuracy is needed.

Read Committed: This level of isolation ensures only committed data is read by transactions which prevents dirty reads. This ensures good data consistency. However, it can result in non-repeatable reads. For instance, if transaction 1 read the data as “1” and then transaction 2 committed the data to “2” and now when transaction 1 again reads the data it would be “2” instead of “1”. This is a non-repeatable read as the transaction got two different results when it read it at different times. This is a default isolation level used by most of the databases.

Repeatable Read: This level of isolation adds on to the previous level and ensures non-repeatable reads don't happen. It holds on to the read and write locks on a DB which don’t allow any other parallel transactions to read/write the same row. However, this doesn’t prevent phantom reads. For instance transaction T1 retrieves a set of rows based on some search criteria and transaction T2 adds similar rows meeting that criteria. If transaction T1 re-executes the query then it would return a different set of rows. This is a phantom read. This isolation level is ideal for consistent read use cases without the requirement of serialization.

Serializable: This is the strictest level of isolation. This level ensures every concurrent transaction is executed sequentially. This eliminates all issues with concurrency like dirty reads, non-repeatable reads and phantom reads. However, this affects performance. It would slow down the system. Usually this kind of isolation is recommended for financial systems. However, nowadays this level of strict isolation is mostly avoided and other options like optimistic locking is used instead. However, it entirely depends on the application use case.?

It is important to understand the trade-offs between isolation and performance while deciding the right isolation level for your application. Incorrect isolation level might slow down your application or might result in inconsistent data.

Conclusion

It is important to optimize the interactions between an application and the database to achieve better reliability, scalability and performance. And this can be achieved by following some of the strategies shared in this article. Splitting traffic between read and write DBs can help improve performance. Connection pooling can help reduce additional overhead, improve latency and achieve scalability. Indexing helps with faster data retrieval. And proper data isolation levels can help with data consistency and performance.?

All the strategies outlined in this article addresses a specific challenge faced by developers in their day to day life. By following a few of these tips and tricks they can ensure their application is performant and runs smoothly providing a seamless user experience.