Tag Archives: SQL

PGConf India 2026: PostgreSQL Query Tuning: A Foundation Every Database Developer Should Build

Posted on March 13, 2026 by Deepak Mahto

PostgreSQL tuning should not rely on quick fixes like adding indexes or rewriting queries but instead focus on understanding the root issues affecting query performance. A slow query highlights underlying problems such as inaccurate statistics or schema inefficiencies. A five-question framework guides effective SQL tuning, as discussed at PGConf India 2026. Continue reading →

Rate this:

Posted in Oracle to PG migration, postgresql | Tagged , , | 1 Comment

PostgreSQL Table Rename and Views – An OID Story

Posted on January 5, 2026 by Deepak Mahto

In a recent post-migration activity, a large table required a UUID column addition. Instead of using ALTER TABLE, which locks the table, a new table was created to improve performance. However, renaming can create dependency issues with views, as PostgreSQL uses OIDs for tracking. Properly managing dependencies is crucial to avoid operational risks. Continue reading →

Rate this:

Posted in Oracle to PG migration, postgresql | Tagged , , , , | 1 Comment

PostgreSQL Partition Pruning: The Role of Function Volatility

Posted on November 4, 2025 by Deepak Mahto

Improper volatility settings in PostgreSQL functions can significantly impact query performance, especially with partitioned tables. Functions like now() and statement_timestamp() (STABLE) allow efficient partition pruning, while volatile functions like clock_timestamp() hinder performance by necessitating full partition scans. Therefore, understanding function volatility is crucial for optimized SQL queries. Continue reading →

Rate this:

Posted in Oracle to PG migration, postgresql | Tagged , , , , , , , , , , | 4 Comments

Exploring PostgreSQL 18: A Developer’s Guide to New Features – Part 1: The COPY Command Gets More User-Friendly

Posted on September 28, 2025 by Deepak Mahto

PostgreSQL 18, released on September 25, 2024, enhances the COPY command with improved error handling through the REJECT_LIMIT option, allowing data loading to be controlled by limiting errors. This version also introduces additional logging options, aiding database developers in maintaining data integrity during file imports and improving overall data quality. Continue reading →

Rate this:

Posted in postgresql | Tagged , , , , , , , , | Leave a comment

Understanding Volatility in PL/pgSQL Functions: A Real-World Lesson

Posted on November 10, 2024 by Deepak Mahto

The PL/pgSQL language in PostgreSQL allows developers to create complex database functions with prescribed volatility categories: IMMUTABLE, STABLE, and VOLATILE. Selecting the right volatility is crucial for maintaining performance and data consistency, especially during migrations from other databases. Careful consideration prevents unexpected results and enhances application reliability. Continue reading →

Rate this:

Posted in postgresql | Tagged , , , , , , , , , | 2 Comments

PL/pgSQL Secrets: How Conditional Expressions Are Parsed and Evaluated Under the Hood.

Posted on November 2, 2024 by Deepak Mahto

In a recent discussion on PostgreSQL Slack, a user noted that a seemingly incomplete PL/pgSQL IF statement did not cause a syntax error. The condition is processed using a SELECT statement, allowing flexibility in evaluation. This insight enables developers to creatively incorporate SELECT elements into conditional statements without triggering errors. Continue reading →

Rate this:

Posted in postgresql | Tagged , , , , , , | 1 Comment

How to Connect to PostgreSQL Database Through AWS SSM Using pgAdmin or DBeaver.

Posted on September 26, 2024 by Deepak Mahto

Based on my extensive experience working with customers across domains and regions, especially in cloud databases, one of the most time-consuming processes is establishing a secure connection to a managed database instance. I fully understand the steps involved and why … Continue reading →

Rate this:

Posted in AWS - Database | Tagged , , , , , , , , , | Leave a comment

Exploring PostgreSQL 17: A Developer’s Guide to New Features – Part 7: pg_maintain Predefined Role for Maintenance.

Posted on September 23, 2024 by Deepak Mahto

PostgreSQL 17 introduces the pg_maintain role, enhancing access control over maintenance activities like VACUUM, ANALYZE, and REINDEX. This predefined role allows granular permission management, ensuring that only authorized users can perform critical maintainence operations. Continue reading →

Rate this:

Posted in postgresql | Tagged , , , , , , | 2 Comments

Simplify PostgreSQL Execution Plan Analysis with pg_sqltxplain.

Posted on August 2, 2024 by Deepak Mahto

SQL performance tuning requires understanding execution plans and object statistics. StatsViaExplainAnalyze simplifies plan analysis for PostgreSQL by curating execution plans and object statistics in a single report. This tool automates the curation of object statistics by fetching them using the psql command line and creating an HTML output. It’s available on GitHub. Continue reading →

Rate this:

Posted in postgresql | Tagged , , , , , , , , , , | Leave a comment

Exploring PostgreSQL 17: A Developer’s Guide to New Features – Part 3: The COPY Command Gets More User-Friendly

Posted on July 17, 2024 by Deepak Mahto

PostgreSQL 17 Beta, released on May 23, 2024, brings new features for database developers and migration engineers. Notable enhancements include improved handling of data type incompatibilities, better logging, and observability while loading data from flat files. The COPY command now allows for more user-friendly data loading directly into target tables, minimizing the need for staging tables. Continue reading →

Rate this:

Posted in postgresql | Tagged , , , , , , , , , | 3 Comments