In PostgreSQL up to version 16, there is no way to evict the buffer cache except by restarting the instance. In Oracle you can do that since ages with “alter system flush buffer cache“, but not in PostgreSQL. This will change when PostgreSQL 17 will be released later this year. Of course, flushing the buffer cache is nothing you’d usually like to do in production, but this can be very handy for educational or debugging purposes. This is also the reason why this is intended to be a developer feature. For getting access to the pg_buffercache_evict function you need to install the pg_buffercache ..read more

Creating REST APIs is a fairly repetitive task. You’re always rewriting the Create, Read, Update, Delete methods… In my search for a framework capable of generating a REST API, I discovered Feathers, a simple and useful framework for Node.js. In this article, we’ll show you how to create a REST API from a PostgreSQL database in just a few minutes. Introduction to Feathers Feathers is a framework for creating APIs in TypeScript or JavaScript. It can connect to various SQL (PostgreSQL, MySQL, Oracle, MSSQL…) or NoSQL (MongoDB) databases. It can generate a REST or real-time API (web-socket), a C ..read more

I’m on a mission to tackle scary topics around DevOps these days! In my 2 previous blog posts (Part 1 and Part 2) I’ve tried to demystified the DevOps word (and world!) by describing what it actually is with a simple example. But there is an even scarier topic (true story!), which is… deploying a database in Kubernetes! Database is to Kubernetes what the garlic is to the vampires! Yes you get the picture below but it doesn’t have to be that way! It is a common belief that it is not recommended or even possible for production databases and some people just move away from it. However other peop ..read more

1. Introduction In the world of PostgreSQL database management, ensuring robust backup strategies is mandatory. This week I discovered on postgresql.org latest news one useful tool that streamlines this process: pg_back. pg_back serves as a dedicated dumping tool designed for PostgreSQL. Its primary objective is to facilitate the comprehensive dumping of either all or specific databases, along with global entities, in a user-specified format. This distinction is crucial, as a conventional call to pg_dumpall only exports databases in the plain SQL format. Under the hood, pg_back make use of pg_ ..read more

Last week was the week of the European PostgreSQL Conference in Prague which I was allowed to join once more. An amazing location was waiting for all the participants and once I entered the hotel, I already felt warmly welcomed in the PostgreSQL Community world. Besides the very interesting program the committee prepared this year, there was of course a lot of time for some community exchange and of course a lot of delicious food (once again). I think there should be a special mention that the talks covered a wide range of Technical talks, but also some non-technical talks, which are of grow ..read more

I know,  few years ago, it was uncredible to associate SQL Server & PostgreSQL or see Microsoft in the Open source world. The world changes and we follow it… My colleague Daniel Westermann will enjoy that I write a blog about PostgreSQL but also SQL Server… My first one in 10 years by dbi services but it’s good to begin new challenges! I will also thanks Daniel to share with me the information and he knows my curiosity… I write this article to introduce this new Open Source RDBMS  based on PostgreSQL (I don’t believe what I writing) and can accept T-SQL queries… Ok more seriou ..read more

Introduction TimescaleDB is a “time-series” database (TSDB). This kind of databases are optimized for storing, manipulating and querying time-series data. But… what is time-series data ? Time-series data is a collection of metrics (regular) or measurements (irregular) that are tracked over time. In other words, time-series data is data that collectively represents how a system, process, or behavior changes over time. As you can imagine, time-series database are not new. The first time-series databases were mainly used for storing financial data. Today, time-series data are everywhere and we c ..read more

Is the Oracle Cloud a hot topic for you? dbi services organized an event about the Cloud today in Lausanne: “Oracle Cloud Infrastructure: everything for an efficient migration“. After a welcome coffee to meet each other, Pascal Zanette shortly introduced the event and the agenda of the day. Our CTO Hervé Schweitzer presented then YaK, developed by dbi services. The idea was born 2 years ago, to find a solution to deploy Oracle instances on several Cloud providers (Oracle, Microsoft Azure, AWS) and On-Premises: – in the same heterogeneous way – avoiding constraints of each Cloud providers solu ..read more

Introduction Hi everybody If you work with databases, you will be familiar with the two main philosophies used in this domain: The SQL philosophy (RDBMS) and, of course, NoSQL databases. Some examples of SQL databases are Oracle and Postgres, while Nosql databases are MongoDB and Redis. How do these two philosophies work? Is it possible to switch from one language system to the other,for example for a migration? What are the main corresponding commands form SQL to MongoDB commands ? Let’s find out! MongoDB vs SQL concept MongoDB and traditional relational database share a lot of points and sim ..read more

pgFormatter is a command-line tool used to format SQL code for PostgreSQL databases. It helps improve code readability and maintainability by applying consistent indentation and formatting rules to your SQL queries. This guide will walk you through the process of installing and using pgFormatter on a Linux system, specifically openSUSE. I. Installation For this guide, I will install pgFormatter using the sources. The latest version at the time I wrote this blog was 5.5. 14:33:06 postgres@localhost:/home/postgres/ [PG16] version=5.5 14:33:32 postgres@localhost:/home/postgres/ [PG16] wget https ..read more