Postgresql
notes
addons
-
- purpose neon is used to decouple storage from compute for the database. This enables it to provide copy on write semantics such as database snapshots/branching.
- architecture Uses a patched postgres(compute node) with a pageserver and safekeeper (storage nodes) to decouple storage and computation.
compute nodes communicate to the pageserver for reads while streaming writes to the safekeeper. safekeeper stores the writes into storage backend which the pageserver reads from.
- crunchydata operator
- Uses kubernetes operator framework to manage postgres and backups with pgbackrest using yaml custom resource definitions.
- Local setup and usage with kind.
- pgpodman extension for managing containers (redis cache) from db
- https://www.postgresql.org/docs/current/limits.html
- postgres compiled to wasm playground
- https://github.com/snaplet/postgres-wasm
- https://www.crunchydata.com/developers/playground/high-level-performance-analytics
- uses https://copy.sh/v86/ and a alpine qemu img with postgres
SET TRANSACTION SERIALIZABLEfor strict isolation- extension for secrets
- foreign data wrappers
- WAL streaming
replication
- Network attached storage (EBS) for Database
- Read replicas (writes lag)
- DBMS optimized (Neon/Aurora/alloydb) streaming WAL and reading from replicated pages.
- Active-active (any node accepts writes causing disjoint reads)
- Sharding (citius) requires application change to query shard/tenant for low latency
- Spanner/Distributed KV (Yugabyte/CockroachDB) not actually postgres (just protocol), no local joins
- synchronouscommit=off
- wallevel=minimal
- walwriterdelay=200ms
- checkpointcompletiontarget=0.9
- autovacuum=off
- bgwriterdelay=10000ms
- sharedbuffers=512MB
- workmem=64MB
- UNLOGGED tables
- run from RAM Disk (/dev/shm)