Optimize your self-managed PostgreSQL data warehouse with Amazon FSx for OpenZFS

A data warehouse is the analytical backbone of a modern enterprise, consolidating data from disparate sources into a single, authoritative view that enables complex queries, trend analysis, and confident decision-making. In financial services, this means sharper regulatory reporting, faster fraud detection, and deeper customer understanding. The operational reality is demanding. Enterprises juggle multiple source databases that require continuous synchronization, periodic full reloads, and redundant copies for disaster recovery. All this drives rapid storage growth. The core challenge is building a storage architecture that is simultaneously scalable, performant, resilient, and cost-effective.

This post addresses teams running a self-managed PostgreSQL data warehouse on Amazon Web Services (AWS) where the storage footprint and full-reload time are growing unchecked. The guidance is tailored for teams that have already chosen self-managed PostgreSQL; teams still evaluating their options should first consider managed services such as Amazon Redshift or Amazon Relational Database Service (Amazon RDS).

Although enterprises have historically run these systems on-premises, the cloud is now a more compelling option, with benefits including access to a rich ecosystem of analytics and AI services alongside better economics. With AWS, organizations can choose from three well-defined paths depending on their needs:

• Amazon Redshift delivers purpose-built, petabyte-scale analytics with columnar storage and massively parallel processing.
• Amazon RDS offers a fully managed alternative, handling backups, patching, and high availability, suitable for multi-terabyte workloads where operational simplicity matters.
• For teams needing deeper control, Amazon Elastic Compute Cloud (Amazon EC2) with AWS storage services enables a self-managed deployment with operating system (OS)-level tuning and independent scaling of compute and storage.

The AWS Well-Architected Framework provides guidance to build secure, high-performing, resilient, and scalable infrastructure for workloads on AWS, helping organizations optimize their architecture for their specific needs while maintaining the flexibility and control they require.

In this advanced-level (300) post for database administrators and infrastructure engineers, we show how to modernize a self-managed PostgreSQL-based data warehouse setup using AWS storage services. By using Amazon FSx for OpenZFS, you can reduce your storage footprint using its compression feature and improve performance for full data reloads from your production database.

Solution overview

In this solution, AWS Database Migration Service (AWS DMS) handles data synchronization from production online transaction processing databases to the data warehouse (for more details, see Working with AWS DMS tasks). The storage layer uses FSx for OpenZFS, a fully managed shared storage system built on the popular open source OpenZFS platform. FSx for OpenZFS provides optimal PostgreSQL database storage, combining low-latency access for read-heavy analytics workloads with high throughput for batch operations. The architecture uses FSx for OpenZFS as the storage layer, accessed using NFS protocol from EC2 instances that host PostgreSQL databases. The PostgreSQL data directories reside on the mounted FSx storage, benefiting from built-in compression while maintaining high performance.

This architecture is designed with careful considerations of both compute and storage requirements, providing optimal performance for data warehouse operations:

• Deploy the primary (active) PostgreSQL database on an EC2 compute instance in Availability Zone 1 (AZ1).
• Configure a read (standby) replica PostgreSQL database on a smaller EC2 compute instance in Availability Zone 2 (AZ2) to optimize costs while providing a failover option when the primary EC2 compute instance is not available.
• Deploy Single-AZ based fully managed shared file storage in each Availability Zone, same as the respective database EC2 instances (primary and replica) to provide local data access with the lowest possible latency.
• Establish PostgreSQL asynchronous replication between primary and replica systems for data synchronization (not applicable for read-after-write workloads).

Figure 1 shows a primary-replica PostgreSQL data warehouse deployment on AWS across multiple Availability Zones using FSx for OpenZFS as the storage layer. The solution spans two Availability Zones within a virtual private cloud (VPC) for resilience and fault tolerance. For more information about Availability Zones, see Regions and Availability Zones.

Figure 1: Multi-AZ PostgreSQL data warehouse architecture with Amazon FSx for OpenZFS

Deployment considerations

This section outlines the key deployment guidelines for the proposed solution, including infrastructure requirements, performance, data compression, and disaster recovery.

Compute
The compute infrastructure requires selecting an appropriate EC2 instance type to optimize performance and cost, and the right OS option. To achieve adequate network throughput for data warehouse workloads, the solution implements different networking tiers for the primary and replica instances. For example, the primary PostgreSQL database in AZ1 runs on an EC2 instance (such as r8in.12xlarge) with 48 vCPUs, memory 384 GiB, and high-performance networking speed at 75 Gbps to handle intensive write operations and data processing. The read replica in AZ2 uses a cost-effective EC2 instance (such as r8in.4xlarge) with 16 vCPUs, memory 128 GiB, and networking speed up to 50 Gbps (instance size can be scaled up, if needed).

