PostgreSQL Convention 2024
No rules, no standards. Some developer conventions for PostgreSQL 16.
Tags: PG-Development
AI Large Models and Vector Database PGVector
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.
Implementing Advanced Fuzzy Search
How to implement relatively complex fuzzy search logic in PostgreSQL?
Frontend-Backend Communication Wire Protocol
Understanding the TCP protocol used for communication between PostgreSQL server and client, and printing messages using Go
Transaction Isolation Level Considerations
PostgreSQL actually has only two transaction isolation levels: Read Committed and Serializable
CDC Change Data Capture Mechanisms
Change Data Capture is an interesting ETL alternative solution.
Locks in PostgreSQL
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.
O(n2) Complexity of GIN Search
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.
GeoIP Geographic Reverse Lookup Optimization
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)
PostgreSQL Trigger Usage Considerations
Detailed understanding of trigger management and usage in PostgreSQL
PostgreSQL Development Convention (2018 Edition)
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.
Efficient Administrative Region Lookup with PostGIS
How to efficiently solve the typical reverse geocoding problem: determining administrative regions based on user coordinates.
KNN Ultimate Optimization: From RDS to PostGIS
Ultimate optimization of KNN problems, from traditional relational design to PostGIS
Distinct On: Remove Duplicate Data
Use Distinct On extension clause to quickly find records with maximum/minimum values within groups
Function Volatility Classification Levels
PostgreSQL functions have three volatility levels by default. Proper use can significantly improve performance.
Implementing Mutual Exclusion Constraints with Exclude
Exclude constraint is a PostgreSQL extension that can implement more advanced and sophisticated database constraints.
Implementing Cache Synchronization with Go and PostgreSQL
Cleverly utilizing PostgreSQL’s Notify feature, you can conveniently notify applications of metadata changes and implement trigger-based logical replication.
Auditing Data Changes with Triggers
Sometimes we want to record important metadata changes for audit purposes. PostgreSQL triggers can conveniently solve this need automatically.
Building an ItemCF Recommender in Pure SQL
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
UUID properties, principles and applications, and how to manipulate UUIDs using PostgreSQL stored procedures.