Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly
Appearance settings

Commit 1d323fc

Browse filesBrowse files
author
Montana Low
committed
blog
1 parent dd3a338 commit 1d323fc
Copy full SHA for 1d323fc

File tree

Expand file treeCollapse file tree

2 files changed

+217
-3
lines changed
Filter options
Expand file treeCollapse file tree

2 files changed

+217
-3
lines changed

‎pgml-docs/docs/blog/benchmarking.md

Copy file name to clipboard
+207Lines changed: 207 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,207 @@
1+
Benchmarking Rust vs Python Functions in Postgres
2+
=================================================
3+
4+
Generating Random Embeddings
5+
----------------------------
6+
```sql
7+
CREATE TABLE embeddings AS
8+
SELECT id, ARRAY_AGG(rand) AS vector
9+
FROM (
10+
SELECT row_number() over () % 10000 + 1 AS id, random()::REAL AS rand
11+
FROM generate_series(1, 1280000) AS t
12+
) series
13+
GROUP BY id
14+
ORDER BY id;
15+
```
16+
17+
6MB of data, tiny.
18+
19+
```sql
20+
\dt+ embeddings
21+
List of relations
22+
Schema | Name | Type | Owner | Persistence | Access method | Size | Description
23+
--------+------------+-------+---------+-------------+---------------+---------+-------------
24+
public | embeddings | table | montana | permanent | heap | 5728 kB |
25+
(1 row)
26+
```
27+
28+
```sql
29+
SELECT * FROM embeddings LIMIT 1 OFFSET 9999;
30+
```
31+
32+
```sql
33+
id |
34+
35+
36+
37+
vector
38+
39+
40+
41+
42+
43+
-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
44+
10000 | {0.4677334838785363,0.6199169805362921,0.008509639150592108,0.5498286015691214,0.07871558785325661,0.8348774190441794,0.6311723407102399,0.19212339727556937,0.4009401071299443,0.4433296885198992,0.08637908622108981,0.5807890489919068,0.9247415078083385,0.4776815048800849,0.16229492344792007,0.7429632683623062,0.9189767474432564,0.22078690362144826,0.9475800478605869,0.6344527428610185,0.7874705875445933,0.8748915523207792,0.8128351151340247,0.43367844829237256,0.6754609060773227,0.48057806313494567,0.642046553648246,0.08562096129077545,0.3260440099614961,0.08571130530089377,0.31403187698834145,0.09576577182071233,0.1866199475722432,0.16438371763564774,0.43388499644447975,0.5740485956767465,0.7653378212397719,0.28586062103630994,0.3144215335119078,0.5288006869835193,0.2375410997493681,0.033525530980060836,0.20762413239095068,0.41069415069575044,0.3022129308413959,0.17382547002415905,0.7860877724870114,0.9066872550982374,0.28842145371129746,0.0752162803059413,0.8744650809105714,0.9839697443058562,0.5117651115095931,0.37710793604282244,0.4309116449771899,0.18390815076356049,0.8808179638418814,0.9088098560322955,0.7785313247075045,0.16599110774638248,0.7535749992688388,0.8584762161519315,0.08682156532723084,0.5017746618594323,0.04389744780621996,0.1920512618593797,0.10311870976716264,0.7188275855378983,0.5779580934248578,0.3222146206923675,0.1340433111631718,0.5946766895470468,0.7006865865506633,0.027326614768700352,0.27694544535620835,0.7007521354822224,0.13221475656462545,0.6203735732862619,0.9013500233923004,0.19633274826884772,0.07432192430281148,0.36791314502301375,0.38040210402255425,0.8724913286624236,0.5668802145563667,0.1590196108369959,0.07231220620315426,0.446039965748529,0.09429986857151462,0.42422919616632626,0.551801621595363,0.10347534944282089,0.44197455654955675,0.30448538352920096,0.09154772706552095,0.29492408299103445,0.8027266901468728,0.07008422502389422,0.393629297185047,0.778087995468983,0.13325273497492063,0.7896196548287584,0.5709718167304381,0.9121894542825579,0.7605685231002361,0.24892880170222398,0.3787927931832691,0.2019166776677288,0.23845456554443345,0.12003730148279956,0.6960470692301932,0.09136904719054328,0.45390136478420473,0.3801046095890719,0.2568721352629417,0.008002420247127162,0.9835547272513203,0.9803693135197058,0.632033441878324,0.6565744425636844,0.7001610758326287,0.7650944453534443,0.46036971237822755,0.5630898606964969,0.6290127623700563,0.6405127942491085,0.930238255269618,0.868745834531925}
45+
(2 rows)
46+
```
47+
48+
Benchmarks
49+
----------
50+
51+
### SQL
52+
```sql
53+
CREATE OR REPLACE FUNCTION sql_norm_l2(vector REAL[])
54+
RETURNS REAL
55+
LANGUAGE sql
56+
LEAKPROOF
57+
IMMUTABLE
58+
STRICT
59+
PARALLEL SAFE
60+
AS $$
61+
SELECT SQRT(SUM(squared.values))
62+
FROM (SELECT UNNEST(vector) * UNNEST(vector) AS values) AS squared;
63+
$$;
64+
```
65+
66+
```sql
67+
SELECT sql_norm_l2(vector) FROM embeddings;
68+
```
69+
110ms
70+
71+
### PLPGSQL
72+
```sql
73+
CREATE OR REPLACE FUNCTION plpgsql_norm_l2(vector REAL[])
74+
RETURNS REAL
75+
LANGUAGE plpgsql
76+
LEAKPROOF
77+
IMMUTABLE
78+
STRICT
79+
PARALLEL SAFE
80+
AS $$
81+
BEGIN
82+
RETURN SQRT(SUM(squared.values))
83+
FROM (SELECT UNNEST(vector) * UNNEST(vector) AS values) AS squared;
84+
END
85+
$$;
86+
```
87+
88+
```sql
89+
SELECT plpgsql_norm_l2(vector) FROM embeddings;
90+
```
91+
120 ms
92+
93+
### Python
94+
```sql
95+
CREATE OR REPLACE FUNCTION python_norm_l2(vector REAL[])
96+
RETURNS REAL
97+
LANGUAGE plpython3u
98+
LEAKPROOF
99+
IMMUTABLE
100+
STRICT
101+
PARALLEL SAFE
102+
AS $$
103+
import math
104+
return math.sqrt(sum([x * x for x in vector]))
105+
$$;
106+
```
107+
108+
```sql
109+
SELECT python_norm_l2(vector) FROM embeddings;
110+
```
111+
-- 80ms
112+
113+
### Numpy
114+
```sql
115+
CREATE OR REPLACE FUNCTION numpy_norm_l2(vector REAL[])
116+
RETURNS REAL
117+
LANGUAGE plpython3u
118+
LEAKPROOF
119+
IMMUTABLE
120+
STRICT
121+
PARALLEL SAFE
122+
AS $$
123+
import numpy
124+
return numpy.linalg.norm(vector, ord=2)
125+
$$;
126+
```
127+
128+
```sql
129+
SELECT numpy_norm_l2(vector) FROM embeddings;
130+
```
131+
-- 150ms
132+
133+
### Rust
134+
135+
```sql
136+
SELECT pgx_norm_l2(vector) FROM embeddings;
137+
```
138+
139+
140+
141+
142+
143+
144+
145+
146+
Benchmarking SQL functions
147+
--------------------------
148+
149+
Benchmarking PgPlSQL functions
150+
-----------------------------
151+
152+
```sql
153+
CREATE SCHEMA pgml;
154+
155+
---
156+
--- Euclidean distance from the origin
157+
---
158+
CREATE OR REPLACE FUNCTION pgml.norm_l2(vector REAL[])
159+
RETURNS REAL
160+
LANGUAGE plpgsql
161+
LEAKPROOF
162+
IMMUTABLE
163+
STRICT
164+
PARALLEL SAFE
165+
AS $$
166+
BEGIN
167+
RETURN SQRT(SUM(squared.values))
168+
FROM (SELECT UNNEST(vector) * UNNEST(vector) AS values) AS squared;
169+
END
170+
$$;
171+
172+
---
173+
--- A projection of `a` onto `b`
174+
---
175+
CREATE OR REPLACE FUNCTION pgml.dot_product(a REAL[], b REAL[])
176+
RETURNS REAL
177+
LANGUAGE plpgsql
178+
LEAKPROOF
179+
IMMUTABLE
180+
STRICT
181+
PARALLEL SAFE
182+
AS $$
183+
BEGIN
184+
RETURN SUM(multiplied.values)
185+
FROM (SELECT UNNEST(a) * UNNEST(b) AS values) AS multiplied;
186+
END
187+
$$;
188+
189+
190+
---
191+
--- The size of the angle between `a` and `b`
192+
---
193+
CREATE OR REPLACE FUNCTION pgml.cosine_similarity(a REAL[], b REAL[])
194+
RETURNS REAL
195+
LANGUAGE plpgsql
196+
LEAKPROOF
197+
IMMUTABLE
198+
STRICT
199+
PARALLEL SAFE
200+
AS $$
201+
BEGIN
202+
RETURN pgml.dot_product(a, b) / (pgml.norm_l2(a) * pgml.norm_l2(b));
203+
END
204+
$$;
205+
```
206+
207+
```sql

‎pgml-docs/docs/gym/quick_start.md

Copy file name to clipboardExpand all lines: pgml-docs/docs/gym/quick_start.md
+10-3Lines changed: 10 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1,3 +1,10 @@
1+
<style>
2+
.md-content video, .md-content img {
3+
max-width: 90%;
4+
margin: 2em 5%;
5+
}
6+
</style>
7+
18
# Quick Start
29

310
PostgresML is easy to get started with. If you haven't already, sign up for our [Gym](https://gym.postgresml.org/signup/) to get a free hosted PostgresML instance you can use to follow this tutorial. You can also run one yourself by following the instructions in our Github repo.
@@ -7,9 +14,9 @@ PostgresML is easy to get started with. If you haven't already, sign up for our
714
</p>
815

916
<video autoplay loop muted>
10-
<source src="../images/demo.webm" type="video/webm">
11-
<source src="../images/demo.mp4" type="video/mp4">
12-
<img src="../images/console.png" alt="PostgresML in practice" loading="lazy">
17+
<source src="/images/demo.webm" type="video/webm">
18+
<source src="/images/demo.mp4" type="video/mp4">
19+
<img src="/images/console.png" alt="PostgresML in practice" loading="lazy">
1320
</video>
1421

1522
Once you have your PostgresML instance running, we'll be ready to get started.

0 commit comments

Comments
0 (0)
Morty Proxy This is a proxified and sanitized view of the page, visit original site.