PostgreSQL FOR UPDATE vs FOR NO KEY UPDATE
Introduction In this article, we are going to investigate the difference between the PostgreSQL FOR UPDATE and FOR NO KEY UPDATE when locking a parent record and inserting a child row.
PostgreSQL Heap-Only-Tuple or HOT Update Optimization
Introduction In this article, we are going to analyze how PostgreSQL Heap-Only-Tuple or HOT Update optimization works, and why you should avoid indexing columns that change very frequently.
PostgreSQL Index Types
Introduction In this article, we are going to analyze the PostgreSQL Index Types so that we can understand when to choose one index type over the other. When using a relational database system, indexing is a very important topic because it can help you speed up your SQL queries by reducing the number of pages that have to be scanned or even avoid some operations altogether, such as sorting. If you want to receive automatic index and SQL rewrite recommendations based on your database workloads, check out EverSQL by Aiven.
PostgreSQL Performance Tuning Settings
Introduction In this article, we are going to explore various PostgreSQL performance tuning settings that you might want to configure since the default values are not suitable for a QA or production environment. As explained in this PostgreSQL wiki page, the default PostgreSQL configuration settings were chosen to make it easier to install the database on a wide range of devices that might not have a lot of resources. Not only can the QA and production system benefit from choosing the proper PostgreSQL performance tuning settings, but even a local database running… Read More
The best way to test the data access layer
Introduction In this article, we are going to see what is the best way to test the data access layer when using a relational database system. First, we will talk about the pros and cons of unit testing. While unit testing is fine for validating independent logic units, when it comes to testing the data access layer, integration testing is more useful. Next, we will discuss about the difference between integration testing and system integration testing and see the benefits of using a data platform like Aiven. With Aiven, not only can… Read More
PostgreSQL JDBC Statement Caching
Introduction In this article, we’re going to see how the PostgreSQL JDBC Driver implements Statement Caching and what settings we need to configure in order to optimize the performance of our data access layer.
YugabyteDB Architecture
Introduction In this article, we are going to explore the YugabyteDB architecture and see how it manages to provide automatic sharding and failover without compromising data integrity. YugabyteDB is a distributed SQL database, so its architecture is different than the ones employed by traditional relational database systems.
Fault Tolerance with Spring Data and YugabyteDB
Introduction In this article, we are going to see how we can achieve fault tolerance in your Spring Data application with the help of YugabyteDB. As previously explained, YugabyteDB is an open-source distributed SQL database that combines the benefits of traditional relational databases with the advantages of globally-distributed auto-sharded database systems.
YugabyteDB Connection Pooling
Introduction In this article, we are going to see the overhead of acquiring a new connection when using YugabyteDB and why connection pooling is mandatory for performance.
The best UUID type for a database Primary Key
Introduction In this article, we are going to see what UUID (Universally Unique Identifier) type works best for a database column that has a Primary Key constraint. While the standard 128-bit random UUID is a very popular choice, you’ll see that this is a terrible fit for a database Primary Key column.
