Agentic AI Learning Portal

Appearance

🗄️ postgresql-database ​

Your Linux environment runs an active PostgreSQL service on port 5432 which acts as the relational foundation and long-term memory store for your AI agents and workflows.

For the corresponding hands-on module, see agent-memory.md.

🔑 Database Connection (.env) ​

Your credentials and connection parameters for agent frameworks are typically defined in a .env file at the root of your workspace:

text
POSTGRES_HOST="127.0.0.1"
POSTGRES_PORT="5432"
POSTGRES_DATABASE="postgres"
POSTGRES_USER="agent_user"
POSTGRES_PASSWORD="YOUR_DB_PASSWORD"

WARNING

Keep your .env configuration file protected and do not commit it to version control systems like GitHub. Load these values in your application code using packages like python-dotenv or native system environment variables.

🛠️ PostgreSQL Administration (Linux CLI) ​

Your ~/.bash_aliases file exposes rapid-access commands to manage the database:

1. Check Service Status ​

Use this alias to quickly verify if the PostgreSQL daemon is active and listening.

bash
pg-status
# Output: /var/run/postgresql:5432 - accepting connections

2. Start PostgreSQL ​

If the database is down, execute this command to start the service.

bash
pg-start
# (Runs sudo service postgresql start)

3. Stop PostgreSQL ​

To safely shut down the database without data corruption, run this command.

bash
pg-stop
# (Runs sudo service postgresql stop)

4. Restart PostgreSQL ​

Apply configuration changes immediately by restarting the PostgreSQL service.

bash
pg-restart
# (Runs sudo service postgresql restart)

5. Access the SQL Shell (psql) ​

To log in manually and execute SQL queries:

bash
psql -h 127.0.0.1 -U agent_user -d postgres

🧠 PostgreSQL as a Vector Store (pgvector) ​

For agentic memory systems, semantic search, and RAG workflows, we leverage the pgvector extension in PostgreSQL. This allows storing high-dimensional vector embeddings directly in your relational tables.

Quick Setup for pgvector: ​

If you create a new database for an AI agent, enable the extension:

sql
-- Connect to your database and execute:
CREATE EXTENSION IF NOT EXISTS vector;

Python/SQL Example (LangChain/PGVector with Google Gemini): ​

You can interface with your local database in Python using LangChain's PGVector integration.

python
import os
from langchain_postgres.vectorstores import PGVector
from langchain_google_genai import GoogleGenAIEmbeddings

# Connection string built from environment variables:
db_user = os.getenv("POSTGRES_USER", "agent_user")
db_pass = os.getenv("POSTGRES_PASSWORD", "YOUR_DB_PASSWORD")
db_host = os.getenv("POSTGRES_HOST", "127.0.0.1")
db_port = os.getenv("POSTGRES_PORT", "5432")
db_name = os.getenv("POSTGRES_DATABASE", "postgres")

connection = f"postgresql+psycopg://{db_user}:{db_pass}@{db_host}:{db_port}/{db_name}"

# Initialize Google Gemini Embeddings
gemini_embeddings = GoogleGenAIEmbeddings(model="models/embedding-001")

vector_store = PGVector(
    embeddings=gemini_embeddings,
    collection_name="agent_memory",
    connection=connection,
)

📈 Performance Tuning for Linux ​

Because PostgreSQL runs on your local Linux system, optimizing its memory and system buffers is crucial for handling high-volume agent queries:

  1. Memory Boundary: By default, PostgreSQL is tuned conservatively. Under your optimized 12GB system RAM allocation, you can safely set shared_buffers = 3GB and work_mem = 64MB in /etc/postgresql/16/main/postgresql.conf for faster queries during massive embedding lookups.
  2. Vector Indexing (pgvector): For datasets exceeding 10,000 memories, always build an HNSW index to accelerate semantic queries.