Reference

• 1: PG Distribution
• 2: RDS Alternative
• 3: Architecture
• 4: Comparing
• 5: Modules
• 6: FAQ

    Create self-healing High-Available PG clusters without hassle, with pre-configured Point-In-Time-Recovery, ACL, CA, SSL...

    Declare your entire infra with code, and setup everything PG needs from Bare OS: LB, Nginx, NTP, DNS, Local Repo, etc...

    Unparalleled monitoring best practices built upon modern Prometheus & Grafana observability stacks out-of-the-box

    <span class="text-red-800 font-bold">437</span> PGSQL Extensions battery-included! Alone with forks: Babelfish, Oriole, IvorySQL, OpenHalo, PolarDB, Supabase,...

1 - PG Distribution

PostgreSQL is eating the database world, and it is becoming the Linux kernel of the database world.

But where are the distributions?

What is Distribution?

Nowadays, people use OS distributions like Ubuntu, Debian, and RHEL, rather than the raw Linux kernel directly. You’ll need a lot of components to build a practical operating system, such as systemd, cron, NTP, DNS, logging, …, to make the raw Linux kernel usable.

The linux kernel is several MB in size, but a full OS DVD can easily take up 10+GB, including all the necessary parts and software packages. That’s what a PostgreSQL Distribution is all about —— Gives you everything to build a production-grade Database Service.

Why do we need Distribution?

We have two things to forge a powerful PostgreSQL distribution: Extensions and Services.

Extensions

There are 1000+ extensions in the PostgreSQL ecosystem. But only 100 of them are accessible via the “Official” PGDG Repositories.

So we packed the most popular and useful extensions into pre-made RPM/DEB packages for 10 linux distributions and 5 PG Major version. Now there are unparalleled 422 extensions available out-of-the-box, and we will continue to add more extensions in the future.

What’s more, we even have support for 8 flavors of PostgreSQL kernels (ext, fork, wrapper, etc.), including:

Services

You can get started with raw PostgreSQL kernel easily like systemctl start postgresql, but it’s far away from production grade service. That’s the main reason why people pay 160 $ / vCPU·Month for a managed PostgreSQL service like AWS RDS.

But what if you can just build an enterprise-grade PostgreSQL service on your own, with a few commands, and no license fees? Pigsty enables you to do that. It gives you HA PostgreSQL clusters with PITR, Monitoring & Alerting, Connection Pooling, along with

2 - RDS Alternative

What is RDS?

You can get started with raw PostgreSQL kernel easily like systemctl start postgresql, but it’s far away from production grade service. That’s the main reason why people pay 160 $ / vCPU·Month for a managed PostgreSQL service like AWS RDS. and even pay more for traditional “enterprise” database services.

The expertise of Building and Managing Production-Grade PostgreSQL Service is rare and expensive.

What if…

But what if you can just build an enterprise-grade PostgreSQL service on your own, with a few commands, and no license fees? Pigsty enables you to do that. It gives you HA PostgreSQL clusters with PITR, Monitoring & Alerting, Connection Pooling, along with

It’s all starts from a few commands, and you can build a production-grade PostgreSQL service on your own, without the need for expensive licenses or expertise.

3 - Architecture

Modular Architecture and Declarative Interface!

• Pigsty deployment is described by config inventory and materialized with ansible playbooks.
• Pigsty works on Linux common nodes, i.e., bare metals or virtual machines.
• Pigsty uses a modular design that can be freely composed for different scenarios.
• The config controls where & how to install modules with parameters
• The playbooks will adjust nodes into the desired status in an idempotent manner.

Modules

Pigsty uses a modular design, and there are six default modules: PGSQL, INFRA, NODE, ETCD, REDIS, and MINIO.

• PGSQL: Autonomous HA Postgres cluster powered by Patroni, Pgbouncer, HAproxy, PgBackrest, etc…
• INFRA: Local yum/apt repo, Prometheus, Grafana, Loki, AlertManager, PushGateway, Blackbox Exporter…
• NODE: Tune node to desired state, name, timezone, NTP, ssh, sudo, haproxy, docker, promtail, keepalived
• ETCD: Distributed key-value store will be used as DCS for high-available Postgres clusters.
• REDIS: Redis servers in standalone master-replica, sentinel, cluster mode with Redis exporter.
• MINIO: S3 compatible simple object storage server, can be used as an optional backup center for Postgres.

You can compose them freely in a declarative manner. If you want host monitoring, INFRA & NODE will suffice. Additional ETCD and PGSQL are used for HA PG Clusters. Deploying them on multiple nodes will form an HA cluster. You can reuse pigsty infra and develop your modules, considering optional REDIS and MINIO as examples.

Singleton Meta

Pigsty will install on a single node (BareMetal / VirtualMachine) by default. The install.yml playbook will install INFRA, ETCD, PGSQL, and optional MINIO modules on the current node, which will give you a full-featured observability infrastructure (Prometheus, Grafana, Loki, AlertManager, PushGateway, BlackboxExporter, etc… ) and a battery-included PostgreSQL Singleton Instance (Named meta).

