<?xml version="1.0" encoding="UTF-8"?>
<rss version="2.0" xmlns:atom="http://www.w3.org/2005/Atom" xmlns:dc="http://purl.org/dc/elements/1.1/">
  <channel>
    <title>DEV Community: Sharath Chandra Kampili</title>
    <description>The latest articles on DEV Community by Sharath Chandra Kampili (@sharath568).</description>
    <link>https://dev.to/sharath568</link>
    <image>
      <url>https://media2.dev.to/dynamic/image/width=90,height=90,fit=cover,gravity=auto,format=auto/https:%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Fuser%2Fprofile_image%2F4152652%2F4eae4e2a-cd8c-4483-8f62-2d3a935d9195.jpeg</url>
      <title>DEV Community: Sharath Chandra Kampili</title>
      <link>https://dev.to/sharath568</link>
    </image>
    <atom:link rel="self" type="application/rss+xml" href="https://dev.to/feed/sharath568"/>
    <language>en</language>
    <item>
      <title>RAG Without a Second Database</title>
      <dc:creator>Sharath Chandra Kampili</dc:creator>
      <pubDate>Mon, 16 Nov 2026 18:32:28 +0000</pubDate>
      <link>https://dev.to/sharath568/rag-without-a-second-database-3opb</link>
      <guid>https://dev.to/sharath568/rag-without-a-second-database-3opb</guid>
      <description>&lt;p&gt;Almost every RAG tutorial I read starts the same way. Take your documents, split them into chunks, embed them, and load the vectors into a vector database. Then keep your original data where it was, and write something that keeps the two in sync.&lt;/p&gt;

&lt;p&gt;I was an Oracle DBA for a long time before I moved into architecture work, and that second step always makes me a little uneasy. Not because vector databases are bad. Because "add another database" is never just one decision. It is a second copy of the data, a sync job that can fall behind, a second set of permissions, backups that are not consistent with each other, and one more thing to patch, monitor and pay for.&lt;/p&gt;

&lt;p&gt;So I wanted to see how far I could get with just Postgres. Specifically Azure Database for PostgreSQL Flexible Server, which gives you pgvector, a DiskANN index through the &lt;code&gt;pg_diskann&lt;/code&gt; extension, and the &lt;code&gt;azure_ai&lt;/code&gt; extension for calling Azure OpenAI from SQL. I wrote a small tool, &lt;a href="https://github.com/sharath568/azure-data-pg-rag" rel="noopener noreferrer"&gt;pgrag&lt;/a&gt;, to try it properly, with tests and real measurements. This post is what I learned.&lt;/p&gt;

&lt;h2&gt;
  
  
  One table for text, vectors and keywords
&lt;/h2&gt;

&lt;p&gt;The core of it is boring on purpose. Here is the chunks table, trimmed a little:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;CREATE&lt;/span&gt; &lt;span class="k"&gt;TABLE&lt;/span&gt; &lt;span class="n"&gt;chunks&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;
    &lt;span class="n"&gt;id&lt;/span&gt;           &lt;span class="kt"&gt;bigserial&lt;/span&gt; &lt;span class="k"&gt;PRIMARY&lt;/span&gt; &lt;span class="k"&gt;KEY&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="n"&gt;document_id&lt;/span&gt;  &lt;span class="kt"&gt;bigint&lt;/span&gt; &lt;span class="k"&gt;NOT&lt;/span&gt; &lt;span class="kc"&gt;NULL&lt;/span&gt; &lt;span class="k"&gt;REFERENCES&lt;/span&gt; &lt;span class="nf"&gt;documents&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;id&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;ON&lt;/span&gt; &lt;span class="k"&gt;DELETE&lt;/span&gt; &lt;span class="k"&gt;CASCADE&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="n"&gt;content&lt;/span&gt;      &lt;span class="kt"&gt;text&lt;/span&gt;   &lt;span class="k"&gt;NOT&lt;/span&gt; &lt;span class="kc"&gt;NULL&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="n"&gt;metadata&lt;/span&gt;     &lt;span class="kt"&gt;jsonb&lt;/span&gt;  &lt;span class="k"&gt;NOT&lt;/span&gt; &lt;span class="kc"&gt;NULL&lt;/span&gt; &lt;span class="k"&gt;DEFAULT&lt;/span&gt; &lt;span class="s1"&gt;'{}'&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="n"&gt;embedding&lt;/span&gt;    &lt;span class="kt"&gt;vector&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="mi"&gt;1536&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="n"&gt;content_tsv&lt;/span&gt;  &lt;span class="kt"&gt;tsvector&lt;/span&gt; &lt;span class="no"&gt;GENERATED&lt;/span&gt; &lt;span class="no"&gt;ALWAYS&lt;/span&gt; &lt;span class="k"&gt;AS&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="nf"&gt;to_tsvector&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="s1"&gt;'english'&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;content&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="no"&gt;STORED&lt;/span&gt;
