AI Engineer - My Proof-of-Concept RAG Application

A Retrieval-Augmented Generation (RAG) application is an AI framework that enhances Large Language Models (LLMs) by integrating a targeted information retrieval mechanism. This allows LLMs to access and query domain-specific, external data beyond their pre-trained knowledge base, leading to more accurate, up-to-date, and contextually grounded responses. 

To demonstrate this concept, I developed a command-line Python utility named llm_chat.py. The application acts as a specialized AI Assistant for Database Administrators (DBAs). It takes a user’s plain-text query, converts it into a vector embedding, and queries a PostgreSQL database powered by the pgvector extension to pull the top 10 most relevant historical DBA kowledge base event  posts. It then passes both the question and the retrieved contextual data to Gemini to generate two distinct responses: a step-by-step instructional answer and an executive narrative summary.

How the RAG Architecture Works 

Standard Large Language Models generate answers using only the general knowledge present in their training data. This program implements true RAG by breaking the response pipeline into three distinct phases:

1. Retrieval: Instead of querying the model directly, the application first retrieves exact, domain-specific knowledge (matching historical event titles and IDs) from a PostgreSQL vector database using cosine distance searching (<=>).
2. Augmentation: It augments the prompt by inserting those retrieved database logs directly into the CONTEXT block of the system prompt. 
3. Generation: Finally, the LLM generates an answer strictly grounded in your private, real-world DBA data - mitigating generic answers and preventing hallucinations. 

Example Outputs 

Example 1: Software Installation Query

(.venv) C:\Users\dsun\Denis_files\python_proj\ai_apps\dbaets_pgvector>python llm_chat.py
Ask a question to your expert DBA AI Assistant: (ctrl-c to exit)

how to install Oracle 19c software?
Your query: how to install Oracle 19c software?

========== Your DBA AI will answer your questions based on the following context ==================
event_id:   3241  score:0.140  title: Install Oracle Database 19c software on Linux 9
event_id:   2757  score:0.266  title: Solaris 11.4 install oracle-database-preinstall-19c 
event_id:   2774  score:0.288  title: New Oracle 19c - Grid Install Error / Remediation  on Redhat Linux 8.4
event_id:   2762  score:0.294  title: Upgrade a 12.2 RAC database to 19.3
event_id:   2765  score:0.315  title: Oracle GI 19.3 on Linux 8 installation issue and resolution
event_id:   2771  score:0.333  title: Cloning An Oracle 19c Oracle Home through gold image
event_id:   2763  score:0.347  title: Oracle Database 19.0.0.0.0 is certified on Linux x86-64 Red Hat Enterprise Linux 8 Update 0+
event_id:   2596  score:0.351  title: oracle 19c multitenant license notes
event_id:   2634  score:0.368  title: check required linux os packages for Oracle software
event_id:   2866  score:0.374  title: clone a 19c database using rman duplicate from active database 


======= How to do things as a DBA =========================================================
Question: how to install Oracle 19c software?
Answer:
1. **OS Prep:** Use `oracle-database-preinstall-19c` package (if available for your OS) to install required packages. Manually install if needed.
2. **Download:** Download Oracle 19c software from Oracle.
3. **Extract:** Unzip the downloaded software.
4. **Run Installer:** Execute `runInstaller` from the extracted directory.
5. **Follow Prompts:** Choose installation options. For RAC, install Grid Infrastructure first.
6. **Root Scripts:** Run scripts as root when prompted.
7. **Verify:** Check logs for errors after installation.

   Consider Gold Image cloning for faster deployments.


======= Your DBA AI is giving summarization ==================================================
Question: how to install Oracle 19c software?
Answer:
DBA tasks involve installing Oracle 19c on Linux/Solaris, addressing installation errors, upgrading RAC databases, cloning Oracle homes/databases, verifying OS package requirements, and working with multitenant licenses.

Example 2: Performance Tuning Query

(.venv) C:\Users\dsun\Denis_files\python_proj\ai_apps\dbaets_pgvector>python llm_chat.py
Ask a question to your expert DBA AI Assistant: (ctrl-c to exit)

how to add hint to tune sql?
Your query: how to add hint to tune sql?