This node now has a self-monitoring system, visualization toolsets, and a Postgres database with autoconfigured PITR. You can use this node for devbox, testing, running demos, and doing data visualization & analysis. Or, furthermore, adding more nodes to it!

Monitoring

The installed Singleton Meta can be used as an admin node and monitoring center, to take more nodes & Database servers under it’s surveillance & control.

If you want to install the Prometheus / Grafana observability stack, Pigsty just deliver the best practice for you! It has fine-grained dashboards for Nodes & PostgreSQL, no matter these nodes or PostgreSQL servers are managed by Pigsty or not, you can have a production-grade monitoring & alerting immediately with simple configuration.

HA PG Cluster

With Pigsty, you can have your own local production-grade HA PostgreSQL RDS as much as you want.

And to create such a HA PostgreSQL cluster, All you have to do is describe it & run the playbook:

pg-test:
  hosts:
    10.10.10.11: { pg_seq: 1, pg_role: primary }
    10.10.10.12: { pg_seq: 2, pg_role: replica }
    10.10.10.13: { pg_seq: 3, pg_role: replica }
  vars: { pg_cluster: pg-test }

$ bin/pgsql-add pg-test

Which will give you the following cluster with monitoring, replica, backup all set.

Hardware failures are covered by self-healing HA architecture powered by patroni, etcd, and haproxy, which will perform auto failover in case of leader failure under 30 seconds. With the self-healing traffic control powered by haproxy, the client may not even notice there’s a failure at all, in case of a switchover or replica failure.

Software Failures, human errors, and DC Failure are covered by pgbackrest, and optional MinIO clusters. Which gives you the ability to perform point-in-time recovery to anytime (as long as your storage is capable)

Database as Code

Pigsty follows IaC & GitOPS philosophy: Pigsty deployment is described by declarative Config Inventory and materialized with idempotent playbooks.

The user describes the desired status with Parameters in a declarative manner, and the playbooks tune target nodes into that status in an idempotent manner. It’s like Kubernetes CRD & Operator but works on Bare Metals & Virtual Machines.

Take the default config snippet as an example, which describes a node 10.10.10.10 with modules INFRA, NODE, ETCD, and PGSQL installed.

# infra cluster for proxy, monitor, alert, etc...
infra: { hosts: { 10.10.10.10: { infra_seq: 1 } } }

# minio cluster, s3 compatible object storage
minio: { hosts: { 10.10.10.10: { minio_seq: 1 } }, vars: { minio_cluster: minio } }

# etcd cluster for ha postgres DCS
etcd: { hosts: { 10.10.10.10: { etcd_seq: 1 } }, vars: { etcd_cluster: etcd } }