&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;

&lt;span class="k"&gt;CREATE&lt;/span&gt; &lt;span class="k"&gt;INDEX&lt;/span&gt; &lt;span class="k"&gt;ON&lt;/span&gt; &lt;span class="n"&gt;chunks&lt;/span&gt; &lt;span class="k"&gt;USING&lt;/span&gt; &lt;span class="n"&gt;hnsw&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;embedding&lt;/span&gt; &lt;span class="n"&gt;vector_cosine_ops&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;span class="k"&gt;CREATE&lt;/span&gt; &lt;span class="k"&gt;INDEX&lt;/span&gt; &lt;span class="k"&gt;ON&lt;/span&gt; &lt;span class="n"&gt;chunks&lt;/span&gt; &lt;span class="k"&gt;USING&lt;/span&gt; &lt;span class="n"&gt;gin&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;content_tsv&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;span class="k"&gt;CREATE&lt;/span&gt; &lt;span class="k"&gt;INDEX&lt;/span&gt; &lt;span class="k"&gt;ON&lt;/span&gt; &lt;span class="n"&gt;chunks&lt;/span&gt; &lt;span class="k"&gt;USING&lt;/span&gt; &lt;span class="n"&gt;gin&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;metadata&lt;/span&gt; &lt;span class="n"&gt;jsonb_path_ops&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;The text, its embedding, a full-text search column and the metadata all sit in one row. The &lt;code&gt;tsvector&lt;/code&gt; is a generated column, so Postgres keeps it current on every insert and update. There is no separate indexing pipeline.&lt;/p&gt;

&lt;p&gt;As a DBA, this is the part I like most. Re-ingesting a document is a &lt;code&gt;DELETE&lt;/code&gt; and an &lt;code&gt;INSERT&lt;/code&gt; in one transaction, so nobody ever searches half an update. Point-in-time restore brings back the vectors with the rows they belong to. Row-level security and normal joins work on search results. When someone asks "where does our RAG data live?", there is one answer.&lt;/p&gt;

&lt;h2&gt;
  
  
  Hybrid search is one SQL function
&lt;/h2&gt;

&lt;p&gt;Pure vector search is good at meaning. Ask "how do I pause the database to save money" and it can find a passage about stopping the server. It is weaker on exact tokens: parameter names like &lt;code&gt;maintenance_work_mem&lt;/code&gt;, SKU names like &lt;code&gt;Standard_B1ms&lt;/code&gt;, error codes. Full-text search is the other way round. You want both.&lt;/p&gt;

&lt;p&gt;The usual way to combine them is Reciprocal Rank Fusion. Each result gets &lt;code&gt;1 / (60 + rank)&lt;/code&gt; from every list it appears in, and you add those up. A chunk that is 1st in the vector list and 3rd in the keyword list scores &lt;code&gt;1/61 + 1/63&lt;/code&gt;, about 0.0323. A chunk only one list found, at rank 1, scores about 0.0164. RRF only looks at ranks, so you never have to compare a cosine similarity with a &lt;code&gt;ts_rank&lt;/code&gt; score.&lt;/p&gt;

&lt;p&gt;In Postgres this is a single function: one CTE for the nearest vectors (&lt;code&gt;ORDER BY embedding &amp;lt;=&amp;gt; $1 LIMIT 50&lt;/code&gt;, written so the planner uses the index), one CTE for the keyword matches, and a &lt;code&gt;FULL OUTER JOIN&lt;/code&gt; that adds up the scores. The metadata filter is applied to both sides. I also wrote the same maths in plain Python and an integration test checks that the SQL returns exactly the same ids, ranks and scores, because I don't trust fusion code I can't check.&lt;/p&gt;

