PostgreSQL Shared Buffers: A Production DBA Guide

A PostgreSQL server had just returned from planned operating-system maintenance. Recovery completed cleanly, connections were available, and the database log showed nothing unusual. Yet application latency was several times higher than before the restart, read IOPS had increased, and the first recommendation was to increase shared_buffers.

That may help, but it is not a diagnosis. The PostgreSQL cache is cold after a restart. A shared-buffer miss may be served by the operating-system page cache or may reach storage. A query can also have an excellent cache-hit ratio and still be slow because it processes millions of cached blocks. On the write side, dirty buffers bring WAL, the background writer, client backends, and the checkpointer into the same performance path.

This article explains what PostgreSQL keeps in shared buffers, how cache hits and misses should be interpreted, and what happens when pages are modified. It also covers practical sizing, monitoring SQL, common production failures, and the operational differences Oracle DBAs should expect when moving from the SGA buffer cache to PostgreSQL.

Oracle RAC - Oracle DBA Interview Questions with Practical Answers

Oracle DBA interviews, especially for senior roles, usually go beyond definitions. The discussion often moves quickly into troubleshooting, recovery, RAC, Data Guard, patching, GoldenGate, and real production scenarios.

Below are some recent Oracle DBA interview questions reportedly asked in a product-based organization, along with practical answers and the checks I would expect a DBA to discuss during an interview.

My Learning and Exam Experience – Oracle Agentic AI Foundations Associate [1Z0-1157-26]

I recently completed the Oracle 1Z0-1157-26 – Agentic AI Foundations Associate exam.

I had been reading and learning about AI agents, MCP, vector search and some of the newer AI capabilities around Oracle for a while, so this certification came at a good time. I decided to go through the complete learning path rather than preparing only from an exam point of view. And I think that is the right way to approach this one.

Oracle Standby Redo Logs: The Gap Most DBAs Miss

The first warning often appears during a DR drill. Redo transport is healthy, managed recovery is running, and the standby looks ready. Then Data Guard Broker reports insufficient standby redo logs, or apply lag starts rising after an online redo log resize even though the network has no visible problem.

This is why I do not validate standby redo logs only by checking whether rows exist in V$STANDBY_LOG. The count must be correct for every redo thread, the sizes must match the primary redo layout, RFS must actually be using them, and the same preparation should exist on the current primary for the next role transition.

There is another misconception worth clearing up early. Standby redo logs improve how received redo is stored and applied. They do not convert asynchronous transport into a guaranteed zero-data-loss service. Protection mode, transport progress and apply progress are three different parts of the Data Guard path.

ORA-12547 from SRVCTL While SQL*Plus Startup Works – Tracing It Back to a Post-Patching Relink Issue

I recently worked on an Oracle database that could be started normally from SQL*Plus, but failed every time Clusterware tried to start it through srvctl. The error returned by Clusterware was ORA-12547: TNS:lost contact.

The final fix was to relink the database Oracle Home, but I did not reach that conclusion from the error message alone. The useful clues came from comparing SQL*Plus and SRVCTL startup behaviour, noticing what was happening to the Oracle executable during the SRVCTL attempt, and then going back through the last database startup and patching history.

Oracle CPU Spike After Statistics Gathering: A Real-World Optimizer Investigation

Every Oracle DBA has experienced that moment when monitoring alerts suddenly explode across the dashboard. Everything was running smoothly, and then without a warning - the CPU utilization jumps to 95%, applications slow down, and users start reporting performance issues.

One of the most frustrating parts of such incidents is when nothing obvious changed. No deployments, no schema modifications, and no new batch jobs. Yet the database is clearly struggling.

In many real-world cases, the root cause is surprisingly subtle: a change in optimizer statistics leading to a different execution plan. Oracle's automatic statistics gathering is powerful, but it is not always perfect and especially when data distribution is skewed or histograms behave unexpectedly.

In this article, we will walk through a real-world Oracle CPU spike investigation. You will see the step-by-step troubleshooting approach using AWR, execution plan history, and histogram analysis. We’ll also cover how the issue was fixed using manual statistics and SQL plan baselines to stabilize performance.

