|
1 | 1 | --- |
2 | 2 | description:>- |
3 | | - Thepgml extension for PostgreSQL provides Machine Learning and Artificial |
| 3 | + ThePostgresML extension for PostgreSQL provides Machine Learning and Artificial |
4 | 4 | Intelligence APIs with access to algorithms to train your models, or download |
5 | | -SOTA open source models fromHuggingFace. |
| 5 | +state-of-the-art open source models fromHugging Face. |
6 | 6 | --- |
7 | 7 |
|
8 | | -#SQLExtension |
| 8 | +#SQLextension |
9 | 9 |
|
10 | | -##Open Source Models |
| 10 | +PostgresML is a PostgreSQL extension which adds SQL functions to the database. Those functions provide access to AI models downloaded from Hugging Face, and classical machine learning algorithms like XGBoost and LightGBM. |
11 | 11 |
|
12 | | -PostgresML integrates[🤗 Hugging Face Transformers](https://huggingface.co/transformers)tobring state-of-the-art models intothedata layer. There are tens of thousands of pre-trainedmodelswith pipelines to turn raw inputs into useful results. Many LLMs have been publishedandmade available for download. You will wanttobrowse all the[models](https://huggingface.co/models) available to find the perfect solution for your[dataset](https://huggingface.co/dataset)and[task](https://huggingface.co/tasks). The pgml extension provides a few APIs for different use cases: |
| 12 | +Our SQL API is stable and safetouse in your applications, whilethe models andalgorithms we support continuetoevolveandimprove. |
13 | 13 |
|
14 | | -*[pgml.embed.md](pgml.embed.md"mention") returns vector embeddings for nearest neighbor searches and other vector database use cases |
15 | | -*[pgml.generate.md](pgml.generate.md"mention") returns streaming text responses for chatbots |
16 | | -*[pgml.transform](../../api/sql-extension/pgml.transform/"mention") allows you to perform dozens of natural language processing (NLP) tasks with thousands of models, like sentiment analysis, question and answering, translation, summarization and text generation |
17 | | -*[pgml.tune.md](pgml.tune.md"mention") fine tunes an open source model on your own data |
| 14 | +##Open-source LLMs |
18 | 15 |
|
19 | | -##Train & deploy your ownmodels |
| 16 | +PostgresML defines two SQL functions which use[🤗 Hugging Face](https://huggingface.co/transformers) transformers and embeddingsmodels, running directly in the database: |
20 | 17 |
|
21 | | -PostgresML also supports more than 50 machine learning algorithms to train your own models for classification, regression or clustering. We organize a family of Models in Projects that are intended to address a particular opportunity. Different algorithms can be used in the same Project, to test and compare the performance of various approaches, and track progress over time, all within your database. |
| 18 | +| Function| Description| |
| 19 | +|---------------|-------------| |
| 20 | +|[pgml.embed()](pgml.embed)| Generate embeddings using latest sentence transformers from Hugging Face.| |
| 21 | +|[pgml.transform()](pgml.transform/)| Text generation using LLMs like Llama, Mixtral, and many more, with models downloaded from Hugging Face.| |
| 22 | +| pgml.transform_stream()| Streaming version of[pgml.transform()](pgml.transform/), which fetches partial responses as they are being generated by the model, substantially decreasing time to first token.| |
| 23 | +|[pgml.tune()](pgml.tune)| Perform fine tuning tasks on Hugging Face models, using data stored in the database.| |
22 | 24 |
|
23 | | -###Train |
| 25 | +###Example |
24 | 26 |
|
25 | | -Training creates a Model based on the data in your database. |
| 27 | +Using a SQL function for interacting with open-source models makes things really easy: |
26 | 28 |
|
27 | | -```sql |
28 | | -SELECTpgml.train( |
29 | | - project_name=>'Sales Forecast', |
30 | | - task=>'regression', |
31 | | - relation_name=>'hist_sales', |
32 | | - y_column_name=>'next_sales', |
33 | | - algorithm=>'xgboost' |
34 | | -); |
| 29 | +{% tabs %} |
| 30 | +{% tab title="SQL" %} |
| 31 | + |
| 32 | +```postgresql |
| 33 | +SELECT pgml.embed( |
| 34 | + 'intfloat/e5-small', |
| 35 | + 'This text will be embedded using the intfloat/e5-small model.' |
| 36 | +) AS embedding; |
| 37 | +``` |
| 38 | + |
| 39 | +{% endtab %} |
| 40 | +{% tab title="Output" %} |
| 41 | + |
| 42 | +``` |
| 43 | + embedding |
| 44 | +------------------------------------------- |
| 45 | + {-0.028478337,-0.06275077,-0.04322059, [...] |
35 | 46 | ``` |
36 | 47 |
|
37 | | -See[pgml.train](../../api/sql-extension/pgml.train/README.md) for more information. |
| 48 | +{% endtab %} |
| 49 | +{% endtabs %} |
| 50 | + |
| 51 | +Using the`pgml` SQL functions inside regular queries, it's possible to add embeddings and LLM-generated text inside any query, without the data ever leaving the database, removing the cost of a remote network call. |
| 52 | + |
| 53 | +##Classical machine learning |
| 54 | + |
| 55 | +PostgresML defines four SQL functions which allow training regression, classification, and clustering models on tabular data: |
| 56 | + |
| 57 | +| Function| Description| |
| 58 | +|---------------|-------------| |
| 59 | +|[pgml.train()](pgml.train/)| Train a model on PostgreSQL tables or views using any algorithm from Scikit-learn, with the additional support for XGBoost, LightGBM and Catboost.| |
| 60 | +|[pgml.predict()](pgml.predict/)| Run inference on live application data using a model trained with[pgml.train()](pgml.train/).| |
| 61 | +|[pgml.deploy()](pgml.deploy)| Deploy a specific version of a model trained with pgml.train(), using your own accuracy metrics.| |
| 62 | +| pgml.load_dataset()| Load any of the toy datasets from Scikit-learn or any dataset from Hugging Face.| |
| 63 | + |
| 64 | +###Example |
| 65 | + |
| 66 | +####Load data |
38 | 67 |
|
39 | | -###Deploy |
| 68 | +Using`pgml.load_dataset()`, we can load an example classification dataset from Scikit-learn: |
40 | 69 |
|
41 | | -Deploy an active Model for a particular Project, using a deployment strategy to select the best model. |
| 70 | +{% tabs %} |
| 71 | +{% tab title="SQL" %} |
42 | 72 |
|
43 | | -```sql |
44 | | -SELECTpgml.deploy( |
45 | | - project_name=>'Sales Forecast', |
46 | | - strategy=>'best_score', |
47 | | - algorithm=>'xgboost' |
| 73 | +```postgresql |
| 74 | +SELECT * |
| 75 | +FROM pgml.load_dataset('digits'); |
| 76 | +``` |
| 77 | + |
| 78 | +{% endtab %} |
| 79 | +{% tab title="Output" %} |
| 80 | + |
| 81 | +``` |
| 82 | + table_name | rows |
| 83 | +-------------+------ |
| 84 | + pgml.digits | 1797 |
| 85 | +(1 row) |
| 86 | +``` |
| 87 | + |
| 88 | +{% endtab %} |
| 89 | +{% endtabs %} |
| 90 | + |
| 91 | +####Train a model |
| 92 | + |
| 93 | +Once we have some data, we can train a model on this data using[pgml.train()](pgml.train/): |
| 94 | + |
| 95 | +{% tabs %} |
| 96 | +{% tab title="SQL" %} |
| 97 | + |
| 98 | +```postgresql |
| 99 | +SELECT * |
| 100 | +FROM pgml.train( |
| 101 | + project_name => 'My project name', |
| 102 | + task => 'classification', |
| 103 | + relation_name =>'pgml.digits', |
| 104 | + y_column_name => 'target', |
| 105 | + algorithm => 'xgboost', |
48 | 106 | ); |
49 | 107 | ``` |
50 | 108 |
|
51 | | -See[pgml.deploy.md](pgml.deploy.md"mention") for more information. |
| 109 | +{% endtab %} |
| 110 | +{% tab title="Output" %} |
52 | 111 |
|
53 | | -###Predict |
| 112 | +``` |
| 113 | +INFO: Metrics: { |
| 114 | + "f1": 0.8755124, |
| 115 | + "precision": 0.87670505, |
| 116 | + "recall": 0.88005465, |
| 117 | + "accuracy": 0.87750554, |
| 118 | + "mcc": 0.8645154, |
| 119 | + "fit_time": 0.33504912, |
| 120 | + "score_time": 0.001842427 |
| 121 | +} |
| 122 | +
|
| 123 | + project | task | algorithm | deployed |
| 124 | +-----------------+----------------+-----------+---------- |
| 125 | + My project name | classification | xgboost | t |
| 126 | +(1 row) |
54 | 127 |
|
55 | | -Use your Model on novel data points not seen during training to infer a new data point. |
| 128 | +``` |
| 129 | + |
| 130 | +{% endtab %} |
| 131 | +{% endtabs %} |
| 132 | + |
| 133 | +[pgml.train()](pgml.train/) reads data from the table, using the`target` column as the label, automatically splits the dataset into test and train sets, and trains an XGBoost model. Our extension supports more than 50 machine learning algorithms, and you can train a model using any of them by just changing the name of the`algorithm` argument. |
| 134 | + |
| 135 | + |
| 136 | +####Real time inference |
| 137 | + |
| 138 | +Now that we have a model, we can use it to predict new data points, in real time, on live application data: |
56 | 139 |
|
57 | | -```sql |
58 | | -SELECTpgml.predict( |
59 | | - project_name=>'Sales Forecast', |
60 | | - features=> ARRAY[ |
61 | | - last_week_sales, |
62 | | - week_of_year |
63 | | - ] |
| 140 | +{% tabs %} |
| 141 | +{% tab title="SQL" %} |
| 142 | + |
| 143 | +```postgresql |
| 144 | +SELECT |
| 145 | + target, |
| 146 | + pgml.predict( |
| 147 | + 'My project name', |
| 148 | + image |
64 | 149 | ) AS prediction |
65 | | -FROM new_sales |
66 | | -ORDER BY predictionDESC; |
| 150 | +FROM |
| 151 | + pgml.digits |
| 152 | +LIMIT 1; |
| 153 | +``` |
| 154 | + |
| 155 | +{% endtab %} |
| 156 | +{% tab title="Output" %} |
| 157 | + |
| 158 | +``` |
| 159 | + target | prediction |
| 160 | +--------+------------ |
| 161 | + 0 | 0 |
| 162 | +(1 row) |
| 163 | +``` |
| 164 | + |
| 165 | +{% endtab %} |
| 166 | +{% endtabs %} |
| 167 | + |
| 168 | +####Change model version |
| 169 | + |
| 170 | +The train function automatically deploys the best model into production, using the precision score relevant to the type of the model. If you prefer to deploy models using your own accuracy metrics, the[pgml.deploy()](pgml.deploy) function can manually change which model version is used for subsequent database queries: |
| 171 | + |
| 172 | +{% tabs %} |
| 173 | +{% tab title="SQL" %} |
| 174 | + |
| 175 | +```postgresql |
| 176 | +SELECT * |
| 177 | +FROM |
| 178 | + pgml.deploy( |
| 179 | + 'My project name', |
| 180 | + strategy => 'most_recent', |
| 181 | + algorithm => 'xgboost' |
| 182 | +); |
| 183 | +``` |
| 184 | + |
| 185 | +{% endtab %} |
| 186 | +{% tab title="Output" %} |
| 187 | + |
| 188 | +``` |
| 189 | + project | strategy | algorithm |
| 190 | +-----------------+-------------+----------- |
| 191 | + My project name | most_recent | xgboost |
| 192 | +(1 row) |
67 | 193 | ``` |
68 | 194 |
|
69 | | -See[pgml.predict](../../api/sql-extension/pgml.predict/"mention") for more information. |
| 195 | +{% endtab %} |
| 196 | +{% endtabs %} |