&lt;p&gt;Two things surprised me while building it.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;The keyword side was silently empty.&lt;/strong&gt; I started with &lt;code&gt;websearch_to_tsquery&lt;/code&gt;, which needs every word of the query to be in the chunk. That works for keyword searches like &lt;code&gt;maintenance_work_mem&lt;/code&gt;. But people ask questions like "how do I stop paying for the server when I'm not using it?", and no chunk contains all of those words. The keyword list came back empty and hybrid search quietly became plain vector search. The fix was to turn the question into an OR of its words and let &lt;code&gt;ts_rank_cd&lt;/code&gt; push chunks that match more of them to the top. With that change, the same question gets the costs page as 1st in both lists.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Filters can return nothing at all.&lt;/strong&gt; An HNSW scan hands back its best &lt;code&gt;ef_search&lt;/code&gt; candidates, and the &lt;code&gt;WHERE&lt;/code&gt; clause filters afterwards. If your filter is selective, that can leave you with nothing. I wrote a test for it: 3,030 chunks, only 30 tagged &lt;code&gt;rare&lt;/code&gt;, ask for the 10 nearest &lt;code&gt;rare&lt;/code&gt; chunks with the HNSW index forced. Without iterative scans I got 0 rows back. With pgvector 0.8's &lt;code&gt;hnsw.iterative_scan = relaxed_order&lt;/code&gt; I got all 10. pgrag now turns that on for filtered searches when pgvector is 0.8 or newer. If you are on an older pgvector, this is worth knowing about before your users find it.&lt;/p&gt;

&lt;h2&gt;
  
  
  Some numbers from a small box
&lt;/h2&gt;

&lt;p&gt;I don't want to hand-wave about performance, so pgrag has a &lt;code&gt;bench&lt;/code&gt; command. It generates clustered synthetic vectors, computes the exact top 10 for each query in NumPy, loads the vectors with binary &lt;code&gt;COPY&lt;/code&gt;, and then measures recall@10 and latency for an exact scan and for HNSW at different &lt;code&gt;ef_search&lt;/code&gt; values.&lt;/p&gt;

&lt;p&gt;To be clear about where these came from: a sandbox Linux container with 2 vCPUs and 7.8 GiB of RAM, PostgreSQL 16.15 and pgvector 0.8.0, client on the same machine. Not Azure, and no DiskANN yet. That comes with the Azure run.&lt;/p&gt;

&lt;div class="table-wrapper-paragraph"&gt;&lt;table&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;data&lt;/th&gt;
&lt;th&gt;index&lt;/th&gt;
&lt;th&gt;setting&lt;/th&gt;
&lt;th&gt;recall@10&lt;/th&gt;
&lt;th&gt;p50 ms&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td&gt;20k x 384&lt;/td&gt;
&lt;td&gt;exact scan&lt;/td&gt;
&lt;td&gt;-&lt;/td&gt;
&lt;td&gt;1.000&lt;/td&gt;
&lt;td&gt;11.61&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;20k x 384&lt;/td&gt;
&lt;td&gt;HNSW&lt;/td&gt;
&lt;td&gt;ef_search=40&lt;/td&gt;
&lt;td&gt;0.990&lt;/td&gt;
&lt;td&gt;0.67&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;100k x 1536&lt;/td&gt;
&lt;td&gt;exact scan&lt;/td&gt;
&lt;td&gt;-&lt;/td&gt;
&lt;td&gt;1.000&lt;/td&gt;
&lt;td&gt;628.53&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;100k x 1536&lt;/td&gt;
&lt;td&gt;HNSW&lt;/td&gt;
&lt;td&gt;ef_search=40&lt;/td&gt;
&lt;td&gt;0.823&lt;/td&gt;
&lt;td&gt;3.23&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;100k x 1536&lt;/td&gt;
&lt;td&gt;HNSW&lt;/td&gt;
&lt;td&gt;ef_search=160&lt;/td&gt;
&lt;td&gt;0.964&lt;/td&gt;
&lt;td&gt;5.94&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;&lt;/div&gt;

&lt;p&gt;The headline is the gap between the exact scan and the index: about 630 ms down to 3 to 6 ms at 100k vectors of 1536 dimensions. The second thing is that &lt;code&gt;ef_search&lt;/code&gt; really is the recall dial. The default of 40 was plenty for the small set and not enough for the big synthetic one. My synthetic 1536-dim data is harder than real embeddings, so please don't quote 0.82 as "pgvector recall". Quote it as "measure with your own queries".&lt;/p&gt;

&lt;p&gt;The index sizes were the most useful result for planning. The 100k x 1536 HNSW index was 781.3 MiB, which is exactly 8 KiB per row. pgvector keeps each element on a page without splitting it, and a 1536-dim element plus its neighbours is a bit over 6 KB, so it gets a page to itself. The 384-dim index came out at about 2 KiB per row, four to a page. That made it easy to put a page-aware size estimate into pgrag's cost command and pin it to these measurements with a unit test.&lt;/p&gt;

&lt;h2&gt;
  
  
  What it costs
&lt;/h2&gt;

&lt;p&gt;The setup I use for this series is deliberately small: a Burstable B1ms Flexible Server (1 vCore, 2 GiB RAM), 32 GiB of storage, no high availability, pay-per-token Azure OpenAI with &lt;code&gt;text-embedding-3-small&lt;/code&gt; and &lt;code&gt;gpt-4.1-mini&lt;/code&gt;, and a budget on the resource group that e-mails me at 50, 80 and 100 % of 10 USD. It is all in one Bicep file with &lt;code&gt;up.sh&lt;/code&gt; and &lt;code&gt;down.sh&lt;/code&gt; scripts.&lt;/p&gt;

&lt;p&gt;&lt;code&gt;pgrag cost&lt;/code&gt; puts rough numbers on it. The prices in it are constants I typed in, so check the Azure pricing calculator before you trust them. Still, the shape of the answer is clear:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;Embedding is the cheap part. 10,000 documents of about 1,500 tokens is roughly 17.5 million tokens, about 35 cents with &lt;code&gt;text-embedding-3-small&lt;/code&gt; at $0.02 per million.&lt;/li&gt;
&lt;li&gt;Compute hours are the real cost. A stopped Flexible Server only bills storage, so &lt;code&gt;az postgres flexible-server stop&lt;/code&gt; is the habit to build. (Azure starts it again by itself after seven days.)&lt;/li&gt;
&lt;li&gt;Memory is the real limit. B1ms is fine for a few thousand chunks. For 100k documents the estimated HNSW index is over 5 GiB, which will not stay in memory on a 2 GiB server. For my 100k benchmark run on Azure I'll rent a General Purpose server for a couple of hours and delete it right after.&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;My budget for all of this is under 100 USD of Azure spend. The Azure free account helps a lot: 750 hours a month of B1ms and 32 GiB of storage for the first year, which covers one small server running all month.&lt;/p&gt;

&lt;h2&gt;
  
  
  The Azure-only extras
&lt;/h2&gt;

&lt;p&gt;Two things you only get on Azure are worth calling out, with the honest note that I have written and tested the code paths locally but not yet measured them on Azure.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;DiskANN.&lt;/strong&gt; &lt;code&gt;pg_diskann&lt;/code&gt; adds a &lt;code&gt;diskann&lt;/code&gt; index type on top of pgvector's &lt;code&gt;vector&lt;/code&gt; column. It is the same &lt;code&gt;CREATE INDEX ... USING diskann (embedding vector_cosine_ops)&lt;/code&gt; you would expect, with &lt;code&gt;diskann.l_value_search&lt;/code&gt; as the query-time knob. pgrag's migration checks whether the extension exists and skips with a notice if it doesn't, and the benchmark runs it automatically when it is there.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;In-database embeddings.&lt;/strong&gt; With the &lt;code&gt;azure_ai&lt;/code&gt; extension, &lt;code&gt;azure_openai.create_embeddings('text-embedding-3-small', content)&lt;/code&gt; returns an array you can cast to &lt;code&gt;vector&lt;/code&gt;. That turns a backfill into a plain &lt;code&gt;UPDATE&lt;/code&gt;, and it lets a search function embed the question itself, so the app sends text and gets ranked chunks back in one round trip. Each call is a billable API request, so batch it.&lt;/p&gt;

&lt;h2&gt;
  
  
  When you do want a second database
