The Role of PostgreSQL in the AI World

As artificial intelligence (AI) continues to revolutionize industries, the need for robust, scalable, and efficient data management solutions has never been greater. PostgreSQL, with its extensibility, performance, and advanced data capabilities, plays a crucial role in enabling AI-driven applications. This talk explores how PostgreSQL supports AI workloads, from managing vast datasets and integrating with machine learning frameworks to leveraging extensions like PostgreSQL ML, TimescaleDB, and JSONB for AI-ready storage and querying. Attendees will gain insights into best practices for using PostgreSQL in AI pipelines, optimizing performance for AI inference, and harnessing the power of open-source innovation to drive intelligent decision-making. Whether you're a data engineer, AI researcher, or PostgreSQL enthusiast, this session will showcase why PostgreSQL is an essential component in the AI ecosystem.

Transcript

1. © 2025, Amazon Web Services, Inc. or its affiliates. All rights reserved. The Role of PostgreSQL in the AI world Narendra Tawar Sr Database Solution Architect Amazon Web Services
2. © 2025, Amazon Web Services, Inc. or its affiliates. All rights reserved. About ME • Senior Database Specialist Solution Architect at AWS. • Over two decades working with databases, but always a student. • I live by 'Learn and Be Curious 'it's not just an Amazon leadership principle for me, it's an obsession. Narendra Tawar
3. © 2025, Amazon Web Services, Inc. or its affiliates. All rights reserved. Agenda What is Artificial Intelligence Why PostgreSQL PostgreSQL Capabilities Patterns Questions
4. © 2025, Amazon Web Services, Inc. or its affiliates. All rights reserved. What is Artificial Intelligence Artificial Intelligence is a field of computer science dedicated to solving problems that we commonly associate with human intelligence Image Creation Agentic AI- Customer care Image Recognition Speech to Text
5. © 2025, Amazon Web Services, Inc. or its affiliates. All rights reserved. Artificial Intelligence’s Demands Relational facts Time Series Events Json Logs Geo-Spatial Data
6. © 2025, Amazon Web Services, Inc. or its affiliates. All rights reserved. Why PostgreSQL for AI
7. © 2025, Amazon Web Services, Inc. or its affiliates. All rights reserved. Reliability • AI Pipelines require reproducibility • Partial Writes or race conditions cause training drift • PostgreSQL uses MVCC (Multi-Version Concurrency Control)
8. © 2025, Amazon Web Services, Inc. or its affiliates. All rights reserved. Extensibility & Ecosystem • Extensions allow new data types, indexes, and languages • Great Python/R connectors mean PostgreSQL fits right into existing ML stacks.
9. © 2025, Amazon Web Services, Inc. or its affiliates. All rights reserved. Vector embeddings • High dimensional vectors • Each dimension encodes semantic meaning • Generated by the embedding models • Enable advanced analytics and insights [-0.02690117619931698, -0.03068459965288639, 0.0047440663911402225, 0.017332255840301514, 0.06465624272823334, 0.05347904562950134, - 0.11054572463035583, 0.01960226148366928, -0.049086809158325195, 0.03278972953557968, 0.0918821394443512, 0.07920648157596588, 0.04882682487368584, -0.020079471170902252, -0.01958928257226944, - 0.025268465280532837, 0.06367341428995132, -0.02912893518805504, …….. ] or Text Embedding model
10. © 2025, Amazon Web Services, Inc. or its affiliates. All rights reserved. What is a Vector database? Image Documents Audio 0.35 0.1 0 0.9 001.0 00 0001.0 0 0… 0.35 0.1 0 0.8 001.0 00 0001.0 0 0… 0.15 0.1 0 0.7 001.0 00 0001.0 0 0… Vector Database Build AI-powered Application Dense Vector Encodings Sparse Vector Encodings (Automatic metadata extraction) Retrieve content most similar to some content: question context, image, music clip… Content classification Salient terms and topics … Retrieve most relevant content by key terms (metadata)… Raw Data Vector Embedding Space Dev-ready and Operationalized Consumable Machine Learning Model (Embedding)
11. © 2025, Amazon Web Services, Inc. or its affiliates. All rights reserved. Vector embeddings and semantic search Search for: „Italian place to eat“ This restaurant offers Mediterranean cuisine. The finest recipes from sunny Sicily. This is the best Italian place to eat. Semantic search Full-text search
12. © 2025, Amazon Web Services, Inc. or its affiliates. All rights reserved. Visual Search
13. © 2025, Amazon Web Services, Inc. or its affiliates. All rights reserved. Retrieval Augmented Generation (RAG) • Modern LLMs can learn new skills from prompt • In-Context Learning • No Model training required • Integrate Domain data to prompt Use the following pieces of context to answer the question at the end. Context: Last week, the company sold 1000 devices. Question: What is the growth rate compared to the last week of January? The growth rate compared to the last week of January is approximately 42.86% Instruction Context Question LLM
14. © 2025, Amazon Web Services, Inc. or its affiliates. All rights reserved. PostgreSQL and AI/ML applications • Existing applications using PostgreSQL are being updated to support new Generative AI tools such as Amazon Bedrock • The "vector" is the lingua franca output from AI/ML models • PostgreSQL natively supports storing vectors: ARRAY and cube
15. © 2025, Amazon Web Services, Inc. or its affiliates. All rights reserved. Why use PostgreSQL for vector searches? • Existing client libraries work without modification • Convenient to keep app + AI/ML data in same database • PostgreSQL acts as persistent transactional store while working with other vector search systems (e.g. OpenSearch)
16. © 2025, Amazon Web Services, Inc. or its affiliates. All rights reserved. JSONB for Metadata CREATE TABLE model_runs ( id bigserial PRIMARY KEY, model_name text, version text, hyperparams jsonb, metrics jsonb, created_at timestamptz DEFAULT now() ); • Store experiment metadata, hyperparameters, pipeline logs, variable document metadata without rigid schema.
17. © 2025, Amazon Web Services, Inc. or its affiliates. All rights reserved. JSONB for Metadata • Index common JSONB keys with GIN expressions if you query them frequently. • Use jsonb for flexible fields, but promote widely-used keys into columns for performance. Best practices
18. © 2025, Amazon Web Services, Inc. or its affiliates. All rights reserved. Full Text Search • Use tsvector and text search to filter candidate documents before vector search • Reduces false positives and saves compute. ALTER TABLE documents ADD COLUMN doc_tsv tsvector; UPDATE documents SET doc_tsv = to_tsvector('english', content); CREATE INDEX idx_documents_doc_tsv ON documents USING GIN(doc_tsv);
19. © 2025, Amazon Web Services, Inc. or its affiliates. All rights reserved. Time-Series with TimescaleDB • High ingest rates and rollups are common for sensor or event features. • Timescale provides hypertables and continuous aggregates for efficient time-based features CREATE EXTENSION IF NOT EXISTS timescaledb; CREATE TABLE metrics ( device_id text, ts timestamptz NOT NULL, temperature double precision, humidity double precision ); SELECT create_hypertable('metrics', 'ts'); inserts are automatically partitioned by time
20. © 2025, Amazon Web Services, Inc. or its affiliates. All rights reserved. Time-Series with TimescaleDB Typical AI / ML Use Cases Sensor & IoT Features • Store high-frequency readings (temperature, GPS, accelerometer). • Downsample with continuous aggregates to create hourly or daily features for models. User Behavior Streams • Capture clickstreams or app events. • Compute rolling windows (e.g., “actions in last 5 minutes”) for real-time personalization. Model Monitoring • Log inference requests and prediction errors over time. • Build dashboards to detect drift or spikes.
21. © 2025, Amazon Web Services, Inc. or its affiliates. All rights reserved. Geospatial Intelligence (PostGIS) • PostGIS is a PostgreSQL extension that adds full spatial (GIS) capabilities to the database • It turns Postgres into a geospatial engine • deal for AI applications involving location, movement, or spatial relationships. CREATE EXTENSION IF NOT EXISTS postgis; CREATE TABLE charging_stations ( id SERIAL PRIMARY KEY, name TEXT, location GEOGRAPHY(Point, 4326) -- longitude/latitude in WGS84 );
22. © 2025, Amazon Web Services, Inc. or its affiliates. All rights reserved. Geospatial Intelligence (PostGIS) SELECT name, ST_Distance(location, 'SRID=4326;POINT(-122.410 37.775)'::geography) AS meters_away FROM charging_stations ORDER BY meters_away LIMIT 1; CREATE INDEX idx_charging_location ON charging_stations USING GIST(location); Query – Nearest Station to a User
23. © 2025, Amazon Web Services, Inc. or its affiliates. All rights reserved. Geospatial Intelligence (PostGIS) Typical AI / ML Use Cases Predictive Logistics: • Route optimization using ST_Distance and ST_Intersects to avoid congested areas. Recommendation Systems: • Location-aware recommendations (restaurants, EV chargers, bike stations).
24. © 2025, Amazon Web Services, Inc. or its affiliates. All rights reserved. Foreign Data Wrappers • Foreign Data Wrappers let PostgreSQL query external data sources as if they were local tables. • Think of FDW as a universal adapter: you keep SQL as the interface while the actual data lives elsewhere • CREATE EXTENSION IF NOT EXISTS postgres_fdw;
25. © 2025, Amazon Web Services, Inc. or its affiliates. All rights reserved. Foreign Data Wrappers Typical AI / ML Use Cases Unified Feature Access Join training data across multiple systems (data lake, cloud warehouse, transactional DB) with a single SQL query Real-Time or Near–Real-Time Query fresh data in place—no nightly ETL lag—useful for up-to-the-minute features or model monitoring. Cost & Storage Efficiency No need to duplicate large datasets; you query where they live.
26. © 2025, Amazon Web Services, Inc. or its affiliates. All rights reserved. Key Takeaways • PostgreSQL is a versatile AI data platform features, vectors, and metadata. • Extensions like pgvector, Timescale, PgAI, and PostGIS unlock powerful use cases. • Start small: build a feature table or a simple RAG pipeline to gain experience.
27. © 2025, Amazon Web Services, Inc. or its affiliates. All rights reserved. Resources PGVector Github Repo PostGIS Github Repo Timescaledb Github Repo MLFlow PostgreSQL Installation
28. © 2025, Amazon Web Services, Inc. or its affiliates. All rights reserved. Thank You! Narendra Tawar tawarn@amazon.co.nl /in/narendra-tawar