Publish-subscribe

Publish-Subscribe (Pub/Sub for short) of a database is a messaging model in which Publisher sends messages to one or more Subscribers, and Subscribers The message is received and processed. In this mode, publishers and subscribers are loosely coupled, and no direct communication is required between them, thus improving the scalability and flexibility of the application.

In databases, the publish-subscribe function is usually used in scenarios such as real-time data updates, cache synchronization, and business event notification. For example, when the data of a particular table in the database changes, the subscribers can be notified in real-time through the publish and subscribe function, to realize real-time data synchronization and processing. In addition, the notification of business events can also be recognized through the publish and subscribe function, such as an order being canceled, a certain inventory quantity is insufficient, and so on.

There can be a many-to-many relationship between publishers and subscribers; one publisher can publish messages to multiple subscribers, and one subscriber can also subscribe to various messages/data. Usually, the publish-subscribe function of the database consists of two parts: Publisher and Subscriber. Publisher is responsible for publishing messages, while Subscriber subscribes to corresponding messages to achieve data synchronization.

Application scenarios

The publish-subscribe function has many typical application scenarios:

  • Data Synchronization: When a database needs to be kept in sync with another database, the publish-subscribe feature can send data changes to the subscriber database. For example, when a website needs to transfer data from one geographic location to another, publish-subscribe functionality can ensure data synchronization between the two databases.

  • Business data distribution: The publish and subscribe function can distribute business data to different systems or processes. For example, when a bank needs to distribute customer account information to multiple business systems, the publish-subscribe function can distribute data to corresponding systems to ensure data consistency between various business processes.

  • Data backup: The publish-subscribe function can back up data. For example, when one database needs to be backed up to another database, the publish-subscribe part can be used to back up the data to the subscriber database so that the data can be recovered in the event of failure of the primary database.

  • Real-time data processing: The publish-subscribe function can be used to realize real-time data processing. For example, when a website needs to process data from different users, the publish-subscribe part can be used to transmit data to a processing program for processing, to realize real-time data analysis and decision-making.

Examples

Publish-Subscribe - 图1

This chapter will give an example to introduce that there are currently three accounts in the MatrixOne cluster, sys, acc1, and acc2, and operate on the three accounts according to the order of operations:

  1. Publisher: sys account creates database sub1 and table t1, and publishes pub1:

    1. create database sub1;
    2. create table sub1.t1(a int,b int);
    3. create publication pub1 database sub;

  2. Subscriber: both acc1 and acc2 create a subscription database syssub1, and thus get the shared table t1:

    1. -- The SQL statements for acc1 and acc2 to create the subscription library are the same, so there will not repeat them
    2. create database syssub1 from sys publication pub1;
    3. use syssub1;
    4. show tables;
    5. mysql> show tables;
    6. +--------------------+
    7. | Tables_in_syssub1  |
    8. +--------------------+
    9. | t1                 |
    10. +--------------------+
    11. 2 rows in set (0.02 sec)

  3. Publisher: sys account creates table t2:

    1. create table sub1.t2(a text);

  4. Subscribers: acc1 and acc2 get shared tables t1 and t2:

    1. show tables;
    2. +--------------------+
    3. | Tables_in_syssub1  |
    4. +--------------------+
    5. | t1                 |
    6. +--------------------+
    7. | t2                 |
    8. +--------------------+
    9. 2 rows in set (0.02 sec)

  5. Publisher: sys account creates database sub2 and table t2, and publishes pub2 to accounts acc1 and acc3:

    1. create database sub2;
    2. create table sub2.t1(a float);
    3. create publication pub2 database sub2 account acc1,acc3;

  6. Subscriber: both acc1 and acc2 create the subscription database syssub2, and acc1 gets the shared data table t1; acc2 fails to create the subscription database syssub2:

    • acc1
    1. create database syssub2 from sys publication pub2;
    2. use syssub2;
    3. mysql> show tables;
    4. +--------------------+
    5. | Tables_in_syssub2  |
    6. +--------------------+
    7. | t1                 |
    8. +--------------------+
    9. 2 rows in set (0.02 sec)
    • acc2
    1. create database syssub2 from sys publication pub2;
    2. > ERROR 20101 (HY000): internal error: the account acc3 is not allowed to subscribe the publication pub2

  7. Publisher: The sys account modifies and publishes pub2 to all accounts:

    1. alter publication pub2 account all;

  8. Subscriber: acc2 successfully created the subscription database syssub2, and got the shared data table t1:

    1. create database syssub2 from sys publication pub2;
    2. use syssub2;
    3. mysql> show tables;
    4. +--------------------+
    5. | Tables_in_syssub2  |
    6. +--------------------+
    7. | t1                 |
    8. +--------------------+
    9. 2 rows in set (0.02 sec)

  9. Publisher: sys account deletes publication pub1:

    1. drop publication pub1;

  10. Subscriber: acc1 failed to connect to syspub1:

    1. use syssub1;
    2. ERROR 20101 (HY000): internal error: there is no publication pub1

  11. Subscriber: acc2 delete syspub1:

    1. drop database syssub1;

  12. Publisher: sys account recreates pub1:

    1. create publication pub1 database sub;

  13. Subscriber: acc1 connects to syspub1 successfully:

    1. create database syssub1 from sys publication pub1;
    2. use syssub1;
    3. mysql> show tables;
    4. +--------------------+
    5. | Tables_in_syssub1  |
    6. +--------------------+
    7. | t1                 |
    8. +--------------------+
    9. 2 rows in set (0.02 sec)

Reference

Publisher Reference

Subscriber Reference

Constraints

  • Only ACCOUNTADMIN or MOADMIN role can create publications and subscriptions on the publishing side.
  • Subscribers are controlled by ACCOUNTADMIN or MOADMIN roles to access subscription data permissions.