From: Alban Hertroys <haramrae@gmail.com>
To: kavya chandren <kavyachandren@gmail.com>
Cc: pgsql-general@lists.postgresql.org
Subject: Re: Challenges with huge postgresql deployments in 300+ TBs
Date: Thu, 8 Oct 2026 12:09:31 +0200
Message-ID: <D2E355FF-9D49-4242-B004-2FA7844031B6@gmail.com> (raw)
In-Reply-To: <CAPXWBGai9XJdoikZXt_7-XmUHF5iSTkKpM3cQnk2xVQVcou8dQ@mail.gmail.com>
References: <CAPXWBGai9XJdoikZXt_7-XmUHF5iSTkKpM3cQnk2xVQVcou8dQ@mail.gmail.com>
> On 8 Oct 2026, at 09:05, kavya chandren <kavyachandren@gmail.com> wrote:
(…)
> 6. Finding equivalents of oracle items (which is common during any oracle to postgres migration)
There are a number of key differences between Oracle and Postgres that I’m aware of. This is by no means an exhaustive list, nor may it still be very accurate today; The last version of Oracle I worked with was Oracle 12i, so things may have changed.
- Oracle doesn’t (did not?) differentiate between '' and NULL, while Postgres does. Make sure you get the right values in the right places. Safest bet is probably to import your data using column types in PG that allow for '’-values, so that you don’t get interrupted by errors from NOT NULL constraints and the like, and fix those later. That’s probably less of a hassle (even with 300+TB) than trying to correct the data beforehand.
- Oracle has CLOBs, where you can dump those values in a (type) text field in PG. That may introduce a character encoding though, on which some values could fail if you don’t take care of those beforehand.
- Oracle has BLOBs, where PG has a separate LOB table or bytea fields. Either approach has its cons and pros.
- Old Oracle DDL may still contain CONNECT BY PRIOR recursion. PG uses recursive CTE’s.
- Space-padded strings (CHAR(n)) in Postgres are supported, but they are usually advised against. Both performance and practical use of VARCHAR(n) are much better, with no practical benefits for CHAR(n). That is, apart from formatting monospace text output without processing (console style output or matrix printers and the like).
Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-general@postgresql.org
Cc: haramrae@gmail.com, kavyachandren@gmail.com
Subject: Re: Challenges with huge postgresql deployments in 300+ TBs
In-Reply-To: <D2E355FF-9D49-4242-B004-2FA7844031B6@gmail.com>
References: <CAPXWBGai9XJdoikZXt_7-XmUHF5iSTkKpM3cQnk2xVQVcou8dQ@mail.gmail.com> <D2E355FF-9D49-4242-B004-2FA7844031B6@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox