Build the example dataset

Development documentation

This page follows the development branch. For the software and documentation of a released version, consult the published releases.

Download the complete SQL script, including dataset setup for a fresh database.

This setup creates 10,000 people and 49,985 directed follows. A ring connects everyone; deterministic hash shortcuts give people varied incoming links. It needs no external dataset, Python packages, or application code.

Start PostgreSQL

If pg_warren is already installed, create a fresh database and skip to the SQL below. Otherwise, start the published PostgreSQL 17 container:

docker run -d --name warren-examples --shm-size=1g \
  -e POSTGRES_PASSWORD=warren-local \
  -e POSTGRES_DB=warren_examples \
  -p 127.0.0.1:55432:5432 \
  ghcr.io/exadev/pg_warren:0.7.0-pg17

Wait until docker exec warren-examples pg_isready -U postgres reports that PostgreSQL is accepting connections. Then open a SQL session:

docker exec -it warren-examples psql -U postgres -d warren_examples

A desktop database client can use host localhost, port 55432, database warren_examples, user postgres, and password warren-local. The image runs PostgreSQL; it does not include a browser GUI. If that host port is already occupied, choose another in the Docker command.

Create the graph

Run this setup once per database, then run recipes in the same database. The warren_examples schema and example_social graph keep the example objects separate from application tables. Schema-qualified source queries make the graph independent of the session’s search path.

-- Run once in a fresh database with pg_warren installed on the server.
CREATE EXTENSION IF NOT EXISTS pg_warren;
CREATE SCHEMA warren_examples;
CREATE TABLE warren_examples.people (id bigint PRIMARY KEY, name text NOT NULL);
CREATE TABLE warren_examples.follows (
    src bigint REFERENCES warren_examples.people(id),
    dst bigint REFERENCES warren_examples.people(id),
    PRIMARY KEY (src, dst)
);
INSERT INTO warren_examples.people
SELECT n, 'person-' || n FROM generate_series(1, 10000) AS n;

-- A ring guarantees reachability; four hash shortcuts per person create
-- varied in-degrees. UNION removes duplicate edges; exclude self-loops.
INSERT INTO warren_examples.follows
SELECT id, id % 10000 + 1 FROM warren_examples.people
UNION
SELECT id, dst
FROM warren_examples.people CROSS JOIN generate_series(1, 4) AS k
CROSS JOIN LATERAL (
    SELECT ('x' || substr(md5(id::text || ':' || k::text), 1, 8))::bit(32)::bigint % 10000 + 1 AS dst
) AS target
WHERE id <> dst;
ANALYZE warren_examples.people;
ANALYZE warren_examples.follows;

SELECT warren.create_graph('example_social',
    'SELECT id FROM warren_examples.people',
    'SELECT src, dst FROM warren_examples.follows');
SELECT warren.refresh('example_social');
SELECT (SELECT count(*) FROM warren_examples.people) AS nodes,
       (SELECT count(*) FROM warren_examples.follows) AS edges;

The final result is nodes = 10000, edges = 49985. The first refresh builds graph version 1. The graph’s external id type is bigint, matching people.id; id-returning calls therefore use 1::bigint or NULL::bigint.

Run a recipe

Choose an example from the library. For a downloaded SQL script, use a fresh database because the download already includes this setup:

docker exec -i warren-examples psql -v ON_ERROR_STOP=1 \
  -U postgres -d warren_examples < bfs.sql

Alternatively, paste the entire downloaded file into pgAdmin’s Query Tool. Run whole scripts so transient scratch vectors remain in the same session. In pgAdmin, select the result set you want in Data Output; loop progress appears under Messages.

Stop the example container with docker stop warren-examples; start it again with docker start warren-examples. The stopped container keeps its database. For installation and shared-memory sizing, see installation and limits.