&lt;/h2&gt;

&lt;p&gt;I'm not arguing that nobody needs a vector database. If you have hundreds of millions of vectors, or a very high vector-only query rate that you want to scale separately from your transactional database, or your team simply doesn't run Postgres, a dedicated system can make sense. But I'd want to see the measurement that says so, the same way I'd want to see it before adding any new database to a stack.&lt;/p&gt;

&lt;p&gt;For a team that already runs Postgres and has up to a few million chunks, my suggestion is to start with the database you already back up, secure and know how to operate.&lt;/p&gt;

&lt;h2&gt;
  
  
  Try it
&lt;/h2&gt;

&lt;p&gt;The code is at &lt;a href="https://github.com/sharath568/azure-data-pg-rag" rel="noopener noreferrer"&gt;github.com/sharath568/azure-data-pg-rag&lt;/a&gt;. The local quickstart needs Docker and Python and costs nothing: &lt;code&gt;docker compose up -d&lt;/code&gt;, &lt;code&gt;pgrag init&lt;/code&gt;, &lt;code&gt;pgrag ingest examples/docs&lt;/code&gt;, &lt;code&gt;pgrag search "..."&lt;/code&gt;. The Azure quickstart is one script and one budget e-mail address.&lt;/p&gt;

&lt;p&gt;If you run the benchmark on Azure, especially with DiskANN, I would really like to see your numbers. Open an issue or a PR with the SKU and region, and I'll add them to the repo.&lt;/p&gt;

</description>
      <category>azure</category>
      <category>postgres</category>
      <category>ai</category>
      <category>rag</category>
    </item>
    <item>
      <title>Before You Point AI at Your Oracle Schema</title>
      <dc:creator>Sharath Chandra Kampili</dc:creator>
      <pubDate>Mon, 16 Nov 2026 18:28:31 +0000</pubDate>
      <link>https://dev.to/sharath568/before-you-point-ai-at-your-oracle-schema-5ecm</link>
      <guid>https://dev.to/sharath568/before-you-point-ai-at-your-oracle-schema-5ecm</guid>
      <description>&lt;p&gt;I keep seeing the same demo. Someone copies a few hundred lines of Oracle DDL and a package body into a chat window, asks for PostgreSQL, and gets back something that looks great. It compiles. The tables are there. The functions are there. It took two minutes.&lt;/p&gt;

&lt;p&gt;I spent a lot of years as an Oracle DBA, and my honest reaction to that demo is "nice, now let's find out what it changed."&lt;/p&gt;

&lt;p&gt;Because an Oracle schema isn't just its DDL. A lot of how an Oracle database behaves is never written down in the schema at all. It lives in defaults, in conventions the database enforces quietly, and in code that runs when you aren't looking. A model converting text can only convert what's in the text.&lt;/p&gt;

&lt;p&gt;This post is about those invisible parts, and a small open source tool I built to find them before anyone starts converting: &lt;a href="https://github.com/sharath568/azure-data-oracle2pg-toolkit" rel="noopener noreferrer"&gt;azure-data-oracle2pg-toolkit&lt;/a&gt;.&lt;/p&gt;

&lt;h2&gt;
  
  
  Five things the model can't see
&lt;/h2&gt;

