-
PostgreSQL开发规约（2018版）
2018年06月20日 in PG 开发
微信公众号原文 0x00背景 没有规矩，不成方圆。 PostgreSQL的功能非常强大，但是要把PostgreSQL用好，需要后端、运维、DBA的协力配合。 本文针对PostgreSQL数据库原理与特性，整理了一份开发规范，希望可以减少大家在使用PostgreSQL数据库过程中遇到的困惑。你好我也好，大家都好。 0x01 命名规范 无名，万物之始，有名，万物之母。 【强制】 通用命名规则 本规则适用于所有对象名，包括：库名、表名、表名、列名、函数名、视图名、序列号名、别名等。 对象名务必只使用 …
-
PostGIS高效解决行政区划归属查询
2018年06月06日 in PG 开发
Module:
Categories:
微信公众号原文 在应用开发中，很多时候我们需要解决这样一个问题：根据用户的经纬度坐标，定位用户的行政区划。 我们收集到的是诸如28°00'00"N 100°00'00.000"E这样的经纬度坐标，但实际感兴趣的是这个点所属的行政区划：（中华人民共和国，云南省，迪庆藏族自治州，香格里拉市）。这种将地理坐标映射到某条记录的操作就称为地理编码（GeoEncode）。高效实现地理编码是一个很有趣的问题。 本文介绍了该问题的解决与优化方案：能在确保正确性的前提下，能用几兆的空 …
-
KNN极致优化：从RDS到PostGIS
2018年06月06日 in PG 开发
Module:
Categories:
灵活应用数据库的功能，可以轻松实现 GIS 圈选场景下三万倍的性能提升。 Level 方法 性能/耗时(ms) 可维护性/可靠性 备注 1 暴力扫表 30,000 - 形式简单 2 经纬索引 35 复杂度/魔数问题 额外复杂度 3 联合索引 10 复杂度/魔数问题 额外复杂度 4 GIST 4 最简表达，完全精确 形式简单，距离更精确，PostgreSQL限定 5 btree_gist联合索引 1 最简表达，完全精确 形式简单，距离更精确，PostgreSQL限定 场景 互联网中的很多业务都涉及 …
-
用 Exclude 实现互斥约束
2018年04月06日 in PG 开发
Exclude约束是一个PostgreSQL扩展，它可以实现一些更高级，更巧妙的的数据库约束。 前言 数据完整性是极其重要的，但由应用保证的数据完整性并不总是那么靠谱：人会犯傻，程序会出错。如果能通过数据库约束来强制数据完整性那是再好不过了：后端程序员不用再担心竞态条件导致的微妙错误，数据分析师也可以对数据质量充满信心，不需要验证与清洗。 关系型数据库通常会提供PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK约束，然而并不是所有的业务约束都可以用这几种约束表达。一些 …
-
Distinct On 去除重复数据
2018年04月06日 in PG 开发
Distinct On是PostgreSQL提供的特有语法，可以高效解决一些典型查询问题，例如，快速找出分组内具有最大最小值的记录。 前言 找出分组内具有最大最小值的记录，这是一个非常常见的需求。用传统SQL当然有办法解决，但是都不够优雅，PostgreSQL的SQL扩展语法Distinct ON能一步到位解决这一类问题。 DISTINCT ON 语法 SELECT DISTINCT ON (expression [, expression ...]) select_list ... Here …
-
file_fdw妙用无穷——从数据库读取系统信息
2017年12月01日 in PG 开发
PostgreSQL是最先进的开源数据库，其中一个非常给力的特性就是FDW：外部数据包装器（Foreign Data Wrapper）。通过FDW，用户可以用统一的方式从Pg中访问各类外部数据源。file_fdw就是其中随数据库附赠的两个fdw之一。随着pg10的更新，file_fdw也添加了一颗赛艇的功能：从程序输出读取。 小霸王妙用无穷，我们能通过file_fdw，轻松查看操作系统信息，拉取网络数据，把各种各样的数据源轻松喂进数据库里统一查看管理。 安装与配置 file_fdw是Pg自带的组 …
-
GO与PG实现缓存同步
2017年08月03日 in PG 开发
Parallel与Hierarchy是架构设计的两大法宝，缓存是Hierarchy在IO领域的体现。单线程场景下缓存机制的实现可以简单到不可思议，但很难想象成熟的应用会只有一个实例。在使用缓存的同时引入并发，就不得不考虑一个问题：如何保证每个实例的缓存与底层数据副本的数据一致性（和实时性）。 PostgreSQL在版本9引入了流式复制，在版本10引入了逻辑复制，但这些都是针对PostgreSQL数据库而言的。如果希望PostgreSQL中某张表的部分数据与应用内存中的状态保持一致，我们还是需要自 …
-
用触发器审计数据变化
2017年06月09日 in PG 开发
有时候，我们希望记录一些重要的元数据变更，以便事后审计之用。 PostgreSQL的触发器就可以很方便地自动解决这一需求。 -- 创建一个审计专用schema，并废除所有非superuser的权限。 DROP SCHEMA IF EXISTS audit CASCADE; CREATE SCHEMA IF NOT EXISTS audit; REVOKE CREATE ON SCHEMA audit FROM PUBLIC; -- 审计表 CREATE TABLE …
-
SQL实现ItemCF推荐系统
2017年04月05日 in PG 开发
推荐系统大家都熟悉，猜你喜欢，淘宝个性化什么的，前年双十一搞了个大新闻，还拿了CEO特别贡献奖。 今天就来说说怎么用PostgreSQL 5分钟实现一个最简单ItemCF推荐系统，以推荐系统最喜闻乐见的movielens数据集为例。 原理 ItemCF的原理可以看项亮的《推荐系统实战》，不过还是稍微提一下吧，了解的直接跳过就好。 Item CF，全称Item Collaboration Filter，即基于物品的协同过滤，是目前业界应用最多的推荐算法。ItemCF不需要物品与用户的标签、属性，只 …