Split Brain in Oracle RAC: What Every DBA Must Know

   If you have worked with Oracle RAC long enough, you know the cluster is only as strong as its communication layer. Most days, RAC behaves beautifully ; instances coordinate, cache fusion works seamlessly, and life is good. But when inter-node communication breaks down, things can get ugly very quickly.

PostgreSQL Crash Recovery: An Oracle DBA’s View

  The first PostgreSQL crash I handled felt unusually quiet. I was prepared to mount the database, check the required logs and start a recovery procedure. Instead, PostgreSQL detected the unclean shutdown, replayed WAL and opened the database without any DBA command.

That automatic restart can make crash recovery appear simpler than it really is. Recovery time still depends on checkpoint activity, WAL volume, storage performance and the availability of required WAL files. A database that normally starts in seconds can behave very differently after a write-heavy workload or storage incident.

Oracle Indexes: Fix Slow SQL Without Overdoing It

A SQL query running for 8 seconds may not create panic during testing. In production, the same query can become a real incident when it runs thousands of times from an application, report, or batch process.

I have seen queries drop from seconds to milliseconds after adding one correct index. I have also seen OLTP systems become slower because every column was indexed without understanding the workload. Both situations are common.

Indexes are powerful, but they are not free. They reduce unnecessary reads for SELECT queries, but they add cost to INSERT, UPDATE, DELETE, storage, statistics gathering, and maintenance. A good DBA does not create indexes blindly. A good DBA checks the SQL pattern, data volume, selectivity, execution plan, and write workload before touching production.

The Oracle DBA Role in the AI Era - See how Oracle AI Database 26ai, Autonomous AI Database and MAA are reshaping the DBA role across reliability, security and enterprise AI

Database automation has removed plenty of manual effort, but it has not removed operational accountability. Oracle can now automate provisioning, patching, backup, scaling, and parts of performance management. At the same time, applications are becoming more distributed, security boundaries are expanding, and AI workloads are placing new demands on enterprise data.

Oracle Key Vault: DBA Guide for TDE Keys and scenarios

A database backup can finish successfully and still fail during recovery.

That usually sounds strange until encryption enters the picture. RMAN backup is available, archive logs are available, Data Guard is in sync, but during restore someone asks the real question: where is the TDE wallet or master key?

This is where many production environments become risky. TDE wallets are present on database servers, Java keystores are owned by middleware teams, SSH keys are copied between hosts, certificates are renewed manually, and old wallet backups are kept in folders nobody wants to touch.

Sometimes It Is Not One Big Problem. It Is Everything Running in the Background.

After more than 12 years of working with production databases, I have learned that performance problems do not always begin with one terrible query.

Sometimes it is several smaller things happening together. One session is using slightly more CPU, another is holding a lock, a background job is running longer than expected, and storage latency has quietly increased. None of them looks disastrous alone, but together they make the whole system struggle.

Oracle Redo Logs: Sizing Without Guesswork

Redo log problems rarely start with a clean error message. They usually show up as slow commits, archive destinations filling faster than expected, Data Guard apply lag, or batch jobs that suddenly take longer during month-end. By the time someone checks the alert log, the database may already be switching logs too frequently, checkpointing aggressively, or waiting for archiving to catch up.

A common reaction is to increase redo log size and move on. Sometimes that is required, but it is not always the real fix. Redo log sizing controls how frequently Oracle switches logs. It does not reduce the amount of redo generated by bad SQL, unnecessary indexes, row-by-row commits, or poorly designed batch jobs.

This post looks at Oracle redo log sizing from a production DBA angle. We will check log switch frequency, estimate redo generation rate, review MTTR guidance, discuss common failure scenarios, and compare the idea briefly with PostgreSQL WAL pressure.

Oracle 26ai AWR Enhancements for DBAs

 AWR used to be simple when most databases were single-instance, single-tenant, and all important workload stayed on the primary database.

That world is mostly gone.

Today, one CDB may host multiple PDBs. Reporting may run on Active Data Guard. Read traffic may be pushed toward True Cache. Replication may be handled through GoldenGate or XStream. When performance breaks, the first question is no longer just “what was the top wait event on the database?”

pg_switch_wal() in PostgreSQL: Small Command, Big Recovery Impact

A PostgreSQL database can look completely healthy until the day you try to restore it.

