You added a column to a PostgreSQL view. Now you want it gone. You ran CREATE OR REPLACE VIEW with fewer columns and PostgreSQL stopped you cold: Your table is fine. Your data is safe. But CREATE OR REPLACE VIEW has one hard rule — it can add columns at the end, but it cannot Read more about Cannot Drop Columns from View in PostgreSQL — Fix [42P16][…]
Last Updated: May 2026 Tested On: PostgreSQL 16.1, pgvector 0.8.1 / 0.8.2 The most common pgvector dimension mismatch error lands within the first hour of setup. After that come casting failures, ALTER TABLE dead-ends, and indexes that exist but never get used. Some are obvious — wrong dimension count, missing cast. Some are subtle — Read more about pgvector Gotchas: Dimension Mismatch, Casting Errors, and ALTER TABLE Solved [2026][…]
Last Updated: May 2026 Tested On: PostgreSQL 16.1 + 17.x, pgvector 0.8.1 / 0.8.2 Platforms: RHEL 9 / Rocky Linux, Ubuntu 22.04 / 24.04 Installing pgvector is not complicated. Two commands on most Linux distributions and you’re done. But I’ve seen enough production incidents — wrong package, wrong schema, extension loaded in the right database Read more about Install and Configure pgvector on PostgreSQL 16 and 17: Step-by-Step Guide [2026][…]
Last Updated: May 2026 Context: pgvector 0.8.1, Pinecone serverless, Weaviate 1.25+ The pgvector vs Pinecone vs Weaviate debate lands on my desk every time a new AI project starts, the first debate that erupts is not about the embedding model or the LLM. It’s this: “Should we just use pgvector or do we need a Read more about pgvector vs Pinecone vs Weaviate: A Production DBA’s Verdict [2026][…]
Last Updated: May 2026 | Tested On: PostgreSQL 16.1, pgvector 0.8.1 | Author: Sanjeeva Kumar, Oracle ACE Associate If you landed here searching “pgvector updates 2026” — you’re not looking for a setup tutorial. You already have pgvector running, or you’re evaluating it, and you want to know: what changed, what broke, and is it Read more about pgvector Release Notes & Updates [2025–2026]: What Changed and What It Means for Your PostgreSQL Stack[…]
If you’ve ever hit ORA-00020 maximum processes exceeded in production, you know it rarely announces itself cleanly. Instead, it hides behind a cascade of unrelated-looking errors that send you down completely the wrong diagnostic path. Here’s a real example. You try to reset a password on the ddvpdb pluggable database. Routine task. And Oracle fires Read more about ORA-00020: Maximum Processes Exceeded — How Three Different Errors Led to One Root Cause[…]
A CI/CD pipeline pushing a schema change to production failed at 2 AM with ORA-00054. The release engineer opened a P1 ticket. The on-call DBA identified the blocker and resolved it in under 3 minutes. The issue was not a bug, not a configuration problem, and not an application defect. It was a classic lock Read more about ORA-00054: Resource Busy — How to Find and Kill the Blocking Session[…]
Introduction When PostgreSQL logs show “configuration file contains errors” after a pg_reload_conf(), most DBAs immediately fear the worst. We were in the middle of a PostgreSQL performance tuning exercise — sequential scan audit, memory configuration review, the usual production DBA drill. After carefully calculating safe values for random_page_cost, effective_cache_size, work_mem, and wal_buffers, we fired off Read more about PostgreSQL “configuration file contains errors” — Don’t Panic, Here’s What’s Really Happening[…]
The debate about the future of engineering leadership in the AI era has been wrong from the start. The industry keeps asking: Will AI replace engineers? It is a convenient question. It avoids the harder one. This is Series of blog. Two Types of Engineers Are Emerging in 2026. Which One Are You? The Question Read more about The Engineers Who Will Lead the Next Decade Aren’t Fighting AI. They’re Thinking With It.[…]
Something is dividing the engineering community in 2026 — and it has nothing to do with which AI tools engineers are using. It is not experience level. It is not the company they work for. Engineers using AI in 2026 largely have access to the same platforms, the same models, the same capabilities. It is Read more about Two Types of Engineers Are Emerging in 2026. Which One Are You?[…]
Every organization running an Oracle production database has a backup. Not every organization has a backup strategy. That distinction sounds subtle. It is not. A backup is a scheduled job and a storage location. A strategy is a set of deliberate decisions — what to back up, how much of it, how fast it needs Read more about 5 Key Considerations for Your Oracle Database Backup Strategy[…]
A request came in on a Thursday afternoon — the UAT team needed a fresh copy of the production PDB by Friday morning for regression testing. We had done local PDB clones before. The command is short, the documentation looks clean, and on a quiet system the operation finishes in minutes. What we had not Read more about PDB Cloning in Production: Gotchas Nobody Warns You About[…]
We had four PDBs on the same CDB running without any Oracle PDB resource plan for CPU and memory isolation. Three of them handled routine OLTP workloads — order processing, user authentication, a reporting schema. The fourth was a batch analytics PDB that ran nightly aggregations across three years of transactional history. For the first Read more about Managing PDB Resources: CPU and Memory Isolation Done Right[…]
Three weeks before go-live on an Oracle AI Database 26ai deployment, we ran a readiness review mostly with the help of Oracle AI Database 26ai production DBA checklist. At the surface level, things looked stable. Vector search was working in staging. JSON Relational Duality Views were serving the REST layer. Select AI was configured against Read more about Building an AI-Ready Oracle Database: A Production DBA Checklist[…]
A developer on our team sent a message last week: “Is there a way to just ask the database how many Critical errors are in the Memory category without writing SQL?” He had heard about Oracle Select AI 26ai and wanted to know if it was live on our instance. That question is exactly the Read more about Select AI: Talking to Your Database in Plain English[…]
Last month we were reviewing a staging incident where an application team had migrated their REST layer to use JSON Relational Duality Views in Oracle AI Database 26ai. The feature worked exactly as advertised during unit testing — clean JSON in, clean JSON out, relational tables staying normalized underneath. Then they ran a load test Read more about 5 Things DBAs Must Know About JSON Relational Duality in Oracle AI Database 26ai[…]
We have run vector search workloads on both Oracle AI Database 26ai and PostgreSQL with pgvector and we are here to show you the architectural breakdown of Oracle AI Database 26ai vector search vs pgvector. The technical capabilities of both are real — pgvector works, and PostgreSQL shops have built production vector search on it. Read more about Oracle AI Database 26ai Vector Search vs PostgreSQL pgvector — A DBA’s Perspective[…]
We had a support tool prototype running vector search against an Oracle error knowledge base. Pure semantic search — query comes in, top five similar errors come back. Worked well in testing. In production, the first complaint arrived within a week: “It keeps returning memory errors when I search for connection problems — they are Read more about Hybrid Search in Oracle AI Database 26ai — Combining SQL and Semantic Queries[…]
In the life of an Oracle Database Expert, It usually early morning when the monitoring alert fired. Typically 3 AM in then morning, applications throwing ORA-04031, Developers calling and the on-call DBA logged in to find the shared pool exhausted — Oracle unable to allocate a single new object in memory. No new SQL could Read more about ORA-04031: Shared Pool Exhaustion — Root Cause and Fix[…]
In Post -2 of this series, We had a vector search query running clean in development — ten milliseconds, HNSW index in the plan, results looking right. The moment the same query hit a table with eight million rows in staging, response time jumped to forty seconds. The execution plan had quietly switched to TABLE Read more about Vector Indexes in Oracle AI Database 26ai — How HNSW and IVF Work Under the Hood[…]
In Post 1 we established the architecture — Oracle 23ai puts vector search inside the database engine, not in a separate system. We titled that post using “23ai” deliberately — because that is the name most production DBAs are still searching for, still referencing in upgrade discussions. Since then, Oracle announced Oracle AI Database 26ai Read more about Oracle Vector Search: Your First Hands-On Walkthrough[…]
A few months ago, we were in an architecture review when the solutions architect put up a slide titled “AI Stack — Proposed.” Oracle was in the middle tier, doing what it always does — transactions, reporting, the core workload. Sitting beside it, in its own separate box, was a vector database. Pinecone, specifically. The Read more about Oracle 23ai — Why Oracle Calls It The AI Database[…]
By Sanjeeva Kumar | Senior Oracle DBA | Oracle ACE Apprentice | dbadataverse.com Have you ever looked at an Oracle version string and struggled to figure out exactly when it was released? You are not alone. Recently, while reviewing patch compliance across our Oracle 19c production environments, a colleague asked a simple question — “Are Read more about Oracle 26 AI Version Numbering: What Does 23.26.1.0.0 Actually Mean?[…]
Recently, we encountered a perplexing situation with one of our production PostgreSQL systems. The buffer cache hit ratio, which had been consistently above 95%, started gradually declining – hovering around 90% and continuing to drop. The strange part? No slow query alerts, no CPU saturation, no memory pressure warnings. Everything looked normal on the surface. Read more about Why PostgreSQL Cache Hit Ratio Keeps Dropping: The Missing Index Problem[…]
By Sanjeeva Kumar | Senior Oracle DBA & Database ArchitectPublished: February 2026 | Reading time: 8 minutes The Scaling Advice That’s Costing You Millions I’ve been a DBA for over 20 years. Oracle, PostgreSQL, MySQL—I’ve scaled them all. And I’m here to tell you something that might save your company millions of dollars and months Read more about PostgreSQL Scaling: What OpenAI Proved Wrong at 800M Users[…]
Two weeks ago, I dropped a development CDB called TESTCDB on one of our lab servers. The DROP DATABASE command worked perfectly—but that was just the beginning. The server was still littered with configuration files, diagnostic logs, and startup scripts pointing to a database that no longer existed. If you’ve ever run DROP DATABASE INCLUDING Read more about Complete Oracle Database Cleanup Checklist: Remove CDB After DROP DATABASE[…]
Last month, I needed to decommission a development PDB that was consuming valuable storage on our production CDB. The challenge? Ensuring all datafiles were completely removed without affecting other PDBs in the container. If you’ve ever hesitated before dropping a PDB—worried about orphaned datafiles or accidentally keeping files you meant to delete—this guide walks you Read more about How to Drop a Pluggable Database Including Datafiles in Oracle 19c[…]
Last Updated: December 2025 | By Sanjeeva Kumar | 20+ Years in Database Systems The Plot Twist Nobody Saw Coming Two weeks ago, PostgreSQL’s pgvector extension released version 0.8.0. Within days, the entire vector database market collectively held its breath. Why? Because that “old guard” relational database just made billion-dollar vector database startups look like Read more about Why PostgreSQL Beat Specialized Vector Databases: A DBA’s Perspective[…]
Last Updated: December 2025 | Oracle 19c/21c | By Ahaaryo Oracle Version Scope: This guide covers Oracle 19c (fully supported) and Oracle 21c (Innovation Release, already desupported but widely deployed). Introduction “The password is definitely correct!” – Every DBA has said this at least once while troubleshooting ORA-01017 invalid username password. I remember a production Read more about ORA-01017: Invalid Username/Password – Complete Guide[…]
Introduction Have you ever been asked to explain why your Oracle database was slow last Tuesday at 2:30 PM? Or needed to identify which SQL statements were causing CPU spikes during last night’s batch run? This is where DBA_HIST_ACTIVE_SESS_HISTORY becomes your most powerful diagnostic tool. Recently, we faced a production issue where users reported intermittent Read more about DBA_HIST_ACTIVE_SESS_HISTORY: Complete Guide to Oracle ASH Analysis[…]
Last Updated: April 10, 2026Tested On: PostgreSQL 16.1, pgvector 0.8.1 The explosion of AI and Large Language Models (LLMs) has created massive demand for vector databases. Instead of adopting a new specialized database, you can transform PostgreSQL into a powerful vector database using pgvector. This guide shows you exactly how to install, configure, and use Read more about pgvector Complete Guide: HNSW Tuning, Hybrid Search & Production Setup [2026][…]
The Problem: TNS-12541 and Database Connection Failures You’ve just installed Oracle 19c on your server. The database is up and running perfectly—you can connect locally using SQL*Plus without any issues. But the moment you try connecting from a remote application or another server, you’re hit with the dreaded TNS-12541: TNS:no listener error. Sound familiar? This Read more about How to Configure Oracle Listener for Standalone Database: Step-by-Step Guide[…]
The Problem: Long-Running Query Fails with ORA-01555 You’re running a critical report or data export, and after 30 minutes it fails with: When this happens: The query was working fine yesterday. The data hasn’t changed much. But today it fails with ORA-01555. This guide shows you exactly how to fix it permanently. What is ORA-01555 Read more about ORA-01555: Snapshot Too Old – Complete Fix Guide for Oracle 19c[…]
The Problem: Cannot Connect to Oracle Database You’re trying to connect to your Oracle database and you get this error: When this happens: You check the database – it’s running. You check the listener – it’s up. But nothing connects. This is ORA-12154, and it means Oracle can’t find or read your TNS configuration. Let Read more about ORA-12154: TNS Could Not Resolve the Connect Identifier – Complete Fix Guide[…]
Last Updated: December 2025 | Oracle 19c | ASM Grid Infrastructure Have you ever encountered a frustrating situation where your Oracle database is running on local storage, and you need to migrate everything to ASM (Automatic Storage Management) without significant downtime? Recently, we faced this exact scenario with a production Oracle 19c Container Database (CDB) Read more about Migrating Oracle 19c Database from Local Storage to ASM: A Complete Step-by-Step Guide[…]
Introduction Have you ever encountered a situation where your Oracle database suddenly stops accepting new connections, and users start complaining that the application is down? One of the most common culprits behind this scenario is the dreaded ORA-00257: archiver error. Recently, we faced this exact scenario with one of our production databases during a high-transaction Read more about ORA-00257: Archiver Error Connect Internal Only Until Freed – Complete Resolution Guide[…]
Last Updated: December 10, 2025Tested On: Oracle Enterprise Linux 9.5, Oracle ASM 19c Patch 25 Oracle Automatic Storage Management (ASM) provides a robust storage management solution for Oracle databases. In this comprehensive guide, we’ll walk through ASM 19c Installation (patch set 25) in a standalone “Oracle Restart” grid infrastructure configuration on Oracle Enterprise Linux (OEL) Read more about Oracle ASM 19c Installation: Complete Guide to Oracle Restart Grid Infrastructure [2025][…]
Introduction Oracle ASM Response file 19c Standalone is a critical thing to understand if you want to install this using CLI. This blog shows how to install Oracle 19c Grid Infrastructure in standalone (Oracle Restart) mode using a response file from CLI without any GUI. This is particularly helpful for DBAs working on headless servers Read more about Response file for Oracle ASM Standalone Oracle 19c[…]
Introduction Need to enable archive log mode in Oracle 19c but unsure of the exact steps? Converting from NOARCHIVELOG to ARCHIVELOG mode is essential for production databases, yet many DBAs hesitate due to concerns about downtime and configuration complexity. In this guide, I’ll walk through the complete process of enabling archive log mode in Oracle Read more about How to Enable Archive Log Mode in Oracle 19c (Complete Guide)[…]
Introduction Database memory management is crucial for maintaining optimal performance in Oracle databases. Inefficient memory usage can lead to slower query execution, increased I/O operations, and poor application response times. This comprehensive guide provides database administrators and developers with practical tools and techniques for monitoring and optimizing Oracle database memory components. Why Memory Monitoring Matters Read more about Oracle Database Memory Monitoring Guide[…]
Have you ever encountered a frustrating situation where your PostgreSQL function works perfectly when executed directly but fails through your ETL tool? Recently, we faced this exact scenario with our Database (dv_d), and I wanted to share our investigation and solution to help others facing similar issues. This turned out to be a PostgreSQL schema Read more about PostgreSQL Schema Resolution Issues in ETL Processes[…]
The Problem: Data Pump Import Fails with Date Format Error You’re running a Data Pump import and it crashes with date format errors like ‘1998-01-01 00:00:00′. You try setting export NLS_DATE_FORMAT=’YYYY-MM-DD HH24:MI:SS’ in your shell, but the import still fails. Error you’re seeing: This happens because Data Pump doesn’t always respect environment-level NLS settings, especially Read more about How to Export NLS_DATE_FORMAT in Oracle: Data Pump Import Date Format Fix[…]
Are you struggling with PostgreSQL read-only user permissions? This step-by-step guide will show you exactly how to create and configure read-only users in PostgreSQL 16. Whether you’re a database administrator or developer, you’ll learn how to properly set up schema-level permissions and avoid common pitfalls. The Challenge: PostgreSQL Read-Only Access Not Working Many developers face Read more about PostgreSQL Read-Only User: Complete Permission Setup Guide[…]
Last Updated: December 10, 2025Tested On: RHEL 9.3, PostgreSQL 16.1 PostgreSQL 16 is the stable release of the powerful open-source relational database system, offering enhanced performance, security, and new features. This comprehensive guide walks you through installing PostgreSQL 16 on RHEL 9 with step-by-step instructions, commands, and best practices. Why Upgrade to PostgreSQL 16 on Read more about How to Install PostgreSQL 16 on RHEL 9/Red Hat Linux [2025 Guide][…]
Introduction For decades, relational databases have been the backbone of enterprise data management, relying on predefined schemas, manual query optimization, and structured data patterns. However, the emergence of generative AI is fundamentally transforming this landscape. By introducing capabilities like intelligent data synthesis, automated performance tuning, and adaptive query processing, AI is evolving databases from passive Read more about Generative AI for Databases: Transforming Data Management[…]
Have you ever encountered a frustrating situation where your Oracle Enterprise Manager couldn’t monitor your ASM environment, leaving you blind to storage issues? Recently, we faced this exact scenario with a client’s production ASM cluster, and ASMSNMP user comes to rescue. The ASMSNMP user plays a crucial role in Oracle ASM (Automatic Storage Management), primarily Read more about ASMSNMP User Setup: Complete Oracle ASM Monitoring Guide[…]
Understanding PostgreSQL Configuration Parameters PostgreSQL’s default configuration rarely fits production workloads. Whether you’re managing a small application database or an enterprise data warehouse, knowing which PostgreSQL configuration parameters to adjust can mean the difference between sub-second queries and application timeouts. In this guide, we’ll cover the essential PostgreSQL configuration parameters that directly impact performance, with Read more about PostgreSQL Configuration Parameters: Essential Settings Guide[…]
Introduction Managing swap space efficiently is essential for optimal Linux performance, especially when handling memory-intensive applications. Sometimes, Oracle databases or Oracle Enterprise manager or any other application which is asking for swap will fail installations. This guide covers three common scenarios to Increase swap size in Linux: Follow these step-by-step instructions to optimize your system’s Read more about How to Increase Swap Size in Linux: A Complete Guide[…]
Introduction Encountering, ORA-65177 ,a Pluggable Database (PDB) that opens in RESTRICTED mode can cause operational challenges, especially when working in a multitenant environment. This issue is often linked to synchronization failures between the Container Database (CDB) and its associated PDBs. We will delve into the root cause of the problem and provide a step-by-step resolution. Read more about Resolving Pluggable Database (PDB) Opening in RESTRICTED MODE (ORA-65177) in Oracle 19c[…]
Why Enable Timing in PSQL? When optimizing your SQL queries, it’s crucial to measure their execution time to understand their performance. Enabling timing in psql lets you see how long a query takes to execute. Why Turn Off the Pager? By default, psql uses a pager (like less) to display query results when the output Read more about Mastering PSQL: Enable Timings and Fetch All Rows Without Paging[…]
Introduction Artificial Intelligence (AI) has rapidly transformed various aspects of our lives, from personalized recommendations to autonomous vehicles. However, with great power comes great responsibility. The European Union (EU) recognizes the need to strike a balance between fostering AI innovation and safeguarding fundamental rights. On March 13, 2024, the EU took a significant and bold step Read more about From Regulation to Responsibility: Understanding the EU’s AI regulation[…]
Generative AI! If you’ve ever marveled at a piece of artwork created by an AI, or interacted with a chatbot that seemed almost human, then you’ve already had a glimpse into what Generative AI can do. Welcome to this fascinating world of Gen AI! Generative AI is a subset of artificial intelligence that focuses on Read more about The World of Generative AI: An Introductory Exploration[…]
In our previous blog, we talked about the optimizer access path. Over the last few blogs, we have walked through different access paths one by one. In this blog, we will see how the index unique scan works — and why it is the most efficient index access path Oracle has to offer. Index unique Read more about Optimizer Access Paths: index unique scan[…]
An index fast full scan reads the entire index as it is i.e. as it exists on the disk. This index read is unsorted. This access path usage multiblock IO to perform the index scan. Index fast full scan simply reads all of the leaf, branch and root block of index quickly using multiblock IO Read more about Optimizer Access Paths : Fast Full Scan[…]
In this final blog of our PostgreSQL synchronous replication setup series, we close the loop on Master-Slave replication — and we save the best for last. Through the previous three blogs, we built our foundation, configured the async node, and watched streaming replication come alive. Async worked beautifully — but async also means the master Read more about Data Replication Setup Using Master and Slave: PostgreSQL- IV[…]
In last few blogs, we have seen how to automate steps required to setup Master-slave nodes. This blog would be the last in this series. Tested on PostgreSQL 11. For PostgreSQL 12+, recovery.conf is replaced by postgresql.conf settings with standby.signal file. The core replication concepts and verification steps remain the same. Until now we have Read more about Data Replication Setup Using Master and Slave: PostgreSQL- III[…]
This is in continuation of My last blog about Meta Commands in PostgreSQL. In last blog I have explained what a Meta command and how to list databases. In next few blogs we will explore few of Meta commands. Lets start exploring schemas. Visit this series of last blog for other command. The command we Read more about PostgreSQL Meta-Command using psql: Command Information -IV[…]
Setting up high availability in PostgreSQL requires a master-slave replication environment where the slave takes over if the master fails. This post covers the architecture design. In next few blogs we will explore how High Availability(HA) will be setup. This is Part 1 of a 4-part series on PostgreSQL replication In this blog we will Read more about Data Replication Setup Using Master and Slave: PostgreSQL – I[…]
This is Part 2 of our PostgreSQL streaming replication configuration series. In this post, we configure the master and slave nodes — covering every postgresql.conf and recovery.conf parameter you need for streaming replication. Tested on PostgreSQL 11. Note: Starting from PostgreSQL 12, recovery.conf has been removed. Replication parameters now go directly into postgresql.conf and a Read more about Data Replication Setup Using Master and Slave: PostgreSQL – II[…]
Have you ever wondered why some of your Oracle queries bypass the buffer cache entirely and read data directly from disk? Recently, we encountered this exact scenario while investigating performance issues in a data warehouse environment, and I wanted to share our findings about Oracle’s direct path read mechanism. Oracle Direct Path Read is a Read more about Oracle Direct Path Read: Complete Performance Guide with Real Examples[…]
A production AWR report flagged a query with an INDEX FULL SCAN operation consuming significant I/O. The developer assumed it was an index range scan gone wrong. It was not. The optimizer had a reason — and understanding that reason is what separates reactive troubleshooting from deliberate tuning. This post breaks down what Oracle index Read more about Oracle Index Full Scan: How It Works and When the Optimizer Uses It[…]
Master-slave replication setup is the implementation of HA in PostgreSQL. Deployment architecture of 1 master and at least one slave in SYNC mode would be able to provide senses of High Availability of the database. To achieve this we require some steps to be performed into the database which will make one of the nodes Read more about Automating Master and slave replication: PostgreSQL – I[…]
We have explored the PostgreSQL implementation of MVCC in the last two blogs. We will understand the pros and cons of this way of implementation in this blog. PostgreSQL MVCC Pros and Cons in Production Understanding the PostgreSQL MVCC pros and cons helps DBAs make informed decisions about vacuum tuning, table design, and when bloat Read more about MVCC Concurrency Architecture: PostgreSQL way of implementation – III( pros/cons)[…]
PostgreSQL supports many other tools in form of database extensions like UUID support, dblink. In the same way, it also supports geographic and spatial data with help of PostGIS. This blog will explain how to install PostGIS 2.5 which can be used by PostgreSQL 11. Tested on PostgreSQL 11 with PostGIS 2.5. For newer PostgreSQL Read more about PostGIS Installation with PostgreSQL[…]
In the last blog, we have installed PostgreSQL on different Linux platforms ( CentOs, Ubuntu). However, We have leftover to initialize the installation and connection to the database that we will discuss here. Postgres provides more than one way to initialize a database cluster. We will use pg_createcluster in this blog. But before that, we Read more about What is a Database Cluster: PostgreSQL[…]
T his is in continuation of My last blog about Meta Commands in PostgreSQL. In last blog I have explained what a Meta command and how to list databases. In next few blogs we will explore few of Meta commands. Lets start exploring schemas. Visit this series of last blog for other command. The command Read more about PostgreSQL Meta-Command using psql: Command Information -III[…]
This is in continuation of My last blog about Meta Commands in PostgreSQL. In last blog I have explained what a Meta command is all about. In next few blogs we will explore few of Meta commands. Lets start exploring databases. How to List Databases in psql Using \l The command we will explore here Read more about PostgreSQL Meta-Command using psql: Command Information -II[…]
Distinct is the keyword that oracle used to filter out unique values from a column. However, it is very interesting to see the behavior followed by the optimizer for its access path. How Oracle Index Fast Full Scan Handles DISTINCT Queries? In this “Optimizer Access Paths” blog, we will see how we can make optimizer Read more about Optimizer Access Paths: How Index FFS works with Distinct[…]
This is in continuation from my last blog on PostgreSQL implementation of MVCC. We will understand transaction id (XID), versioning of a tuple (xmin & xmax) in more detail. Before we move to understand the implementation of a transaction using MVCC, let’s first understand how a tuple is created/updated/deleted. PostgreSQL assigns the current transaction id Read more about MVCC Concurrency Architecture: PostgreSQL way of implementation – II[…]
Oracle optimizer access paths determine whether your query runs in 18 seconds or 18 milliseconds. Every SQL statement hitting your Oracle database goes through the optimizer, which silently picks between a full table scan, index range scan, or index unique scan — and that single decision defines your query’s cost. What Is an Access Path? Read more about Why Is Oracle Doing a Full Table Scan Instead of the Index?[…]
In previous blog, we have detailed out about the inception of PostgreSQL. In this blog, we will explain the architecture (MVCC) with respect to PostgreSQL. Concurrency Control: There are more than one way to provide concurrency control by database. Concurrency is an important factor to allow many users to work on the same dataset at Read more about Concurrency Control in PostgreSQL[…]
This is in continuation of My last blog about Meta Commands in PostgreSQL. In last blog we have explained what a Meta command is all about. In next few blogs we will explore few of Meta commands. Let’s start with psql command itself. How to Use psql to Connect to a PostgreSQL Database Once we Read more about PostgreSQL Meta-Command using psql: Command Information -I[…]
There are many things needed in the DBA’s life for which there are no simple and straight forward ways to find out and they sometimes are tricky to find out. One of them is to find the keys of a collection. There are alternate methods we can certainly explore to retrieve all keys. the catch Read more about How To: List keys of collection MongoDB[…]
In our last blog we have discussed the history of origination of PostgreSQL database. This database, like all other RDBMS, has more than one interface to connect to the database. PostgreSQL has psql program as command line interface (CLI) and PgAdmin as graphical user interface (GUI) Validated on PostgreSQL 14, 15, and 16. Commands work Read more about PostgreSQL Meta-Command using psql: Generic Information[…]
There are multiple ways of installation of the PostgreSQL database on Unix like platforms. We will explore the installation of PostgreSQL version 12 on CentOS 7 using YUM installation. Let’s start. Verifying the version of CentOS: root@localhost yum.repos.d]# rpm -q centos-release centos-release-7-7.1908.0.el7.centos.x86_64 PostgreSQL allows us to download many version across multiple platform, one should visit its official Read more about PostgreSQL Installation on CentOS 7[…]
There are multiple ways of installation of the PostgreSQL database on Unix like platforms. We will explore the installation of PostgreSQL version 11 on Ubuntu 18.04 LTS using apt installation. Let’s start. Login to Ubuntu and check the version of Ubuntu. postgres@sanjeeva:/home/sanjeeva/postgres$ lsb_release -a No LSB modules are available. Distributor ID: Ubuntu Description: Ubuntu 18.04.5 LTS Release: Read more about PostgreSQL Installation on Ubuntu[…]
Research papers of “System R” from IBM were initially picked up by two professors, Michael Stonebraker and Eugene Wong, at Berkeley University, California. This resulted in a new database called INteractive Graphics REtrieval System i.e. “Ingres“. The work done by this duo for Ingres becomes the foundation of many relational databases like MS SQL Server, Read more about Evolvement of PostgreSQL: Background[…]
Indexes are a very important structure in any database and the same is true with MongoDB as well. It adds spices to the performance of your query. It is also very important to know what and how many indexes are created on your DB. In the normal life of DBA, one needs to find out Read more about How To: List all indexes in MongoDB[…]
Many a time there are required to create a new collection from existing ones with the same or different size. In this blog, we will see how to create a new collection from the existing one and with a smaller size. For Sample data, We may use Kaggle to get huge data set. In my collection, I Read more about How To: Creating a collection subset from collection in MongoDB[…]
This is second part of my installation blog on InfluxDB. In the First part of this blog we have discussed single instance installation. In this part we are going to discuss cluster installation for InfluxDB. For cluster installation which is not a production ready system InfluxDB provides a special cluster installation package which they called Read more about InfluxDB: How to perform Quick cluster installation on CentOS[…]
Among many Time Series databases (TSDB), InfluxDB is able to secure its position in the market. This is because InfluxDB provides many features that give added advantages and make it sustainable in various scenarios. Besides these, it also provides easy installation and configuration. InfluxDB gives flexibility to the user to install a single instance, QuickStart Read more about InfluxDB: How to perform a single node installation on CentOS[…]
Rapid growth of sensor-based, IoTs, social media, financial data like stock market activities and many other information streaming platforms created opportunity to design a whole new database which can capture streaming information with highlighting the importance of time into it. This is so because even traditional RDBMS was not able to efficiently handle complex business Read more about Time Series Database : Evolvement[…]
With its inception in 2006, Amazon AWS has definitely gone a long way. Engineers from Amazon worked really-really well which has not only completely changed the horizon of cloud but also emerges as one of the boon for any business to adopt. Although, there are many other vendors available in the cloud market and they Read more about Cloud database war: Advantage shifting to Red?[…]
WiredTiger became the default storage engine in MongoDB 3.2 and MMAPv1 was removed entirely in MongoDB 4.2+. If you are running any current MongoDB version, you are already on WiredTiger. The architecture concepts covered here remain relevant for understanding MongoDB internals and tuning Storage engine is one of the key component of any database. It Read more about WiredTiger: A game changer for MongoDB[…]
There are times when you require to drop your existing database for more than one reason. Dropping a database is not a tough job at all, if you are very sure that which database you should drop. Originally written for Oracle 11g. The DROP DATABASE command syntax and prerequisites remain the same in Oracle 12c, Read more about Drop a database[…]
Problem Statement: Restore entire database using Data Pump. Restore table(s) Restore tablespace(s) Restore schema(s) Restore using Transportable tablespaces (TTS) Restore from multiple small sizes of dump files Restore in parallel mode Originally written covering Oracle 11g/12c Data Pump. The impdp command syntax and parameters remain valid through Oracle 19c and 21c. Newer versions have added Read more about Data Pump: impdp[…]
Introduction Have you ever needed to analyze Oracle database growth trends but found yourself limited by current snapshot data? In real production environments, tracking database growth trends is a critical aspect of DBA life because this allows businesses not only to foresee the real position in terms of existing hardware but also enables planning future Read more about DBA_HIST_SEG_STAT: SQL Queries to Track Oracle Growth[…]
Problem Statement: Backup entire database using Data Pump. Backup table(s) Backup tablespace(s) Backup schema(s) Backup using Transportable tablespaces (TTS) Generate multiple small sizes of dump files Backup in parallel mode Approach: There are single shot solution to all the above problem statement and it is Data Pump. It is one of various backup tools provided Read more about Oracle Data Pump: expdp & impdp[…]
Among various techniques of backing up your database Oracle provides data pump as one of tool which they are constantly improving and making this tool sharper release by release. Data Pump was introduced in Oracle 10g as a replacement for exp/imp. This post covers the concepts that remain valid through Oracle 19c and 21c. For Read more about Data Pump: a tool to backup and restore database[…]
MongoDB is one of the document oriented open source database developed in c++, first come into shape in 2007 when in order to overcome the shortfall of existing database while working for an advertising company “DoubleClick” development team has decided to go further rather than struggling with database. Originally written for MongoDB 3.x Enterprise Read more about MongoDB Enterprise Edition Installation – Ubuntu[…]
When you need to uninstall MongoDB Ubuntu — whether for a clean reinstall, version upgrade, or decommissioning — it is important to remove not just the packages but also the data directories and log files to avoid conflicts. MongoDB is one of the document oriented open source database developed in c++, first come into shape Read more about How to: Uninstallation of MongoDB on Ubuntu[…]
MongoDB is one of the document oriented open source database developed in c++, first come into shape in 2007 when in order to overcome the shortfall of existing database while working for an advertising company “DoubleClick” development team has decided to go further rather than struggling with database. The team of this advertising company was Read more about MongoDB Installation – Ubuntu[…]
This MongoDB introduction covers what makes document-oriented databases different from traditional relational systems — and why MongoDB emerged as the most widely adopted NoSQL database. About NoSQL: Any MongoDB introduction would be incomplete without understanding how it stores data as flexible JSON-like documents instead of rigid rows and columns. Let’s understand about the NoSQL. We Read more about Mongo DB – An Introduction[…]
Knowing how to Oracle move datafile from one storage location to another is a routine but critical DBA task — whether you are migrating to faster storage, rebalancing disk usage, or standardizing datafile naming conventions. Problem statement: How to move data files from one location to another on same storage. How to move data files Read more about How to re-organize your data files of a tablespace[…]
Updated for Oracle 19c — March 2026 Originally written for Oracle 11g. Updated March 2026 for Oracle 19c. The migration strategies and concepts apply across all supported Oracle versions Problem statement: How to migrate huge data from One DB to another DB. Multi-Terabyte data loaded on one database should be copied to another database. Read more about How to copy Multi terabyte data to another Database Server[…]
When you need to Oracle clone database to a new server — whether for creating DEV/UAT from production or migrating to new storage — copying the Oracle binaries along with the datafiles is one of the fastest approaches. Problem Statement: Move DB with Oracle Binaries on New Storage Create new DEV/UAT from Production. How to Read more about Move DB with Oracle 11g Binaries[…]
Batch window shrinking. ETL job running 60 seconds when it should run 9. Three million rows and the clock is ticking. The fix is direct path insert — and most DBAs never configure it deliberately. Here’s the complete breakdown with a live benchmark proving the difference. The Problem: Three Scenarios Where INSERT Kills Performance Loading Read more about Oracle INSERT Too Slow? Fix It Now[…]
In our previous blogs we have studied about Big data, Hadoop. We have also explained MapReduce internal workings like how Map works using short and shuffle. This blog is dedicated to Reduce in MapReduce. Once this shuffling completed, it is where Reduce in MapReduce come into action. Its task is to process the input provided Read more about Reduce in MapReduce … Unwinding[…]
This is in continuation of MapReduce Processing We are going to see how the input is provided to SORT process, how this is sorted and distributed on all available DNs and this input is taken over to the next step Shuffle. This output will be input for next process which is SORT. Sort takes Read more about MapReduce – Sort & Shuffle[…]
In last discussion on MapReduce, we discussed the algorithm which is used by Hadoop for data processing using MapReduce. In this blog, we will discuss the specific section of MAP in MapReduce and it’s functionality. Unwinding Map We will explain this in details and with example here. Example: Lets consider our scenario : The Scenario: Read more about MapReduce – Unwinding Map[…]