PGSpider

What PGSpider is

PGSpider is a SQL cluster engine for querying distributed data through Foreign Data Wrappers. It can combine similarly structured tables from multiple sources into a Multi-Tenant virtual table that can be queried with one SQL statement. Queries and result fetching run across child nodes in parallel, and WHERE clauses and aggregation functions can be pushed down to those nodes. Multi-Tenant tables support AVG, STDDEV, and VARIANCE, along with INSERT, UPDATE, DELETE, and bulk INSERT using a batch_size option. The README demonstrates PostgreSQL and SQLite and names PostgreSQL, MySQL, Oracle, GridDB, PGSpider, InfluxDB v2.0, and Amazon S3 Parquet storage in its migration list. PGSpider uses PostgreSQL-like operation and documents client use with libpq and psql. It is free and open source, with an included license permitting use, copying, modification, and distribution without a fee. It is self-hosted on Linux; the documented deployment route involves building from source and installing to /usr/local/pgspider. Some modification patterns are unsupported, and the README recommends auto-commit for modifications because data may not be readable within a transaction and non-auto-commit changes may be inconsistent. Its tree-structure feature is described as beta and not tested enough.

Who it suits

PGSpider suits teams running Linux who want to query distributed data through SQL and Foreign Data Wrappers. It may fit users comfortable building and installing software from source.

Limits

Modification does not support RETURNING, WITH CHECK OPTION, ON CONFLICT, or foreign-partition COPY and modification. The README recommends auto-commit because modified data may not be readable within a transaction and non-auto-commit changes may be inconsistent; the tree-structure feature is in beta.

Getting started

1. Open the PGSpider project website on GitHub
2. Build PGSpider from source as documented
3. Install it to /usr/local/pgspider
4. Use a PostgreSQL-like client such as libpq or psql

Verdict

PGSpider brings distributed sources into a SQL workflow and supports parallel queries and several modification operations. Its documented setup and transaction caveats matter for teams planning deployment.