A Case Study: Oracle Database vs. PostgreSQL

 Considering the best database management system for your organization? Let's explore the key differences between Oracle Database and PostgreSQL, two powerful contenders in the database landscape.

Facts: Native vs pglogical Logical Replication in PostgreSQL

Here are some of the facts about Native vs pglogical Logical Replication in PostgreSQL 

Trace performance issues in PostgreSQL using the pg_stat_statements

👉 The pg_stat_statements view is a PostgreSQL extension that provides information about the execution statistics of all SQL statements that have been executed by the PostgreSQL server. 

PostgreSQL VACUUM and MVCC

   PostgreSQL uses MVCC (multi-version concurrency control) to provide transactional semantics. In the simplest form, MVCC lets each transaction work on a different copy of a row in a table. What it means is whenever a row is updated, a new version of the row is created.

Feedspot - Top database blog list

Dear All,

I am pleased to inform  that I received this recognition from Feedspot and I'm proud to be part of the awesome family- Thank you Anuj and Team for this recognition!

Oracle or PostgreSQL

 

At the point when you take a gander at PostgreSQL versus Prophet data set administration frameworks, the primary distinction between these two information bases is that PostgreSQL is an open-source data set, while Oracle is a shut data set framework.

PostgreSQL SSL Setup on Linux platform

PostgreSQL has native support for using SSL connections to encrypt client/server communications for increased security.

Installation and setting up Postgres 13 on RHEL

Setting up the PostgreSQL RDBMS in Red-Hat Linux is pretty easy.  Following article describes the installation and initial setup for a PostgreSQL database.

Official documentation : https://www.postgresql.org/download/linux/redhat/

PostgreSQL Basic & Required Parameter Setting

    PostgreSQL works even if you start using it with the default settings without changing the parameter settings, but there may be various problems later, especially the default of performance-related parameters.

Postgres Frequently used commands - CHEAT SHEET

 \db- List all tablespaces

\dn- List all schemas

Postgresql Database Starts with 100% CPU processes ?

If you have a problem with PostgreSQL, it starts some processes that consume lots of resources. You kill those processes but they will restart after a few minutes.

Later you check the /var/log but there is nothing about this. What does that mean?😟

Postgres High Availability Terms

 What is High Availability?

It is the amount of time that a service is available and is usually defined by the business.

Possible ways to recover space from deleted rows with insufficient disk space remaining for full vacuum

 A recent scenario,

We had a single table with about 50M records that use around 80gb of space. Application recently deleted older records which was no longer needed reducing the Number of records to 30M.

The Surprising Impact of Medium-Size Texts on PostgreSQL Performance

 This is a great explanation of Postgres toast internals for anyone who doesn't know about them 

https://hakibenita.com/sql-medium-text-performance

PostgreSQL : Influencing Query Execution Plan

By enabling and disabling the following parameters, we can influence the query execution plan generated by the optimizer.

Create Extension Fails : ERROR: could not access file "$libdir/dblink"

 

After my recent DB upgrade activity, I was unable to create extension and was failing with the below errors.

Find Uptime in PostgreSQL

 There are various ways to know since how long PostgreSQL is running. 

Below will cover 3 ways to find this.

PostgreSQL Set DB Timezone

If you plan to work with date and time data in PostgreSQL, you’ll want to ensure that you’ve set the correct time zone for your location. Fortunately, it’s easy to make this change using the psql command-line interface. In this article, we’ll explain how to connect to PostgreSQL and set the time zone.

Clear RAM cache, buffer and swap space in Linux

 

Clean up the page-cache :

   # sync; echo 1> / proc / sys / vm / drop_caches

  

Clean dentrie and inode caches:

     # sync; echo 2> / proc / sys / vm / drop_caches

 

To clean the page-cache, dentrie and inode caches:

     # sync; echo 3> / proc / sys / vm / drop_caches

 

# swapoff -a && swapon -a

 

1) Shutdown the database and its services (pg_ctl stop , sudo service postgresql stop, etc.)

2) sync

3) sudo echo 3> / proc / sys / vm / drop_caches

4) Start the database server

 

free && sync && echo 3> / proc / sys / vm / drop_caches && free

Found this post interesting? Subscribe us  😊😉

Read more 

Configure Streaming Replication in PostgreSQL

All about Physical Replication and Log shipping in Postgres 

Streaming-Replication Sync and Async, benefits of streaming replication over Log-shipping

Streaming replication - Sync and Async, benefits of streaming replication over Log-shipping in Postgres

In my previous post here, I tried to explain how the log shipping works and its advantages. Let's see more about Streaming replication in Postgres.