Storage
A variety of fully managed storage services are available for hosting self-managed data warehouse workloads. We recommend FSx for OpenZFS for PostgreSQL database storage because it gives shared NFS access across compute instances, built-in snapshots/clones at no incremental cost, compression, and a high availability solution.

The design incorporates an FSx for OpenZFS file system with a Single-AZ (HA) deployment type, where EC2 instances are connected using the NFS protocol for the data warehouse setup. Although FSx for OpenZFS also offers Multi-AZ deployment option, the selected Single-AZ (HA) configuration provides an optimal balance between business requirements and cost-efficiency.

Under this design, the primary FSx file system is configured with provisioned SSD capacity 5120 GiB, provisioned throughput of 2560 MBps, and provisioned SSD IOPS 40,000 (as shown in Figure 2), and the secondary FSx file system is configured with provisioned SSD capacity 4096 GiB (replicating only subset of databases), provisioned throughput of 64 MBps, and default SSD IOPS (it can be scaled up, if needed). To maintain optimal write performance, keep storage utilization below 80% of provisioned SSD capacity.

Figure 2: FSx for OpenZFS file system creation options

After you create the FSx file system, a volume is required that can be mounted by multiple Linux EC2 instances using NFS protocol, as shown in Figure 3.

In a PostgreSQL database, data is stored in fixed-size pages (default 8 KiB blocks) that contain multiple variable-length rows. When writing, PostgreSQL appends or updates rows within these pages (logging changes using WAL), and when reading, it loads entire pages from disk into memory and then retrieves the required rows from those pages.

Record size is a per-volume setting in FSx for OpenZFS that specifies the suggested maximum logical block size for file data. The default is 128 KiB for FSx for OpenZFS file systems using provisioned SSD storage. Record size affects copy-on-write behavior, compression efficiency, metadata overhead, and I/O performance. The right value depends on how the workload accesses its files.

• Compression – ZFS compresses each logical block independently. Larger records can improve compression ratios by giving the algorithm more data per block, though the gain depends on the data and the compression algorithm.
• Sequential and bulk I/O – Large sequential writes, such as a full data reload, let ZFS create full records efficiently. Larger records reduce the number of logical blocks, lower metadata overhead, improve prefetch efficiency, and produce larger storage I/O operations.
• Small random access – When the database updates an 8 KiB page inside an existing 128 KiB record, ZFS performs a partial-record update, which needs the unchanged remainder of the record. If the record is in ARC, ZFS reads that data from memory; otherwise it reads the existing record from storage. ZFS then writes a new copy-on-write version of the logical record and recompresses it. This can create significant read and write amplification, though the physical write may be smaller than 128 KiB after compression. Similarly, an uncached random 8 KiB read can require ZFS to fetch and decompress the containing record, whereas a cached record needs no storage read.

This data warehouse runs bulk reloads and read-heavy analytical scans, so 128 KiB is a reasonable conservative choice. AWS recommends the default record size for most workloads, though that doesn’t make 128 KiB optimal for every case. Larger values such as 256 KiB, 512 KiB, or 1 MiB can perform better for predominantly sequential workloads and are worth evaluating through workload testing. For OLTP or other workloads dominated by small random page accesses, a smaller record size closer to PostgreSQL’s 8 KiB page can reduce read and write amplification, weighed against the higher metadata overhead and lower compression of smaller records.

Figure 3: Amazon FSx for OpenZFS volume creation options

The NFS mount options to optimize FSx for OpenZFS performance on Linux clients is shown in Figure 4.

Figure 4: NFS mount options for Amazon FSx for OpenZFS volume on Linux EC2 instance

The parameters are as follows:

• nconnect=16 – Allows multiple TCP connections associated with a single NFS mount.
• rsize=1048576, wsize=1048576 – Sets the maximum number of bytes of data that the NFS client can receive or send for each network read or write request, respectively.
• sync – NFS server acknowledges a write request only after the data has been committed to stable storage, providing stronger durability.
• nfsvers=3 – Explicitly specifies the use of the NFSv3 protocol.
• timeo=100 – Sets the timeout value that the NFS client uses to wait for a response before it retries an NFS request.
• proto=tcp – Uses the TCP transport protocol for more reliable communication over the network compared to UDP.
• hard – If the NFS server becomes unavailable, the client will wait indefinitely for a response. This prevents the soft timeout errors that can lead to unrecoverable I/O errors and database corruption.
• noatime – Disables the automatic updating of a file’s access time (atime) when the file is accessed but not modified.