&lt;p&gt;None of these are exotic. You will find most of them in any Oracle schema that has been in production for a while.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;1. An empty string is NULL.&lt;/strong&gt; In Oracle, &lt;code&gt;''&lt;/code&gt; and &lt;code&gt;NULL&lt;/code&gt; are the same thing. So this trigger line, which looks like it clears empty phone numbers:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;IF&lt;/span&gt; &lt;span class="p"&gt;:&lt;/span&gt;&lt;span class="no"&gt;NEW&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;phone_number&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="s1"&gt;''&lt;/span&gt; &lt;span class="k"&gt;THEN&lt;/span&gt;
  &lt;span class="p"&gt;:&lt;/span&gt;&lt;span class="no"&gt;NEW&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;phone_number&lt;/span&gt; &lt;span class="o"&gt;:=&lt;/span&gt; &lt;span class="kc"&gt;NULL&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;span class="k"&gt;END&lt;/span&gt; &lt;span class="k"&gt;IF&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;never actually fires in Oracle, because &lt;code&gt;x = NULL&lt;/code&gt; is never true. Translate it literally to PostgreSQL and it suddenly starts working, which changes behaviour. The same rule means a &lt;code&gt;NOT NULL&lt;/code&gt; &lt;code&gt;VARCHAR2&lt;/code&gt; column also rejects empty strings in Oracle. In PostgreSQL &lt;code&gt;''&lt;/code&gt; sails straight through &lt;code&gt;NOT NULL&lt;/code&gt;. Your constraint just got weaker and nobody touched it.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;2. &lt;code&gt;DATE&lt;/code&gt; has a time.&lt;/strong&gt; Oracle &lt;code&gt;DATE&lt;/code&gt; stores hours, minutes and seconds. PostgreSQL &lt;code&gt;date&lt;/code&gt; doesn't. If the conversion maps &lt;code&gt;DATE&lt;/code&gt; to &lt;code&gt;date&lt;/code&gt; because the names match, you lose the time portion on every row, and every &lt;code&gt;hire_date + 1&lt;/code&gt; in your code turns into an error once the column becomes a &lt;code&gt;timestamp&lt;/code&gt;.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;3. Bare &lt;code&gt;NUMBER&lt;/code&gt; means "up to 38 digits, any scale".&lt;/strong&gt; It isn't an integer and it isn't a float. Models love to turn &lt;code&gt;NUMBER&lt;/code&gt; into &lt;code&gt;integer&lt;/code&gt; or &lt;code&gt;double precision&lt;/code&gt; because that's what the column "looks like". Sometimes that's right. You only know after you look at the data.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;4. Triggers do work the DDL doesn't mention.&lt;/strong&gt; A &lt;code&gt;BEFORE INSERT&lt;/code&gt; trigger that fills in the key from a sequence, stamps &lt;code&gt;last_updated&lt;/code&gt;, uppercases the email and writes to an audit table is part of the table's behaviour. If the model only sees &lt;code&gt;CREATE TABLE&lt;/code&gt;, you get a table that silently stops doing those things.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;5. Packages keep state.&lt;/strong&gt; A variable declared at package level lives for the whole session. Code uses that as a cache, a counter, or a way to pass context from a trigger to a procedure. PostgreSQL functions have nothing like it, and a model converting one function at a time has no way to know the variable is shared.&lt;/p&gt;

&lt;p&gt;There are more. &lt;code&gt;SYSDATE&lt;/code&gt; is server clock time and moves during a transaction, while &lt;code&gt;now()&lt;/code&gt; is frozen at transaction start. &lt;code&gt;'a' || NULL&lt;/code&gt; is &lt;code&gt;'a'&lt;/code&gt; in Oracle and &lt;code&gt;NULL&lt;/code&gt; in PostgreSQL. Oracle code runs with the owner's privileges by default, PostgreSQL code runs as the caller. &lt;code&gt;ROWNUM &amp;lt;= 10 ORDER BY x&lt;/code&gt; gives you ten arbitrary rows, sorted, and a model will "fix" that into a real top 10 without asking.&lt;/p&gt;

&lt;p&gt;The common thread: the source doesn't say any of this. You have to know it.&lt;/p&gt;

&lt;h2&gt;
  
  
  So I wrote it down as code
&lt;/h2&gt;

&lt;p&gt;&lt;code&gt;o2pg&lt;/code&gt; is an offline triage tool. It doesn't connect to anything and it doesn't convert anything. It reads an inventory of your schema and tells you what will hurt when you move to Azure Database for PostgreSQL Flexible Server, including which of those problems are the kind a model will get wrong.&lt;/p&gt;

&lt;p&gt;There are two pieces.&lt;/p&gt;

&lt;p&gt;A read-only SQL*Plus script that a DBA runs against Oracle. It queries the &lt;code&gt;ALL_&lt;/code&gt; dictionary views (tables, columns, constraints, indexes, partitioning, triggers, PL/SQL source, links, jobs, grants and so on) and spools one JSON file. It never touches table data. That matters, because the people who can run things against production and the people doing the migration analysis are often not the same people.&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight shell"&gt;&lt;code&gt;sqlplus -s app_reader/secret@//dbhost:1521/ORCLPDB1 @collector/collect.sql HR,SALES
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;And a Python CLI that analyses the file anywhere:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight shell"&gt;&lt;code&gt;pip install git+https://github.com/sharath568/azure-data-oracle2pg-toolkit
o2pg assess o2pg_inventory.json -f html -o assessment
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;If you just want to see it, the repo ships a sample HR/SALES schema that I made deliberately messy:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight shell"&gt;&lt;code&gt;o2pg sample -o sample_inventory.json
o2pg assess sample_inventory.json
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;h2&gt;
  
  
  What the output looks like
