Infrastructure at your Service

Category

Postgres

Franck Pachot

DBPod – le podcast Bases de Données

By | AWS, Cloud, Database Administration & Monitoring, Database management, Development & Performance, DevOps, NoSQL, Oracle, Postgres, SQL Server | No Comments

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…

Read More
Franck Pachot

The myth of NoSQL (vs. RDBMS) “joins dont scale”

By | AWS, Database Administration & Monitoring, NoSQL, Oracle, Postgres, SQL Server | 13 Comments

By Franck Pachot . I’ll reference Alex DeBrie article “SQL, NoSQL, and Scale: How DynamoDB scales where relational databases don’t“, especially the paragraph about “Why relational databases don’t scale”. But I want to make clear that my post here is not against this article, but against a very common myth that even precedes NoSQL databases. Actually, I’m taking this article as reference because the author, in his website and book, has really good points about…

Read More
Franck Pachot

Some myths about PostgreSQL vs. Oracle

By | Oracle, Postgres | One Comment

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…

Read More
Joël Cattin

pgBackRest – a simple privileges-related issue

By | Database Administration & Monitoring, Database management, Postgres | No Comments

I recently had to replace Barman by pgBackRest as Backup & Recovery solution for one of my customer. The purpose of this blog is not to show how I made the setup (some dbi blogs already exists for that), but only to share a small issue I encountered during the creation of the Stanza : [email protected]:/ [cms2] pgbackrest –stanza=cms2_primary stanza-create ERROR: [058]: version ’11’ and path ‘(null)’ queried from cluster do not match version ’11’…

Read More
Franck Pachot

PostgreSQL Shared Buffers vs free RAM

By | Postgres | 3 Comments

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…

Read More
Franck Pachot

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

By | AWS, Database Administration & Monitoring, NoSQL, Oracle, Postgres, SQL Server | 3 Comments

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…

Read More
Franck Pachot

AWS Aurora vs. RDS PostgreSQL on frequent commits

By | AWS, Postgres | No Comments

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…

Read More
Franck Pachot

AWS Aurora IO:XactSync is not a PostgreSQL wait event

By | AWS, Postgres | No Comments

By Franck Pachot . In AWS RDS you can run two flavors of the PostgreSQL managed service: the real PostgreSQL engine, compiled from the community sources, and running on EBS storage mounted by the database EC2 instance, and the Aurora which is proprietary and AWS Cloud only, where the upper layer has been taken from the community PostgreSQL. The storage layer in Aurora is completely different. In PostgreSQL, as in most RDBMS except for exclusive…

Read More
Franck Pachot

ysql_bench: the YugaByteDB version of pgbench

By | Postgres | No Comments

By Franck Pachot . This follows the previous post on testing YugaByteDB 2.1 performance with pgbench: https://blog.dbi-services.com/yugabytedb-2-1/ A distributed database needs to reduce inter-node synchronization latency and then replaces two-phase pessimistic locking by optimistic concurrency control in many places. This means more serialization errors where a transaction may have to be re-tried. But the PostgreSQL pgbench does not have this possibility and this makes benchmarking distributed database very hard. For example when CERN tested CoackroachDB…

Read More
Franck Pachot

YugaByteDB 2.1: the Open Source multi-region distributed database with PostgreSQL API is in GA with huge performance improvement

By | Postgres | No Comments

By Franck Pachot . 9 months ago I was looking at YugaByteDB which was still in beta version for its ‘YSQL’ API. I published my first test on Medium: https://medium.com/@FranckPachot/running-pgbench-on-yugabytedb-1-3-3a15450dfa42. I have been very enthusiastic about the idea, the architecture, the way they open-sourced it and how all was documented in their blog. I’ve even met them in Sunnyvale when I traveled to California for Oracle Open World. Great people with a great vision on…

Read More