Tags: PG-Development
PostgreSQL Convention 2024
No rules, no standards. Some developer conventions for PostgreSQL 16.
No rules, no standards. Some developer conventions for PostgreSQL 16.
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.
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?
How to implement relatively complex fuzzy search logic in PostgreSQL?
Transaction Isolation Level Considerations
PostgreSQL actually has only two transaction isolation levels: **Read Committed** and **Serializable**
PostgreSQL actually has only two transaction isolation levels: **Read Committed** and **Serializable**
Frontend-Backend Communication Wire Protocol
Understanding the TCP protocol used for communication between PostgreSQL server and client, and printing messages using Go
Understanding the TCP protocol used for communication between PostgreSQL server and client, and printing messages using Go
CDC Change Data Capture Mechanisms
Change Data Capture is an interesting ETL alternative solution.
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`.
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.
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
Detailed understanding of trigger management and usage in PostgreSQL
Detailed understanding of trigger management and usage in PostgreSQL
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)
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)
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.
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
Ultimate optimization of KNN problems, from traditional relational design to PostGIS
Ultimate optimization of KNN problems, from traditional relational design to PostGIS
Efficient Administrative Region Lookup with PostGIS
How to efficiently solve the typical reverse geocoding problem: determining administrative regions based on user coordinates.
How to efficiently solve the typical reverse geocoding problem: determining administrative regions based on user coordinates.
Implementing Mutual Exclusion Constraints with Exclude
Exclude constraint is a PostgreSQL extension that can implement more advanced and sophisticated database constraints.
Exclude constraint is a PostgreSQL extension that can implement more advanced and sophisticated database constraints.
Function Volatility Classification Levels
PostgreSQL functions have three volatility levels by default. Proper use can significantly improve performance.
PostgreSQL functions have three volatility levels by default. Proper use can significantly improve performance.
Distinct On: Remove Duplicate Data
Use Distinct On extension clause to quickly find records with maximum/minimum values within groups
Use Distinct On extension clause to quickly find records with maximum/minimum values within groups
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.
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.
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.
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.
UUID properties, principles and applications, and how to manipulate UUIDs using PostgreSQL stored procedures.