Backups are available. WAL archiving is enabled. The archive directory has files. Monitoring shows green. Then, during PITR, PostgreSQL throws something like:

The Outage Usually Begins Before the Database Fails

A database incident rarely begins when the monitoring system raises an alert. It usually begins weeks earlier - with an unreviewed execution plan, an ignored capacity trend, an oversized privilege, or a recovery procedure that nobody has tested.

By the time the DBA receives the call, the database is often exposing an operational weakness that was already present. The immediate symptom may be exhausted storage, a failed deployment, excessive I/O, or a missing object, but the underlying cause is frequently a gap in engineering discipline.

I have seen expensive Oracle platforms fail because nobody noticed that the Fast Recovery Area was growing rapidly. I have also seen relatively modest PostgreSQL environments remain stable for years because the team consistently reviewed changes, monitored capacity, controlled access, and tested recovery.

RESETLOGS and Backups: Debunking a Common Oracle Myth

 If you have ever recovered an Oracle database and opened it with RESETLOGS, chances are someone whispered or shouted—the dreaded question: “Are all my old backups now useless?” Lets set the record straight: in modern Oracle releases, your old backups are still very much usable, and panicking is optional.

In this post, I wil walk you through why this myth exists, how Oracle’s handling of RESETLOGS has evolved over the years, and what you should do to ensure your recovery strategy remains solid. We will cover the mechanics of RESETLOGS, explain the differences between pre-10g version and modern behaviour, and share some practical tips I have learned from managing production environments with terabytes of data. By the end, you you will understand why you can sleep a little easier after recovery and make smarter backup decisions.

DB Time vs CPU: The Metric Most DBAs Ignore

 If you have worked in production environments long enough, you have probably seen this situation before. An alert fires in the middle of your daily shift appears.. as "CPU is at 90%. The database must be overloaded." 

Infrastructure teams immediately start discussing scaling CPU, adding cores, or moving the database to a bigger server. But experienced DBAs know something important: High CPU utilization does not automatically mean the database is the bottleneck.

How Oracle RAC Handles a Node Failure: Quick Insights About Interview Discussion

 In high‑availability database environments, About Interview discussion often centers on how systems react when things go wrong .,  especially in mission‑critical deployments like Oracle RAC (Real Application Clusters). One of the most common interview questions DBAs face is: In a 3‑node RAC, if one node goes down, how does instance recovery occur?

 Understanding this not only helps you ace interviews but also equips you with real‑world insights into RAC's fault‑tolerance mechanics.

pg_gather: PostgreSQL Snapshot Tool Every DBA Should Use

 Assume Its 2 AM. Alerts are firing. CPU is maxed out, connections are piling up, and the application team is already asking for an ETA.

You SSH into the server, open psql, and start your usual routine - check pg_stat_activity, look at locks, scan logs, maybe run a few custom queries you’ve built over the years. Fifteen minutes in, you still don’t have the full picture.

This is exactly where things break down in production - - not because PostgreSQL lacks visibility, but because the data is scattered.

That is where pg_gather changes the game.

Oracle PDB Point-in-Time Recovery Without Downtime of other PDBs

 Most Oracle outages do not begin with hardware failure.

They start with a bad deployment, an accidental delete statement, a broken batch job, or a developer connecting to the wrong pluggable database at 2 AM. In a large multitenant environment, that usually means one application becomes corrupted while dozens of other applications inside the same CDB continue running normally.

Years ago, recovering from that kind of incident often meant painful decisions. Either accept application-level data loss or restore the entire database and impact every tenant sharing the environment. Neither option was ideal for production systems running critical workloads.

PostgreSQL vs Oracle: Choosing the Right Database for Your Architecture

  Selecting a database today isn’t just about technology - it's a strategic business decision. In production environments, the choice between PostgreSQL and Oracle Database affects scalability, reliability, compliance, and cost for years. As a DBA, architect, or infrastructure engineer, understanding the trade-offs between these systems can save significant headaches during implementation and future growth.

PostgreSQL Checkpoint Tuning for Stable Performance

   If you have been running PostgreSQL in production for a while, you have probably seen this pattern. Everything looks fine on the surface, queries are tuned, indexes are in place, and yet the system slows down at regular intervals. No obvious reason. No runaway query. Just sudden latency.