&lt;/h2&gt;

&lt;p&gt;Here is the top of the report for the sample schema, unedited:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;| Metric               | Value                                   |
|----------------------|-----------------------------------------|
| Migration complexity | 86/100 (Very high)                      |
| Effort band          | L: ~2 to 6 person-months (133.3 points) |
| Blockers             | 5                                       |
| AI readiness         | 26/100                                  |
| Rules triggered      | 89 of 97                                |
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;The five blockers are the things Flexible Server simply can't do: a &lt;code&gt;BFILE&lt;/code&gt; column, &lt;code&gt;UTL_FILE&lt;/code&gt; writing CSVs to a server directory, &lt;code&gt;UTL_MAIL&lt;/code&gt; sending mail from inside a procedure, a wrapped package body nobody has the source for, and a scheduler job that runs a shell script. None of those are conversion problems. They are architecture decisions, and it's much better to find them in week one.&lt;/p&gt;

&lt;p&gt;The part I care about most is the AI readiness section. It lists every rule that fired because of implicit behaviour, with a sentence you can paste straight into a prompt or a conversion spec:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;26/100. Do not paste this schema into an LLM as-is. Too much behaviour is
implicit; convert with a written spec and review every object.

1. DATE columns carry a time of day (O2PG-DT-003, 6 object(s)): Oracle DATE
   includes hours, minutes and seconds. Tell the model to map DATE to
   timestamp(0), not date.
2. Package-level state (global variables) (O2PG-PL-002, 2 object(s)): These
   packages keep state between calls. A model converting one function at a
   time cannot see that; tell it which variables are session state and how
   you want them replaced (for example current_setting('app.x')).
3. Autonomous transactions (O2PG-PL-003, 1 object(s)): Autonomous transactions
   commit even when the caller rolls back. Models tend to drop the pragma and
   silently change that behaviour; tell it how you want it handled.
4. Empty string literals ('' is NULL in Oracle) (O2PG-SQ-008, 1 object(s)):
   Oracle treats '' as NULL everywhere. A model will translate '' literally.
   Tell it to treat every '' as NULL and to flag comparisons with '' for review.
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;That list is the point of the whole exercise. I'm not against using AI for migrations. I use it. But the model needs the same briefing a new team member would get, and this generates the briefing from your actual schema instead of from memory.&lt;/p&gt;

&lt;p&gt;There's also &lt;code&gt;o2pg map-types&lt;/code&gt;, which writes suggested PostgreSQL DDL with conservative type choices and leaves &lt;code&gt;TODO&lt;/code&gt; comments where a person has to decide:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;CREATE&lt;/span&gt; &lt;span class="k"&gt;TABLE&lt;/span&gt; &lt;span class="n"&gt;sales&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;orders&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;
    &lt;span class="n"&gt;order_id&lt;/span&gt; &lt;span class="kt"&gt;bigint&lt;/span&gt; &lt;span class="k"&gt;NOT&lt;/span&gt; &lt;span class="kc"&gt;NULL&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="n"&gt;order_date&lt;/span&gt; &lt;span class="kt"&gt;timestamp&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="mi"&gt;0&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;NOT&lt;/span&gt; &lt;span class="kc"&gt;NULL&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;
    &lt;span class="n"&gt;order_tz_ts&lt;/span&gt; &lt;span class="kt"&gt;timestamptz&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="mi"&gt;6&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;  &lt;span class="c1"&gt;-- PostgreSQL normalises to UTC and does not keep the original offset&lt;/span&gt;
    &lt;span class="k"&gt;CONSTRAINT&lt;/span&gt; &lt;span class="n"&gt;orders_pk&lt;/span&gt; &lt;span class="k"&gt;PRIMARY&lt;/span&gt; &lt;span class="k"&gt;KEY&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;order_id&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;PARTITION&lt;/span&gt; &lt;span class="k"&gt;BY&lt;/span&gt; &lt;span class="no"&gt;RANGE&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;order_date&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;span class="c1"&gt;-- TODO ORDERS_PK must include the partition key (ORDER_DATE)&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;That last line is a good example of something that is easy to miss. Oracle is happy to have a primary key on &lt;code&gt;order_id&lt;/code&gt; alone for a table partitioned by &lt;code&gt;order_date&lt;/code&gt;, because it uses a global index. PostgreSQL requires every primary key on a partitioned table to include the partition key. You either weaken the uniqueness guarantee or rethink the partitioning, and you want to have that conversation before you move 180 million rows.&lt;/p&gt;