========== Your DBA AI will answer your questions based on the following context ==================
event_id:   1775  score:0.286  title: postgresql pg_hint_plan -- controls execution plan with hinting phrases in comment of special form
event_id:   3183  score:0.287  title: General advice for developer to tune sql
event_id:    216  score:0.315  title: Suggest tuning a query by adding hint to skip a particular index - NO_INDEX(smc SMC_IDX_RELEASE_DATE)
event_id:   3290  score:0.367  title: mtasCronOptPurge_request.sh - tuning a delete statement adding hints
event_id:   1908  score:0.369  title: STRAIGHT_JOIN sql tuning
event_id:   3028  score:0.371  title: Run SQL tuning advisor for 1r8xa9fumhq2d
event_id:   2093  score:0.377  title: My SQL Tuning Primer
event_id:    248  score:0.392  title: about query 4xx9vuzyj1m71 - RE Dev SQL tuning opportunity- RE Introscope Alert Message
event_id:    245  score:0.394  title: SQL -2rg6vs7y2kr64 - takes long time - fixed by restoring old stats to generate a better [execution plan] [sql tuning]
event_id:   3303  score:0.396  title: spfprdsc - 11trc93bakbv0 sql tuning


======= How to do things as a DBA =========================================================
Question: how to add hint to tune sql?
Answer:
To tune SQL using hints (like `NO_INDEX` or `STRAIGHT_JOIN`), embed them within a special comment block in your SQL:

/*+ NO_INDEX(table_alias index_name) */

For example:

SELECT /*+ NO_INDEX(smc SMC_IDX_RELEASE_DATE) */ * FROM smc WHERE ...

This tells the optimizer to skip `SMC_IDX_RELEASE_DATE` on table `smc`. Remember to test thoroughly! Consider also using SQL Tuning Advisor for systematic optimization.


======= Your DBA AI is giving summarization ==================================================
Question: how to add hint to tune sql?
Answer:
The DBA tuned SQL queries using hints within comments. Examples include skipping indexes (NO_INDEX) and forcing join order (STRAIGHT_JOIN). The DBA also used SQL Tuning Advisor and restored old statistics to improve execution plans. Several specific queries were tuned.

Prompt Engineering & Future Enhancements 

Notice how applying two different prompt templates to the exact same retrieved context yields dramatically different outputs tailored to specific roles: 

Instructional Prompt (Prompt 1):

Designed to act as an expert senior mentor explaining practical, step-by-step procedures to junior DBAs.

prompt = f"""
INSTRUCTIONS:
You are an expert Oracle database administrator, based on the context provided, you can explain how to do things to any junior to mid-level DBAs.
Please limit your answer within 2000 characters

CONTEXT:
{context}

QUESTION:
{query}

ANSWER:
"""

Summarization Prompt (Prompt 2): 

Designed to synthesize historical enterprise activities into a high-level operational narrative.

prompt = f"""
INSTRUCTIONS:
Based on the context provided, you can summarize and give a narrative about what are the tasks or activities DBA performed.
Please limit your answer within 2000 characters

CONTEXT:
{context}

QUESTION:
{query}

ANSWER:
"""

Currently, this proof of concept extracts embeddings exclusively from Event Titles in the DBAETS knowledge base. In future iterations, I plan to chunk and generate vector embeddings from both the Event Titles and full Event Descriptions/Body Content. Combining title metadata with rich document content will significantly increase context granularity, enabling Gemini to produce even deeper, more accurate diagnostic guidance. 

Summary 

This proof of concept demonstrates how easily a local enterprise dataset - such as DBAETS event posts - can be transformed into an intelligent operational assistant using RAG architecture. By pairing vector similarity search in PostgreSQL (pgvector) with the reasoning capabilities of Gemini, llm_chat.py bridges the gap between static enterprise documentation and interactive, multi-purpose guidance. Whether generating actionable technical steps for junior staff or summarizing complex administrative histories for management, RAG transforms raw relational data into an indispensable knowledge retrieval tool.

AI Engineer - Embedding Generation from Gemini API and Storing in PostgreSQL

In this post, I first demonstrate that sample Python programs have been developed and tested to do the following two tasks: 

1. Create embeddings from sample source data using the Germini API 

2. Use PostgreSQL as a vector database and store embeddings data in it using pgvector. 

Then I demonstrate similarity search in PostgreSQL with pgvector. 

1. Source data preparation: 

 From the DBAETS application `events` table in the PostgreSQL, dumping ` event_id` and `title` data into a csv file. Total 2021 records.

 \copy ( select event_id, title from events order by 1 ) to 'events_title.csv' with csv 

 The `events` table stores data about database administration events, each entry represents a certain kind of DBA task or activity. The `title` of the `event` table is trivial, but the purpose of using it as the source of content is to make me familiar with the embedding creation and storing process. The end goal is to use the `description` column of the `events` table, which contains a detailed description of the event, therefore providing the useful domain knowledge for DBAs.

 2. Embedding generation 