In many of these cases, the real issue is not in the foreground workload but in the background engine, specifically checkpoints.

PostgreSQL HugePages Explained for DBAs

   If you have spent years tuning Oracle Database, HugePages is probably second nature to you. You would not even think about running a large SGA without it.

Now, when moving into PostgreSQL, many DBAs assume memory works differently or that HugePages are optional. Technically, they are optional. Practically, ignoring them in a serious production system is a mistake I have seen more than once.

PostgreSQL relies heavily on shared memory, especially for its buffer cache. As systems scale and memory grows into tens or hundreds of GB, the way Linux manages memory pages starts to matter a lot. That is where HugePages step in.

In this article, I will walk through how HugePages behave in PostgreSQL, how they differ from Oracle, and what actually matters when you enable them in real production environments. More importantly, I will share the kind of operational lessons you only learn after seeing systems misbehave at 2 AM.

Oracle 26ai Read-Only Users and Sessions

One very common production request sounds simple:

"Can we give this user access, but make sure they cannot change anything?"

Before Oracle AI Database 26ai, DBAs usually handled this by creating a separate reporting user, granting only SELECT, removing DML privileges, or depending on carefully designed roles. That approach works when access is cleanly designed from the beginning. But in real production systems, users often collect privileges over time. Support users get emergency grants, application users may have broader access than expected, and batch accounts sometimes have privileges that nobody wants to touch during an incident.

PostgreSQL Performance Tuning That Survives Production

    A PostgreSQL performance issue rarely starts with one bad setting.

In production, it usually looks like this: the application team says the database is slow, CPU is not always high, storage graphs look confusing, and nobody changed anything “major”. Then we check deeper and find long transactions, dead tuples, stale statistics, unused indexes, chatty application queries, or checkpoint pressure.

PostgreSQL VACUUM: Bloat, Autovacuum and Real Fixes

    A PostgreSQL table can grow quietly for weeks before anyone notices. The application team says they already deleted old data. Storage still looks high. Queries are touching more blocks than expected. Autovacuum is running, but the table does not seem to become smaller. This is where many DBAs first realize that DELETE in PostgreSQL is not the same as physically removing rows from the table file.

PostgreSQL uses MVCC, so old row versions remain inside the table until VACUUM can clean them. This is normal behavior, not a bug. The problem starts when dead tuples grow faster than VACUUM can remove them, or when long-running transactions prevent cleanup. Then you get table bloat, index bloat, stale statistics, poor plans, unnecessary I/O, and sometimes transaction ID wraparound pressure.

My Oracle ACE Journey: From Practical Sharing to Oracle ACE Pro

  Some journeys do not begin with a plan. They begin with curiosity, consistency, and a simple intention to share what we learn.

My Oracle ACE journey is one such journey.

Recognized Among FeedSpot’s Top 70 Database Blogs to Follow in 2026

I am happy to share that Learn DBA : A Life Long Learning Experience has been selected by FeedSpot as one of the Top 70 Database Blogs to Follow in 2026.

RMAN Restartable Backups Explained for Production DBAs

Most DBAs have seen this at least once. You wake up, check the overnight backup report, and RMAN failed halfway through a 14 TB database backup because the backup filesystem filled up, a network mount disconnected, or one RAC node crashed during the run.

ORA-01017 in RAC 12c and above ? Stepwise Permission Fix & Cause identification

  As an Oracle DBA, few things are more frustrating than a sudden loss of remote connectivity right after a routine SYS password reset. You type in the credentials, and bam -- ORA-01017 greets you, even though your local connections work fine. In production RAC environments, this isn’t just about a mistyped password.

Recently, I faced a tricky scenario in an Oracle 19c RAC setup with proper role separation between the grid and oracle OS users. What seemed like a simple password mismatch quickly unraveled into a multi-layered “permission deadlock,” involving rogue listeners, contaminated IPC sockets, and GPnP directory access issues. It took a careful, stepwise approach to restore connectivity across all nodes without compromising the cluster.

Oracle RAC Load Balancing Internals Explained

 Most RAC clusters look healthy until the workload shifts suddenly.

A reporting job starts hammering one node. Connection pools keep sending sessions to the same instance. CPU climbs, gc waits spike, application response times become unpredictable, and suddenly everyone starts blaming storage, SQL plans, or the network.

