Infrastructure at your Service

Category Archives: Postgres

Mouhamadou Diaw

Masking Data With PostgreSQL

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

I was searching a tool for anonymizing data in a PostgreSQL database and I have tested the extension pg_anonymizer. PostgreSQL_anonymizer is a set of SQL functions that remove personally identifiable values from a PostgreSQL table and replace them with random-but-plausible values. The goal is to avoid any identification from the data record while remaining suitable for testing, data analysis and data processing. In this blog I am showing how this extension can be used. I…

 
Read More
Oracle Team

Drilling down the pgSentinel Active Session History

By | Database Administration & Monitoring, Postgres | No Comments

By Franck Pachot . In pgSentinel: the sampling approach for PostgreSQL I mentioned that one of the advantages of the ASH approach is the ability to drill down from an overview of the database activity, down to the details where we can do some tuning. The idea is to always focus on the components which are relevant to our tuning goal: Filter/Group by the relevant dimension to focus on what you want to tune (a…

 
Read More
Oracle Team

pgSentinel: the sampling approach for PostgreSQL

By | Database Administration & Monitoring, Postgres | 3 Comments

By Franck Pachot . Here is the first test I did with the beta of pgSentinel. This Active Session History sampling is a new approach to Postgres tuning. For people coming from Oracle, this is something that has made our life a lot easier to optimize database applications. Here is a quick example showing how it links together some information that are missing without this extension.  

 
Read More
Oracle Team

PGIO, PG_STAT_ACTIVITY and PG_STAT_STATEMENTS

By | Oracle, Postgres | No Comments

By Franck Pachot . For an Oracle DBA, we are used to join active sessions (from V$SESSION where status=’ACTIVE) with active statements (from V$SQL where users_executing>0) on the SQL_ID. V$ACTIVE_SESSION_HISTORY also displays the TOP_LEVEL_SQL_ID to get the entrypoint of the usercall if we need it. With Postgres it is a bit more difficult because it seems that PG_STAT_ACTIVITY do not show the active statement but only the top-level one. But pg_stat_statement collects statistics for the…

 
Read More
Oracle Team

Lighty for PostgreSQL

By | Database Administration & Monitoring, Postgres | No Comments

By Franck Pachot . If you follow this blog, you should know how I like Orachrome Lighty for Oracle, for its efficiency to monitor database performance statistics. Today Orachrome released the beta version of Lighty for Postgres: https://orachrome.com/news/la-beta-de-lighty-for-postgresql-est-ouverte/ The Cloud is perfect to do short tests with more resources than my laptop, especially the predictability of performance, then I started a Bitnami Postgres Compute service on the Oracle Cloud and did some tests with pgbench…

 
Read More
Oracle Team

Which Bitnami service to choose in the Oracle Cloud Infrastructure?

By | Oracle, Postgres | No Comments

By Franck Pachot . In the Oracle Cloud PaaS you have a marketplace where you can choose your service. Some are packaged from Bitnami and available on multiple OS. My first idea is that in PaaS you do not care about the OS. But Oracle Cloud has this very nice feature where you still have full access to the OS, as root, even in PaaS. Then, you choose the Linux distribution of your preference. Except…

 
Read More
Oracle Team

Postgres, the fsync() issue, and ‘pgio’ (the SLOB method for PostgreSQL)

By | Postgres | 2 Comments

By Franck Pachot . That’s a long blog post title, which is actually just a good pretext to play with Kevin Closson SLOB method for PostgreSQL: pgio I use the beta version of pgio here. If you want to read more about it, you can start on https://kevinclosson.net/2018/05/22/sneak-preview-of-pgio-the-slob-method-for-postgressql-part-i-the-beta-pgio-readme-file/. If you are used to the SLOB for Oracle (https://kevinclosson.net/slob/) you will quickly understand the ‘why’ and ‘how’ of pgio.  

 
Read More
Joël Cattin

PostgreSQL – logical replication with pglogical

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

Although PostgreSQL 10 integrate native logical replication (have a look here or here), it is always interesting to be aware of alternative solutions to the available in-core features. One of those is called pglogical. It’s a Postgres extension developed by 2ndQuadrant, major contributor to PostgreSQL development. The goal of this blog post is to discover how to install pglogical and configure it in order to generate simple logical replication cases. Installation To get the extension…

 
Read More
Oracle Team

Full page logging in Postgres and Oracle

By | Oracle, Postgres | 4 Comments

By Franck Pachot . 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…

 
Read More
Oracle Team

Server process name in Postgres and Oracle

By | Oracle, Postgres | No Comments

By Franck Pachot . 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…

 
Read More