Pigsty Blog Articles
GeoIP Geographic Reverse Lookup Optimization
July 07, 2018 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)
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)
Understanding Character Encoding Principles
July 01, 2018 in Database
Without understanding the basic principles of character encoding, even simple string operations like comparison, sorting, and random access can easily lead you into pitfalls. This article attempts to clarify these issues through a comprehensive explanation.
Without understanding the basic principles of character encoding, even simple string operations like comparison, sorting, and random access can easily lead you into pitfalls. This article attempts to clarify these issues through a comprehensive explanation.
PostgreSQL Development Convention (2018 Edition)
June 20, 2018 in 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.
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.
Concurrency Anomalies Explained
June 19, 2018 in Database
Concurrent programs are hard to write correctly and even harder to write well. Many programmers simply throw these problems at the database... But even the most sophisticated databases won't help if you don't understand concurrency anomalies and isolation levels.
Concurrent programs are hard to write correctly and even harder to write well. Many programmers simply throw these problems at the database... But even the most sophisticated databases won't help if you don't understand concurrency anomalies and isolation levels.
What Are PostgreSQL's Advantages?
June 10, 2018 in PostgreSQL
PostgreSQL's slogan is "The World's Most Advanced Open-Source Relational Database," but I think the most vivid characterization should be: The Full-Stack Database That Does It All - one tool to rule them all.
PostgreSQL's slogan is "The World's Most Advanced Open-Source Relational Database," but I think the most vivid characterization should be: The Full-Stack Database That Does It All - one tool to rule them all.
Blockchain and Distributed Databases
June 09, 2018 in Database
The technical essence, functionality, and evolution of blockchain is distributed databases. Specifically, it's a **Byzantine Fault Tolerant (resistant to malicious node attacks) distributed (leaderless replication) database**.
The technical essence, functionality, and evolution of blockchain is distributed databases. Specifically, it's a **Byzantine Fault Tolerant (resistant to malicious node attacks) distributed (leaderless replication) database**.
KNN Ultimate Optimization: From RDS to PostGIS
June 06, 2018 in PostgreSQL
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
June 06, 2018 in PostgreSQL
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.
Monitoring Table Size in PostgreSQL
May 14, 2018 in PostgreSQL
Tables in PostgreSQL correspond to many physical files. This article explains how to calculate the actual size of a table in PostgreSQL.
Tables in PostgreSQL correspond to many physical files. This article explains how to calculate the actual size of a table in PostgreSQL.
Consistency: An Overloaded Term
May 08, 2018 in Database
The term "consistency" is heavily overloaded, representing different concepts in different contexts. For example, the C in ACID and the C in CAP actually refer to different concepts.
The term "consistency" is heavily overloaded, representing different concepts in different contexts. For example, the C in ACID and the C in CAP actually refer to different concepts.