Pigsty Blog Articles
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.
Why Study Database Principles
April 20, 2018 in Database
Those who only know how to code are just programmers; **learn databases well, and you can at least make a living**; but for **excellent** engineers, merely **using** databases is far from enough.
Those who only know how to code are just programmers; **learn databases well, and you can at least make a living**; but for **excellent** engineers, merely **using** databases is far from enough.
PgAdmin Installation and Configuration
April 14, 2018 in PostgreSQL
PgAdmin is a GUI program for managing PostgreSQL, written in Python, but it's quite dated and requires some additional configuration.
PgAdmin is a GUI program for managing PostgreSQL, written in Python, but it's quite dated and requires some additional configuration.
Incident-Report: Uneven Load Avalanche
April 08, 2018 in PostgreSQL
Recently there was a perplexing incident where a database had half its data volume and load migrated away, but ended up being overwhelmed due to increased load.
Recently there was a perplexing incident where a database had half its data volume and load migrated away, but ended up being overwhelmed due to increased load.
Bash and psql Tips
April 07, 2018 in PostgreSQL
Some tips for interacting between PostgreSQL and Bash.
Some tips for interacting between PostgreSQL and Bash.
Implementing Mutual Exclusion Constraints with Exclude
April 06, 2018 in PostgreSQL
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
April 06, 2018 in PostgreSQL
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.