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.
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.
Building a Distributed Audit Log with YugabyteDB
Introduction In this article, we are going to see what is the best way you can build an Audit Log using YugabyteDB. And what’s even more interesting is that this solution is almost the same on PostgreSQL. The only difference is how we are building the audit log table PRIMARY KEY, but everything else is exactly the same, demonstrating how easily you can migrate from PostgreSQL to YugabyteDB.
How to format SQL using the command line
Introduction In this article, I’m going to show you how to format SQL using the command line and transform a single-line SQL statement into a multi-line SQL string that’s way more readable.
SQL Recursive WITH CTE queries
Introduction In this article, we are going to see how SQL Recursive WITH CTE (Common Table Expression) queries work and how we can apply them for processing hierarchical data models.
