41 articles
Knowledge Base
PostgreSQL
41 articles in the PostgreSQL category · page 1 of 2.
- Full-text search in PostgreSQL with tsvector and tsquery Build a production full-text search in PostgreSQL — generated tsvector columns, GIN indexes, ranking, multilingual dictionaries, and operational care.
- Postgres timeline divergence after failback — pg_rewind vs base backup After a failover the old primary cannot rejoin as a standby because it's on a different timeline. The decision tree between pg_rewind (fast) and full pg_basebackup (slow but always safe).
- PostgreSQL materialized views with concurrent refresh Use materialized views in PostgreSQL — REFRESH MATERIALIZED VIEW CONCURRENTLY, the unique-index prerequisite, and the operational pattern for dashboards.
- PostgreSQL streaming-replication manual promotion runbook When Patroni isn't in the picture or automation is unavailable, you still need to promote a standby cleanly. Exact pg_ctl, recovery, and verification flow for a manual primary promotion.
- PgBouncer connection pooling for PostgreSQL Install PgBouncer, choose the right pool mode for your application, tune pool sizes, and avoid the prepared-statement trap in transaction-mode pools.
- Splitting reads and writes in a managed Postgres setup Read replicas only help when the application sends reads to them. Here is how we route read and write traffic on managed Postgres without breaking transactions.
- PostgreSQL VACUUM and autovacuum — keeping bloat in check How VACUUM and autovacuum work, the parameters that matter on busy tables, how to read pg_stat_user_tables, and what to do about transaction-ID wraparound.
- Installing PostgreSQL on Ubuntu with the PGDG repository Install PostgreSQL 16 on Ubuntu from the PGDG repo, verify the cluster, set up authentication, and prepare the host for day-2 operations.
- Deploying a CockroachDB 24.x 3-node cluster on Ubuntu 22.04 with TLS Stand up a self-hosted CockroachDB 24.x cluster on three Ubuntu 22.04 VMs — node certificates, internal-only listener, snapshot strategy aligned with cloud volumes.
- TimescaleDB hypertables for time-series on PostgreSQL Run time-series workloads on PostgreSQL with TimescaleDB — hypertables, chunk sizing, continuous aggregates, retention policies, and compression.
- PostgreSQL logical replication in depth Operate PostgreSQL logical replication beyond the basics — publication filters, conflict handling, replication slots, and the failure modes you only see in production.
- Citus sharding for PostgreSQL in depth Operate Citus distributed PostgreSQL — coordinator/worker layout, distribution columns, reference tables, colocated joins, and rebalance operations.
- Row-level security for multi-tenant PostgreSQL Enforce tenant isolation in PostgreSQL with row-level security — policies, FORCE RLS, session variables, performance considerations, and bypass roles.
- pgAudit for row-level auditing on PostgreSQL Use the pgAudit extension to log who did what to PostgreSQL — session and object audit, row-level filters, performance trade-offs, and downstream pipelines.
- TimescaleDB continuous aggregates: designing rollups for fast dashboards Designing a continuous-aggregates hierarchy in TimescaleDB so dashboards stay sub-second as your hypertable crosses a billion rows — bucket sizes, refresh policies, real-time aggregates, hierarchical rollups.
- JSONB indexing with GIN and JSONPath on PostgreSQL Index JSONB columns in PostgreSQL — pick between jsonb_ops and jsonb_path_ops, use expression indexes, and use JSONPath for surgical queries.
- VACUUM strategies for very large PostgreSQL tables Tune autovacuum for large PostgreSQL tables — per-table thresholds, parallel workers, freeze management, and the patterns that prevent transaction-ID wraparound.
- pgvector similarity search on PostgreSQL Run vector similarity search in PostgreSQL with pgvector — HNSW vs IVFFlat, dimensionality, indexing trade-offs, and operational sizing for RAG workloads.
- PostgreSQL streaming replication — setup and verification Configure asynchronous streaming replication, bootstrap a standby with pg_basebackup, monitor lag, and prepare for a clean promotion path.
- pgBadger log analysis for PostgreSQL Configure PostgreSQL logging and run pgBadger to produce HTML reports on slow queries, lock waits, errors, and traffic patterns — daily ops cadence.
- PostgreSQL roles and pg_hba.conf — authentication done right How PostgreSQL roles, group membership, and pg_hba.conf interact — with the rule order, the TLS-required pattern, and the audit query for drift detection.
- Deploying PostgreSQL 16 HA with Patroni + etcd + HAProxy on AlmaLinux 9 Stand up a self-hosted Postgres 16 HA cluster on AlmaLinux 9 — Patroni for orchestration, etcd for consensus, HAProxy for routing, pgBouncer in front.
- pg_dump and pg_restore — logical backups for PostgreSQL Produce parallel custom-format dumps with pg_dump, restore selectively with pg_restore, and use directory format for the fastest restores at scale.
- pg_repack for online table and index rebuilds on PostgreSQL Use pg_repack to reclaim bloat without long ACCESS EXCLUSIVE locks — install, plan a run, monitor progress, and handle the edge cases that trip up first-timers.