Performance tests IRIS - PostgreSQL - MySQL
Tags: docker, java, database, jdbc
Published: 2024-06-16 16:14:32

<p>As a former JAVA developer it has always been a challenge to decide which database was the most suitable for the project we were going to develop, one of the main criteria I used was their performance, as well as their HA configuration capabilities ( high availability). Well, now is the time to put IRIS to the test with respect to some of the most commonly used databases, so I've decided to create a small Java project based on SpringBoot that connects via JDBC with a MySQL database, another of PostgreSQL and finally with IRIS.</p>
<p>We are going to take advantage of the fact that we have Docker images of these databases to use them in our project and allow you to try it yourself without having to carry out any installation. We can check the docker configuration in our <strong>docker-compose.yml</strong> file</p>
<pre class="codeblock-container" idlang="4" lang="YAML" tabsize="4"><code class="language-yaml hljs"><span class="hljs-attr">version:</span> <span class="hljs-string">"2.2"</span>
<span class="hljs-attr">services:</span>
  <span class="hljs-comment"># mysql</span>
<span class="hljs-attr">  mysql:</span>
<span class="hljs-attr">    build:</span>
<span class="hljs-attr">      context:</span> <span class="hljs-string">mysql</span>
<span class="hljs-attr">    container_name:</span> <span class="hljs-string">mysql</span>
<span class="hljs-attr">    restart:</span> <span class="hljs-string">always</span>
<span class="hljs-attr">    command:</span> <span class="hljs-bullet">--default-authentication-plugin=mysql_native_password</span>
<span class="hljs-attr">    environment:</span>
<span class="hljs-attr">      MYSQL_ROOT_PASSWORD:</span> <span class="hljs-string">SYS</span>
<span class="hljs-attr">      MYSQL_USER:</span> <span class="hljs-string">testuser</span>
<span class="hljs-attr">      MYSQL_PASSWORD:</span> <span class="hljs-string">testpassword</span>
<span class="hljs-attr">      MYSQL_DATABASE:</span> <span class="hljs-string">test</span>
<span class="hljs-attr">    volumes:</span>
<span class="hljs-bullet">    -</span> <span class="hljs-string">./mysql/sql/dump.sql:/docker-entrypoint-initdb.d/dump.sql</span>
<span class="hljs-attr">    ports:</span>
<span class="hljs-bullet">      -</span> <span class="hljs-number">3306</span><span class="hljs-string">:3306</span>
  <span class="hljs-comment"># postgres</span>
<span class="hljs-attr">  postgres:</span>
<span class="hljs-attr">    build:</span>
<span class="hljs-attr">      context:</span> <span class="hljs-string">postgres</span>
<span class="hljs-attr">    container_name:</span> <span class="hljs-string">postgres</span>
<span class="hljs-attr">    restart:</span> <span class="hljs-string">always</span>
<span class="hljs-attr">    environment:</span>
<span class="hljs-attr">      POSTGRES_USER:</span> <span class="hljs-string">testuser</span>
<span class="hljs-attr">      POSTGRES_PASSWORD:</span> <span class="hljs-string">testpassword</span>
<span class="hljs-attr">    volumes:</span>
<span class="hljs-bullet">    -</span> <span class="hljs-string">./postgres/sql/dump.sql:/docker-entrypoint-initdb.d/dump.sql</span>
<span class="hljs-attr">    ports:</span>
<span class="hljs-bullet">      -</span> <span class="hljs-number">5432</span><span class="hljs-string">:5432</span>
<span class="hljs-attr">  adminer:</span>
<span class="hljs-attr">    container_name:</span> <span class="hljs-string">adminer</span>
<span class="hljs-attr">    image:</span> <span class="hljs-string">adminer</span>
<span class="hljs-attr">    restart:</span> <span class="hljs-string">always</span>
<span class="hljs-attr">    depends_on:</span>
<span class="hljs-bullet">      -</span> <span class="hljs-string">mysql</span>
<span class="hljs-bullet">      -</span> <span class="hljs-string">postgres</span>
<span class="hljs-attr">    ports:</span>
<span class="hljs-bullet">      -</span> <span class="hljs-number">8081</span><span class="hljs-string">:8080</span>
  <span class="hljs-comment"># iris</span>
