All About Database learnings both On Prem, Cloud and with Migrations.
The author emphasizes the importance of using Ora2pg for Oracle-to-PostgreSQL migrations, particularly for table DDL conversion. With over a decade of experience, they highlight Ora2pg's robust configuration options that provide precise control. The post outlines specific features and transformation directives that enhance migration efficiency and accuracy, making complex migrations manageable.…
Migrations from Oracle and SQL Server to PostgreSQL often encounter overlooked challenges. The tools, while capable, can produce flawed code that compromises performance and functionality. Issues like improper data type mapping, silent errors from implicit casting, incomplete code, and extension lock-in require careful attention. Understanding these pitfalls is crucial for a successful migration.…
During an Oracle to PostgreSQL migration, a critical issue arose regarding sequence value resets. The tool ora2pg generated inadequate commands causing sequence values to mismatch post-cutover. A fix was implemented to replace the incorrect ALTER SEQUENCE START WITH command with RESTART WITH or setval() for accurate sequence resetting, crucial for successful migrations. Continue reading
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
The article examines issues encountered when migrating an Oracle application to PostgreSQL, particularly differences in operator precedence and implicit casting. A case study reveals how similar SQL expressions yield different results due to inherent database behaviors. The piece emphasizes the need for explicit intent in SQL to avoid silent data corruption and bugs post-migration. Continue…
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
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…
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
Migrating from Oracle or SQL Server to PostgreSQL often brings challenges with case-insensitive searches, since PostgreSQL doesn’t support them natively. Workarounds include nondeterministic collations or custom operators, though the recommended approach is to use the built-in "C" collation with ILIKE. Notably, PostgreSQL 18 is set to improve case-insensitive matching, making migrations smoother.…
PostgreSQL 18 Beta introduces powerful enhancements to statistics management. For the first time, users can export, import, and modify planner statistics—making it easier to replicate production behavior in lower environments and fine-tune query performance without relying on actual data loads. Continue reading