Intelligent apps using Azure Database for PostgreSQL

App Service
Cognitive Services
Database for PostgreSQL
Machine Learning
Power BI

Solution ideas

This article is a solution idea. If you'd like us to expand the content with more information, such as potential use cases, alternative services, implementation considerations, or pricing guidance, let us know by providing 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.

Potential use cases

PostgreSQL's support for unstructured data and 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 a mobile application, and it's more appropriate for downstream analysis.

Architecture

Architecture Diagram Download a PNG file of this architecture.

Dataflow

  1. An Azure Function App instance is triggered as a part of an Azure Data Factory pipeline, by creating an Azure Function Activity.
  2. Data can come from various sources, such as Event Hubs for high volumes of data ingestion, or data that's uploaded to Blob Storage. The Azure Function App is triggered as new data is received as part of the pipeline.
  3. The Azure Function App calls the Azure Cognitive Services API to analyze the data (for example, for Sentiment Analysis).
  4. The results of the analysis are returned in JSON format from the Cognitive Services API (Text Analytics in this example).
  5. The Azure Function App stores the data and results from the Cognitive Services API (Text Analytics in this example) in Azure Database for PostgreSQL.
  6. Azure Machine Learning Studio is used to further analyze the data, using custom Machine Learning algorithms to gain further insights.
    • 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.
  7. Finally, human-interpretable insights can be explored in Power BI, through the PostgreSQL connector or a custom web application.

Components

Considerations

These considerations implement the pillars of the Azure Well-Architected Framework, which is a set of guiding tenets that can be used to improve the quality of a workload. For more information, see Microsoft Azure Well-Architected Framework.

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

Security provides assurances against deliberate attacks and the abuse of your valuable data and systems. For more information, see Overview of the security pillar.

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 more information, 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.

Cost optimization

Cost optimization is about looking at ways to reduce unnecessary expenses and improve operational efficiencies. For more information, see Overview of the cost optimization pillar.

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