# pg_igraph - High-Performance Graph Traversal Engine

**Transform your PostgreSQL into a high-performance graph database. No external systems needed.**

## Performance That Speaks

| Operation | Dataset | pg_igraph | Recursive CTE | **Improvement** |
|-----------|---------|-----------|---------------|-----------------|
| BFS Traversal | 335K nodes | 227ms | 47,000ms | **🚀 207x** |
| Shortest Path | 10K nodes | 49ms | 8,500ms | **🚀 173x** |
| Multi-hop Query | 50K nodes | 156ms | 12,000ms | **🚀 77x** |

## Quick Start

```bash
# Install dependencies
# Make sure pg_ilib is installed first:
# https://pgxn.org/dist/pg_ilib/

# Install pg_igraph
pgxn install pg_igraph
```

```sql
-- Enable extensions (order matters!)
CREATE EXTENSION pg_ilib;  -- Required dependency
CREATE EXTENSION pg_igraph;

-- Start using immediately
SELECT graph_add_node('User');
SELECT graph_add_edge(1, 2, 'follows');

-- Query with JSON parameters (v1.1)
SELECT igraph_query('',
  'MATCH (u:User)-[:follows]->(f) 
   WHERE f.influence > &data.threshold 
   RETURN f.name',
  '{"data":{"threshold":100}}'
);
```

## Why pg_igraph?

### ✅ **Native PostgreSQL Integration**
- No external graph databases needed
- Leverage existing PostgreSQL infrastructure
- ACID compliance and backup strategies work

### ✅ **Adaptive Performance** 
- Automatically switches between SPI and C hash maps
- Smart thresholds based on graph topology
- Zero SQL overhead for large traversals

### ✅ **Version 1.1 Features**
- **JSON Parameters**: `&data.field` syntax for dynamic queries
- **Enhanced WHERE**: All comparison operators (`=`, `>`, `<`, `>=`, `<=`, `!=`)
- **Multi-Graph Support**: Multiple isolated graphs per database
- **Clean API**: Pure data arrays `[{...}]` or empty objects `{}`

### ✅ **Rich Query Language**
- Cypher-like syntax integrated into SQL
- Full AST with flex/bison parser
- Support for complex pattern matching

## Installation

### Prerequisites

```bash
# PostgreSQL 14+ with development headers
sudo apt-get install postgresql-server-dev-14

# Required build tools
sudo apt-get install flex bison

# Required dependency
pgxn install pg_ilib
```

### From PGXN

```bash
pgxn install pg_igraph
```

### From Source

```bash
git clone https://github.com/ineron/pg_igraph
cd pg_igraph
make && sudo make install
```

### Database Setup

```sql
-- Enable extensions (order matters!)
CREATE EXTENSION pg_ilib;  -- Install this first
CREATE EXTENSION pg_igraph;

-- Optional: Initialize schema with custom partitioning
-- Copy and edit .env.example, then run ./init_graph.sh
```

## API Reference

### Core Functions

```sql
-- Graph management (with optional table_prefix support)
graph_add_node(label, table_prefix DEFAULT '') → BIGINT
graph_add_edge(from_id, to_id, relationship, table_prefix DEFAULT '') → VOID

-- Properties
graph_set_property(node_id, prop_name, type, value, table_prefix DEFAULT '') → VOID
graph_get_property(node_id, prop_name, table_prefix DEFAULT '') → TEXT

-- Traversal
graph_traverse(start, rel, direction, max_depth) → SETOF BIGINT
graph_shortest_path(start, end, rel) → BIGINT[]

-- Query Language
igraph_query(table_prefix, query, json_params) → JSONB
```

### Query Language Examples

```sql
-- Pattern matching with JSON parameters
SELECT igraph_query('',
  'MATCH (n:User)-[:FOLLOWS*1..3]->(m) 
   WHERE n.id > &data.min_id AND m.score != &data.exclude
   RETURN m',
  '{"data":{"min_id":1, "exclude":0}}'
);

-- Path finding
SELECT igraph_query('', 'PATH FROM 1 TO 100 VIA follows');

-- Multi-graph usage
SELECT graph_add_node('Employee', 'company_org');
SELECT igraph_query('company_org', 'MATCH (e:Employee) RETURN e');
```

## Architecture

- **Dual-Direction Storage**: Forward and reverse edges for optimal access
- **Hash Partitioning**: 8-64 partitions for parallel processing  
- **Covering Indexes**: Three indexes per partition for maximum performance
- **Adaptive Algorithms**: Automatic SPI ↔ C hash map switching

## Perfect For

- **Social Networks**: Friend recommendations, influence analysis
- **E-commerce**: Product recommendations, customer journeys
- **Enterprise**: Org charts, workflow dependencies  
- **Geographic**: Route optimization, network topology
- **Security**: Access control, fraud detection

## Configuration

### Partitioning Strategy
```bash
# Recommended settings in .env
GRAPH_PARTITIONS=16  # For 10M-100M edges

# Scaling guidelines:
# 8 partitions:  up to 10M edges
# 16 partitions: 10M-100M edges
# 32 partitions: 100M-500M edges
# 64 partitions: 500M+ edges
```

### Performance Tuning
```sql
-- Recommended PostgreSQL settings
SET shared_buffers = '4GB';
SET effective_cache_size = '12GB';
SET random_page_cost = 1.1;  -- For SSD storage
SET work_mem = '256MB';
```

## Dependencies

- **PostgreSQL**: 14.0 or higher
- **pg_ilib**: 1.0.0 or higher (install first from PGXN)
- **Build tools**: flex, bison, make, gcc

## License

MIT License - see LICENSE file for details.

## Support

- **Issues**: https://github.com/ineron/pg_igraph/issues
- **Documentation**: Complete API docs in the repository
- **Author**: Eugene <ineron.spb@gmail.com>

---

**Created with ❤️ for the PostgreSQL community**