PostgreSQL Archives - ITFROMZERO

Designing and Optimizing Hierarchical Tree Data in SQL: A Practical Comparison of 4 Models

By IT From Zero October 28, 2026

A practical comparison of four hierarchical data models in SQL—Adjacency List, Path Enumeration, Nested Sets, and Closure Table—with real-world benchmarks and query optimization tips.

Centos

Installing Keycloak on CentOS Stream 9: Building a Production-Ready SSO System with PostgreSQL 16 and Nginx

By IT From Zero October 28, 2026

A detailed guide to deploying Keycloak on CentOS Stream 9 with PostgreSQL 16 and Nginx reverse proxy. Covers SSO architecture, secure systemd configuration, JVM heap optimization, and troubleshooting SELinux in production.

Development

A Guide to Testcontainers with TypeScript: Production-Grade Dockerized Integration Tests for PostgreSQL and Redis

By IT From Zero October 27, 2026

Integration tests touching databases and caches are notoriously prone to dirty state and port collisions. Testcontainers paired with TypeScript lets you automatically spin up, isolate, and tear down real PostgreSQL and Redis containers directly from your test code.

Database

Diagnosing and Resolving Deadlocks in PostgreSQL: Inspecting Lock Trees and Configuring Proper Timeouts

By IT From Zero October 26, 2026

An in-depth look at the root causes of PostgreSQL deadlocks, step-by-step reproduction, Lock Tree query techniques to locate root blockers, and timeout tuning to safeguard your system.

Docker

How to Deploy Umami Analytics with Docker Compose and PostgreSQL: Self-Host a Lightweight GA4 Alternative

By IT From Zero October 23, 2026

A step-by-step guide to deploying Umami Analytics with Docker Compose and PostgreSQL. A lightweight 2KB, GDPR-compliant, real-time self-hosted GA4 alternative.

Python

Battle-Tested SQLAlchemy 2.0 Async: Eliminating MissingGreenlet and Connection Pool Exhaustion

By IT From Zero October 21, 2026

Hit by connection pool exhaustion and MissingGreenlet at 2 AM? Here is a practical guide to configuring AsyncSession, eager loading, and streaming in SQLAlchemy 2.0 Async.

Development

A Practical Guide to pgvector on PostgreSQL: Vector Embeddings and Semantic Search for Node.js

By IT From Zero October 19, 2026

Practical production lessons deploying pgvector on PostgreSQL: Node.js integration, HNSW index tuning, and memory optimization techniques to slash latency from 8s to 20ms.

Database

How to Install and Configure pgRouting on PostgreSQL: Build Your Own Shortest Path Routing Engine

By IT From Zero October 17, 2026

A hands-on guide to resolving logistics dispatch bottlenecks: installing pgRouting on PostgreSQL, building graph topologies, and computing optimal shortest paths.

Database

Real-Time Without a Message Broker: Leveraging PostgreSQL LISTEN and NOTIFY with WebSockets

By IT From Zero October 15, 2026

Is your infrastructure overwhelmed by relentless polling and bulky message brokers? Leverage PostgreSQL's built-in LISTEN/NOTIFY alongside WebSockets to build a sleek, transaction-safe, and resource-efficient real-time notification system.

Monitoring

A Practical Guide to Monitoring PgBouncer Connection Pooler with Prometheus and Grafana

By IT From Zero October 14, 2026

Hands-on experience monitoring PgBouncer with Prometheus and Grafana. Learn how to track waiting queries, measure pool saturation, and craft alert rules that eliminate alert fatigue in production.