Search

Top 60 Oracle Blogs

Recent comments

Postgres

PostgreSQL on Linux: what is cached?

By Franck Pachot

.
In a recent tweet I wanted to highlight the importance of knowing what you measure with pgbench, because looking at “Transactions per second” without knowing if you are in shared buffer cache hits, or filesystem cache hit, or storage cache hit, or physical read… is just meaningless:

Recovery in the ☁ with Google Cloud SQL (PostgreSQL)

By Franck Pachot

.
In a previous post I started this series of “Recovery in the ☁” with the Oracle Autonomous database. My goal is to explain the recovery procedures, especially the Point-In-Time recovery procedures because there is often confusion, which I tried to clarify in What is a database backup (back to the basics). And the terms used in managed cloud services or documentation is not very clear, not always the same, and sometimes misleading.

DBPod – le podcast Bases de Données

By Franck Pachot

.
J’essaie quelque chose de nouveau. Je publie beaucoup en anglais (blog, articles, présentations) mais cette fois quelque chose de 100% francophone. En sortant du confinement, on reprend les transports (train, voiture,…) et c’est l’occasion de se détendre en musique mais aussi de s’informer avec des podcasts. J’ai l’impression que c’est un format qui a de l’avenir: moins contraignant que regarder une video ou ou lire un article ou une newsletter. Alors je teste une plateforme 100% gratuite: Anchor (c’est un peu le ‘Medium’ du Podcast).

Some myths about PostgreSQL vs. Oracle

By Franck Pachot

.
I originally wrote this as a comment on the following post that you may find on internet:
https://www.2ndquadrant.com/en/blog/oracle-to-postgresql-reasons-to-migrate/
but my comment was not published (many links in it… I suppose it has been flagged as spam?) so I put it there.

You should never take any decision on what you read on the internet without verifying. It is totally valid to consider a move to Open Source databases, but doing it without good understanding is a risk for your migration project success.

PostgreSQL Shared Buffers vs free RAM

PostgreSQL, like all other database engines, modifies the table and index blocks in shared buffers. People think that the main goal of buffered reads is to act as a cache to avoid reading from disk. But that’s not the main reason as this is not mandatory. For example PostgreSQL expects that the filesystem cache is used. The primary goal of shared buffers is simply to share them because multiple sessions may want to read a write the same blocks and concurrent access is managed at block level in memory. Without shared buffers, you would need to lock a whole table. Most of the database engines use the shared buffers for caching. Allocating more memory can keep the frequently used blocks in memory rather than accessing disk. And because they manage the cache with methods suited to the database (performance and reliability) they don’t need another level of cache and recommend direct I/O to the database files. But not with Postgres.

The myth of NoSQL (vs. RDBMS) agility: adding attributes

By Franck Pachot

.
There are good reasons for NoSQL and semi-structured databases. And there are also many mistakes and myths. If people move from RDBMS to NoSQL because of wrong reasons, they will have a bad experience and this finally deserves NoSQL reputation. Those myths were settled by some database newbies who didn’t learn SQL and relational databases. And, rather than learning the basics of data modeling, and capabilities of SQL for data sets processing, they thought they had invented the next generation of persistence… when they actually came back to what was there before the invention of RDBMS: a hierarchical semi-structured data model. And now encountering the same problem that the relational database solved 40 years ago. This blog post is about one of those myths.

AWS Aurora vs. RDS PostgreSQL on frequent commits

This post is the second part of https://blog.dbi-services.com/aws-aurora-xactsync-batch-commit/ where I’ve run row-by-row inserts on AWS Aurora with different size of intermediate commit. Without surprise the commit-each-row anti-pattern has a negative effect on performance. And I mentioned that this is even worse in Aurora where the session process sends directly the WAL to the network storage and waits, at commit, that it is acknowledged by at least 4 out of the 6 replicas. An Aurora specific wait event is sampled on these waits: XactSync. At the end of the post I have added some CloudWatch statistics about the same running in RDS but with the EBS-based PostgreSQL rather than the Aurora engine. The storage is then an EBS volume mounted on the EC2 instance.