I’m running databases and knowing if the pressure is on CPU, RAM or I/O is crucial, and that’s not easy to infer from the metrics provided in CloudWatch or OS usual monitoring. Recent Linux kernels provide PSI (Pressure Stall Information) for that, so let’s enable it.

I have EC2 instances provisioned from
aws-marketplace/CentOS Linux 7 images but Centos is not moving fast and has an old kernel:

[yugabyte]$ cat /etc/system-releaseCentOS Linux release 7.4.1708 (Core)[yugabyte]$ uname --kernel-release3.10.0-693.5.2.el7.x86_64

I need a more recent one which I’ll install from ELRepo

[yugabyte]$ sudo yum update -yNo packages marked for update…

In a past post I detailed how the most common RDBMS can avoid the most expensive operation in an access by index, the lookup to the rows scattered in the table, with Index Only Scan. I mentioned the limitation with PostgreSQL where the ACID visibility of the row is not stored in the index and then Index Only Scan makes sense with freshly vacuumed tables only. There’s another limitation, which is easy to workaround with a little redundant storage in the index or the table.

Here is my table without indexes for the moment:

postgres=# set enable_bitmapscan=false;
SET
postgres=# create table demo…

With MVCC databases, a transaction sees a virtual snapshot of committed data, as of the beginning of the statement (in READ COMMITED) or the transaction (in REPEATABLE READ or SERIALIZABLE). In Oracle 9i , this capability has been enhanced so that we can define a snapshot that is even earlier. This was called Flashback Query. Let’s see something that, in PostgreSQL, can be equivalent in some cases.

I’ll run multiple concurrent sessions for this example

psql -v PROMPT1="$((1+$(jobs|wc -l)))%R%x%# "

This connects psql and sets the prompt to the job number.

1=# drop table if exists demo;
DROP TABLE
1=# create table…

You know the powerful psql command line to connect to a PostgreSQL database. In the YugabyteDB binaries, you find it under the name ysqlsh to be consistent with the ycsqlsh (YCQL the Cassandra-like API) but it is the same as the PostgreSQL one. The YSQL API is actually based on PostgreSQL code: same protocol, same SQL (and PL/pgSQL), and same open source license. But if you use many database, SQL or not, and want a common client, usql is for you, very similar to psql.

I’ll show how to connect to YugabyteDB and that’s also the occasion to connect to…

I know jOOQ for a while, and I’ve recommended it many times to database developers because it overcomes two of the major problems of SQL:

• SQL queries being embedded as character strings, without compile-time validation
• SQL syntax being dependent on the database engine

But, as I’m not a Java developer, I actually never used it myself. This was in my to-do for a long time, so here is my first jOOQ program 😎 The occasion is there: verify that it works with YugabyteDB. …

In a distributed database, some operations are offloaded to the storage layer in order to avoid shipping though the network a large number of rows that will be filtered out or aggregated later. I said offloaded because that’s the term you know if you heard about Oracle Exadata, but this can be confusing with offloading reads on replicas. YugabyteDB started to support mostly all SQL (PostgreSQL) features and improves with pushdowns some of them, to the storage layer (DocDB), when the need for optimization comes. As it is used mostly for OLTP workloads, aggregations on a large number of rows…

Here, you are building a general architecture mantra (stored procedures are bad) from a context-specific experience (SO had bad performance, You had a monolithic DB that didn't scale out, Your test/deployment procedures works better with application, Some database do not accept common languages to write procedures, "Top Architects" says ...).

Because of rumors from others bad experience, you immediately discredit the performance point, which is the most important when it comes to where to run the code. But think about the physics of it: how can you get better performance by adding network roundtrips, system calls and context switches between…

Here is a little post about pg_hint_plan to set the cardinality estimations for the PostgreSQL query planner. The postgres optimizer can do great estimations when provided the correct statistics, and this cost based optimization is best approach as it adapts to the change of data. However, for a stable OLTP application, you want to keep it stable. And providing the cardinalities in the query may be a way to achieve that. Note that you don’t need to provide the exact number of rows, but a relevant model for the data access order and path.

$ psql postgres://franck:switzerland@yb1.pachot.net:5433/yb_demo_northwind
psql (14beta1, server 11.2-YB-2.7.1.1-b0)
Type…