The NFS mount parameters rsize and wsize define the maximum payload size per NFS operation. Although PostgreSQL uses an 8 KiB page size, these settings act as upper bounds, not minimums; small I/O requests remain small. However, keeping them at 1 MiB allows the NFS client and OS to coalesce sequential I/O into larger transfers, improving throughput and reducing RPC overhead during bulk operations and scans.

Data compression

Data compression reduces storage consumption and increases effective throughput. Although compression can be enabled at either the database or storage level, implementing FSx for OpenZFS compression at the storage layer results in substantial CPU resource savings.

As shown in Figure 5, FSx for OpenZFS offers two types of volume compression algorithms:

• Zstandard (ZSTD) – Provides a higher level of on-disk data compression with higher read throughput but reduced write throughput performance compared to LZ4 compression
• LZ4 – Provides higher write throughput performance but achieves lower levels of data compression than ZSTD compression

For this solution, we used ZSTD to achieve better compression and reduce the storage footprint.

Figure 5: Data compression options for Amazon FSx for OpenZFS volume

Data replication, backup, and maintenance window strategy
The data protection strategy encompasses both replication and backup solutions. For replication, the design implements PostgreSQL database-level replication between primary and replica instances, enabling a near real-time data copying process that provides resilience when the primary instance fails. This configuration can achieve a Recovery Point Objective (RPO) of less than 1 minute and a Recovery Time Objective (RTO) of less than 2 minutes for database failover operations. A read-after-write workload can’t use the read replica immediately until the data is synced from the primary to the replica instance. During maintenance activities on the primary database, the replica database can be promoted to primary status.

Alternatively, there is an additional option with FSx for OpenZFS (Multi-AZ), which provides a single file system interface with built-in automated data sync between preferred and standby file servers hosted in separate Availability Zones. The solution discussed in this post uses PostgreSQL-level asynchronous replication, which gives flexibility and a non-zero RPO with manual database promotion across Availability Zones, while FSx for OpenZFS (Multi-AZ) gives synchronous cross-AZ replication (RPO=0) with automatic failover and additional durability for data stored across Availability Zones.

The backup design includes daily automated FSx snapshots with up to a 30-day retention period for backing up OpenZFS file system data, providing an additional recovery option when data restoration is needed. For a comprehensive data resilience strategy, AWS recommends using AWS Backup to centralize data protection and recovery tasks including protection from ransomware and malware.

Another important aspect of FSx for OpenZFS is the maintenance window as shown in the following screenshot. FSx for OpenZFS performs routine software patching (for security and reliability) for the OpenZFS software it manages as part of its maintenance window (30-minute default). While patching is in progress and requires only a portion of the 30-minute maintenance window, Single-AZ (HA) file systems will remain available and automatically fail over and fail back between the preferred file server and the standby file server. The maintenance window makes it possible to adjust as needed to accommodate workload and operational requirements. To mitigate system impact, AWS recommends keeping the maintenance window different for primary and secondary FSx for OpenZFS file systems in the solution design, so the administrator can fail over between the primary and secondary file systems (data remains replicated due to PostgreSQL DB replication).

Figure 6: Backup and maintenance options for FSx for OpenZFS file system

Cleanup

To avoid incurring ongoing charges from the resources created in this walkthrough, delete the following resources when they are no longer needed:

1. On the Amazon FSx console, select each FSx for OpenZFS file system (primary and replica) and choose Delete file system. Confirm that you no longer need any snapshots or backups associated with these file systems before deletion. Deleting an FSx for OpenZFS file system is irreversible and removes all volumes and automatic backups associated with it. Ensure you have backed up any data you need before proceeding. [7]
2. On the Amazon EC2 console, terminate both the primary (for example, r8in.12xlarge) and replica (for example, r8in.4xlarge) instances used for the PostgreSQL databases.
3. If you created AWS DMS replication instances, endpoints, or migration tasks for this solution, delete them on the AWS DMS console to stop incurring charges.
4. If you created any manual backups (or retained final backups during file system deletion), navigate to the Backups section on the Amazon FSx console and delete any that are no longer needed.
5. If you created a dedicated VPC, subnets, security groups, or route tables for this walkthrough, delete them on the Amazon VPC console. Remove security groups, then subnets, then the VPC.
6. If you configured Amazon CloudWatch monitoring resources as part of the performance monitoring best practices, delete them on the CloudWatch console.