But many times, the real problem sits in the RAC connection routing layer itself.

I have seen large RAC environments where all nodes were technically UP, yet one instance was drowning while another sat nearly idle. The cluster wasn’t failing. The load balancing strategy was.

Oracle RAC load balancing is often misunderstood because people assume SCAN alone magically distributes workload intelligently. It does not.

Oracle Data Pump Migrations: What Breaks in Real Upgrades

Most DBAs discover the real complexity of Oracle database migrations only after the first large-scale Export/Import cutover goes sideways. On paper, Oracle Data Pump looks straightforward. Export the database, import it into a newer release, validate the objects, switch applications, done.

Reality is usually messier.

PostgreSQL Backup Guide: pg_dump vs pg_dumpall

 If you have ever been in a production outage where a single schema was corrupted or a developer dropped the wrong table, you already know one thing ; your backup strategy is only as good as your restore flexibility.

In PostgreSQL, logical backups using pg_dump and pg_dumpall are your first line of defense for granular recovery. Unlike physical backups, they give you the ability to restore specific objects, migrate databases across environments, and even troubleshoot data inconsistencies without touching the entire cluster.

Essential Oracle Database Keywords for DBAs - Part 2

When you are preparing for an Oracle DBA interview — or troubleshooting a production issue at 2 AM — definitions alone are not enough. You need clarity. You need context. And most importantly, you need to understand how Oracle behaves under pressure.

When Oracle Uses PGA Instead of SGA for Large Table Scans

Most Oracle DBAs learn early in their careers that the System Global Area (SGA) is the primary memory structure used for caching data blocks. The assumption is simple: data blocks are read from disk, placed in the buffer cache, and reused by future sessions.

However, that assumption doesn’t always hold true in production.

In many real-world workloads , especially analytics queries, reporting jobs, or large batch processing-- . Oracle intentionally bypasses the buffer cache and reads data directly into the Program Global Area (PGA). This behaviour surprises many DBAs when they notice unusual wait events like direct path read, or when frequently queried tables seem to generate repeated disk I/O.

Adding an Instance to Oracle RAC: Step-by-Step Guide for DBAs

 Expanding your Oracle Real Application Clusters (RAC) database by adding a new instance can be intimidating, especially if you want to ensure zero downtime and seamless integration. Whether you're introducing a new node or simply adding an instance to an existing one, the process involves careful planning, configuration, and execution. In this guide, we’ll walk you through every step, from preparing the environment to verifying the new instance’s operation. By the end of this article, you will have a clear understanding of both DBCA and SRVCTL methods for adding instances, best practices for shared storage and network setup, and tips to avoid common pitfalls.

Oracle Utilities for DBAs: Complete Guide to Essential Tools

 Managing an Oracle Database efficiently requires mastering a variety of utilities designed for administration, data movement, performance tuning, and troubleshooting. Whether you’re performing backups, moving data, monitoring performance, or tuning SQL queries, Oracle provides tools to streamline every task. In this guide, we’ll cover the most commonly used Oracle utilities, grouped by functionality, with practical examples and best practices for real-world DBA scenarios.

Understanding Oracle 19c DML Internals for OLTP Performance

For Oracle DBAs managing high-volume OLTP (Online Transaction Processing) systems, understanding how core DML (Data Manipulation Language) operations function under the hood is essential. SELECT, INSERT, UPDATE, and DELETE statements are the backbone of any database, but their internal mechanics—parsing, execution, undo/redo generation, buffer cache interactions, and transaction control—can significantly impact system performance.

Oracle 26ai ASC: Fast Loads Without HCC Pain

 Most DBAs who run large Oracle data warehouses already know the trade-off.

The ETL team wants maximum direct-path load speed because nightly ingestion windows keep shrinking. The storage team wants Hybrid Columnar Compression (HCC) everywhere because storage costs are exploding. Meanwhile, DBAs get stuck in the middle trying to balance ingest performance, archive growth, Smart Scan efficiency, backup footprint, and query response times.

Oracle Backup Success Story : Predictable Backups, Confident Restores - Oracle Features for Modern VLDBs

 When we talk about database growth, we usually celebrate it. But growth without backup redesign is silent risk.

