Tag: PG Development
Git for Data: Instant PostgreSQL Database Cloning
Every programmer has used git clone. Hit enter, wait a few seconds, and a complete code repository appears on your disk. But what about databases? Want a copy of production data for your test environment? The traditional approach is pg_dump + …
Every programmer has used git clone. Hit enter, wait a few seconds, and a complete code repository appears on your disk. But what about databases? Want a copy of production data for your test environment? The traditional approach is pg_dump + …
PostgreSQL Convention 2024
Background 0x01 Naming Convention 0x01 Design Convention 0x01 Query Convention 0x01 Admin Convention Roughly translated from PostgreSQL Convention 2024 with Google. 0x00 Background No Rules, No Lines The functions of PostgreSQL are very powerful, …
Background 0x01 Naming Convention 0x01 Design Convention 0x01 Query Convention 0x01 Admin Convention Roughly translated from PostgreSQL Convention 2024 with Google. 0x00 Background No Rules, No Lines The functions of PostgreSQL are very powerful, …
AI Large Models and Vector Database PGVector
New AI applications have shown exponential growth over the past year, and these applications face a common challenge: how to store and query AI embeddings represented as vectors at scale. This article focuses on vector databases hyped by AI, …
New AI applications have shown exponential growth over the past year, and these applications face a common challenge: how to store and query AI embeddings represented as vectors at scale. This article focuses on vector databases hyped by AI, …
Implementing Advanced Fuzzy Search
In daily development, we often encounter requirements for fuzzy search. Today, let’s briefly discuss how to implement some advanced fuzzy search using PostgreSQL. Of course, the fuzzy search I’m talking about here isn’t the old-fashioned LIKE …
In daily development, we often encounter requirements for fuzzy search. Today, let’s briefly discuss how to implement some advanced fuzzy search using PostgreSQL. Of course, the fuzzy search I’m talking about here isn’t the old-fashioned LIKE …
PG Replica Identity Explained
Introduction: DIY Logical Replication The concept of replica identity serves logical replication. The basic working principle of logical replication is to decode row-level INSERT/UPDATE/DELETE events from logical publication-related tables and …
Introduction: DIY Logical Replication The concept of replica identity serves logical replication. The basic working principle of logical replication is to decode row-level INSERT/UPDATE/DELETE events from logical publication-related tables and …
Transaction Isolation Level Considerations
PostgreSQL actually has only two transaction isolation levels: Read Committed and Serializable Basics The SQL standard defines four isolation levels, but PostgreSQL actually has only two transaction isolation levels: Read Committed and Serializable …
PostgreSQL actually has only two transaction isolation levels: Read Committed and Serializable Basics The SQL standard defines four isolation levels, but 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 Startup Phase The basic flow of the startup phase is as follows: Client sends a StartupMessage (F) to initiate connection request to server Payload includes …
Understanding the TCP protocol used for communication between PostgreSQL server and client Startup Phase The basic flow of the startup phase is as follows: Client sends a StartupMessage (F) to initiate connection request to server Payload includes …
CDC Change Data Capture Mechanisms
In actual production, we often need to synchronize database states to other places, such as synchronizing to data warehouses for analysis, to message queues for downstream consumption, or to caches to accelerate queries. Generally speaking, there are …
In actual production, we often need to synchronize database states to other places, such as synchronizing to data warehouses for analysis, to message queues for downstream consumption, or to caches to accelerate queries. Generally speaking, there are …
Locks in PostgreSQL
PostgreSQL relies on snapshot isolation (SI) for concurrency and two-phase locking (2PL) as a supporting act. DML (SELECT/INSERT/UPDATE/DELETE) uses SSI; DDL (CREATE TABLE etc.) still uses 2PL. Understanding locks is essential when diagnosing …
PostgreSQL relies on snapshot isolation (SI) for concurrency and two-phase locking (2PL) as a supporting act. DML (SELECT/INSERT/UPDATE/DELETE) uses SSI; DDL (CREATE TABLE etc.) still uses 2PL. Understanding locks is essential when diagnosing …
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. Here is the detail of why that query have O(N^2) inside GIN …
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. Here is the detail of why that query have O(N^2) inside GIN …
Understanding Time - Leap Years, Leap Seconds, Time and Time Zones
A few days ago, we encountered the quadrennial leap year February 29th. Every time this day comes around, some poorly written software experiences major failures. If you’re unlucky, this type of problem might take four years to surface. For example, …
A few days ago, we encountered the quadrennial leap year February 29th. Every time this day comes around, some poorly written software experiences major failures. If you’re unlucky, this type of problem might take four years to surface. For example, …
PostgreSQL Trigger Usage Considerations
Overview Trigger behavior overview Trigger classification Trigger functionality Trigger types Trigger firing Trigger creation Trigger modification Trigger queries Trigger performance Trigger Overview Trigger behavior overview: English, Chinese …
Overview Trigger behavior overview Trigger classification Trigger functionality Trigger types Trigger firing Trigger creation Trigger modification Trigger queries Trigger performance Trigger Overview Trigger behavior overview: English, Chinese …
GeoIP Geographic Reverse Lookup Optimization
Efficient implementation of IP geolocation lookups In application development, a ‘very common’ requirement is GeoIP conversion - converting source IP addresses from requests into corresponding geographic coordinates or administrative divisions …
Efficient implementation of IP geolocation lookups In application development, a ‘very common’ requirement is GeoIP conversion - converting source IP addresses from requests into corresponding geographic coordinates or administrative divisions …
Understanding Character Encoding Principles
Programmers deal with Code (both programming code and encoding), and character encoding is the most fundamental type of encoding. The problem of how to represent characters using binary numbers - this character encoding problem is not as simple as it …
Programmers deal with Code (both programming code and encoding), and character encoding is the most fundamental type of encoding. The problem of how to represent characters using binary numbers - this character encoding problem is not as simple as it …
PostgreSQL Development Convention (2018 Edition)
0x00 Background Without rules, there can be no order. PostgreSQL is extremely powerful, but to use PostgreSQL well requires coordinated effort from backend developers, operations teams, and DBAs. This article compiles a development specification …
0x00 Background Without rules, there can be no order. PostgreSQL is extremely powerful, but to use PostgreSQL well requires coordinated effort from backend developers, operations teams, and DBAs. This article compiles a development specification …
KNN Ultimate Optimization: From RDS to PostGIS
Flexibly applying database functionality can easily achieve a 30,000-fold performance improvement in GIS selection scenarios. Level Method Performance/Time(ms) Maintainability/Reliability Notes 1 Brute Force 30,000 - Simple form 2 Coordinate Index 35 …
Flexibly applying database functionality can easily achieve a 30,000-fold performance improvement in GIS selection scenarios. Level Method Performance/Time(ms) Maintainability/Reliability Notes 1 Brute Force 30,000 - Simple form 2 Coordinate Index 35 …
Efficient Administrative Region Lookup with PostGIS
Author: Vonng (@Vonng) Original WeChat article In application development, we often need to solve this problem: determining administrative regions based on user coordinates. We collect coordinates like 28°00'00"N 100°00'00.000"E, but what we …
Author: Vonng (@Vonng) Original WeChat article In application development, we often need to solve this problem: determining administrative regions based on user coordinates. We collect coordinates like 28°00'00"N 100°00'00.000"E, but what we …
Implementing Mutual Exclusion Constraints with Exclude
Exclude constraint is a PostgreSQL extension that can implement more advanced and sophisticated database constraints. Introduction Data integrity is extremely important, but data integrity guaranteed by applications isn’t always reliable: humans make …
Exclude constraint is a PostgreSQL extension that can implement more advanced and sophisticated database constraints. Introduction Data integrity is extremely important, but data integrity guaranteed by applications isn’t always reliable: humans make …
Function Volatility Classification Levels
PostgreSQL functions have three volatility levels by default. Proper use can significantly improve performance. Core Differences VOLATILE: Has side effects, cannot be optimized. STABLE: Executes database queries. IMMUTABLE: Pure function, execution …
PostgreSQL functions have three volatility levels by default. Proper use can significantly improve performance. Core Differences VOLATILE: Has side effects, cannot be optimized. STABLE: Executes database queries. IMMUTABLE: Pure function, execution …
Distinct On: Remove Duplicate Data
Distinct On is a unique syntax provided by PostgreSQL that can efficiently solve typical query problems, for example, quickly finding records with maximum/minimum values within groups. Introduction Finding records with maximum/minimum values within …
Distinct On is a unique syntax provided by PostgreSQL that can efficiently solve typical query problems, for example, quickly finding records with maximum/minimum values within groups. Introduction Finding records with maximum/minimum values within …
Implementing Cache Synchronization with Go and PostgreSQL
Parallel and Hierarchy are the two great principles of architectural design, and caching is the embodiment of Hierarchy in the IO domain. Implementing caching mechanisms in single-threaded scenarios can be surprisingly simple, but it’s hard to …
Parallel and Hierarchy are the two great principles of architectural design, and caching is the embodiment of Hierarchy in the IO domain. Implementing caching mechanisms in single-threaded scenarios can be surprisingly simple, but it’s hard to …
Auditing Data Changes with Triggers
Author: Vonng (@Vonng) Sometimes we want to record important metadata changes for audit purposes. PostgreSQL triggers can conveniently solve this need automatically. -- Create an audit-specific schema and revoke all non-superuser privileges DROP …
Author: Vonng (@Vonng) Sometimes we want to record important metadata changes for audit purposes. PostgreSQL triggers can conveniently solve this need automatically. -- Create an audit-specific schema and revoke all non-superuser privileges DROP …
Building an ItemCF Recommender in Pure SQL
Everyone knows “item-based collaborative filtering” (ItemCF): Amazon recommendations, YouTube watch-next, etc. Here’s how to implement it in PostgreSQL using the MovieLens dataset. No Python, just SQL. Theory in one minute ItemCF recommends items …
Everyone knows “item-based collaborative filtering” (ItemCF): Amazon recommendations, YouTube watch-next, etc. Here’s how to implement it in PostgreSQL using the MovieLens dataset. No Python, just SQL. Theory in one minute ItemCF recommends items …
UUID Properties, Principles and Applications
A recent project needed to generate business transaction IDs with the following requirements: IDs must be generated in a distributed manner, cannot depend on central node allocation while ensuring global uniqueness. IDs must contain timestamps and …
A recent project needed to generate business transaction IDs with the following requirements: IDs must be generated in a distributed manner, cannot depend on central node allocation while ensuring global uniqueness. IDs must contain timestamps and …