&lt;h2&gt;
  
  
  The Azure part
&lt;/h2&gt;

&lt;p&gt;Moving to a managed service changes the rules in a few specific ways, and the tool has a category just for that.&lt;/p&gt;

&lt;p&gt;There's no file system you can reach from SQL, so &lt;code&gt;UTL_FILE&lt;/code&gt;, &lt;code&gt;BFILE&lt;/code&gt; and external tables need a new home, usually Azure Blob Storage plus something outside the database. There are no outbound calls from the database, so &lt;code&gt;UTL_HTTP&lt;/code&gt; and &lt;code&gt;UTL_MAIL&lt;/code&gt; become an outbox table and a worker. OS-level scheduler jobs move to whatever runs your other scheduled work.&lt;/p&gt;

&lt;p&gt;On the other hand, a lot of what you need is there once you allow-list it. &lt;code&gt;pg_cron&lt;/code&gt; covers most &lt;code&gt;DBMS_SCHEDULER&lt;/code&gt; jobs. &lt;code&gt;pg_partman&lt;/code&gt; handles the partition creation that Oracle interval partitioning did for you. &lt;code&gt;orafce&lt;/code&gt; gives you &lt;code&gt;NVL&lt;/code&gt;, &lt;code&gt;DECODE&lt;/code&gt;, &lt;code&gt;ADD_MONTHS&lt;/code&gt; and friends as a bridge while you convert. &lt;code&gt;postgis&lt;/code&gt; takes over from Oracle Spatial. The report lists which of these your schema actually needs, and the repo includes a small Bicep template for a Burstable B1ms server so you can load the suggested DDL and watch what breaks.&lt;/p&gt;

&lt;h2&gt;
  
  
  What it doesn't do
&lt;/h2&gt;

&lt;p&gt;I'd rather say this up front.&lt;/p&gt;

&lt;p&gt;It's not a parser. Code is scanned with regular expressions after stripping comments and masking string literals. That's good enough to find patterns reliably, but it can't follow types or look inside dynamic SQL. The scores are heuristics for comparing objects and schemas, not a project estimate. And the collector script was written against the 19c dictionary documentation and is checked by the test suite for shape, but it hasn't been run against a live database in CI yet. If it breaks on yours, I really want the issue.&lt;/p&gt;

&lt;h2&gt;
  
  
  What's next
&lt;/h2&gt;

&lt;p&gt;The toolkit has moved on since the first version. v0.2 added &lt;code&gt;o2pg convert&lt;/code&gt;, which makes the mechanical PL/SQL to PL/pgSQL rewrites and leaves everything else as clearly marked review points. v0.3 adds &lt;code&gt;o2pg ai-convert&lt;/code&gt;, which hands only those review points to a model and accepts nothing that doesn't compile in PostgreSQL and return the same results Oracle did on a set of recorded test cases. That's the next post. After that come data validation between the two databases, and a harness to compare query performance before cutover.&lt;/p&gt;

&lt;p&gt;If you have an Oracle schema headed for PostgreSQL, run the collector, read the AI readiness section, and then decide how much you want to hand to a model. The code is MIT licensed at &lt;a href="https://github.com/sharath568/azure-data-oracle2pg-toolkit" rel="noopener noreferrer"&gt;github.com/sharath568/azure-data-oracle2pg-toolkit&lt;/a&gt;. Rules for things that have bitten you are the best contribution I could ask for.&lt;/p&gt;

</description>
      <category>azure</category>
      <category>postgres</category>
      <category>oracle</category>
      <category>database</category>
    </item>
  </channel>
</rss>
