AI for Postgres

OpenShift Commons Gathering Raleigh 2023
https://commons.openshift.org/gatherings/raleigh-23-oct-18/
October 18, 2023 ~ Raleigh, North Carolina

Speakers:
Bob Pacheco, Crunchy Data

YouTube Playlist:
https://youtube.com/playlist?list=PLaR6Rq6Z4IqfydAvmOWDVt6pBnbVVyejd&feature=shared

Join the community: https://commons.openshift.org/join

Transcript

1. AI for Postgres Bob Pacheco
2. • Datatypes • Transactional DDL • Foreign Data Wrappers • Concurrent Index Creation • Conditional indexes • JSON • Common Table Expressions • JSON/JSONB • Vector / AI support • Listen/Notify • Upsert • Partitioning • Window function • Continued innovation Why Postgres ✔ Established, Reliable and Secure ✔ Feature Rich ✔ Extensibility ✔ No Central Owner ✔ Hiring (Open Source) The Technical Details
3. Load data Table "public.recipes" Column | Type | Collation | Nullable | Default -------------+---------+-----------+----------+---------------------------------- id | integer | | not null | generated by default as identity name | text | | | description | text | | | Embedding | vector | | | Indexes: "recipes_pkey" PRIMARY KEY, btree (id)
4. pgvector
5. Classify data openai = OpenAI::Client.new(access_token: ENV['OPENAI_API_KEY']) while recipe = DB[:recipes].where(embedding: nil).exclude(description: nil).first do submitted_value = recipe[:description].gsub(/\n/, ' ') response = openai.embeddings( parameters: { model: 'text-embedding-ada-002', input: submitted_value } ) begin embedding_value = response["data"][0]["embedding"].to_s DB[:recipes].where(id: recipe[:id]).update(embedding: embedding_value) rescue puts [$!, response].inspect end sleep 1.2 end
6. pgvector
7. Querying for similarities recipe_1.embedding <=> recipe_2.embedding SELECT recipe_1.id, recipe_1.name, recipe_2.id, recipe_2.name FROM (SELECT * FROM recipes WHERE name = 'Pizza') recipe_1, recipes AS recipe_2 ORDER BY recipe_1.embedding <=> recipe_2.embedding LIMIT 4;
8. Results id | name | id | name -----+-------+-----+------------------- 431 | Pizza | 431 | Pizza 431 | Pizza | 433 | Pizza, 12 in, fzn 431 | Pizza | 126 | Chicken, parmesan 431 | Pizza | 435 | Pizza, treats (4 rows)
9. Querying for similarities with exclusions SELECT recipe_1.id, recipe_1.name, recipe_2.id, recipe_2.name FROM (SELECT * FROM recipes WHERE name = 'Pizza') recipe_1, recipes AS recipe_2 WHERE LOWER(recipe_2.name) NOT LIKE '%pizza%' ORDER BY recipe_1.embedding <=> recipe_2.embedding LIMIT 4;
10. Results id | name | id | name -----+-------+-----+------------------------------------- 431 | Pizza | 126 | Chicken, parmesan 431 | Pizza | 31 | Beef, ground, hamburger, w/parmesan 431 | Pizza | 211 | Dish, eggplant, parmesan 431 | Pizza | 229 | Dish, lasagna (4 rows)
11. Querying for opposites SELECT recipe_1.id, recipe_1.name, recipe_2.id, recipe_2.name FROM (SELECT * FROM recipes WHERE name = 'Corn Dog' LIMIT 1) recipe_1, recipes AS recipe_2 WHERE recipe_2.description IS NOT NULL ORDER BY recipe_1.embedding <=> recipe_2.embedding DESC LIMIT 2;
12. Results id |. Name. | id | name -----+----------+-----+-------------------------- 165 | Corn Dog | 538 | Salad, green, tossed 165 | Corn Dog | 519 | Salad, chicken, tropical (2 rows)
13. About me • Sr. Solutions Architect at Crunchy Data • 25 years of development and database experience. • Specialize in running PostgreSQL on OpenShift and other cloud platforms. • Long time MMORPG player. ◦ For the Horde! Blogs: • https://www.crunchydata.com/blog/author/bob-pacheco Email: • bob.pacheco@crunchydata.com
14. Postgres Support for Red Hat Products
15. • Datatypes • Transactional DDL • Foreign Data Wrappers • Concurrent Index Creation • Conditional indexes • JSON • Common Table Expressions • JSON/JSONB • Vector / AI support • Listen/Notify • Upsert • Partitioning • Window function • Continued innovation Why Postgres ✔ Established, Reliable and Secure ✔ Feature Rich ✔ Extensibility ✔ No Central Owner ✔ Hiring (Open Source) The Technical Details
16. Thank You Bob Pacheco