PostgreSQL实战教程 (it-ebooks)（Z-Library）

# PostgreSQL实战教程 — Reading Guide ## 【One-Line Pitch】 A practical, hands-on collection of PostgreSQL advanced techniques — from unusual index types and spatial-temporal analytics to high-dimensional vector search and production monitoring — written for DBA engineers and developers who want to move beyond basic SQL and build a competitive edge in the era of domestic database adoption. ## 【Book Arc】 - **Opening (~0%–9%)**: Industry context and career motivation — why PostgreSQL is positioned as the "new foundation" amid China's database localization wave, how it compares architecturally to Oracle/DB2, and a learning methodology for experienced database engineers looking to switch. - **Early (~9%–26%)**: Deep dive into PostgreSQL's distinctive index ecosystem — BRIN, GIN, GiST, and pg_trgm — with concrete, runnable examples: array indexing for phone lookups, IP-range geolocation via range types, LIKE '%xxx%' acceleration, and JSONB user profiling. - **Early (~26%–35%)**: Introduction to Ganos, Alibaba Cloud's spatial-temporal engine built on PostgreSQL — covering vector/raster/trajectory models, PB-level remote sensing image management with OSS, and a COVID-19 case study combining spatial statistics with trajectory tracking. - **Middle (~35%–48%)**: Vector retrieval fundamentals — ANN algorithms (IVFFlat, IMI, HNSW), their trade-offs, and a survey of existing libraries (Faiss, SPTAG, Proxima, Vearch), plus the rationale for choosing PostgreSQL as a vector search engine. - **Middle (~48%–52%)**: PostgreSQL's custom index extension mechanism — Page structure internals, the IndexAmRoutine API, and how IVFFlat/HNSW algorithms are implemented as custom index plugins (PASE). - **Late (~52%–100%)**: Production concerns — replication principles and high-availability clustering, monitoring with Pigsty, performance optimization, and systematic operations. *(Excerpts thin here; details limited.)* ## 【Key Takeaways】 - **BRIN is a PostgreSQL differentiator** (Early): Block Range Indexes store min/max summaries per physical block range, making them ideal for large, naturally ordered tables (e.g., time-series or sequential IDs) where they dramatically shrink index size while keeping queries fast. - **GIN indexes unlock array and JSONB power** (Early): PostgreSQL allows indexing array columns and JSONB documents directly — enabling fast "contains" queries (`@>`) for scenarios like phone-number lookup in contact arrays or tag-based user profiling with sub-millisecond response times. - **GiST + range types solve range-lookup problems elegantly** (Early): Instead of querying `ip_begin <= x AND ip_end >= x` (which forces range scans), store IP intervals as a single range column and use a GiST index — turning a costly range query into an efficient containment check. - **pg_trgm makes `LIKE '%xxx%'` indexable** (Early): A problem other databases struggle with — PostgreSQL solves it via trigram indexes, cutting execution time from hundreds of milliseconds to ~2ms on million-row tables. - **Ganos is PostGIS on steroids** (Early): Alibaba's spatial-temporal engine extends PostgreSQL with grid models, trajectory models, and point clouds — plus vector pyramid indexing that renders billions of spatial records in seconds without traditional tile-slicing. - **Vector search is about precision-to-performance conversion** (Middle): ANN algorithms trade accuracy for speed; the best algorithms (like HNSW) achieve large performance gains with minimal precision loss — the key metric for evaluating any ANN approach. - **HNSW is the industrial-grade choice** (Middle): Hierarchical Navigable Small World graphs deliver both high speed and accuracy (10ms-level latency on tens of millions of vectors), at the cost of memory for neighbor storage — suitable for strict-latency production scenarios. - **PostgreSQL's custom index API is a strategic advantage** (Middle): With a fully customizable Page structure and the IndexAmRoutine interface, PG allows building bespoke index plugins (e.g., PASE for vector search) — something MySQL and Elasticsearch cannot match easily. ## 【Reading Tips】 - **Skim the opening industry analysis** (~0%–9%) if you're already convinced about PostgreSQL; it's motivational context rather than technical content — but the Oracle-to-PG architecture comparison table is worth a quick scan for migration-minded readers. - **Deep-read the index chapters** (~9%–26%): These are the most immediately applicable — each example (BRIN, GIN on arrays, GiST on ranges, pg_trgm, JSONB) comes with runnable SQL. Recreate them in your own test database to internalize the patterns. - **Treat the Ganos chapter** (~26%–35%) as a feature showcase: The COVID-19 case study demonstrates real spatial-temporal workflows, but the specific SQL is Alibaba Cloud-specific — focus on understanding the *capabilities* (vector-raster fusion, trajectory tracking) rather than memorizing syntax. - **The vector search section** (~35%–52%) gets technical: If you're not building a vector engine, skim the algorithm comparisons and focus on the decision framework (when to choose IVFFlat vs. HNSW vs. IMI). The PG internals discussion (Page structure, IndexAmRoutine) is only essential for plugin developers. - **The final sections** (replication, monitoring, performance) appear in the table of contents but are thinly covered in the excerpts — treat them as pointers for further research rather than comprehensive guides. ## 【Coverage Limits】 This guide synthesizes the first ~52% of the book in detail (industry context, index techniques, Ganos, vector search). The later sections on replication/high-availability, Pigsty monitoring, and performance optimization are listed in the table of contents but not covered by the available excerpts. ##