PG-Development

No rules, no standards. Some developer conventions for PostgreSQL 16.

This article focuses on vector databases hyped by AI, introduces the basic principles of AI embeddings and vector storage/retrieval, and demonstrates the functionality, performance, acquisition, and application of the vector database extension PGVECTOR through a concrete knowledge base retrieval case study.

How to implement relatively complex fuzzy search logic in PostgreSQL?

PostgreSQL actually has only two transaction isolation levels: Read Committed and Serializable

Understanding the TCP protocol used for communication between PostgreSQL server and client, and printing messages using Go

Change Data Capture is an interesting ETL alternative solution.

Snapshot isolation does most of the heavy lifting in PG, but locks still matter. Here’s a practical guide to table locks, row locks, intention locks, and pg_locks.

When GIN indexes are used to search with very long keyword lists, performance degrades significantly. This article explains why GIN index keyword search has O(n^2) time complexity.

Detailed understanding of trigger management and usage in PostgreSQL

A common requirement in application development is GeoIP conversion - converting source IP addresses to geographic coordinates or administrative divisions (country-state-city-county-town-village)

Without rules, there can be no order. This article compiles a development specification for PostgreSQL database principles and features, which can reduce confusion encountered when using PostgreSQL.

Ultimate optimization of KNN problems, from traditional relational design to PostGIS

How to efficiently solve the typical reverse geocoding problem: determining administrative regions based on user coordinates.

Exclude constraint is a PostgreSQL extension that can implement more advanced and sophisticated database constraints.

PostgreSQL functions have three volatility levels by default. Proper use can significantly improve performance.

Use Distinct On extension clause to quickly find records with maximum/minimum values within groups

Cleverly utilizing PostgreSQL’s Notify feature, you can conveniently notify applications of metadata changes and implement trigger-based logical replication.

Sometimes we want to record important metadata changes for audit purposes. PostgreSQL triggers can conveniently solve this need automatically.

Five minutes, PostgreSQL, and the MovieLens dataset—that’s all you need to implement a classic item-based collaborative filtering recommender.

UUID properties, principles and applications, and how to manipulate UUIDs using PostgreSQL stored procedures.