Infrastructure at your Service

Category Archives: Postgres

Franck Pachot

Full page logging in Postgres and Oracle

By | Oracle, Postgres | One Comment

In my opinion, the volume of logging (aka redo log, aka xlog, aka WAL) is the most important factor for OLTP performance, availability and scalability, for several reasons: This is the only structure where disk latency is a mandatory component of response time This is a big part of the total volume of backups This is sequential by nature, and very difficult to scale by parallelizing In this post, I look at the volume of…

 
Read More
Franck Pachot

Server process name in Postgres and Oracle

By | Oracle, Postgres | No Comments

Every database analysis should start with system load analysis. If the host is in CPU starvation, then looking at other statistics can be pointless. With ‘top’ on Linux, or equivalent such as process explorer on Windows, you see the process (and threads). If the name of the process is meaningful, you already have a clue about the active sessions. Postgres goes further by showing the operation (which SQL command), the state (running or waiting), and…

 
Read More
Franck Pachot

Postgres vs. Oracle access paths IX – Tid Scan

By | Oracle, Postgres | No Comments

In the previous post we have seen how Postgres and Oracle finds the table row from the index entry. It uses the TID / ROWID. I’ll focus on this access path and I will have covered all Postgres access paths to table data. Oracle ACCESS BY ROWID I start with Oracle because we already have seen the TABLE ACCESS BY ROWID. I’ll decompose an index acces to the table. The first step is getting the…

 
Read More
Franck Pachot

Postgres vs. Oracle access paths VIII – Index Scan and Filter

By | Oracle, Postgres | 2 Comments

In the previous post we have seen a nice optimization to lower the consequences of bad correlation between the index and the table physical order: a bitmap, which may include false positives and then requires a ‘recheck’ of the condition, but with the goal to read each page only once. Now we are back to the well-clustered table where we have seen two possible access paths: IndexOnlyScan when all columns we need are in the…

 
Read More
Franck Pachot

Postgres vs. Oracle access paths IV – Order By and Index

By | Oracle, Postgres | No Comments

I realize that I’m talking about indexes in Oracle and Postgres, and didn’t mention yet the best website you can find about indexes, with concepts and examples for all RDBMS: http://use-the-index-luke.com. You will probably learn a lot about SQL design. Now let’s continue on execution plans with indexes. As we have seen two posts ago, an index can be used even with a 100% selectivity (all rows), when we don’t filter any rows. Oracle has…

 
Read More