Slide 1

Slide 1 text

AI for Postgres Bob Pacheco

Slide 2

Slide 2 text

• 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

Slide 3

Slide 3 text

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)

Slide 4

Slide 4 text

pgvector

Slide 5

Slide 5 text

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

Slide 6

Slide 6 text

pgvector

Slide 7

Slide 7 text

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;

Slide 8

Slide 8 text

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)

Slide 9

Slide 9 text

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;

Slide 10

Slide 10 text

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)

Slide 11

Slide 11 text

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;

Slide 12

Slide 12 text

Results id |. Name. | id | name -----+----------+-----+-------------------------- 165 | Corn Dog | 538 | Salad, green, tossed 165 | Corn Dog | 519 | Salad, chicken, tropical (2 rows)

Slide 13

Slide 13 text

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: ● [email protected]

Slide 14

Slide 14 text

Postgres Support for Red Hat Products

Slide 15

Slide 15 text

• 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

Slide 16

Slide 16 text

Thank You Bob Pacheco