Skytools: PgQ Queues and applications

Skytools: PgQQueues and applicationsAgenda PgQ basics Queues, producers and consumers New features in 3.0 Skytools – toolset and scripting framework Custom consumers Replication toolset Managing the Skytools environment Instal ing Migrations and upgrades MonitoringWhat is PgQ? A queue implementation on PostgreSQL with a stored procedure interface. Events are queued by producers to be subsequently processed by consumers.producer queueconsumereventeventsDatabasePgQ: what it's good for? Asynchronous messaging Batch processing Replication Distributed transactionsPgQ: Features Transactional. Events are created transactional y, can be coupled with surrounding business logic. Efficient. Events are processed in batches which gives low per event overhead. Flexible. No limits on the number of producers or consumers. Custom event formats. Reliable. Events are stored in PostgreSQL database – this adds the benefit of write ahead logging and crash recovery. Easy to use. Simple SQL interface, API-s for several languages. Open Source. No licensing fees, but occasional y you'l have to get your hands dirty.PgQ: exampleDatabase for registering user accounts.Events are generated by a pl/pgsql stored procedure.Consumers also talk to the queue through stored procedure interface.java consumer:welcome emailUserDBproducer application:web clientfunction:create_userC++ consumer:user counterqueue:notificationspython consumer:londiste replicaqueue:user_eventsPgQ: at Skype Used everywhere where asynchronous data processing is needed. Hundreds of queues and consumers. Central y monitored.PgQ: brief history Started on 2006 Inspired by ideas from Slony First application was Londiste replication Open source since 2007 as part of Skytools framework Version 3.0 in progress, alpha version out.PgQ: glossaryEvent - atomic piece of data created by Producers. In PgQ event is one record in one of tables that services that queue. PgQ guarantees that each event is seen at least once but it is up to consumer to make sure that event is processed no more than once if that is needed.Batch - PgQ is designed for efficiency and high throughput so events are grouped into batches for bulk processing. Queue - Event are stored in queue tables i.e queues. Several producers can write into same queue and several consumers can read from the queue. Events are kept in queue until all the consumers have seen them. Producer - applications that pushes event into queue. Producer can be written in any language that is able to run stored procedures in PostgreSQL.Consumer - application that reads events from queue. Consumers can be written in any language that can interact with PostgreSQL. PgQ: QueueEssentially a set of tables in a userdb=# select * from pgq.queue;PostgreSQL database. -[ RECORD 1 ]------------+---Default is to have 3 tables per queue, queue_id | 1these are rotated to efficiently purge queue_name | q1discarded events.queue_ntables | 3Event is discarded when all the queue_cur_table | 2consumers have processed it.queue_data_pfx | pgq.event_1...Queues are accessible through stored procedure API. Tools also available.userdb=# \dt pgq.event_1* List of relationsThere can be multiple queues in one Schema | Name | Type | Owner database. --------+-----------+-------+---------Any number of producers and pgq | event_1 | table | martinpconsumers to the queue. pgq | event_1_0 | table | martinp pgq | event_1_1 | table | martinp pgq | event_1_2 | table | martinp(4 rows)Document Outline

• ÿ
• ÿ
• ÿ
• ÿ
• ÿ
• ÿ
• ÿ
• ÿ
• ÿ
• ÿ
• ÿ
• ÿ
• ÿ
• ÿ
• ÿ
• ÿ
• ÿ
• ÿ
• ÿ
• ÿ
• ÿ
• ÿ
• ÿ
• ÿ
• ÿ
• ÿ
• ÿ
• ÿ
• ÿ
• ÿ
• ÿ
• ÿ
• ÿ
• ÿ
• ÿ
• ÿ
• ÿ