Machine learning
AnalyticDB for PostgreSQL integrates the MADlib extension to run machine learning directly in the database using SQL. MADlib is an open-source library that provides machine learning and graph computing modules for PostgreSQL databases, including mathematical statistics functions, stored procedures, and both supervised and unsupervised algorithm libraries.
AnalyticDB for PostgreSQL uses MADlib 1.16, which does not support XGBoost, LightGBM, or GBDT.
Machine learning algorithms
Classification and regression
| Algorithm | Use when |
|---|---|
| K-Nearest Neighbor (KNN) | Classifying data points based on similarity to neighbors |
| Multilayer perceptron (neural network) | Complex pattern recognition tasks |
| Support vector machine (SVM) | Binary classification with clear decision boundaries |
| Decision tree | Interpretable classification and regression |
| Least-squares regression | Predicting continuous values with a linear relationship |
| Generalized linear model (GLM) | Regression with non-normally distributed outcomes |
| Logistic regression | Binary classification with probability output |
| Multinomial logistic regression | Multi-class classification problems |
Clustering
The K-means algorithm groups data points into clusters based on similarity. Use it for customer segmentation or anomaly grouping.
Correlation analysis
The Apriori algorithm discovers association rules between items in a dataset. A classic example: identifying that customers who buy diapers also tend to buy beer.
Time series analysis
Autoregressive integrated moving average (ARIMA) models predict future values in a time series. Use ARIMA when your data has temporal dependencies and you need to forecast trends.
Dimensionality reduction and topic modeling
| Algorithm | Purpose |
|---|---|
| Principal component analysis (PCA) | Extracts the most informative features, reducing the number of input variables |
| Latent Dirichlet Allocation (LDA) | Classifies documents and discovers latent topics in text corpora |
Graph computing algorithms
MADlib includes a graph computing model for relationship and network analysis.
| Category | Algorithm | Description |
|---|---|---|
| Shortest path | Shortest path among all vertices | Calculates the shortest path between all vertex pairs and stores results in an output table for subsequent queries. |
| Shortest path | Shortest path from a single vertex | Calculates the shortest path between a specific source vertex and all other vertices and saves the result to a specific result table. This model queries the shortest path from a specific vertex to any other vertex based on the result table. |
| Traversal | Breadth-first search (BFS) | Finds all vertices reachable from a given source vertex. |
| Ranking | HITS score | Returns hub scores and authority scores for all vertices in a directed graph. |
| Ranking | PageRank | Returns the PageRank value for all vertices in a directed graph. |
| Connectivity | Weakly connected component | Identifies all weakly connected components in a directed graph. |
| Measure | Average path length | Calculates the average shortest path length across the graph. |
| Measure | Proximity | Calculates the closeness centrality of all nodes in a graph. |
| Measure | Graph diameter | Returns the longest shortest path in the graph. |
| Measure | In/out-degree | Calculates the in-degree and out-degree for all vertices. |
Limitations
MADlib version: 1.16
Not supported: XGBoost, LightGBM, GBDT
Key capabilities
SQL-based access. Run machine learning on large datasets using standard SQL — no additional programming languages or frameworks required.
Highly lightweight. AnalyticDB for PostgreSQL helps you solve complex problems such as combination issues on classification and social network analysis.
Elastic scaling. Scale CPU and compute nodes based on workload size using the cloud-native architecture of AnalyticDB for PostgreSQL.