pgConflux

pgConflux is an extension for PostgreSQL to provide fast access to personalized timelines created by merging event streams from a dynamic set of sources.

Installation

Ensure that the pg_config binary is in your path (shipped with PostgreSQL). Ensure that GNU Make is available (either as make or gmake). Then simply type:

make install

Usage

The extension can be loaded with the following SQL command:

CREATE EXTENSION conflux;

The extension introduces the following two data types:

  • pstamp
  • pstamp_query

Values of type pstamp can be created using the pstamp(int4, int8) function. The first argument references the source (e.g. an author) of an entry (e.g. of a post). The second argument is a 64-bit integer that should be monotonically increasing with time (e.g. a SERIAL8). An index can then be created as follows:

CREATE INDEX posting_all ON posting USING gist ((pstamp(author_id, post_id)));

Using the pstamp_query data type, it is then possible to efficiently select the newest postings while limiting the result set to certain sources (e.g. authors). To create a value in order to perform a timeline query, the function pstamp_query(int4[], int8) can be used. The first argument to the function is an array referencing certain sources (e.g. authors who are being followed in a social media platform). The second argument is optional and can refer to a maximum ID to successively retrieve older entries. A query may look as follows:

SELECT * FROM posting WHERE pstamp(author_id, post_id) && pstamp_query(ARRAY[17,105,19]) ORDER BY pstamp(author_id, post_id) <-> pstamp_query(ARRAY[17,105,19]) LIMIT 10;

This query would efficiently select the 10 newest postings from the set of postings created by authors 17, 19, and 105, even if the table contains millions (or billions?) of entries.

To only consider entries with a serial ID equal to or smaller than a certain value (e.g. 12345), the pstamp_query call can, for example, be written as: pstamp_query(ARRAY[17,105,19], 12345)

For a complete example, refer to the README file that is included in the source code archive.

Download

Changes

  • 2018-12-05: Initial release of version 0.1