The python program emb_to_csv.py is developed to generate embedding from source csv file and generate a csv file including embeddings of `title`. Gemini API is used with model: ‘text-embedding-004’, which generates vectors with 768 dimensions .

==============  screenshot run the program ==============
(.venv) C:\Users\dsun\Denis_files\python_proj\ai_apps\dbaets_pgvector>python emb_to_csv_2.py
   event_id                                              title
0         3  PREP CALL for the 2pm actual call to discuss r...
1         4                   Query Certification / 04-11-2012
2         5        [DemoApp] [Migration] -  RD175935 - Tonight
3         6  RE: myhostpd3:testprod3 ALERT_LOG Error: [SR 3-...
4         7                    space add in mrosple/mxdsacdd02
total batches : 22 batch size: 100 total records: 2120
process batch no: 0
loop: 0 event_id: 3
loop: 10 event_id: 13
loop: 20 event_id: 24
loop: 30 event_id: 34
loop: 40 event_id: 44
loop: 50 event_id: 54
loop: 60 event_id: 64
loop: 70 event_id: 74
loop: 80 event_id: 84
loop: 90 event_id: 94
process batch no: 1
loop: 100 event_id: 104
loop: 110 event_id: 114
loop: 120 event_id: 124
loop: 130 event_id: 134
loop: 140 event_id: 144
loop: 150 event_id: 155
loop: 160 event_id: 165
loop: 170 event_id: 175
loop: 180 event_id: 185
loop: 190 event_id: 195
process batch no: 2
loop: 200 event_id: 205
…
(ommitting ..)

I intentionally used some small batch size and sleep 200 seconds between batches to avoid Gemini API free tier rate limit, if I don't do that, I could end up with errors "RATE_LIMIT_EXCEEDED". 

3. Embedding loacding 

The python program emb_csv_to_pgvector.py is developed to load the embeddings from csv to a PostgreSQL table. 

4. Similarity search using SQL 

 The following query calculates vector distances using the <#> negative inner product operator to compare event titles against a target event (event_id = 2690). It retrieves the target event's self-similarity score alongside the top 10 most similar distinct events, returning their event IDs, titles, and similarity scores in a single combined result.

etsdb=> -- top event titles that are mostly similar to first title
etsdb=> (
etsdb(> select event_id, title,  embedding <#> embedding as similarity
etsdb(> from event_title_embeddings
etsdb(> where event_id=2690
etsdb(> )
etsdb-> union all
etsdb-> (
etsdb(> select t.event_id , t.title , (t.embedding <#> i.embedding ) as similarity
etsdb(> from event_title_embeddings t join event_title_embeddings i
etsdb(> on t.event_id != i.event_id
etsdb(> and i.event_id=2690
etsdb(> order by similarity
etsdb(> limit 10
etsdb(> )
etsdb-> ;
 event_id |                              title                               |     similarity
----------+------------------------------------------------------------------+---------------------
     2690 | Master note for operation support - mydb2                        | -0.9999982118606567
     2431 | Master note for operation support - mydb1 BVC                    | -0.8375328183174133
     2629 | Master note for operation support - omrtppdb OEM repostory       | -0.8193813562393188
     2444 | Master note for operation support - iexdb                        | -0.7917341589927673
     2450 | mydb4 database support master notes                              | -0.7660208344459534
     2459 | master note for operation support - mydb12sc APP1                | -0.766010582447052
     2876 | Operations Master notes for EV6V- mmypos on dbhost41/42/43       | -0.6405361294746399
     2953 | Master Notes -APP2                                               | -0.6208126544952393
     2937 | SCM master notes                                                 | -0.6061583161354065
     2275 | mydb1 database related doc                                       | -0.6051017045974731
     2879 | Master Notes - APP1 - mydb3                                      | -0.6019692420959473
(11 rows)

In summary, I successfully established an end-to-end embedding pipeline using the Gemini text-embedding-004 model and PostgreSQL with pgvector. By generating 768-dimensional vector embeddings from DBA event titles and querying them using inner product distance (<#>), the setup demonstrated accurate semantic similarity retrieval across database task records. This foundational work sets the stage for scaling up to full event descriptions and implementing a robust domain-specific vector search system.