pgConflux is an extension for PostgreSQL to provide fast access to personalized timelines created by merging event streams from a dynamic set of sources.
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:
The extension can be loaded with the following SQL command:
CREATE EXTENSION conflux;
The extension introduces the following two data types:
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 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.
- 2020-02-09: Version 0.2
- Changed operators from && and <-> to <| and <-|
- Operator <| only returns IDs strictly smaller than the value given as second parameter to pstamp_query (instead of values smaller than or equal to the value)
- Fixed code to compile with PostgreSQL 11 and 12
- 2018-12-05: Initial release of version 0.1