<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Lior Amouyal on Medium]]></title>
        <description><![CDATA[Stories by Lior Amouyal on Medium]]></description>
        <link>https://medium.com/@lior.amouyal?source=rss-fe061cc80f08------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*lRRVJP7tyD7dSLeDB-cUgg.jpeg</url>
            <title>Stories by Lior Amouyal on Medium</title>
            <link>https://medium.com/@lior.amouyal?source=rss-fe061cc80f08------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Sat, 21 Nov 2026 17:33:42 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@lior.amouyal/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[Dear DB, We Have To Talk.]]></title>
            <link>https://blog.devgenius.io/dear-db-we-have-to-talk-3b4871a82233?source=rss-fe061cc80f08------2</link>
            <guid isPermaLink="false">https://medium.com/p/3b4871a82233</guid>
            <category><![CDATA[database]]></category>
            <category><![CDATA[backend]]></category>
            <category><![CDATA[nodejs]]></category>
            <category><![CDATA[postgresql]]></category>
            <category><![CDATA[performance]]></category>
            <dc:creator><![CDATA[Lior Amouyal]]></dc:creator>
            <pubDate>Tue, 30 May 2023 00:29:19 GMT</pubDate>
            <atom:updated>2024-01-11T18:05:30.204Z</atom:updated>
            <content:encoded><![CDATA[<p><em>A beginner’s guide to database performance analysis and optimization — made for developers, by a developer.</em></p><p>In May 2023, I delivered a presentation at the monthly meetup of the <a href="https://www.meetup.com/nodejs-israel/events/290611771">Israeli Node.js community</a>. The meetup took place at our Check Point offices in Tel-Aviv. This post aims to provide a concise overview of the key topics discussed during the talk.</p><p>The recorded presentation can be found on YouTube (Hebrew): <a href="https://youtu.be/8-hrFMYLPUs">https://youtu.be/8-hrFMYLPUs</a>.</p><p>The abstract of the talk was as follows:</p><p>DBA positions are disappearing. It’s on us Node.js developers to assume ownership and responsibility for the performance of our databases, which directly impacts the performance of our applications.</p><p>This presentation will offer practical, hands-on approaches for writing more efficient code to communicate with our databases and utilizing tools for monitoring and analyzing their performance.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*aduYApIfYpqIneGGo0isjw.jpeg" /><figcaption>The “Aha” Moment: Tired Backend Developer Discovers More Database Fine-tuning Work (<a href="https://www.shutterstock.com/image-photo/photo-unhappy-upset-guy-wear-glasses-2232720787">Shutterstock</a>)</figcaption></figure><h3>Part 1 — know your system</h3><blockquote>“The first step in solving any problem is <strong>recognizing there is one</strong>”.<br>(Unknown)</blockquote><p>Get yourself familiar with the components involved with and around your database:</p><ul><li>Is it a relational DB? If so, you will need to have some basic SQL knowledge. In any case, you’ll need to have knowledge on basic operations: creating tables/collections, defining indexes, etc.</li><li>Which DB flavor is it? We use <a href="https://www.postgresql.org/">PostgreSQL</a>, but every DB has its own unique features and set of tools.</li><li>Do you use an ORM to write code that communicates with the DB? If so, you will have to get familiar with its syntax (we use <a href="https://sequelize.org/">Sequelize</a>).</li><li>Where is the DB hosted? A cloud provider like AWS? Perhaps a different service provider? Each will offer its set of tools for monitoring and analysis.</li></ul><h3>Part 2 — database monitoring and analysis tools</h3><blockquote>”The second step in solving a problem is to <strong>analyze it and find the root-cause”.<br>(</strong>Me)</blockquote><h4>External sources</h4><p>Gain insights by utilizing monitoring and analysis tools provided by “external” sources:</p><p><strong>Cloud provider</strong></p><p>As our database is hosted on AWS, we have access to a variety of powerful tools that facilitate efficient management and analysis. Some notable tools provided by AWS include:</p><ol><li><a href="https://aws.amazon.com/cloudwatch/">CloudWatch</a>: monitoring and observability service that enables tracking of database performance metrics, such as CPU utilization, storage usage, and network activity.</li><li><a href="https://aws.amazon.com/rds/performance-insights/">RDS Performance Insights</a>: “database performance tuning and monitoring feature that helps you quickly assess the load on your database, and determine when and where to take action”.</li><li><a href="https://aws.amazon.com/devops-guru/features/devops-guru-for-rds/">RDS DevOps Guru</a>: provides usage anomaly alerts.</li></ol><p>If your database is hosted on a different cloud or service provider, make sure you get familiar with the variety of database monitoring and analysis tools they offer.</p><p><strong>Observability tools — Distributed Tracing</strong></p><p>Integration of distributed tracing in your backend services is strongly recommended. This enables you to analyze the flow from the initiation of an incoming request until the response is sent to the client. With distributed tracing, you can visualize the involvement of each service in handling the request and track the database calls associated with it.</p><p>We use <a href="https://opentelemetry.io/docs/instrumentation/js/getting-started/nodejs/">OpenTelemetry</a> for emitting traces from our Node.js services, and <a href="https://grafana.com/oss/tempo/">Grafana Tempo</a> for the tracing backend and UI.</p><h4>Database analysis tools</h4><p>Irrespective of the database you have in your application, it will typically offer its own set of analysis tools.</p><p><strong>Executing a query</strong></p><p>When you have written a query in your JavaScript/TypeScript code, whether it is a raw query or constructed using an ORM/query builder, and you want to ensure its proper functioning, testing becomes essential. Additionally, if you intend to make modifications to an existing query and want to verify the outcome of your changes, running the query directly on the database becomes beneficial. To achieve this, you can use a CLI to establish a connection with the database or opt for a specialized database IDE such as <a href="https://www.jetbrains.com/datagrip/">JetBrains DataGrip</a>. These tools allow you to execute queries directly on the database, enabling you to validate the expected results and fine-tune your queries for optimal performance.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*0jJ_e9yIJzzkCFVjhvHPoA.png" /><figcaption>execute queries on the database using the Datagrip IDE</figcaption></figure><p><strong>Utilize database views</strong></p><p>PostgreSQL, among other databases, offers various views that provide valuable statistics on tables, indexes, and query usage. These views serve as a useful resource for obtaining insights into the performance and utilization of your database. Similarly, other databases may provide similar methods or views to gather similar information. Below are some examples of useful views and corresponding queries that utilize them.</p><p><strong>pg_stat_statements</strong>: provide query usage statistics. <br>The queries returned from executing the following query can be considered as good candidates for further analysis, as they may not be performing optimally in terms of speed and efficiency.</p><pre>-- get the top 20 SQL statements that have mean run time of more than 10ms<br>SELECT query,<br>  round(total_time::numeric, 2) AS total_time,<br>  calls,<br>  round(mean_time::numeric, 2) AS mean,<br>  round(stddev_time::numeric, 2) AS std_dev,<br>  round ((100* total_time / sum (total_time::numeric)<br>      OVER ())::numeric, 2) AS percentage_overall,<br>  userid, dbid, queryid<br>FROM pg_stat_statements<br>WHERE mean_time &gt; 10<br>ORDER BY total_time DESC<br>LIMIT 20;</pre><p><strong>pg_stat_user_tables</strong>: provides table scan statistics.<br>Tables with high seq_scan values may be missing one or more indexes.</p><pre>SELECT relname AS table_name, seq_scan, seq_tup_read,<br>  seq_tup_read / seq_scan AS avg, idx_scan<br>FROM pg_stat_user_tables<br>WHERE seq_scan &gt; 0 AND schemaname=&#39;public&#39;<br>ORDER BY seq_tup_read DESC;</pre><p><strong>pg_stat_user_indexes</strong>: provide index usage statistics.<br>Indexes with low idx_scan values may be good candidates for deletion or may indicate that queries that <em>should</em> benefit from them are not written in an optimal manner.</p><pre>SELECT relname AS table_name, indexrelname AS index_name,<br>  idx_scan, idx_tup_read, idx_tup_fetch<br>FROM pg_stat_user_indexes<br>WHERE schemaname=&#39;public&#39;<br>ORDER BY relname;</pre><p><strong>The Query Plan (aka Execution Plan)</strong></p><p>A detailed explanation of how a database system intends to execute a specific query. It provides a step-by-step outline of the operations and algorithms the database engine will employ to retrieve and manipulate the data requested by the query.</p><p>By examining the query plan, we can gain insights into how the database is executing a query and identify potential performance bottlenecks or areas for optimization, for example: if the query performs sequential table scan or utilizes an index.</p><p>We can see the query plan by running the EXPLAINor the EXPLAIN ANALYZEcommands, e.g.:</p><pre>EXPLAIN ANALYZE<br>SELECT name<br>FROM &quot;Users&quot;<br>WHERE id = &#39;12345678-1234-1234-1234-123456789012&#39;;</pre><p>The EXPLAINcommand will only output the query plan and the EXPLAIN ANALYZEcommand will also run the query.</p><p><strong>Note that</strong> EXPLAIN <strong>is a read-only DB operation but</strong> EXPLAIN ANALYZE <strong>isn’t!</strong> <strong>Be very careful not to use </strong>EXPLAIN ANALYZE<strong> on an </strong>UPDATE<strong> or </strong>DELETE<strong> statement, as it will actually execute the query and modify the database.</strong></p><p>Note: this applies for most (or perhaps all??) major SQL databases but also for some NoSQL databases (e.g. MongoDB has the explaincommand).</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*-z2QhTkvlQyPoqegfleXCg.png" /><figcaption>The query plan of the query mentioned above</figcaption></figure><h3>Part 3 — do’s and don’ts</h3><blockquote>“The final step in solving a problem is to <strong>implement a fix</strong>”<br>(Me)</blockquote><h4><strong>Add missing indexes</strong></h4><ul><li>The information obtained from utilizing the database views mentioned earlier may suggest which tables could benefit from adding indexes.</li><li>Postgres automatically creates an index on a table’s primary key when a new table is created, but it will not do the same for foreign keys. We may benefit from adding indexes on foreign keys, for example if they are used for joining tables or when deleting database rows on joined tables.</li></ul><h4><strong>Delete redundant indexes</strong></h4><p>This will save storage space and prevent the database from updating the unused table index when adding/deleting entries to the table.</p><h4><strong>Multi-column indexes</strong></h4><p>Creating a multi-column index can bring significant benefits in certain scenarios, particularly when querying involves multiple columns.</p><p>But if not used correctly, we may see encounter various problems:</p><p><strong>Avoid creating redundant indexes</strong></p><p>In the following example, the first index is redundant as the second index will give the same results when filtering by the userId only:</p><pre>CREATE INDEX userId_idx ON Users (userId) -- this index adds no value<br>CREATE INDEX userId_status_idx ON Users (userId, status)</pre><p><strong>Use the most selective column first</strong></p><p>In case you have an index on userId and status, the second query will not utilize this index since it queries on the second column:</p><pre>SELECT * FROM Users WHERE userId = &#39;userId123&#39;; -- the index will be used<br>SELECT * FROM Users WHERE status = &#39;ACTIVE&#39;; -- inefficient query, idx will not be used</pre><h4><strong>Using functions on indexed columns</strong></h4><p>Let’s assume we have an index in the creation date of a user account (the created_atcolumn) and we would like to count the number of accounts created in the year 2023. We may wrap the created_atcolumn in the YEAR function and produce the following query:</p><pre>SELECT count(*) FROM UserAccounts<br>WHERE YEAR(created_at) = 2023;</pre><p>In this scenario, the query will not utilize the index efficiently. If the database has created an index on a date column containing full date strings, but the query is filtering by the year only, the database does not have the specific year information within the index.</p><p>One possible solution may be to change the query’s WHEREclause:</p><pre>SELECT count(*) FROM UserAccounts<br>WHERE created_at &gt;= &#39;2023-01-01&#39; AND created_at &lt; &#39;2024-01-01&#39;;</pre><h4>Other things to consider (these were not covered during the talk):</h4><ol><li>Use partial indexes where applicable (<a href="https://www.postgresql.org/docs/current/indexes-partial.html">examples</a>).</li><li>Use other index types where applicable (<a href="https://www.postgresql.org/docs/current/indexes-types.html">examples</a>).</li><li>Create/build indexes concurrently to avoid table locks (<a href="https://www.postgresql.org/docs/current/sql-createindex.html#SQL-CREATEINDEX-CONCURRENTLY">examples</a>).</li><li>DB engine upgrades: may improve database performance and add features (but beware of db downtime :-)).</li></ol><p>One word about <a href="https://chat.openai.com/">ChatGPT</a>.</p><p>Yes, it may help. For example, use it to get its recommendations on optimizing your database queries.</p><p>But never take its word for anything without testing it out yourself, the tools mentioned in this article will help you do that.</p><p>Otherwise you may bump into ChatGPT’s hallucinations or get conflicting responses like these:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/897/1*T8xva4GHWuAyUqVhshtoMg.png" /><figcaption>A real chat with ChatGPT. Is optional chaining supported in Node.js v12 or not???</figcaption></figure><h3>Summary</h3><p>Database performance indeed plays a crucial role in the overall performance of an application.</p><p>As backend developers, we hold the responsibility of writing code that interacts with the database, including creating tables, defining relations, crafting queries, and designing indexes.</p><p>To assist us in our tasks, we have the option to utilize monitoring and analysis tools provided by external sources or directly by the database engine.</p><p>And finally, by properly defining and utilizing table indexes we will gain significant performance improvements in our database system.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=3b4871a82233" width="1" height="1" alt=""><hr><p><a href="https://blog.devgenius.io/dear-db-we-have-to-talk-3b4871a82233">Dear DB, We Have To Talk.</a> was originally published in <a href="https://blog.devgenius.io/">Dev Genius</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[How this low-hanging fruit improved our DB throughput by over 200x and prevented outages in…]]></title>
            <link>https://medium.com/saas-infra/how-this-low-hanging-fruit-improved-our-db-throughput-by-over-x200-and-prevented-outages-in-313aa4ea87dc?source=rss-fe061cc80f08------2</link>
            <guid isPermaLink="false">https://medium.com/p/313aa4ea87dc</guid>
            <category><![CDATA[postgres]]></category>
            <category><![CDATA[performance]]></category>
            <category><![CDATA[postgresql]]></category>
            <category><![CDATA[database]]></category>
            <category><![CDATA[nodejs]]></category>
            <dc:creator><![CDATA[Lior Amouyal]]></dc:creator>
            <pubDate>Sat, 07 Jan 2023 15:23:28 GMT</pubDate>
            <atom:updated>2023-01-08T05:41:28.728Z</atom:updated>
            <content:encoded><![CDATA[<h3>How this low-hanging fruit improved our DB throughput by over 200x and prevented outages in production</h3><p>Intro to Database Performance Analysis for Software Developers</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*u6_jRGUIQVd8Ya_ff-VZng.png" /><figcaption>An elephant never forgets (to properly use table indexes)</figcaption></figure><p><strong>Team Lead</strong>: Lior, your next task is to improve the stability of our database and fix any performance issues.<br><strong>Me</strong>: No problem, I’ll see what I can do.<br><strong>Also me</strong> (5 minutes later, realizing I have very little hands-on DB experience): !?$#$?#?%%#@!#@&amp;^%$##.</p><p>My team is responsible for maintaining the infrastructure for a software as a service (SaaS) product that includes a web portal with a Node.js based backend and multiple PostgreSQL databases. We were experiencing frequent outages and unacceptable levels of latency, so I was tasked with finding ways to improve the performance of our databases.</p><p>Database performance is a complex topic and in this article I will focus on one specific technique: <strong>using table indexes to optimize queries</strong>.</p><p>Improving database performance is a valuable goal in and of itself, but it’s ultimately just a means to an even greater end: <strong>improving the performance and responsiveness of our applications</strong>. When done correctly, this can provide a significant performance boost with minimal effort.</p><p>A couple of disclaimers:</p><ul><li>Although the techniques and ideas discussed are specific to PostgreSQL, they can often be applied to other types of databases, both SQL and NoSQL. A quick search online should provide guidance on adapting these techniques for other databases.</li><li>This article was written for software developers who have little or no prior hands-on databases experience, just like I was a few months ago :-)</li></ul><h3>Some databases background</h3><p>This section is a brief and simplified introduction to how databases store and retrieve data, provided for the purpose of helping you understand the rest of this article.</p><h4><strong>Sequential vs. Index scans</strong></h4><p>Relational databases store data in tables and when we perform a query, the database searches these tables to retrieve the requested data.</p><p>One method for searching a database is to read the entire table into memory and scan it from start to end, called a <strong>sequential scan </strong>(or <strong>full table scan</strong>). <br>However, this can be very inefficient in many cases.</p><p>A more efficient approach is to use indexes on certain columns in the table. When querying the database, it can search the index only, which is optimized for quick searches and is much smaller in size compared to the entire table. When the requested values are found, the database then accesses the disk to fetch the specific parts of the table it needs, rather than reading the entire table. This is called an <strong>index scan</strong>.</p><p>A couple of important notes about indexes:</p><ol><li>Postgres automatically creates an index on a table’s primary key, but this may not be enough for optimal performance. Depending on the query that is executed, indexes on other columns may also be necessary, so you may need to add them manually.</li><li>Indexes can be created on one or more columns, depending on the queries you will be running. For example, if a query includes a condition such as WHERE tenantId = &#39;some_tenant_id&#39;, you may find that an index on the tenantId column is sufficient. <br>However, if the query has a more complex condition such as:<br>WHERE tenantId = &#39;some_tenant_id&#39; AND status = &#39;some_status&#39;,<br>you may benefit more from a <a href="https://www.postgresql.org/docs/current/indexes-multicolumn.html">multi-column index</a> on both tenantId and status.</li></ol><h4>Database Views</h4><p><a href="https://www.postgresql.org/docs/current/sql-createview.html">Database views</a> allow you to give a name to a predefined query that you can then use like a regular table. Postgres has many built-in views, but the one I find most useful for analyzing queries and optimizing them is <a href="https://www.postgresql.org/docs/current/pgstatstatements.html">pg_stat_statements</a>.</p><p>The pg_stat_statements view returns statistics for all SQL queries that have run on the database since the last reset of statistics.</p><p>Note that the pg_stat_statements view is not enabled by default in Postgres. You may need to have a chat with your DevOps/DB admin team to enable it.</p><h4>Query Analysis</h4><p>Postgres tries to optimize queries to minimize their “cost” and creates a plan for how to execute the query. This planning step is usually quick and occurs before the query is run.</p><p>You can use the EXPLAIN and EXPLAIN ANALYZE commands to view the <a href="https://www.postgresql.org/docs/current/using-explain.html">query plan</a> and see if it makes sense (i.e. by running EXPLAIN ANALYZE &lt;query_statement&gt;).</p><p><strong>Note that</strong> EXPLAIN <strong>is a read-only DB operation but</strong> EXPLAIN ANALYZE <strong>isn’t!</strong> <strong>Be very careful not to use </strong>EXPLAIN ANALYZE<strong> on an </strong>UPDATE<strong> or </strong>DELETE<strong> statement, as it will actually execute the query and modify the database.</strong></p><h3>Applying the Theory: Improving Database Performance</h3><p>Now that we have learned about sequential scans versus index scans, database views, and how to analyze queries, we can use this knowledge to improve the performance of our database.</p><h4>Identifying Poorly Performing Queries</h4><p>We can use the pg_stat_statements view in a query to see a list of the top 20 queries that have consumed the most compute time on the database:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/a19dc0486f028d12af645c963607fd9d/href">https://medium.com/media/a19dc0486f028d12af645c963607fd9d/href</a></iframe><p>The following screenshot shows the top four most time-consuming queries that we identified in our databases:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*vbysEjwlOtPP5zuz" /><figcaption>The most time-consuming queries in our DB</figcaption></figure><p>It is worth noting that these queries were executed frequently compared to other queries in the list, about 2.3 million times each. The execution time for each query was also relatively high at 16–28 milliseconds, which is significant for a SELECT query as the next section shows.</p><h4>Analyze the Poorly Performing Queries</h4><p>Now that we have identified the queries that take the longest to exectue, let’s see if we can improve their performance.</p><p>We ran EXPLAIN ANALYZE on each of the previously mentioned queries to view their execution plans. Here is an example of the results for one of the queries:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/960/0*WnvNOKcmYAHmzKXT" /><figcaption>Query plan for a poorly performing query</figcaption></figure><p>This query took 28 milliseconds to execute because the Notes and Actions tables were being sequentially scanned, indicating that they were lacking table indexes.</p><p>To fix this issue, we added an index on the noteId column in the Actions table (noteId is a foreign key in the Actions table) and a multi-column index on the tenantId and status columns in the Notes table.</p><p>Here is the query plan for the same query after adding these indexes:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/960/0*n-U_2GKn-eSN9py3" /><figcaption>Same query with missing indexes added</figcaption></figure><p>The execution time for this query <strong>dropped from 28 to 0.07 milliseconds</strong>! Hooray!</p><p>The plan also shows that the new indexes are being used instead of sequential scans of the entire tables and that the query cost has significantly decreased.</p><h4>Bonus Tip</h4><p>Upon closer analysis of the four previously mentioned queries, I noticed that they were essentially the same, with the only difference being the order of the conditions in their WHERE clause.<br>Two of the queries had the following WHERE clause:<br>WHERE &quot;tenantId&quot; = &#39;some_tenant_id&#39; AND &quot;status&quot; != &#39;dismissed&#39;.<br>The other two had the conditions in the opposite order:<br>WHERE &quot;status&quot; != &#39;dismissed&#39; AND &quot;tenantId&quot; = &#39;some_tenant_id&#39;.</p><p>Postgres may choose different execution plans <strong>depending on the order of the conditions</strong> in the WHERE clause, so it treated these as distinct queries. As mentioned earlier, I added a multi-column index on the (tenantId, status) columns in the Notes table, but this only improved the performance of the first two queries. The other two queries, which had the opposite order of conditions in the WHERE clause, were unable to benefit from this index.</p><p>One possible solution would be to create another multi-column index on (status, tenantId), but this would incur additional storage costs. In addition, an index takes time to update when items are added or removed from the table, and we are already incurring this cost when updating the (tenantId, status) index.</p><p>Therefore, it is more efficient to find another solution, such as modifying the query or, in our case, changing the code that generates the query since we are using an ORM library (Object-Relational Mapping).</p><p>By rearranging the order of the WHERE clause conditions in our Node.js service code, all four queries became identical in the eyes of Postgres and were able to utilize the newly created index. The following simplified code snippet shows the changes we made in the module that interfaces with the ORM:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/d721f73c316ab385c465eb6970a3ac0e/href">https://medium.com/media/d721f73c316ab385c465eb6970a3ac0e/href</a></iframe><h3><strong>Overall Performance Improvement</strong></h3><p>After implementing the changes mentioned earlier, the execution time of all four queries <strong>was reduced from 78 milliseconds to 0.29 milliseconds</strong> on average, resulting in a <strong>270-fold improvement in performance!</strong></p><p>Since each of these queries run 4 times per second, the database now spends <strong>only 1.16 milliseconds executing them per second, instead of 312 milliseconds.</strong></p><h3>Summary</h3><p>Optimizing databases performance is a complex process with many different considerations and in this article we have only touched on the topic of indexing best practices.</p><p>However, even by performing a few simple steps such as analyzing queries and adding missing indexes, it is possible to significantly improve the throughput of a database and its associated applications.</p><p>If you have any performance analysis and improvement tips of your own, feel free to share them in the comments section.</p><p>Good luck!</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/747/1*KkI6P3ab7TZF8uWTAo8Zww.jpeg" /><figcaption>Even young Borat is excited about our results</figcaption></figure><p>The images in this article were made with tools from @midjourney, you can sign up for their private beta here <a href="http://bit.ly/3J2NNVs">http://bit.ly/3J2NNVs</a>.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=313aa4ea87dc" width="1" height="1" alt=""><hr><p><a href="https://medium.com/saas-infra/how-this-low-hanging-fruit-improved-our-db-throughput-by-over-x200-and-prevented-outages-in-313aa4ea87dc">How this low-hanging fruit improved our DB throughput by over 200x and prevented outages in…</a> was originally published in <a href="https://medium.com/saas-infra">saas-infra</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Mastering Codility’s Online Coding Test — My Personal Tips]]></title>
            <link>https://medium.com/saas-infra/mastering-codilitys-online-coding-test-my-personal-tips-b3e3324cffb?source=rss-fe061cc80f08------2</link>
            <guid isPermaLink="false">https://medium.com/p/b3e3324cffb</guid>
            <category><![CDATA[coding-test]]></category>
            <category><![CDATA[success]]></category>
            <category><![CDATA[job-interview]]></category>
            <category><![CDATA[software-engineering]]></category>
            <dc:creator><![CDATA[Lior Amouyal]]></dc:creator>
            <pubDate>Mon, 19 Dec 2022 23:09:38 GMT</pubDate>
            <atom:updated>2022-12-30T23:42:58.789Z</atom:updated>
            <content:encoded><![CDATA[<h3>Mastering Codility’s Online Coding Test — My Top Tips</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ROOgVWt5X-hsXYh_wox2Eg.png" /><figcaption>Succeeding on your online coding test. DALL-E 2022</figcaption></figure><p>It’s no secret that the process of applying for a job can be quite lengthy. In addition to interviews and possibly completing a home assignment, you may also be asked to take an online coding skills test. One popular platform for these tests is <a href="https://www.codility.com/">Codility</a>.</p><p>The test consists of several programming problems that you will need to solve within a predefined time frame. Every minute counts, so it’s important to use your time wisely.</p><p>This guide offers some preparation tips and things to keep in mind during the test to help you succeed.</p><blockquote>This post is not affiliated with Codility and is simply a personal account of my experience with the platform. I hope that sharing my experience may be helpful to some readers.</blockquote><p><strong>Should you prepare for this test?</strong></p><p>It is optional, but I recommend you do.</p><p><strong>How can I prepare for a Codility test?</strong></p><ol><li>Focus on <strong>one</strong> programming language.<br>Codility’s platform supports various programming languages, including C, Java, Javascript, and Python, so choose the one you feel most comfortable using.</li><li>Know the basics.<br>It is important that you are familiar with basic array, string, and object/dictionary operations, including their parameters, return values, and edge cases. This will help you avoid wasting time during the test by having to look up basic information.</li><li>Practice.<br>Codility offers a variety of <a href="https://app.codility.com/programmers/lessons/">lessons</a>, <a href="https://app.codility.com/programmers/trainings/">exercises</a> and <a href="https://app.codility.com/programmers/challenges/">challenges</a> to help you prepare and improve your skills. The more of them you complete, the better prepared you will be.</li><li>Simulate.<br>Take Codility’s <a href="https://app.codility.com/demo/take-sample-test/">demo test</a>.</li><li>Playground.<br>Use your preferred IDE for writing your solution. When you are finished, paste your code into Codility’s UI. Your familiar IDE will format your code properly, highlight syntax and may even yell at you when you write bad code. This method is also recommended by Codility.</li><li>Test your code!<br>Write simple yet thorough tests that run your solution on variety of inputs and verify that it produces the correct output. I recommend writing these tests before implementing your solution (TDD-style), this will help you understand the problem and consider all edge cases.</li><li>Final preparation tip:<br>I suggest creating templates for the problems you will be solving, each template should include all the boilerplate code needed to test a generic solution. This method will save you precious time during the test.<br>Here’s a very simple example (in Javascript):</li></ol><pre>function mySolution(PARAMS_AS_DESCRIBED_IN_PROBLEM_SET) {<br>  IMPLEMENTATION_GOES_HERE<br>}<br><br>// Test cases<br>console.log(mySolution(INPUT_PARAMS_1))<br>console.log(mySolution(INPUT_PARAMS_2))<br>console.log(mySolution(INPUT_PARAMS_3))</pre><p>It is probably better to use assertions or your preferred testing library to further enhance your testing process, but even just using this simple skeleton can save you a lot of time. To use this skeleton, start by carefully reading the problem you are trying to solve. Then, enter the function parameters described in the problem, add input parameters to the test cases (you can add as many test cases as you want), and finally begin implementing your solution.</p><p><strong>During the test</strong></p><ol><li>Breathe 🙂</li><li>Read the test instructions carefully.</li><li>Wisely divide your time between all questions. If you think one question is easier, start with it, but leave enough time for the rest.</li><li>Similar to the preparation stage, use your preferred programming language and IDE. And do not forget to write comprehensive tests to ensure the correctness of your solution.</li><li>Do the best you can, good luck!</li></ol><p>Have a few tips of your own? Please share them in the comments section 💪</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=b3e3324cffb" width="1" height="1" alt=""><hr><p><a href="https://medium.com/saas-infra/mastering-codilitys-online-coding-test-my-personal-tips-b3e3324cffb">Mastering Codility’s Online Coding Test — My Personal Tips</a> was originally published in <a href="https://medium.com/saas-infra">saas-infra</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
    </channel>
</rss>