7 surprisingly easy performance boosters make your legacy apps fly

Everyone dreams about scalable, state-of-the-art, event-driven microservice-based architecture. However, the cold reality is that 99% of the existing applications are legacy monolithic implementations. The most common bottleneck in these traditional applications is the database.

Especially in today's data-driven world, databases are at the heart of most applications and systems. As data volumes grow, it is essential to ensure that database performance remains optimal to maintain smooth operations. While there are long-term solutions, such as application and architecture redesign, there are also some easy, quick, short-term options for improving database performance. In this article, I will explore a few you can implement immediately to boost your database's performance.

Table of Contents

1. Overutilizing Indexes
2. Underutilizing indexes
3. Too many triggers
4. Lack of Partitioning
5. Stats are Stale
6. Query optimization
7. Database settings

Overutilizing Indexes?

is when a database system has too many indexes created on a table or set of tables, leading to performance issues.?

• It can slow down the performance of INSERT, UPDATE, and DELETE operations, as the database system must update all the indexes whenever data is modified.
• It can cause the database system to use excessive amounts of disk space, impacting performance and increasing costs.
• It can result in the database system choosing suboptimal query execution plans, as it may have to evaluate many indexes to determine the best plan.

Underutilizing indexes

is when a database system does not have enough indexes created on a table or set of tables, leading to slower query performance.

• It can slow down the performance of SELECT queries that need to search through large amounts of data. The database system may scan the entire table without an appropriate index.
• It can result in higher disk I/O operations, as the database system must retrieve more data from the disk to satisfy a query.?
• It can cause issues when a table has many distinct values in a particular column. A simple search may require a full table scan, which can be slow and resource intensive.

Too many triggers

Each trigger can affect the performance, and having too many can significantly slow down database operations, leading to poor overall performance. Besides, it may increase complexity and cause inconsistencies, debugging, and maintenance challenges.

Lack of Partitioning

One of the most trivial performance boosters is partitioning. As the amount of data stored in a table grows, queries against that table can become slower and more resource-intensive, leading to poor overall performance and making the maintenance tasks (backups, index rebuilds, etc.) longer and more complex. An inappropriate partitioning strategy is the most frequent reason for the inability to scale.

Stats are Stale

When the statistics are stale, the query optimizer may not be able to make the best decisions about how to execute queries, leading to suboptimal query plans and poor performance. In addition, the database may allocate more resources than necessary to execute queries, leading to inefficient CPU, memory, and disk I/O usage. In the worst case, stale statistics can lead to inaccurate query results.

Query optimization

involves analyzing the query to identify the most efficient way for execution. Several techniques are used for query optimization, including index tuning, join optimization and query restructuring. These techniques can involve modifying the database schema, rewriting the query, or changing the way the database engine executes the query. According to my experience, optimizing only the top 10 long-running/critical queries can significantly improve database performance.

Database settings

Finally, it makes sense to review the?database settings, which may improve the overall performance.

Here is an example of database parameter tunning in PostgreSQL:

• shared_buffers: dedicated system memory for caching Tables, Indexes, etc. Typically set to 25% of total system memory.
• work_mem: the amount of memory used by internal sort, ORDER BY, and DISTINCT operations, and for joining tables by merge-join and hash-join operations, etc. Recommendations varied between 64MB and 512MB. I typically set it to 256MB, or 3 times the largest temp file.
• maintenance_work_mem: maximum amount of memory used by routine maintenance tasks such as VACUUM, CREATE INDEX and ALTER TABLE. Set the value to 10% of system memory, up to 1GB.
• Effective_cache_size: the infamous most confusing parameter responsible for how much memory is available for disk caching by the operating system. Set to 50% of total system memory.
• Temp_buffers: the memory to hold the temporary tables for each session. This will be cleared when the connection is terminated. The recommended value is 32MB.

In conclusion, while short-term solutions can alleviate some of the immediate issues we face, it's crucial to also focus on long-term solutions that can help us create sustainable and lasting change. There's much more to explore on this topic. I look forward to delving into it in greater detail in an upcoming article focusing on scalability and high performance with microservice-based architecture, event sourcing, and more. Stay tuned!"