Intelligent apps using Azure Database for PostgreSQL

App Service
Cognitive Services
Database for PostgreSQL
Machine Learning
Power BI

Solution Idea

If you'd like to see us expand this article with more information, implementation details, pricing guidance, or code examples, let us know with GitHub Feedback!

Develop sophisticated, transformational apps using state-of-the-art machine learning algorithms and integrated visualization tools to get actionable insights and analytics.

In this example of an intelligent app, PostgreSQL is the heart of the architecture as the main database for a common AIML use case of social media text analysis. PostgreSQL's support for unstructured data, ability to execute parallel queries and declarative partitioning makes it an effective database choice for a highly data-intensive AIML task. Since PostgreSQL is a cloud-based solution, this architecture isn't recommended for mobile application, and is more appropriate for downstream analysis.

Architecture

Architecture Diagram Download an SVG of this architecture.

Data flow

  1. Data could come from various sources, such as Event Hubs for high volumes of data ingestion, or data that's uploaded to Blob Storage. An Azure Function App is triggered as new data is received.
  2. The Azure Function App calls the Text Analytics API in Azure Cognitive Services to analyze the data (for example, for Sentiment Analysis). The results of the analysis are returned in JSON format.
    • The Text Analytics API can detect user language, key phrases used in a review, identify specific named entities and understand how customers really feel about products they purchased.
  3. The Azure Function App stores the data and results from Text Analytics in Azure Database for MySQL.
  4. Deep learning Natural Language Processing (NLP) models can then be applied on the API insights from PostgreSQL - or the initial raw data - through Azure Machine Learning Studio
    • If you're approaching the machine learning component of this architecture with a no-code perspective, you can implement further text analytics operations on the data, like feature hashing, Word2Vector and n-gram extraction. Instead, you can use your favorite open-source NLP model if you prefer a code-first approach and run your model as an Experiment in Azure Machine Learning.
    • Results from this further ML analysis are saved back to PostgreSQL
  5. Finally, human-interpretable insights can be explored in Power BI through the PostgreSQL connector.

Components

Considerations

Azure Cognitive Services Text Analytics API has a maximum size of 5120 characters for a single document and a maximum request size of 1 MB. View the data and rate limits.

Depending on the volume and velocity of data being ingressed, you can select one of three deployment modes: single server, flexible, and Hyperscale (Citus). Assuming that you would be mining large workloads of customer opinions and reviews, Hyperscale is a recommended solution. Explore the When to use Azure Database for PostgreSQL Learn Module to understand when to use each deployment mode.

Security

All data in PostgreSQL is automatically encrypted and backed up. You can configure Azure Advanced Threat Protection for further mitigation of threats. Read more at Advanced Threat Protection in Azure Database for PostgreSQL.

DevOps

You can configure GitHub Actions to connect to your Azure PostgreSQL database by using its connection string and setting up a workflow. For further information on how to do so, see Quickstart: Use GitHub Actions to connect to Azure PostgreSQL.

Additionally, you can automate your Azure Machine Learning lifecycle by using Azure Pipelines. The MLOps with Azure ML GitHub repo demonstrates how to operationalize an MLOps workflow and build out a CI/CD pipeline for your project.

Pricing

Azure Cognitive Services Text Analytics API pricing is determined by the instance selected and the number of transactions per month. For further details, explore the pricing calculator for Text Analytics here.

Next steps