Upgrade to Pro — share decks privately, control downloads, hide ads and more …

What are Containerized Postgres Storage Require...

OnGres
September 18, 2023

What are Containerized Postgres Storage Requirements

Postgres is increasingly being run on production on containers and also on Kubernetes, thanks to the various existing Postgres operators. But running applications like databases in containers, with heavy requirements on the storage layer, definitely presents challenges.This talk will be focused on describing how Postgres interacts with the Storage subsystem, and in particular what advanced requirements it poses on the storage. This is easy to extrapolate to any other database or even other containerized data-intensive workloads.

In particular, this talk will analyze:

* What is Postgres storage architecture.
* What are usual performance expectations.
* How backups are/should be handled.
* How and what for snapshots can be used.
* The implications of database replication.
* Other advanced topics like tablespaces or WAL separation.

OnGres

September 18, 2023
Tweet

More Decks by OnGres

Other Decks in Technology

Transcript

  1. • Founder & CEO, OnGres • 20+ years Postgres user

    and DBA • Mostly doing R&D to create new, innovative software on Postgres • More than 135 tech talks • Founder and President of the NPO Fundación PostgreSQL • AWS Data Hero `whoami` Álvaro Hernández <[email protected]> aht.es
  2. • Describe how Postgres works at the storage layer •

    List the needs for an “ideal” storage system • Explain how actually Postgres K8s operators use storage • What may the future look like for Postgres Storage Goals for this talk
  3. • Like most RDBMSs, Postgres is a shared-nothing architecture. •

    Each instance/pod has a full copy of the data. • Data is replicated via streaming replication (physical replication over TCP connection). • Data is guaranteed to be the same (eventually or synchronously) but disks are not a exact copy. • Replicas serve read-only queries but require a read-write filesystem (e.g. processing WAL, temporary queries, etc). Postgres Storage Architecture. Replication
  4. • Postgres uses by default a 8192 bytes page size.

    • It can be grown or shrunk by recompiling Postgres. • There’s little experience with different sizes. Almost everybody goes with the default. • Depending on the storage block size, it may be convenient to experiment with different page sizes. Block Size
  5. • Postgres relies on a POSIX filesystem. • Most used

    ones are: • ext4 • XFS • There are some uses of ZFS (mostly on BSD). Performance is lower than ext4/XFS but may be worth. Requires explicit tuning. • Requires careful operation if run on a distributed filesystem (e.g. NFS) as it may lead to data corruption. Filesystem
  6. • Backups are taken in physical form. They consist of

    a copy of the filesystem (PGDATA) along with a set of WAL files (generated over time). • Filesystem copy does not require to be atomic iif pg_start_backup(‘label’) and pg_stop_backup() are called before and after. • Snapshots can replace filesystem copy, and backup start/stop function calls are not needed, but are recommended. • Differential backups are possible (e.g. PgBackRest). Postgres Backups
  7. • Many Postgres workloads are heavy I/O bound. • Usually

    there’s a great difference between running local NVMEs vs network disk. • Latency sensitivity for WAL files. • Sequential throughput for table scans. • Random operations for index traversal. Performance
  8. • Each Postgres node can have one or multiple disks.

    • Multiple are used when: • Separating WAL files. • Separating logs. • Tablespaces. • But separating into disks may introduce consistency issues. Multiple disks
  9. • Snapshots are highly desirable characteristic, even though not very

    widely used in Postgres world. • Can be used for many use cases: • Backups (base backup replacement) • Initializing replicas (requires scripting) • Initializing logical replicas (requires even more scripting) • Postgres upgrades (huge scripting required, but doable) • Database branching! (see DBLab by postgres.ai) Snapshots
  10. • Despite some performance hit, it is highly desirable. •

    Snapshot support is great, including diffs between snapshots. • Compression may provide fair storage and I/O savings. • Built-in encryption solves one usual requirement for database encryption-on-rest (sometimes transparent data encryption, TDE, is required). • ZFS is challenging on container environments due to the need to insert the module in the kernel. ZFS
  11. • For most, it’s quite straightforward: use StorageClass. • One

    or more volumes (main PGDATA; WAL or tablespaces) provisioned from the StorageClass per pod. • Occasionally, existing PVs may be used directly. • CEPH is sometimes used. Works, but there’s not much production experience with it. Storage for Postgres Kubernetes Operators
  12. • Leverage local storage (e.g. NVME) But make it as

    reliable as network storage. • Make snapshots a first-class citizen in Kubernetes. Leverage them for Postgres DBOps. • Move to shared storage architectures? Serverless Postgres (Aurora, Neon). • Expose Object Storage (e.g. S3) to Postgres: • Via specialized FS (exposed as POSIX e.g. ZFS proprietary mods) • As a FDW What the Postgres storage future looks like?