Skip to main content
Background Image

PG Development

AI Large Models and Vector Database PGVector
·1941 words·10 mins
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
·3253 words·7 mins
How to implement relatively complex fuzzy search logic in PostgreSQL?
Transaction Isolation Level Considerations
·2705 words·13 mins
PostgreSQL actually has only two transaction isolation levels: Read Committed and Serializable
Frontend-Backend Communication Wire Protocol
·872 words·5 mins
Understanding the TCP protocol used for communication between PostgreSQL server and client, and printing messages using Go
CDC Change Data Capture Mechanisms
·4573 words·22 mins
Change Data Capture is an interesting ETL alternative solution.
O(n2) Complexity of GIN Search
·674 words·4 mins
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.
PostgreSQL Trigger Usage Considerations
·1069 words·6 mins
Detailed understanding of trigger management and usage in PostgreSQL
GeoIP Geographic Reverse Lookup Optimization
·1551 words·8 mins
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 Development Convention (2018 Edition)
·3438 words·17 mins
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.
KNN Ultimate Optimization: From RDS to PostGIS
·4114 words·20 mins
Ultimate optimization of KNN problems, from traditional relational design to PostGIS
Efficient Administrative Region Lookup with PostGIS
·2180 words·11 mins
How to efficiently solve the typical reverse geocoding problem: determining administrative regions based on user coordinates.
Implementing Mutual Exclusion Constraints with Exclude
·854 words·5 mins
Exclude constraint is a PostgreSQL extension that can implement more advanced and sophisticated database constraints.
Function Volatility Classification Levels
·644 words·4 mins
PostgreSQL functions have three volatility levels by default. Proper use can significantly improve performance.
Distinct On: Remove Duplicate Data
·557 words·3 mins
Use Distinct On extension clause to quickly find records with maximum/minimum values within groups
Implementing Cache Synchronization with Go and PostgreSQL
·1234 words·6 mins
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
·477 words·3 mins
Sometimes we want to record important metadata changes for audit purposes. PostgreSQL triggers can conveniently solve this need automatically.
UUID Properties, Principles and Applications
·1683 words·8 mins
UUID properties, principles and applications, and how to manipulate UUIDs using PostgreSQL stored procedures.