How to calculate percentiles with SQL PERCENTILE_CONT
Introduction In this article, we are going to investigate how to calculate percentiles with the SQL PERCENTILE_CONT function.
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.
Index Selectivity
Introduction In this article, we are going to see how Index Selectivity works in relational database systems and why the database Optimizer might choose to avoid using an index if the number of matching records is large. Index selectivity is inversely proportional to the number of index entries matched by a given value. So, a unique index has the highest selectivity because only a single entry can be matched by any given value. On the other hand, if column values are skewed, then a column value matching a large number of table… 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.
The best way to use one-to-one table relationships
Introduction In this article, we are going to see what is the best way to use one-to-one table relationships. I decided to write this article after reading this Tweet:
SQL Operation Order
Introduction In this article, we are going to see what is the standard SQL operation order. Once you understand the order in which SQL operations are executed, then it will be clear why the Oracle legacy pagination query required a Derived Table in order to make sure that the ROWNUM pseudocolumn is computed after executing the ORDER BY clause.
PostgreSQL Auto Explain
Introduction In this article, we are going to see how the PostgreSQL Auto Explain feature works and why you should use it to gather the actual execution plan for SQL statements that execute on a production system.
The best way to use SQL NOWAIT
Introduction In this article, we are going to see what is the best way to use the amazing SQL NOWAIT feature that allows us to avoid blocking when acquiring a row-level lock. Since all the top major database support this functionality, Hibernate offers a NOWAIT option that allows you to render the proper database-specific syntax associated with this feature without risking database portability.