<span class="hljs-attr">  iris:</span>
<span class="hljs-attr">    init:</span> <span class="hljs-literal">true</span>
<span class="hljs-attr">    container_name:</span> <span class="hljs-string">iris</span>
<span class="hljs-attr">    build:</span>
<span class="hljs-attr">      context:</span> <span class="hljs-string">.</span>
<span class="hljs-attr">      dockerfile:</span> <span class="hljs-string">iris/Dockerfile</span>
<span class="hljs-attr">    ports:</span>
<span class="hljs-bullet">      -</span> <span class="hljs-number">52773</span><span class="hljs-string">:52773</span>
<span class="hljs-bullet">      -</span> <span class="hljs-number">1972</span><span class="hljs-string">:1972</span>
<span class="hljs-attr">    command:</span> <span class="hljs-bullet">--check-caps</span> <span class="hljs-literal">false</span>
  <span class="hljs-comment"># tomcat</span>
<span class="hljs-attr">  tomcat:</span>
<span class="hljs-attr">    init:</span> <span class="hljs-literal">true</span>
<span class="hljs-attr">    container_name:</span> <span class="hljs-string">tomcat</span>
<span class="hljs-attr">    build:</span>
<span class="hljs-attr">      context:</span> <span class="hljs-string">.</span>
<span class="hljs-attr">      dockerfile:</span> <span class="hljs-string">tomcat/Dockerfile</span>
<span class="hljs-attr">    volumes:</span>
<span class="hljs-bullet">      -</span> <span class="hljs-string">./tomcat/performance.war:/usr/local/tomcat/webapps/performance.war</span>
<span class="hljs-attr">    ports:</span>
<span class="hljs-bullet">      -</span> <span class="hljs-number">8080</span><span class="hljs-string">:8080</span>
</code></pre>
<p>With a quick glance we will see that we are using the following images:</p>
<ul>
<li><b>IRIS</b>: IRIS Community instance to which we will connect by JDBC.</li>
<li><b>Postgres</b>: PostgreSQL database image listening on port 5432.</li>
<li><b>MySQL</b>: MySQL database image listening on port 3306.</li>
<li><b>Tomcat</b>: Docker image configured with an Apache Tomcat application server on which we will deploy the WAR file of our application.</li>
<li><b>Adminer</b>: database administrator that will allow us to consult the Postgres and MySQL databases.</li>
</ul>
<p>As you can see, we have configured the listening ports so that they are also mapped on our computer, not only within Docker. In the case of databases, it would not be necessary, since the connection will be made within the Docker containers, so if you have any problems with the <strong>ports</strong>, you can delete the ports line from the <strong>docker-compose.yml</strong> file.</p>
<p>Each database image is running a pre-script that will create the tables needed for performance tests, let's look at one of the <strong>dump.sql</strong> files</p>
<pre class="codeblock-container" idlang="1" lang="SQL" tabsize="4"><code class="language-sql hljs"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">SCHEMA</span> <span class="hljs-keyword">test</span>;

<span class="hljs-keyword">DROP</span> <span class="hljs-keyword">TABLE</span> <span class="hljs-keyword">IF</span> <span class="hljs-keyword">EXISTS</span> test.patient;

<span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> test.country (
    <span class="hljs-keyword">id</span> <span class="hljs-built_in">INT</span> PRIMARY <span class="hljs-keyword">KEY</span>,
    <span class="hljs-keyword">name</span> <span class="hljs-built_in">VARCHAR</span>(<span class="hljs-number">225</span>)
);