Best practices and recommendations

The following best practices should be considered when deploying the storage solution for a data warehouse platform, focusing on four key areas:

• NFS client mount options – Implement the synchronous mount option for high durability in production (opt for async to reduce CPU wait time and durability trade-off), use the nconnect option for improved NFS client performance, and fine-tune record sizes to achieve optimal compression and performance.
• Storage configuration – Adjust provisioned throughput (MBps) for FSx for OpenZFS based on workload patterns, optimize for large I/O read-heavy patterns, and implement an hourly snapshot strategy for quick recovery.
• Performance monitoring – Maintain optimal performance by establishing a robust monitoring process with regular checks on CPU wait times, tracking of network throughput and IOPS utilization, and monitoring of compression ratios and storage growth.
• High availability – High availability is a key consideration in the solution design. Maintain the primary database and read replica in different Availability Zones. Perform maintenance activities on the read replica before promoting it to primary status. After maintenance is complete, promote the replica to primary. This approach provides business continuity and minimizes potential downtime during maintenance windows or in the event of a failure.

Adhering to these best practices across NFS configuration, storage management, performance monitoring, and high availability planning maximizes the benefits of FSx for OpenZFS while maintaining robust and reliable operation of the PostgreSQL data warehouse.

Benefits observed in customer deployment

The implementation of our FSx for OpenZFS for data warehouse solution using a self-managed PostgreSQL database delivered substantial benefits across three key areas for one of our insurance customers:

• Storage optimization – By moving compression to the storage layer with ZSTD on FSx for OpenZFS, the customer reduced storage by approximately 74%, shrinking the primary storage footprint from 19 TB to about 5 TB. Offloading compression to the storage layer also freed host CPU for query and reload work.
• Performance improvements – The AWS DMS data load time dropped from 36 hours to just 5 hours, representing an 86% improvement in full data reload operations. The solution also led to a 50% reduction in IOPS requirements, decreasing from 35,000 to 15,000 IOPS.
• Cost reduction – The customer was incurring storage costs for primary and replica DB instances, accounting for 35% of their total solution cost. From a cost reduction perspective, the customer realized an annual savings of approximately $60,000 in storage costs. The enhanced data compression capabilities resulted in a 74% reduction in monthly storage growth costs. Furthermore, the solution’s efficiency allowed for more flexible EC2 instance selection, leading to an additional 25% cost savings in compute resources.

These combined benefits demonstrated the significant value proposition of implementing FSx for OpenZFS for their PostgreSQL data warehouse environment.

Conclusion

FSx for OpenZFS is a natural fit for self-managed database workloads like PostgreSQL and Oracle, optimizing storage costs through built-in data compression (Zstandard/LZ4), and reduced operational complexity as a fully managed service. For workloads with a mix of frequently and infrequently accessed data, consider Amazon FSx Intelligent-Tiering storage class, which offers full elasticity and data moves automatically between three storage tiers (Frequent Access, Infrequent Access, and Archive) to further reduce costs.

For organizations modernizing their data warehouse on AWS, FSx for OpenZFS offers a compelling path forward: a smaller storage footprint, faster data reloads, and an architecture aligned with the AWS Well-Architected Framework’s principles of scalability, resilience, and operational excellence. The result is a foundation that serves both today’s analytics demands and tomorrow’s growth.

Ready to transform your PostgreSQL data warehouse storage strategy? Speak with your AWS account team to explore what FSx for OpenZFS can do for your environment.

To learn more about the services and concepts discussed in this post, see the following resources:

• Amazon FSx for OpenZFS – Product overview, features, and pricing.
• Amazon FSx for OpenZFS User Guide – Complete documentation.
• Performance for Amazon FSx for OpenZFS – Throughput, IOPS, and deployment type comparisons.
• Amazon FSx for OpenZFS availability and durability – Single-AZ and Multi-AZ deployment options.
• Getting started with self-managed Oracle in AWS using Amazon FSx for OpenZFS – Companion blog post for Oracle database workloads.
• Working with AWS DMS tasks– Data synchronization patterns referenced in this post.
• AWS Backup – Centralized backup and data protection.
• AWS Well-Architected Framework – Best practices for building on AWS.