JChem PostgreSQL Cartridge on a distributed PostgreSQL Citus database


The following software versions were used at testing:

  • JChem PostgreSQL Cartridge 2.1

  • PostgreSQL 9.5

  • Citus 5.1

Setup

  1. Install PostgreSQL and Citus on your master and worker nodes and create the Citus extension on each node (master and workers) as described in the Citus documentation .

  2. Follow the JChem PostgreSQL Cartridge Manual to set up the cartridge on each node:

    1. Install the ChemAxon jchem-psql package on each node.

    2. Create the ChemAxon and hstore extensions on each node.

    3. Put your valid ChemAxon license file to the /etc/chemaxon/ folder on each node.

  3. Initialize the jchem-psql service on each node with

    sudo service jchem-psql init
  4. Start the jchem-psql service on each node

    sudo service jchem-psql start
  5. Configure workers on the master node

    Create the file pg_worker_list.conf in the master node’s PostgreSQL data directory (the directory declared in the postgresql.conf file as data_directory) and add the worker’s hostname and PostgreSQL port setups to this file, like:

    worker-host1 5432
    worker-host2 5421
    (more workers)

Examples of usage

  1. Check worker nodes
    SELECT * FROM master_get_active_worker_nodes();

  2. Create distributed table with an ID and a molecule column

    In the example below the table is created using hash distribution, it has four shards and has one replica for each shard.

    CREATE TABLE mol_table(id int, mol molecule('sample'));
    SELECT master_create_distributed_table('mol_table', 'id', 'hash');
    SELECT master_create_worker_shards('mol_table', 4, 1);

  3. Prepare CSV format SMILES file with ID before each molecule from ordinary SMILES file

    In a command line shell:

    cat -n nci-pubchem_1m_unique.smiles | sed -e 's/^[ \t]*//' | sed -e 's/^[0-9]*/&,/' | sed -e 's/[ \t]*//g' > nci1m_with_id.smiles

  4. Fill the table with data

    The script below cuts the original CSV file to 64 pieces and imports them to the created table parallely. This script has to be executed from command line as postgres user.

    split -n l/64 nci1m_with_id.smiles chunks/
    find chunks/ -type f | xargs -n 1 -P 64 sh -c 'echo $0 `copy_to_distributed_table -C $0 mol_table`'

    The Citus 5.1 documentation states that the PostgreSQL COPY command is also supported and inserts rows into tables parallely, but it failed in our tests.

  5. Use the distributed table as any ordinary table for search, for example:
    CREATE INDEX mol_table_index ON mol_table USING chemindex(mol);
    SELECT id from mol_table WHERE 'c1ccccc1N' |<| mol;

Experienced limitations of Citus Community version

These limitations are not invoked by JChem PostgreSQL Cartridge.

  • Import can be done only with a limited set of PostgreSQL methods. Only a single insert can be performed using SQL , bulk insert can be performed with a command-line tool or COPY , described here , which has a much better performance.

  • No subselects are allowed in a modification statement (e.g. insert, delete, update). For example INSERT INTO table2 SELECT * FROM table1 WHERE 'C' |<| mol is not supported.

  • Only distributed tables can be joint in one SELECT statement. A distributed and non-distributed table join is not supported.

  • Only the postgres user can have distributed tables.

  • Distributed tables can not be renamed.

  • In Citus version 5.0 explain plans are not available, but they are already available in version 5.1.

  • Since the ChemAxon PostgreSQL Cartridge does not contain an equality operator for Molecule type, tables can not be distributed by hashing the Molecule type column. Tables containing molecules have to be sharded by another column.