<span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> test.city (
    <span class="hljs-keyword">id</span> <span class="hljs-built_in">INT</span> PRIMARY <span class="hljs-keyword">KEY</span>,
    <span class="hljs-keyword">name</span> <span class="hljs-built_in">VARCHAR</span>(<span class="hljs-number">225</span>),
    lastname <span class="hljs-built_in">VARCHAR</span>(<span class="hljs-number">225</span>),
    photo BYTEA,
    phone <span class="hljs-built_in">VARCHAR</span>(<span class="hljs-number">14</span>),
    address <span class="hljs-built_in">VARCHAR</span>(<span class="hljs-number">225</span>),
    country <span class="hljs-built_in">INT</span>,
    <span class="hljs-keyword">CONSTRAINT</span> fk_country
        FOREIGN <span class="hljs-keyword">KEY</span>(country)
            <span class="hljs-keyword">REFERENCES</span> test.country(<span class="hljs-keyword">id</span>)
);

<span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> test.patient (
    <span class="hljs-keyword">id</span> <span class="hljs-built_in">INT</span> <span class="hljs-keyword">GENERATED</span> <span class="hljs-keyword">BY</span> <span class="hljs-keyword">DEFAULT</span> <span class="hljs-keyword">AS</span> <span class="hljs-keyword">IDENTITY</span> PRIMARY <span class="hljs-keyword">KEY</span>,
    <span class="hljs-keyword">name</span> <span class="hljs-built_in">VARCHAR</span>(<span class="hljs-number">225</span>),
    lastname <span class="hljs-built_in">VARCHAR</span>(<span class="hljs-number">225</span>),
    photo BYTEA,
    phone <span class="hljs-built_in">VARCHAR</span>(<span class="hljs-number">14</span>),
    address <span class="hljs-built_in">VARCHAR</span>(<span class="hljs-number">225</span>),
    city <span class="hljs-built_in">INT</span>,
    <span class="hljs-keyword">CONSTRAINT</span> fk_city
        FOREIGN <span class="hljs-keyword">KEY</span>(city)
            <span class="hljs-keyword">REFERENCES</span> test.city(<span class="hljs-keyword">id</span>)
);

<span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> test.country <span class="hljs-keyword">VALUES</span> (<span class="hljs-number">1</span>,<span class="hljs-string">'Spain'</span>), (<span class="hljs-number">2</span>,<span class="hljs-string">'France'</span>), (<span class="hljs-number">3</span>,<span class="hljs-string">'Portugal'</span>), (<span class="hljs-number">4</span>,<span class="hljs-string">'Germany'</span>);