This was the story of a 60.5 TB Oracle production database, where individual datafiles had grown between 500GB to 800GB+, and backups were quietly destabilizing the entire ecosystem.

What started as a "long backup" issue turned into something much deeper.

Essential Oracle Database Keywords Every DBA Should Know - Part 1

 Whether you are just stepping into the world of Oracle databases or have years of experience managing complex environments, understanding the foundational keywords and concepts is crucial. Oracle databases come with a rich ecosystem of terms, from memory structures and wait events to transaction control and performance monitoring. This guide walks you through essential Oracle database keywords, explaining each term in plain language with practical examples. You’ll learn not only what these concepts mean but also how they impact daily database operations, troubleshooting, and performance tuning. By mastering these terms, freshers gain a strong starting point, while seasoned DBAs can refresh and refine their knowledge. 

In this first installment, we cover critical keywords ranging from Buffer, Cache, and Parsing, to Data Pump and SQL Plan Baselines, giving you a solid foundation for Oracle administration.

SQL Query Tuning in Oracle: A Practical Guide for DBAs

 If you're an Oracle DBA, you already know this feeling: a message pops up — “The application is slow.” No context. No logs. Just urgency.

And more often than not, the root cause comes down to a poorly performing SQL query.

SQL tuning in Oracle isn’t just about adding an index or running the SQL Tuning Advisor. It’s about following a structured, evidence-based approach that eliminates guesswork. Over the years, I’ve realized that the biggest difference between average and effective SQL query tuning lies in discipline — knowing what to check, in what order, and why.

Oracle Data Archiving Best Practices Guide

As an Oracle DBA, you already know this feeling - the database keeps growing, storage keeps expanding, backups take longer, maintenance windows shrink, and suddenly performance complaints start coming in.

Handling large data volumes isn't just about adding more disks or increasing SGA. It's about implementing Oracle Data Archiving best practices that balance performance, cost, compliance, and scalability.

How to Boost Oracle Data Pump Performance for Faster IMPDP Operations

 Oracle Data Pump (IMPDP) is a powerful utility for moving data between Oracle databases, but large-scale imports and exports can often be slow and resource-intensive. Whether you’re managing a standalone database or handling complex LOB-heavy schemas, improving Data Pump performance is key to reducing downtime and ensuring smooth operations. In this guide, we’ll explore practical strategies to enhance IMPDP performance, including parallelism, network-based imports, LOB optimizations, and buffer tuning.

VLDB Backup Optimization – RMAN Concepts Revision for Interviews

 

Q 1: What is the main challenge when backing up a multi-terabyte Oracle database with very large datafiles?

Republic Day Reflections for DBAs: Oracle, PostgreSQL, MSSQL, and the Art of Governance

As India celebrates 26th January - Republic Day, it’s a perfect moment for DBAs to draw parallels between national governance and database management. Just as the Constitution defines rules, responsibilities, and structures for our nation, robust databases rely on architecture, policies, and governance to thrive.

Oracle Hybrid Partitioned Tables in Real Systems

 Most large Oracle environments eventually hit the same wall.

The database keeps growing, storage costs climb every quarter, backup windows become unpredictable, and suddenly DBAs are spending more time managing historical data than supporting the actual application workload. 

Usually the first reaction is archive-and-delete.

Then the reporting team complains because last year's data disappeared.

All Important Things About Oracle RAC Every DBA Should Know

  In today's always-on digital world, databases are no longer just data stores—they are the backbone of business continuity. Whether it’s a bank processing millions of transactions per second, a pharma company maintaining regulatory compliance, or an eCommerce platform surviving flash sales, downtime is simply not an option. This is exactly where All Important Things About Oracle RAC become critical for anyone working with enterprise databases.

Classic vs Integrated Capture in Oracle GoldenGate: Key Differences Explained

 Oracle GoldenGate is a robust solution for real-time data replication, offering flexibility to suit a wide range of enterprise database environments. One of the fundamental decisions when implementing GoldenGate is choosing between Classic Capture and Integrated Capture. While both approaches serve the same purpose—capturing database changes for replication—they differ significantly in performance, scalability, and compatibility with modern Oracle features such as RAC, multitenant architecture, and TDE (Transparent Data Encryption).