# postgres example cluster: pg-meta
pg-meta: { hosts: { 10.10.10.10: { pg_seq: 1, pg_role: primary }, vars: { pg_cluster: pg-meta } }

To materialize it, use the following playbooks:

./infra.yml -l infra    # init infra module on group 'infra'
./etcd.yml  -l etcd     # init etcd module on group 'etcd'
./minio.yml -l minio    # init minio module on group 'minio'
./pgsql.yml -l pg-meta  # init pgsql module on group 'pgsql'

It would be straightforward to perform regular administration tasks. For example, if you wish to add a new replica/database/user to an existing HA PostgreSQL cluster, all you need to do is add a host in config & run that playbook on it, such as:

pg-test:
  hosts:
    10.10.10.11: { pg_seq: 1, pg_role: primary }
    10.10.10.12: { pg_seq: 2, pg_role: replica }
    10.10.10.13: { pg_seq: 3, pg_role: replica } # <-- add new instance
  vars: { pg_cluster: pg-test }

$ bin/pgsql-add  pg-test 10.10.10.13

You can even manage many PostgreSQL Entities using this approach: User/Role, Database, Service, HBA Rules, Extensions, Schemas, etc…

Check PGSQL Config for details.

4 - Comparing

Notice: this post is outdated and re-generated with claude

Pigsty positions itself as a local-first, open-source PostgreSQL platform that challenges traditional cloud database services and complex orchestration platforms. This comparison demonstrates Pigsty’s advantages across key dimensions.

Cloud RDS Comparison

AWS RDS PostgreSQL vs Pigsty

Cost Analysis

Traditional Cloud RDS Pricing:

• AWS RDS: $1,920-$2,640/vCPU/year
• Alibaba Cloud: $200-1,300/core/month
• Azure Database: Similar premium pricing

Pigsty Total Cost of Ownership:

• Hardware: $27/vCPU/year
• Software: Free (open source)
• Savings: 50-95% compared to cloud RDS

Observability Advantage

Pigsty Monitoring Capabilities:

• 3,000+ metrics vs cloud providers’ 99-200 metrics
• 638 PostgreSQL-specific metrics for deep database insights
• 50+ pre-built dashboards covering all infrastructure layers
• Real-time query analysis and performance tuning
• Custom dashboard creation with low-code tools

Cloud Provider Limitations:

• Basic CloudWatch/Azure Monitor metrics
• Limited customization options
• Additional costs for enhanced monitoring
• No access to underlying system metrics

Kubernetes Operators Comparison

Traditional K8s Operators vs Pigsty

Why Choose Pigsty Over K8s Operators

Simplicity Benefits:

• No container orchestration complexity
• Direct hardware access for optimal performance
• Familiar Linux tools for debugging and maintenance
• Reduced attack surface without container layers

Operational Advantages:

• Lower resource overhead compared to containerized solutions
• Easier troubleshooting with direct system access
• Simpler backup/restore operations
• Native OS integration for security and monitoring

PostgreSQL Distributions Comparison

Commercial Distributions

Open Source Alternatives

Multi-Cloud Strategy

Vendor Lock-in Avoidance

Cloud Provider Lock-in Risks:

• Proprietary APIs and tooling
• Data transfer costs for migration
• Feature dependency on specific platforms
• Pricing changes and service discontinuation

Pigsty Multi-Cloud Benefits:

• Portable across any Linux environment
• Consistent operations regardless of underlying infrastructure
• Freedom to negotiate with cloud providers
• Hybrid deployment capabilities

Migration Flexibility

graph TB
    A[Existing PostgreSQL] --> B[Pigsty Migration Tool]
    B --> C[Cloud Provider A]
    B --> D[Cloud Provider B]
    B --> E[On-Premises]
    B --> F[Hybrid Deployment]

    C --> G[Cross-Cloud Replication]
    D --> G
    E --> G
    F --> G

Migration Capabilities:

• Logical replication for live migration
• Point-in-time recovery across environments
• Configuration portability via Infrastructure as Code
• Zero-downtime migration procedures

Extension Ecosystem

Extension Availability Comparison

Enterprise Extensions

Pigsty Includes:

• pg_duckdb: Extreme analytics performance
• PostgresML: In-database machine learning
• Citus: Distributed PostgreSQL
• PostGIS: Advanced geospatial capabilities
• pgvector: Vector similarity search
• TimescaleDB: Time-series optimization

Cloud Limitations:

• Restricted extension catalog
• Version dependencies
• Installation restrictions
• Limited configuration options

Performance Characteristics

Hardware Optimization

Pigsty Performance Advantages:

• Direct hardware access without virtualization overhead
• Custom kernel tuning for database workloads
• NUMA awareness and CPU affinity optimization
• Storage optimization for specific workload patterns

Benchmark Results:

• 2M rows/second query throughput on optimized hardware
• 1M rows/second write performance
• 75µs 4K random read latency with NVMe storage
• 3M IOPS sustained performance

Efficiency Gains:

• Eliminated virtualization overhead
• Direct I/O access patterns
• Custom memory management
• Optimized network stack

Security and Compliance

Security Model Comparison

Data Sovereignty

Pigsty Sovereignty Benefits:

• Complete data ownership and control
• Local compliance with data protection regulations
• No third-party data access concerns
• Custom security policies implementation
• Air-gapped deployment capabilities

Decision Framework

When to Choose Pigsty

Ideal Use Cases:

• Cost-sensitive deployments requiring significant savings
• High-performance applications needing optimal resource utilization
• Compliance-critical environments requiring data sovereignty
• Complex analytics workloads needing advanced extensions
• Multi-cloud strategies avoiding vendor lock-in
• Existing infrastructure with available hardware resources

When to Consider Alternatives

Cloud RDS Advantages:

• Minimal operational overhead for small teams
• Quick proof-of-concept development
• Geographic distribution without infrastructure management
• Compliance certifications already established

Migration Path:

• Start with cloud RDS for rapid development
• Migrate to Pigsty for production cost optimization
• Maintain hybrid deployments for specific use cases

Pigsty delivers enterprise-grade PostgreSQL capabilities with significant cost savings, superior observability, and complete operational control, making it a compelling alternative to cloud database services and complex orchestration platforms.

5 - Modules

Core Modules

Pigsty consists of multiple modules. The PINE stack: PGSQL / INFRA / NODE / ETCD are ESSENTIAL for self-hosting Postgres RDS service.

    HA PG Cluster with PITR, IaC, ACL, Monitor, and 437 extensions

    Nginx, Repo, DNS, NTP, Prometheus and Grafana stack for Observability

    Enroll nodes into the desired state and monitor it, and VIP, HAProxy

    Reliable distributive consensus storage (DCS), empowering PGSQL HA

Extra Modules

Pigsty also have some OPTIONAL “Bonus” modules, which works well with PostgreSQL, and brings extra value to your data infrastructure.

    S3 compatible object storage compatible, optional backup storage

    High-performance in-memory cache, optional data structure server

    Container runtime, optional for running stateless app and tools

    MongoDB wire-protocol compatible on PostgreSQL, optional middleware

Kernel Modules

Pigsty allows using 8 exotic PostgreSQL KERNEL forks, as an optional in-place replacement:

6 - FAQ