<span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> test.city <span class="hljs-keyword">VALUES</span> (<span class="hljs-number">1</span>,<span class="hljs-string">'Madrid'</span>,<span class="hljs-number">1</span>), (<span class="hljs-number">2</span>,<span class="hljs-string">'Valencia'</span>,<span class="hljs-number">1</span>), (<span class="hljs-number">3</span>,<span class="hljs-string">'Paris'</span>,<span class="hljs-number">2</span>), (<span class="hljs-number">4</span>,<span class="hljs-string">'Bordeaux'</span>,<span class="hljs-number">2</span>), (<span class="hljs-number">5</span>,<span class="hljs-string">'Lisbon'</span>,<span class="hljs-number">3</span>), (<span class="hljs-number">6</span>,<span class="hljs-string">'Porto'</span>,<span class="hljs-number">3</span>), (<span class="hljs-number">7</span>,<span class="hljs-string">'Berlin'</span>,<span class="hljs-number">4</span>), (<span class="hljs-number">8</span>,<span class="hljs-string">'Frankfurt'</span>,<span class="hljs-number">4</span>);
</code></pre>
<p>We are going to create 3 tables for our tests, <strong>patient</strong>, <strong>city</strong> and <strong>country</strong>, these last two are going to have preloaded data of cities and countries.</p>
<p>Perfect, next we are going to see how we will make the connections to the database.</p>
<p>To do this we have created our Java project using a preconfigured Spring Boot project available from Visual Studio Code that provides us with the basic structure.</p>

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/5znztzu2cjsesck45ptv.png)
<p>Don't worry if you don't understand the structure of the project at first glance, the goal is not to learn Java, but still we are going to explain a little more in detail the main documents.</p>
<h4>MyDataSourceFactory.java</h4>
<p>Java class that opens the connections to the different databases.</p>
<h4>PerformancerController.java</h4>
<p>Controller in charge of publishing the endpoints that we will call from Postman.</p>
<h4>application.properties</h4>
<p>Configuration file with the different connections to the databases deployed in our Docker.</p>

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/fmtwjue3prxj6ftye4wj.png)
<p>As you can see, the connection URLs use the container name since, when deployed in a Tomcat container, the databases will be accessible by our Java application only with the corresponding container name. We can also check how the URL is making a connection via JDBC to our databases. The Java libraries used in the project are defined in the pom.xml file.</p>
<p>If you modify the source code, you only have to execute the command:</p>
<pre class="codeblock-container" idlang="11" lang="Bash" tabsize="4"><code class="language-bash hljs">mvn package</code></pre>
<p>And this will generate a file <strong>performance-0.0.1-SNAPSHOT.war</strong>, rename it to <strong>performance.war</strong> and move it to the <strong>/tomcat</strong> directory, replacing the existing one.</p>
<p>As the project is on GitHub, we only need to clone it on our computer from Visual Studio and execute the following commands in the terminal:</p>
<pre class="codeblock-container" idlang="11" lang="Bash" tabsize="4"><code class="language-bash hljs">docker-compose build
docker-compose up -d</code></pre>
<p>Let's check the Docker portal:</p>

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/lmmbhspo5b0oolr8ub2g.png)
<p>Great! Docker containers working. Now let's check from our Adminer and the IRIS management portal that our tables have been created correctly.</p>

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/3yea2wjxros9kgyxs7ns.png)
<p>Let's first access the MySQL database. If you consult the file <strong>docker-compose.yml</strong> we will see that the username and password defined for MySQL and PostgreSQL are the same <strong>testuser</strong>/<strong>testpassword</strong></p>

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/obuxjqj3vrrgriuq2lst.png)
<p>Here we have our three tables inside our Test database, let's look at our PostgreSQL database:</p>

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/n2vjm66x8gkiauevfn2u.png)
<p>Let's select the <strong>testuser</strong> database and the <strong>test</strong> schema:</p>

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/byjbpx3qb387g89v03x9.png)
<p>Here we have our tables perfectly created in PostgreSQL. Let's finally check that everything is configured correctly in IRIS:</p>

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/36ull1lcbui8dcih88m2.png)
<p>All correct, we have our tables created in the <strong>USER</strong> Namespace under the <strong>Test</strong> schema.</p>
<p>Alright, once the checks are done, let's rock! For this we will use Postman, in which we will load the file attached to the project <strong>performance.postman_collection.json</strong></p>

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/z7dczt95yy38j26a3cvx.png)
<p>These are the different tests that we are going to launch, we will start with inserts and continue with queries against the database. I have not included any type of index beyond those that are created automatically with the definition of primary keys in the different databases.</p>
<h2>Insert</h2>
<p>REST call: GET&nbsp;http://localhost:8080/performance/tests/insert/<strong>{database}</strong>?total=1000</p>
<p>The variable {database} may have the following values:</p>
<ul>
<li>postgres</li>
<li>mysql</li>
<li>iris</li>
</ul>
<p>And the total attribute will be the one that we will modify to indicate the total number of insertions that we want to make.</p>
<p>The method that will be invoked is called <strong>insertRecords</strong> and you can find it in the java file <strong>PerformanceController.java</strong> located at <strong>/src/main/java/com/performance/controller/</strong>, you can see that it is an extremely simple insert:</p>
<pre class="codeblock-container" idlang="1" lang="SQL" tabsize="4"><code class="language-sql hljs"><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> test.patient <span class="hljs-keyword">VALUES</span> (<span class="hljs-literal">null</span>, ?, ?, <span class="hljs-literal">null</span>, ?, ?, ?)</code></pre>
<p>The first value is null as it is the autogenerated primary key and the second null corresponds to a BLOB/BYTEA/LONGVARBINARY type field where we will save a photo later.</p>
<p>We are going to launch the following batches of pushes: 100, 1000 , 10000, 20000 and we will check the response times that we will receive in Postman. For each measurement we will do 3 tests and we will calculate the average of the 3 values that we obtain.</p>
<table border="1" cellpadding="1" cellspacing="1" style="width: 500px;">
<tbody>
<tr>
<td style="text-align: center;">&nbsp;</td>
<td style="text-align: center;">100</td>
<td style="text-align: center;">1000</td>
<td style="text-align: center;">10000</td>
<td style="text-align: center;">20000</td>
</tr>
<tr>
<td style="text-align: center;">MySQL</td>
<td style="text-align: center;">0.754</td>
<td style="text-align: center;">8.91 s</td>
<td style="text-align: center;">88 s</td>
<td style="text-align: center;">192 s</td>
</tr>
<tr>
<td style="text-align: center;">PostgreSQL</td>
<td style="text-align: center;">0.23 s</td>
<td style="text-align: center;">2.24 s</td>
<td style="text-align: center;">20.92 s</td>
<td style="text-align: center;">40.35 s</td>
</tr>
<tr>
<td style="text-align: center;">IRIS</td>
<td style="text-align: center;">0.07 s</td>
<td style="text-align: center;">0.33 s</td>
<td style="text-align: center;">2.6 s</td>
<td style="text-align: center;">5 s</td>
</tr>
</tbody></table>
<p>Let's see it graphically.</p>

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/u94whr35ehtfo7lctk3a.png)
<p>&nbsp;</p>
<h2>Insert with a binary file</h2>
<p>In the previous example we did simple inserts, let's go to push the accelerator including in our insert a 50 kB picture as a photo for our patients.</p>
<p>REST call: GET&nbsp;http://localhost:8080/performance/tests/insertBlob/<strong>{database}</strong>?total=1000</p>
<p>The variable {database} may have the following values:</p>
<ul>
<li>postgres</li>
<li>mysql</li>
<li>iris</li>
</ul>
<p>And the total attribute will be the one that we will modify to indicate the total number of insertions that we want to make.</p>
<p>The method that will be invoked is called <strong>insertBlobRecords</strong> and you can find it in the java file <strong>PerformanceController.java</strong> located at <strong>/src/main/java/com/performance/controller/</strong>, you can check that it is an insert similar to the previous one with the exception that we are passing the file in the&nbsp;insert:</p>
<pre class="codeblock-container" idlang="1" lang="SQL" tabsize="4"><code class="language-sql hljs"><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> test.patient (<span class="hljs-keyword">Name</span>, Lastname, Photo, Phone, Address, City) <span class="hljs-keyword">VALUES</span> (?, ?, ?, ?, ?, ?)</code></pre>
<p>Let's slightly modify the number of inserts above to avoid the test taking forever and I will clean the Docker of the images to start again with a total level playing field.</p>
<table border="1" cellpadding="1" cellspacing="1" style="width: 500px;">
<tbody>
<tr>
<td style="text-align: center;">&nbsp;</td>
<td style="text-align: center;">100</td>
<td style="text-align: center;">1000</td>
<td style="text-align: center;">5000</td>
<td style="text-align: center;">10000</td>
</tr>
<tr>
<td style="text-align: center;">MySQL</td>
<td style="text-align: center;">1.87 s</td>
<td style="text-align: center;">17 s</td>
<td style="text-align: center;">149 s</td>
<td style="text-align: center;">234 s</td>
</tr>
<tr>
<td style="text-align: center;">PostgreSQL</td>
<td style="text-align: center;">0.6 s</td>
<td style="text-align: center;">5.22 s</td>
<td style="text-align: center;">23.93 s</td>
<td style="text-align: center;">60.43 s</td>
</tr>
<tr>
<td style="text-align: center;">IRIS</td>
<td style="text-align: center;">0.13 s</td>
<td style="text-align: center;">0.88 s</td>
<td style="text-align: center;">4.58 s</td>
<td style="text-align: center;">12.57 s</td>
</tr>
</tbody></table>
<p>&nbsp;</p>
<p>Let's look at the graph:</p>

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/hw61inxargposh5tf3m4.png)
<h2>&nbsp;</h2>
<h2>Select</h2>
<p>Let's test performance with a simple query that gets us all the records from the Patient table.</p>
<p>REST call: GET&nbsp;http://localhost:8080/performance/tests/select/<strong>{database}</strong></p>
<p>&nbsp;</p>
<p>The variable {database} may have the following values:</p>
<ul>
<li>postgres</li>
<li>mysql</li>
<li>iris</li>
</ul>
<p>The method that will be invoked is called <strong>selectRecords</strong> and you can find it in the java file <strong>PerformanceController.java</strong> located at <strong>/src/main/java/com/performance/controller/</strong>, the query is extremely basic:</p>
<pre class="codeblock-container" idlang="1" lang="SQL" tabsize="4"><code class="language-sql hljs"><span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> test.patient</code></pre>
<p>We'll test the query with the same set of items we used for the first insert test.</p>
<table border="1" cellpadding="1" cellspacing="1" style="width: 500px;">
<tbody>
<tr>
<td style="text-align: center;">&nbsp;</td>
<td style="text-align: center;">100</td>
<td style="text-align: center;">1000</td>
<td style="text-align: center;">10000</td>
<td style="text-align: center;">20000</td>
</tr>
<tr>
<td style="text-align: center;">MySQL</td>
<td style="text-align: center;">0.03 s</td>
<td style="text-align: center;">0,02 s</td>
<td style="text-align: center;">0.03 s</td>
<td style="text-align: center;">0.04 s</td>
</tr>
<tr>
<td style="text-align: center;">PostgreSQL</td>
<td style="text-align: center;">0.03 s</td>
<td style="text-align: center;">0.02 s</td>
<td style="text-align: center;">0.04 s</td>
<td style="text-align: center;">0.03 s</td>
</tr>
<tr>
<td style="text-align: center;">IRIS</td>
<td style="text-align: center;">0.02 s</td>
<td style="text-align: center;">0.02 s</td>
<td style="text-align: center;">0.04 s</td>
<td style="text-align: center;">0.05 s</td>
</tr>
</tbody></table>
<p>&nbsp;</p>
<p>And graphically:</p>

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/4szguxajr2knj2trqgy3.png)
<p>&nbsp;</p>
<h2>Select group by</h2>
<p>Let's test performance with a query that includes a left join as well as aggregation functions.&nbsp;</p>
<p>REST call: GET&nbsp;http://localhost:8080/performance/tests/selectGroupBy/<strong>{database}</strong></p>
<p>The variable {database} may have the following values:</p>
<ul>
<li>postgres</li>
<li>mysql</li>
<li>iris</li>
</ul>
<p>The method that will be invoked is called <strong>selectGroupBy</strong> and you can find it in the java file <strong>PerformanceController.java</strong> located at <strong>/src/main/java/com/performance/controller/</strong>, let's see the query:</p>
<pre class="codeblock-container" idlang="1" lang="SQL" tabsize="4"><code class="language-sql hljs"><span class="hljs-keyword">SELECT</span> <span class="hljs-keyword">count</span>(p.Name), c.Name <span class="hljs-keyword">FROM</span> test.patient p <span class="hljs-keyword">left</span> <span class="hljs-keyword">join</span> test.city c <span class="hljs-keyword">on</span> p.City = c.Id <span class="hljs-keyword">GROUP</span> <span class="hljs-keyword">BY</span> c.Name</code></pre>
<p>We'll test the query again with the same set of items we used for the first insert test.</p>
<table border="1" cellpadding="1" cellspacing="1" style="width: 500px;">
<tbody>
<tr>
<td style="text-align: center;">&nbsp;</td>
<td style="text-align: center;">100</td>
<td style="text-align: center;">1000</td>
<td style="text-align: center;">10000</td>
<td style="text-align: center;">20000</td>
</tr>
<tr>
<td style="text-align: center;">MySQL</td>
<td style="text-align: center;">0.02 s</td>
<td style="text-align: center;">0.02 s</td>
<td style="text-align: center;">0.03 s</td>
<td style="text-align: center;">0.03 s</td>
</tr>
<tr>
<td style="text-align: center;">PostgreSQL</td>
<td style="text-align: center;">0.02 s</td>
<td style="text-align: center;">0.02 s</td>
<td style="text-align: center;">0.02 s</td>
<td style="text-align: center;">0.02 s</td>
</tr>
<tr>
<td style="text-align: center;">IRIS</td>
<td style="text-align: center;">0.02 s</td>
<td style="text-align: center;">0.02</td>
<td style="text-align: center;">0.03 s</td>
<td style="text-align: center;">0.04 s</td>
</tr>
</tbody></table>
<p>&nbsp;</p>
<p>And graphically:</p>

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/rl5idkpl8f3h91v7dudm.png)
<p>&nbsp;</p>
<h2>Update</h2>
<p>For the update we are going to launch a query with an associated subquery within its conditions.</p>
<p>REST Call: GET&nbsp;http://localhost:8080/performance/tests/update/<strong>{database}</strong></p>
<p>The variable {database} may have the following values:</p>
<ul>
<li>postgres</li>
<li>mysql</li>
<li>iris</li>
</ul>
<p>The method that will be invoked is called <strong>UpdateRecords</strong> and you can find it in the java file <strong>PerformanceController.java</strong> located at <strong>/src/main/java/com/performance/controller/</strong>, let's see the query:</p>
<pre class="codeblock-container" idlang="1" lang="SQL" tabsize="4"><code class="language-sql hljs"><span class="hljs-keyword">UPDATE</span> test.patient <span class="hljs-keyword">SET</span> Phone = <span class="hljs-string">'+15553535301'</span> <span class="hljs-keyword">WHERE</span> <span class="hljs-keyword">Name</span> <span class="hljs-keyword">in</span> (<span class="hljs-keyword">SELECT</span> <span class="hljs-keyword">Name</span> <span class="hljs-keyword">FROM</span> test.patient <span class="hljs-keyword">where</span> <span class="hljs-keyword">Name</span> <span class="hljs-keyword">like</span> <span class="hljs-string">'%12'</span>)</code></pre>
<p>Let's launch the query and see the results.</p>
<table border="1" cellpadding="1" cellspacing="1" style="width: 500px;">
<tbody>
<tr>
<td style="text-align: center;">&nbsp;</td>
<td style="text-align: center;">100</td>
<td style="text-align: center;">1000</td>
<td style="text-align: center;">10000</td>
<td style="text-align: center;">20000</td>
</tr>
<tr>
<td style="text-align: center;">MySQL</td>
<td style="text-align: center;">X</td>
<td style="text-align: center;">X</td>
<td style="text-align: center;">X</td>
<td style="text-align: center;">X</td>
</tr>
<tr>
<td style="text-align: center;">PostgreSQL</td>
<td style="text-align: center;">0.02 s</td>
<td style="text-align: center;">0.02 s</td>
<td style="text-align: center;">0.02 s</td>
<td style="text-align: center;">0.03 s</td>
</tr>
<tr>
<td style="text-align: center;">IRIS</td>
<td style="text-align: center;">0.02 s</td>
<td style="text-align: center;">0.02 s</td>
<td style="text-align: center;">0.02 s</td>
<td style="text-align: center;">0.04 s</td>
</tr>
</tbody></table>
<p>We note that MySQL does not allow this type of subqueries on the same table that we are going to update, therefore we cannot measure their times under equal conditions. In this case, we will omit the graph as it is so simple.</p>
<h2>Delete</h2>
<p>For the delete we are going to launch a query with an associated subquery within its conditions.</p>
<p>REST Call: GET&nbsp;http://localhost:8080/performance/tests/delete/<strong>{database}</strong></p>
<p>The variable {database} may have the following values:</p>
<ul>
<li>postgres</li>
<li>mysql</li>
<li>iris</li>
</ul>
<p>The method that will be called is called <strong>DeleteRecords</strong> and you can find it in the java file <strong>PerformanceController.java</strong> located at <strong>/src/main/java/com/performance/controller/</strong>, let's see the query:</p>
<pre class="codeblock-container" idlang="1" lang="SQL" tabsize="4"><code class="language-sql hljs"><span class="hljs-keyword">DELETE</span> test.patient <span class="hljs-keyword">WHERE</span> <span class="hljs-keyword">Name</span> <span class="hljs-keyword">in</span> (<span class="hljs-keyword">SELECT</span> <span class="hljs-keyword">Name</span> <span class="hljs-keyword">FROM</span> test.patient <span class="hljs-keyword">where</span> <span class="hljs-keyword">Name</span> <span class="hljs-keyword">like</span> <span class="hljs-string">'%12'</span>)</code></pre>
<p>Let's launch the query and see the results.</p>
<table border="1" cellpadding="1" cellspacing="1" style="width: 500px;">
<tbody>
<tr>
<td style="text-align: center;">&nbsp;</td>
<td style="text-align: center;">100</td>
<td style="text-align: center;">1000</td>
<td style="text-align: center;">10000</td>
<td style="text-align: center;">20000</td>
</tr>
<tr>
<td style="text-align: center;">MySQL</td>
<td style="text-align: center;">X</td>
<td style="text-align: center;">X</td>
<td style="text-align: center;">X</td>
<td style="text-align: center;">X</td>
</tr>
<tr>
<td style="text-align: center;">PostgreSQL</td>
<td style="text-align: center;">0.01 s</td>
<td style="text-align: center;">0.02 s</td>
<td style="text-align: center;">0.02 s</td>
<td style="text-align: center;">0.03 s</td>
</tr>
<tr>
<td style="text-align: center;">IRIS</td>
<td style="text-align: center;">0.02 s</td>
<td style="text-align: center;">0.02 s</td>
<td style="text-align: center;">0.02 s</td>
<td style="text-align: center;">0.04 s</td>
</tr>
</tbody></table>
<p>We note again that MySQL does not allow this type of subqueries on the same table from which we are going to delete, therefore we cannot measure their times under equal conditions.</p>
<h2>Conclusions</h2>
<p>We can affirm that all of them are quite fine-tuned when it comes to querying data, as well as updating and deleting records (except for the incident with MySQL). Where we find the biggest difference is in the handling of inserts. IRIS is the best of the 3 by far, being 6 times faster than PostgreSQL and up to 20 times faster than MySQL at data ingestion.</p>
<p>In order to operate with large data sets, IRIS is undoubtedly the best option in the tests carried out.</p>
<p>So... we already have a winner! IRIS WINS!</p>
<p>&nbsp;</p>
<p>PS: These are some small examples of tests that you can carry out, feel free to modify the code as you wish.</p>