我最近把自己攒了六七年的笔记全部迁进了一个自托管的系统里。这个系统的代号叫 Madeira正好就是我喝过的一款马德拉酒的名字——那种酒的特点很特别装瓶之后还能继续陈化放得越久味道越醇厚。我希望自己的笔记系统也能这样今天记下的东西十年后翻出来依然有价值而不是换个软件就全废了。这个项目解决的是我一直以来的痛点笔记散落得到处都是手机备忘录、云文档、本地 Markdown 文件、社交软件的收藏夹……想找一条半年前记过的内容得翻好几个地方。而且数据全部放在别人的服务器上哪天服务停了或者我不想续费了东西就没了。所以我决定自己搞一套核心就一句话数据全部留在自己手里存储统一用 PostgreSQL任何笔记、标签、链接关系、日程事件都在同一个数据库里。这个项目适合喜欢折腾自托管、比较在意数据所有权、笔记量大而且需要靠谱全文检索的人。如果你只是偶尔记两句购物清单那确实没必要看下去但如果你跟我一样把笔记当第二大脑用这里面的选型思路、表结构设计、部署流程和踩坑记录应该能给你省不少事。1. 项目到底在做什么Madeira 的定位与缘起1.1 为什么是“Madeira”这个代号项目起名的时候我正喝完一瓶马德拉酒有点上头。查了一下才知道马德拉酒的历史很特别当年大航海时代这种酒要跟着船跨越大西洋在热带海面上晃荡几个月反而因祸得福获得了独特的氧化风味。后来人们发现哪怕装瓶了它还会继续缓慢熟成。我觉得这太像笔记系统该有的样子了。笔记不是写完就封存的东西它应该在长期积累中不断被重新翻阅、重新连接、产生新的内容。现在市面上很多笔记软件更新几次就改版或者干脆关停用户的数据说没就没了。这就像一瓶酒保质期只有三年跟马德拉酒完全两个路数。所以我决定自己做一个“耐放”的知识管理系统代号就叫 Madeira算是给自己提个醒系统的核心指标不是功能多炫而是数据能不能存得久、查得快、用得稳。1.2 解决的痛点笔记碎片化、数据不自主、检索靠文件夹做这个项目之前我的笔记状态非常混乱。手机里有一千多条备忘录里面有临时地址、购物清单、灵感碎片、读书摘抄电脑上有几百个 Markdown 文件按年份和主题分了二十几个文件夹还有一些内容直接存在云端文档里平时根本想不起来去翻。最崩溃的是检索。文件夹分类在笔记量少的时候还管用一旦超过阈值你根本不知道某条笔记应该放在哪个目录下更糟糕的是你连关键词都想不起来只记得“当时看过一篇关于某某的话”“好像跟什么有关”。这种模糊记忆靠翻文件夹是永远找不到的。另一个问题是数据自主权。我把内容存在别人的平台上平台可以限制我的导出格式、可以调整收费策略、甚至可以一夜之间关停。这让我越来越不安。所以 Madeira 的设计目标从一开始就很明确所有数据都存在我自己的 PostgreSQL 实例里界面只是数据的呈现层随时可以换即使某天我停止了开发这个前端只要数据库文件还在我的笔记就还在而且还能用 SQL 直接查。1.3 适合谁看、能拿走什么这篇文章写给三类人。第一类是自托管爱好者想知道一个知识管理系统从零搭起来要怎么做第二类是笔记重度用户正在寻找更可靠、可长期沉淀的方案第三类是后端学习者想看看 PostgreSQL 在真实项目里怎么设计表、怎么做全文检索、怎么解决中文分词问题。我不会从头到尾贴完整源码那样篇幅太长而且每个人需求不同。但我会把核心设计思路、建表语句、部署配置文件、检索配置和踩坑经验都拆开讲。你看完可以直接照抄部署一套也可以只借鉴其中的数据模型设计把它用到自己正在写的项目里。我会尽量把每个选择背后的“为什么”说清楚不是光给你一堆配置让你复制。2. 整体设计与思路拆解为什么核心存储必须选 PostgreSQL2.1 文件型笔记 vs 数据库型笔记的取舍最开始我也想过是不是接着用 Markdown 文件毕竟文件夹加文本文件的方案足够简单Git 还能做版本管理。但认真梳理需求之后我发现自己真正需要的不是文件而是“内容之间的关系”。纯文件方案有几个绕不过去的坎。第一元数据靠文件名和目录表达信息量有限当你需要按标签、时间、来源、状态筛选时非常别扭第二笔记之间的引用关系无法有效维护你只能手动在正文里写个链接但永远没办法回答“哪些笔记引用了这篇文章”第三全文检索要么靠外部工具比如 ripgrep凑合要么按文件名搜索中文内容更是难分词汇。数据库方案天然规避了这些问题。每一篇笔记是一行记录标签是一张独立表笔记之间的引用关系靠链接表维护全文检索靠 PostgreSQL 的全文索引来做。文件系统管不了的数据关系数据库可以文件系统需要你自己维护的索引数据库自动帮你维护。对我来说这个取舍毫无悬念。2.2 选 PostgreSQL 而不是 SQLite / MySQL / MongoDB 的理由其实我一开始用了 SQLite。轻量是真的轻量部署也简单跑在个人服务器上完全没毛病。但用着用着就发现几个不方便的地方并发写入能力弱我同时从手机和电脑往里写东西时偶尔会遇到锁问题全文检索功能相对基础中文分词基本靠外挂JSON 处理能力不如 PostgreSQL 原生 JSONB 来得顺手。MySQL 我也认真考虑过毕竟生态最庞大踩坑资料最多。但对比下来PostgreSQL 在某些关键点上更贴合这个项目。全文检索这块PostgreSQL 的 tsvector 体系非常成熟配合 GIN 索引性能很好扩展生态也强让我可以直接装一个中文分词插件而不是在应用层用第三方分词服务绕一圈。PostgreSQL 对 JSONB 的支持更是敢说同级别中最能打的后面我想给笔记加结构化的元数据直接用 JSONB 列就行。MongoDB 我也看过文档模型确实灵活但 Madeira 的数据不是纯文档型笔记之间有关联关系标签要去重统计日程事件要按日期范围查询这些场景用关系模型更自然。如果硬上 MongoDB我反而要在应用层用 Map 或者关联集合去模拟关系等于自己给自己造摩擦。所以 PostgreSQL 几乎是唯一符合需求的方案性能够、扩展够、关系模型合适、数据安全机制成熟。这不是引战是实际对比后的结果。2.3 三层架构解析界面 / 服务 / 存储Madeira 的整体结构很朴素就是经典的三层架构。最底下是 PostgreSQL 数据库保存所有笔记、标签、链接、事件和元数据。中间是一个 API 服务负责把 Fountain 标记文本解析成结构化数据处理读写请求对外暴露 JSON 接口。最上面是 Web 客户端负责编辑、展示和交互。这个分层看起来普通但有个好处很多人忽略了存储层和界面完全解耦。我可以在不考虑界面的情况下直接用 SQL 批量修改数据也可以另写一个小工具把笔记按自定义格式导出哪怕以后前端看腻了我可以把 API 换成 GraphQL 或其他风格完全不影响数据层。对一个长期项目来说这种自由度比什么都重要。再说说为什么要把数据源单一化。以前我同时用多个软件A 软件的笔记导不进 B 软件B 软件的标签规则跟 C 软件完全不一样。现在所有内容都进一套数据库不管是网页端写的、脚本批量导入的还是手机端通过 API 提交的最终都落入同一套表。数据模型统一了后续做统计、迁移、备份都省心。3. 核心细节解析与实操要点数据模型与关键实现3.1 四张核心表的设计思路在定数据模型之前我先列了一遍实际场景归纳到最后只剩四类核心实体笔记、标签、链接、事件。听起来少但细节都在字段设计里。先看笔记表CREATE TABLE notes ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), slug TEXT UNIQUE NOT NULL, title TEXT NOT NULL DEFAULT , body TEXT NOT NULL DEFAULT , status TEXT NOT NULL DEFAULT inbox, source TEXT NOT NULL DEFAULT , created_at TIMESTAMPTZ NOT NULL DEFAULT now(), updated_at TIMESTAMPTZ NOT NULL DEFAULT now(), metadata JSONB NOT NULL DEFAULT {}::JSONB ); CREATE INDEX idx_notes_created_at ON notes (created_at); CREATE INDEX idx_notes_status ON notes (status);这里有几个设计决策值得解释。id 我选了 UUID 而不是自增整数原因是我不希望把内部主键暴露在 API 里也不希望因为主键顺序猜到系统里的笔记数量。用 UUID 还有另一个好处将来如果要做离线客户端或者数据迁移不需要依赖数据库序列生成客户端可以先造好 ID 再同步。created_at 和 updated_at 都用 TIMESTAMPTZ这是我一直想强调的点。用带时区的时间戳类型数据库里存的其实是 UTC 瞬间值无论你人在哪个时区查询出来的都准确。千万别用本地时间字符串存否则换时区或者换服务器所有时间都是错的。status 字段我用来区分笔记状态收件箱、待整理、已归档、永久笔记。这是借鉴 GTD 和 Zettelkasten 的流程思路让笔记流经一个“从收集到加工”的管道。新内容默认进收件箱抽空整理后再标记为永久笔记。metadata 字段用 JSONB是为了保存那些不固定结构的扩展信息。比如某条笔记来源是某个公众号文章那我可以存一个 source_url某条笔记跟某个播客剧集相关我可以存 episode 信息。你不需要为每一种来源建一列JSONB 就是干这个用的。要注意的关键点是JSONB 列如果要走索引必须建 GIN 索引比如CREATE INDEX idx_notes_metadata ON notes USING GIN (metadata);标签表单独建而不是在笔记里用数组存标签是因为我要做标签统计、去重和聚合分析。数组字段做包含查询还行但要“按标签计数”“删除一个标签关联的所有笔记”就很别扭。分表之后逻辑清晰CREATE TABLE tags ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), name TEXT UNIQUE NOT NULL ); CREATE TABLE note_tags ( note_id UUID REFERENCES notes(id) ON DELETE CASCADE, tag_id UUID REFERENCES tags(id) ON DELETE CASCADE, PRIMARY KEY (note_id, tag_id) ); CREATE INDEX idx_note_tags_tag_id ON note_tags (tag_id);链接表是另一个特色。我鼓励笔记之间互相引用但我不想在正文里用 URL 串来串去所以单独建了一张链接表CREATE TABLE links ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), source_note_id UUID REFERENCES notes(id) ON DELETE CASCADE, target_note_id UUID REFERENCES notes(id) ON DELETE CASCADE, relation TEXT NOT NULL DEFAULT related, created_at TIMESTAMPTZ NOT NULL DEFAULT now(), UNIQUE (source_note_id, target_note_id, relation) );这张表可以回答很多问题哪些笔记引用了这篇、从这篇出发去了哪些地方、两种笔记之间的关联强度如何。做完这个设计之后我发现之前用文件夹组织的笔记本质上就是一棵树而我现在要的是一个图。至于事件表我是为了让日历功能和笔记打通比如某天开会记了个想法那就可以把这个想法和事件关联起来回头按日期翻历史的时候会发现很多惊喜。3.2 用 Fountain 标记语言而不是 Markdown 的原因正文存储格式我也折腾了很久。最初用 Markdown语法大家熟解析器也多。但用着用着发现Markdown 的规则其实比想象中复杂尤其是嵌套列表、转义字符、表格和代码混排稍不注意解析就出错。后来我换成了 Fountain 标记语言就是原本用来写剧本的那套纯文本格式。它最吸引我的点是语法极简、段落导向和 Markdown 的“符号驱动”思路完全不同。Fountain 的核心只有几个约定自然段就是段落空行分段以特定前缀标记元素。在这个基础上我做了简化只保留标题、引用、粗体、斜体、列表、分隔线几类元素足够笔记场景使用。这样做的好处是解析逻辑非常容易写几乎没有歧义正文在纯文本状态下就能读。我本来就用 Vim 写草稿Fountain 格式可以让我在不用看渲染效果的情况下也很舒服地写作。如果你自己做一个类似的系统我的建议是不要迷信某种标记语言要看你的解析能力和使用习惯。Markdown 全家桶看起来很美好但维护一个完整的 CommonMark 解析器并不轻松尤其在时间有限的前提下轻量语法核心反而更可控。3.3 全文检索与中文分词配置 zhparserPostgreSQL 内置的全文检索核心是 tsvector 和 tsquery这个组合处理英文没问题分词靠空格就能完成。但中文不行——中文句子没有天然空格如果按空格切分一整句话会被当成一个 token检索效果基本等于零。所以我需要给 PostgreSQL 装一个中文分词插件。我最终用的是 zhparser基于 SCWS 词库实现中文分词。这里有一些需要注意的地方zhparser 的扩展文件通常要编译源码安装如果你用 Docker 的话可以选已经装好插件的镜像或者自己写一个 Dockerfile 编译。编译过程其实不复杂但有几个依赖要装全比如 postgresql-server-dev-all 和 scws。装好后的配置核心是让全文检索默认用 zhparser 作为解析器同时创建 GIN 索引加速查询。进入 PostgreSQL 执行CREATE EXTENSION IF NOT EXISTS zhparser; CREATE TEXT SEARCH CONFIGURATION zh (PARSER zhparser); ALTER TEXT SEARCH CONFIGURATION zh ADD MAPPING FOR n,v,a,i,e,l WITH simple;这里的映射行是什么意思zhparser 会把每个词标注词性比如 n 表示名词、v 表示动词、a 表示形容词、i 表示成语、e 表示叹词、l 表示惯用语。这些词性都映射到 simple 词典意思是不做额外归一化保留原词。如果你用的是常用词词典建议保留这些基本词性否则“了”“的”“在”这些虚词会占领索引空间。接下来是对正文建索引CREATE INDEX idx_notes_body_zh ON notes USING GIN (to_tsvector(zh, body));查询示例SELECT id, title FROM notes WHERE to_tsvector(zh, body) to_tsquery(zh, PostgreSQL 笔记);这条语句会匹配同时包含“PostgreSQL”和“笔记”的正文。有了这个基础再配合拼音搜索或者模糊匹配就有拓展空间了。说实话初次配置完中文分词之后检索体验跟之前完全两个档次输入一句零散的话也能把相关笔记捞出来。3.4 数据所有权备份、导出、快照策略数据存进 PostgreSQL 只是第一步数据所有权最关键的是备份和导出能力。我现在的备份策略很简单每天凌晨用一个 cron 任务跑 pg_dump生成本地 SQL 文件然后保留最近七天。示例脚本#!/bin/bash BACKUP_DIR/srv/madeira/backups DB_NAMEmadeira DB_USERmadeira DATE$(date %Y%m%d_%H%M%S) pg_dump -U $DB_USER $DB_NAME | gzip $BACKUP_DIR/madeira_$DATE.sql.gz find $BACKUP_DIR -name madeira_*.sql.gz -mtime 7 -delete我还会定期把整个数据库做一个“离线镜像”导出全部笔记正文为 Markdown 文件按标题和时间归档。这样即使哪一天数据库损坏我依然可以拿纯文本文件恢复一部分内容。听起来麻烦但真出过一次事故之后你会发现多一层保险比什么都重要。要特别注意 pg_dump 的权限和备份文件权限不要用 root 跑也不要把备份留在默认位置不加保护。4. 实操过程与核心环节实现从零部署一套 Madeira4.1 部署环境的准备VPS、域名、HTTPS我不建议把 Madeira 只跑在 localhost虽然你确实可以这么做但既然是长期知识库还是应该放到一台自己可控的服务器上这样在任何地方都能访问。我用的是一台 1 核 2G 内存的小机器跑 Ubuntu 22.04对于这个量级的应用完全够用内存占用大概在 400M 到 600M 之间。域名不是必须的但如果你打算长期用建议配一个。使用域名的主要原因是申请 HTTPS 证书方便浏览器地址栏不会有红色警告。我用 Caddy 做反向代理它能够自动申请和续期证书配置也简单。比起 Nginx 需要手动管理证书文件Caddy 真的省心不少。HTTPS 这一层不能省不是因为有什么敏感内容而是因为你的知识库是个人资产中间被劫持的话等于隐私泄露。Caddy 的配置只要几行madeira.example.com { reverse_proxy localhost:8080 }它会自动申请证书并开启 HTTPS。如果你没有域名可以退一步用 IP 自签名证书但那样每次浏览器都会警告体验比较差所以我还是建议花几十块注册个域名一劳永逸。DNS 解析设置里把域名 A 记录指向 VPS 的公网 IP然后等几分钟就能生效。4.2 Docker Compose 快速部署一次把服务拉起来我选择 Docker Compose 来部署而不是直接在系统里装 PostgreSQL 和业务服务理由很实际升级方便、卸载干净、环境隔离。你不用把 PostgreSQL 的数据存到容器里就完事数据目录必须映射到宿主机持久化卷。下面是我项目里的 docker-compose.yml 精简版version: 3.8 services: db: image: postgres:15 container_name: madeira-db restart: unless-stopped environment: POSTGRES_USER: madeira POSTGRES_PASSWORD: change_this_password POSTGRES_DB: madeira volumes: - ./data/db:/var/lib/postgresql/data - ./init:/docker-entrypoint-initdb.d healthcheck: test: [CMD-SHELL, pg_isready -U madeira] interval: 10s timeout: 5s retries: 5 app: image: madeira-app:latest container_name: madeira-app restart: unless-stopped depends_on: db: condition: service_healthy environment: DATABASE_URL: postgres://madeira:change_this_passworddb:5432/madeira PORT: 8080 ports: - 127.0.0.1:8080:8080 web: image: caddy:2 container_name: madeira-web restart: unless-stopped depends_on: - app ports: - 80:80 - 443:443 volumes: - ./Caddyfile:/etc/caddy/Caddyfile:ro - caddy_data:/data - caddy_config:/config volumes: caddy_data: caddy_config:有几个点要特别说明。数据库容器里挂了 ./init 目录放在这个目录下的 .sql 脚本会在容器首次启动时自动执行。我就是用它来建表和创建扩展的这样不用手动进容器执行初始化 SQL。注意只有数据库目录第一次初始化时才会执行 init 脚本如果之后修改脚本不会自动重跑。密码别用明文写在这个文件里长期放着我日常用 .env 文件管理并且用只读权限控制。App 服务的端口我故意绑定到 127.0.0.1不直接暴露公网。外网流量先经过 Caddy 的容器再由 Caddy 反向代理转发到宿主机的 8080这样应用层不直接接触公网少一个攻击面。启动命令很简单docker compose up -d docker compose logs -f第一次启动后用docker compose ps查看服务状态。如果一切正常三四个容器都会显示 healthy 或 running。到这里你的服务已经可以在本地和局域网访问了。如果本地已经能打开再确认公网访问是否正常。4.3 初始化数据库与导入旧笔记数据库初始化我放在 ./init/01_schema.sql 里。除了建表和建索引我还会把全文检索配置也放进去比如创建 zhparser 扩展、配置中文检索语法。这样做的好处是整套环境在一台新服务器上可以一键还原不需要手工敲一长串 SQL。初始化文件的头部长这样-- 01_schema.sql CREATE EXTENSION IF NOT EXISTS pgcrypto; CREATE EXTENSION IF NOT EXISTS zhparser; CREATE TEXT SEARCH CONFIGURATION zh (PARSER zhparser); ALTER TEXT SEARCH CONFIGURATION zh ADD MAPPING FOR n,v,a,i,e,l WITH simple;然后是四张核心表的建表语句。重复执行时最好先DROP TABLE IF EXISTS ... CASCADE;不过生产环境千万别这么干这个脚本只有第一次初始化的时候用。我的建议是初始化之后就把 init 目录里的脚本归档禁止再对生产库执行。旧笔记迁移是另一个大工程。我之前几百个 Markdown 文件要转进新系统写了个一次性 Python 脚本大概思路是遍历目录读取每个 .md 文件解析出标题和正文写入数据库 notes 表并顺手打上原始文件路径作为 source。核心逻辑示例import os import psycopg2 import uuid conn psycopg2.connect(dbnamemadeira usermadeira) cur conn.cursor() for root, _, files in os.walk(./old_notes): for name in files: if not name.endswith(.md): continue path os.path.join(root, name) with open(path, r, encodingutf-8) as f: content f.read() title content.splitlines()[0].strip(# ).strip() body content note_id uuid.uuid4() cur.execute( INSERT INTO notes (id, slug, title, body, status, source) VALUES (%s, %s, %s, %s, archived, %s), (note_id, str(note_id), title, body, path), ) conn.commit() cur.close() conn.close()这段脚本只做说明用途你真的迁移时还需要处理文件编码问题、标签提取、图片链接改写等。一个重要的点迁移之前先备份一次然后小批量试跑别一口气全导。我当年就是直接全量跑结果有一部分文件编码是 GBK读出来乱码好在之后做了编码检测才慢慢修复。4.4 日常使用流程收件箱、标签、周回顾部署完成只是开始真正考验人的是怎么持续用起来。我的流程分为日常、整理、回顾三个阶段。日常阶段任何想法进来就随手记。这个阶段的笔记统一标记为 inbox 状态允许内容乱、允许标题缺、允许只有一句话。关键是先捕获不要纠结格式。整理阶段会定期一般是晚上或周末打开收件箱逐条处理有价值的转成正式笔记补充背景和链接没有价值的直接删除暂时用不上的标记为“待命”状态让它留在系统里但不占注意力。每次整理我都会刻意做标签归类因为标签是后续聚合的唯一入口。回顾阶段我每周做一次。用 SQL 查本周创建和更新的所有笔记SELECT title, status, updated_at FROM notes WHERE updated_at now() - interval 7 days ORDER BY updated_at DESC;我也会查一下热门的标签SELECT t.name, COUNT(nt.note_id) AS note_count FROM tags t JOIN note_tags nt ON nt.tag_id t.id GROUP BY t.name ORDER BY note_count DESC LIMIT 20;把这些结果拼成一份周报既是对自己一周思考的回顾也是系统的健康检查。如果某个标签的内容连续几周没有增长那我可能就不该再往这个方向投入了。5. 常见问题与排查技巧实录5.1 十分钟问题速查表在维护 Madeira 的过程中我整理了一份高频问题速查表遇到对应症状可以直接对照排查。症状可能原因处理方式页面打开很慢服务内存不足或磁盘 IO 瓶颈检查 docker stats 内存占用确认数据卷在本地 SSD中文搜索结果为空没有创建 zhparser 配置或索引失效确认 CREATE TEXT SEARCH CONFIGURATION 已执行检查 GIN 索引时间显示差 8 小时应用层用了本地时间而数据库用 UTC统一用 TIMESTAMPTZ应用只做展示时区转换某条笔记保存失败正文里含有非法字符或 JSON 字段格式错误检查 metadata JSONB 字段是否为合法 JSON数据库连接失败密码不一致或容器没起来docker compose ps 看状态用docker compose logs db看日志备份文件很大没有设置 WAL 归档策略或者历史数据太多定期清理旧备份优化索引重建膨胀表我每次遇到问题都会第一时间看日志而不是猜。容器日志在 docker compose 生态里特别方便一条docker compose logs -f就能把所有容器的输出串起来。排查这类自托管系统最大的忌讳是“凭感觉改配置”一定要找到具体的错误信息再动手。5.2 三个我踩过最深的坑第一个坑是时区。最开始建表时我用的是 timestamp without time zone应用在本地开发时一切正常部署到服务器之后所有时间看起来都是对不上号的。后来才意识到应用层传给数据库的时间没有带时区信息数据库存进去之后不会自动转换。改成 TIMESTAMPTZ 之后所有记录都按 UTC 存储应用展示时再转成本地时间这个问题就彻底消失了。这里的关键认知是时间戳应该是一种无歧义的瞬间记录不应该是“看起来像本地时间”的字符串。第二个坑是中文全文检索索引失效。我给 body 建了 GIN 索引但用to_tsvector(zh, body)查询时发现性能完全没有提升。排查后发现原因创建索引时用了 dbname 默认的 text search configuration而查询时显式指定了 zh导致索引表达式和查询表达式不一致索引根本没有命中。必须确保索引表达式的第一个参数和查询表达式完全一致而且最好在同一个配置下。改成CREATE INDEX ... ON notes USING GIN (to_tsvector(zh, body))之后查询就开始走索引了。第三个坑是 JSONB 字段随手塞东西导致查询变慢。一开始我把来源 URL、阅读进度、情绪标签、地理信息全塞进去结果部分查询要扫全表页面越来越慢。后来我做了约束只放查询时需要过滤的数据其他内容从正文中提取就行。JSONB 是很有用的工具但滥用它会让你付出性能代价关键是要高频查询的字段单独提列低频的才放在 JSONB 里作为附属信息。5.3 让系统更稳的几个小习惯用了快一年我总结出几个让这个系统更稳的习惯。第一个习惯是升级前必备份。不管是升级 PostgreSQL 镜像还是改应用我一定会先跑一次 pg_dump。很多时候你以为“只是改一个小配置”结果数据文件不兼容回滚都没法回。备份成本很低恢复成本极高。第二个习惯是定期重建索引。PostgreSQL 在大量更新删除后索引和表都会膨胀。我大概一个月跑一次REINDEX DATABASE madeira; VACUUM ANALYZE;这两个命令在业务低峰期执行。做了之后查询性能会回到最初的水平数据库文件体积也有明显下降。注意 REINDEX 在重负载期跑可能锁表我都是选在周末凌晨跑。第三个习惯是监控磁盘空间。知识库的数据量增长不算快但日志、备份、Docker 镜像叠加起来可能悄悄把磁盘占满。我写了一个简单脚本检查磁盘剩余量超过阈值就在 cron 里发一个邮件提醒。磁盘满的后果不只是新笔记写不进去还能让整个系统异常这个坑越早防越好。还有一个习惯跟技术关系不大但我觉得值得说定期删东西。知识管理系统的价值不在于存了多少而在于需要用的时候能不能快速找到。我每个月都会翻一遍收件箱和一些低活跃标签把明显无用的内容删除或归档。这样系统里的每一条数据都有意义检索结果也更干净。这算是 Madeira 这个项目教给我的一个道理真正耐放的知识不是什么都往里装而是留下来的都经过筛选。
我最近把自己攒了六七年的笔记全部迁进了一个自托管的系统里。这个系统的代号叫 Madeira正好就是我喝过的一款马德拉酒的名字——那种酒的特点很特别装瓶之后还能继续陈化放得越久味道越醇厚。我希望自己的笔记系统也能这样今天记下的东西十年后翻出来依然有价值而不是换个软件就全废了。这个项目解决的是我一直以来的痛点笔记散落得到处都是手机备忘录、云文档、本地 Markdown 文件、社交软件的收藏夹……想找一条半年前记过的内容得翻好几个地方。而且数据全部放在别人的服务器上哪天服务停了或者我不想续费了东西就没了。所以我决定自己搞一套核心就一句话数据全部留在自己手里存储统一用 PostgreSQL任何笔记、标签、链接关系、日程事件都在同一个数据库里。这个项目适合喜欢折腾自托管、比较在意数据所有权、笔记量大而且需要靠谱全文检索的人。如果你只是偶尔记两句购物清单那确实没必要看下去但如果你跟我一样把笔记当第二大脑用这里面的选型思路、表结构设计、部署流程和踩坑记录应该能给你省不少事。1. 项目到底在做什么Madeira 的定位与缘起1.1 为什么是“Madeira”这个代号项目起名的时候我正喝完一瓶马德拉酒有点上头。查了一下才知道马德拉酒的历史很特别当年大航海时代这种酒要跟着船跨越大西洋在热带海面上晃荡几个月反而因祸得福获得了独特的氧化风味。后来人们发现哪怕装瓶了它还会继续缓慢熟成。我觉得这太像笔记系统该有的样子了。笔记不是写完就封存的东西它应该在长期积累中不断被重新翻阅、重新连接、产生新的内容。现在市面上很多笔记软件更新几次就改版或者干脆关停用户的数据说没就没了。这就像一瓶酒保质期只有三年跟马德拉酒完全两个路数。所以我决定自己做一个“耐放”的知识管理系统代号就叫 Madeira算是给自己提个醒系统的核心指标不是功能多炫而是数据能不能存得久、查得快、用得稳。1.2 解决的痛点笔记碎片化、数据不自主、检索靠文件夹做这个项目之前我的笔记状态非常混乱。手机里有一千多条备忘录里面有临时地址、购物清单、灵感碎片、读书摘抄电脑上有几百个 Markdown 文件按年份和主题分了二十几个文件夹还有一些内容直接存在云端文档里平时根本想不起来去翻。最崩溃的是检索。文件夹分类在笔记量少的时候还管用一旦超过阈值你根本不知道某条笔记应该放在哪个目录下更糟糕的是你连关键词都想不起来只记得“当时看过一篇关于某某的话”“好像跟什么有关”。这种模糊记忆靠翻文件夹是永远找不到的。另一个问题是数据自主权。我把内容存在别人的平台上平台可以限制我的导出格式、可以调整收费策略、甚至可以一夜之间关停。这让我越来越不安。所以 Madeira 的设计目标从一开始就很明确所有数据都存在我自己的 PostgreSQL 实例里界面只是数据的呈现层随时可以换即使某天我停止了开发这个前端只要数据库文件还在我的笔记就还在而且还能用 SQL 直接查。1.3 适合谁看、能拿走什么这篇文章写给三类人。第一类是自托管爱好者想知道一个知识管理系统从零搭起来要怎么做第二类是笔记重度用户正在寻找更可靠、可长期沉淀的方案第三类是后端学习者想看看 PostgreSQL 在真实项目里怎么设计表、怎么做全文检索、怎么解决中文分词问题。我不会从头到尾贴完整源码那样篇幅太长而且每个人需求不同。但我会把核心设计思路、建表语句、部署配置文件、检索配置和踩坑经验都拆开讲。你看完可以直接照抄部署一套也可以只借鉴其中的数据模型设计把它用到自己正在写的项目里。我会尽量把每个选择背后的“为什么”说清楚不是光给你一堆配置让你复制。2. 整体设计与思路拆解为什么核心存储必须选 PostgreSQL2.1 文件型笔记 vs 数据库型笔记的取舍最开始我也想过是不是接着用 Markdown 文件毕竟文件夹加文本文件的方案足够简单Git 还能做版本管理。但认真梳理需求之后我发现自己真正需要的不是文件而是“内容之间的关系”。纯文件方案有几个绕不过去的坎。第一元数据靠文件名和目录表达信息量有限当你需要按标签、时间、来源、状态筛选时非常别扭第二笔记之间的引用关系无法有效维护你只能手动在正文里写个链接但永远没办法回答“哪些笔记引用了这篇文章”第三全文检索要么靠外部工具比如 ripgrep凑合要么按文件名搜索中文内容更是难分词汇。数据库方案天然规避了这些问题。每一篇笔记是一行记录标签是一张独立表笔记之间的引用关系靠链接表维护全文检索靠 PostgreSQL 的全文索引来做。文件系统管不了的数据关系数据库可以文件系统需要你自己维护的索引数据库自动帮你维护。对我来说这个取舍毫无悬念。2.2 选 PostgreSQL 而不是 SQLite / MySQL / MongoDB 的理由其实我一开始用了 SQLite。轻量是真的轻量部署也简单跑在个人服务器上完全没毛病。但用着用着就发现几个不方便的地方并发写入能力弱我同时从手机和电脑往里写东西时偶尔会遇到锁问题全文检索功能相对基础中文分词基本靠外挂JSON 处理能力不如 PostgreSQL 原生 JSONB 来得顺手。MySQL 我也认真考虑过毕竟生态最庞大踩坑资料最多。但对比下来PostgreSQL 在某些关键点上更贴合这个项目。全文检索这块PostgreSQL 的 tsvector 体系非常成熟配合 GIN 索引性能很好扩展生态也强让我可以直接装一个中文分词插件而不是在应用层用第三方分词服务绕一圈。PostgreSQL 对 JSONB 的支持更是敢说同级别中最能打的后面我想给笔记加结构化的元数据直接用 JSONB 列就行。MongoDB 我也看过文档模型确实灵活但 Madeira 的数据不是纯文档型笔记之间有关联关系标签要去重统计日程事件要按日期范围查询这些场景用关系模型更自然。如果硬上 MongoDB我反而要在应用层用 Map 或者关联集合去模拟关系等于自己给自己造摩擦。所以 PostgreSQL 几乎是唯一符合需求的方案性能够、扩展够、关系模型合适、数据安全机制成熟。这不是引战是实际对比后的结果。2.3 三层架构解析界面 / 服务 / 存储Madeira 的整体结构很朴素就是经典的三层架构。最底下是 PostgreSQL 数据库保存所有笔记、标签、链接、事件和元数据。中间是一个 API 服务负责把 Fountain 标记文本解析成结构化数据处理读写请求对外暴露 JSON 接口。最上面是 Web 客户端负责编辑、展示和交互。这个分层看起来普通但有个好处很多人忽略了存储层和界面完全解耦。我可以在不考虑界面的情况下直接用 SQL 批量修改数据也可以另写一个小工具把笔记按自定义格式导出哪怕以后前端看腻了我可以把 API 换成 GraphQL 或其他风格完全不影响数据层。对一个长期项目来说这种自由度比什么都重要。再说说为什么要把数据源单一化。以前我同时用多个软件A 软件的笔记导不进 B 软件B 软件的标签规则跟 C 软件完全不一样。现在所有内容都进一套数据库不管是网页端写的、脚本批量导入的还是手机端通过 API 提交的最终都落入同一套表。数据模型统一了后续做统计、迁移、备份都省心。3. 核心细节解析与实操要点数据模型与关键实现3.1 四张核心表的设计思路在定数据模型之前我先列了一遍实际场景归纳到最后只剩四类核心实体笔记、标签、链接、事件。听起来少但细节都在字段设计里。先看笔记表CREATE TABLE notes ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), slug TEXT UNIQUE NOT NULL, title TEXT NOT NULL DEFAULT , body TEXT NOT NULL DEFAULT , status TEXT NOT NULL DEFAULT inbox, source TEXT NOT NULL DEFAULT , created_at TIMESTAMPTZ NOT NULL DEFAULT now(), updated_at TIMESTAMPTZ NOT NULL DEFAULT now(), metadata JSONB NOT NULL DEFAULT {}::JSONB ); CREATE INDEX idx_notes_created_at ON notes (created_at); CREATE INDEX idx_notes_status ON notes (status);这里有几个设计决策值得解释。id 我选了 UUID 而不是自增整数原因是我不希望把内部主键暴露在 API 里也不希望因为主键顺序猜到系统里的笔记数量。用 UUID 还有另一个好处将来如果要做离线客户端或者数据迁移不需要依赖数据库序列生成客户端可以先造好 ID 再同步。created_at 和 updated_at 都用 TIMESTAMPTZ这是我一直想强调的点。用带时区的时间戳类型数据库里存的其实是 UTC 瞬间值无论你人在哪个时区查询出来的都准确。千万别用本地时间字符串存否则换时区或者换服务器所有时间都是错的。status 字段我用来区分笔记状态收件箱、待整理、已归档、永久笔记。这是借鉴 GTD 和 Zettelkasten 的流程思路让笔记流经一个“从收集到加工”的管道。新内容默认进收件箱抽空整理后再标记为永久笔记。metadata 字段用 JSONB是为了保存那些不固定结构的扩展信息。比如某条笔记来源是某个公众号文章那我可以存一个 source_url某条笔记跟某个播客剧集相关我可以存 episode 信息。你不需要为每一种来源建一列JSONB 就是干这个用的。要注意的关键点是JSONB 列如果要走索引必须建 GIN 索引比如CREATE INDEX idx_notes_metadata ON notes USING GIN (metadata);标签表单独建而不是在笔记里用数组存标签是因为我要做标签统计、去重和聚合分析。数组字段做包含查询还行但要“按标签计数”“删除一个标签关联的所有笔记”就很别扭。分表之后逻辑清晰CREATE TABLE tags ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), name TEXT UNIQUE NOT NULL ); CREATE TABLE note_tags ( note_id UUID REFERENCES notes(id) ON DELETE CASCADE, tag_id UUID REFERENCES tags(id) ON DELETE CASCADE, PRIMARY KEY (note_id, tag_id) ); CREATE INDEX idx_note_tags_tag_id ON note_tags (tag_id);链接表是另一个特色。我鼓励笔记之间互相引用但我不想在正文里用 URL 串来串去所以单独建了一张链接表CREATE TABLE links ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), source_note_id UUID REFERENCES notes(id) ON DELETE CASCADE, target_note_id UUID REFERENCES notes(id) ON DELETE CASCADE, relation TEXT NOT NULL DEFAULT related, created_at TIMESTAMPTZ NOT NULL DEFAULT now(), UNIQUE (source_note_id, target_note_id, relation) );这张表可以回答很多问题哪些笔记引用了这篇、从这篇出发去了哪些地方、两种笔记之间的关联强度如何。做完这个设计之后我发现之前用文件夹组织的笔记本质上就是一棵树而我现在要的是一个图。至于事件表我是为了让日历功能和笔记打通比如某天开会记了个想法那就可以把这个想法和事件关联起来回头按日期翻历史的时候会发现很多惊喜。3.2 用 Fountain 标记语言而不是 Markdown 的原因正文存储格式我也折腾了很久。最初用 Markdown语法大家熟解析器也多。但用着用着发现Markdown 的规则其实比想象中复杂尤其是嵌套列表、转义字符、表格和代码混排稍不注意解析就出错。后来我换成了 Fountain 标记语言就是原本用来写剧本的那套纯文本格式。它最吸引我的点是语法极简、段落导向和 Markdown 的“符号驱动”思路完全不同。Fountain 的核心只有几个约定自然段就是段落空行分段以特定前缀标记元素。在这个基础上我做了简化只保留标题、引用、粗体、斜体、列表、分隔线几类元素足够笔记场景使用。这样做的好处是解析逻辑非常容易写几乎没有歧义正文在纯文本状态下就能读。我本来就用 Vim 写草稿Fountain 格式可以让我在不用看渲染效果的情况下也很舒服地写作。如果你自己做一个类似的系统我的建议是不要迷信某种标记语言要看你的解析能力和使用习惯。Markdown 全家桶看起来很美好但维护一个完整的 CommonMark 解析器并不轻松尤其在时间有限的前提下轻量语法核心反而更可控。3.3 全文检索与中文分词配置 zhparserPostgreSQL 内置的全文检索核心是 tsvector 和 tsquery这个组合处理英文没问题分词靠空格就能完成。但中文不行——中文句子没有天然空格如果按空格切分一整句话会被当成一个 token检索效果基本等于零。所以我需要给 PostgreSQL 装一个中文分词插件。我最终用的是 zhparser基于 SCWS 词库实现中文分词。这里有一些需要注意的地方zhparser 的扩展文件通常要编译源码安装如果你用 Docker 的话可以选已经装好插件的镜像或者自己写一个 Dockerfile 编译。编译过程其实不复杂但有几个依赖要装全比如 postgresql-server-dev-all 和 scws。装好后的配置核心是让全文检索默认用 zhparser 作为解析器同时创建 GIN 索引加速查询。进入 PostgreSQL 执行CREATE EXTENSION IF NOT EXISTS zhparser; CREATE TEXT SEARCH CONFIGURATION zh (PARSER zhparser); ALTER TEXT SEARCH CONFIGURATION zh ADD MAPPING FOR n,v,a,i,e,l WITH simple;这里的映射行是什么意思zhparser 会把每个词标注词性比如 n 表示名词、v 表示动词、a 表示形容词、i 表示成语、e 表示叹词、l 表示惯用语。这些词性都映射到 simple 词典意思是不做额外归一化保留原词。如果你用的是常用词词典建议保留这些基本词性否则“了”“的”“在”这些虚词会占领索引空间。接下来是对正文建索引CREATE INDEX idx_notes_body_zh ON notes USING GIN (to_tsvector(zh, body));查询示例SELECT id, title FROM notes WHERE to_tsvector(zh, body) to_tsquery(zh, PostgreSQL 笔记);这条语句会匹配同时包含“PostgreSQL”和“笔记”的正文。有了这个基础再配合拼音搜索或者模糊匹配就有拓展空间了。说实话初次配置完中文分词之后检索体验跟之前完全两个档次输入一句零散的话也能把相关笔记捞出来。3.4 数据所有权备份、导出、快照策略数据存进 PostgreSQL 只是第一步数据所有权最关键的是备份和导出能力。我现在的备份策略很简单每天凌晨用一个 cron 任务跑 pg_dump生成本地 SQL 文件然后保留最近七天。示例脚本#!/bin/bash BACKUP_DIR/srv/madeira/backups DB_NAMEmadeira DB_USERmadeira DATE$(date %Y%m%d_%H%M%S) pg_dump -U $DB_USER $DB_NAME | gzip $BACKUP_DIR/madeira_$DATE.sql.gz find $BACKUP_DIR -name madeira_*.sql.gz -mtime 7 -delete我还会定期把整个数据库做一个“离线镜像”导出全部笔记正文为 Markdown 文件按标题和时间归档。这样即使哪一天数据库损坏我依然可以拿纯文本文件恢复一部分内容。听起来麻烦但真出过一次事故之后你会发现多一层保险比什么都重要。要特别注意 pg_dump 的权限和备份文件权限不要用 root 跑也不要把备份留在默认位置不加保护。4. 实操过程与核心环节实现从零部署一套 Madeira4.1 部署环境的准备VPS、域名、HTTPS我不建议把 Madeira 只跑在 localhost虽然你确实可以这么做但既然是长期知识库还是应该放到一台自己可控的服务器上这样在任何地方都能访问。我用的是一台 1 核 2G 内存的小机器跑 Ubuntu 22.04对于这个量级的应用完全够用内存占用大概在 400M 到 600M 之间。域名不是必须的但如果你打算长期用建议配一个。使用域名的主要原因是申请 HTTPS 证书方便浏览器地址栏不会有红色警告。我用 Caddy 做反向代理它能够自动申请和续期证书配置也简单。比起 Nginx 需要手动管理证书文件Caddy 真的省心不少。HTTPS 这一层不能省不是因为有什么敏感内容而是因为你的知识库是个人资产中间被劫持的话等于隐私泄露。Caddy 的配置只要几行madeira.example.com { reverse_proxy localhost:8080 }它会自动申请证书并开启 HTTPS。如果你没有域名可以退一步用 IP 自签名证书但那样每次浏览器都会警告体验比较差所以我还是建议花几十块注册个域名一劳永逸。DNS 解析设置里把域名 A 记录指向 VPS 的公网 IP然后等几分钟就能生效。4.2 Docker Compose 快速部署一次把服务拉起来我选择 Docker Compose 来部署而不是直接在系统里装 PostgreSQL 和业务服务理由很实际升级方便、卸载干净、环境隔离。你不用把 PostgreSQL 的数据存到容器里就完事数据目录必须映射到宿主机持久化卷。下面是我项目里的 docker-compose.yml 精简版version: 3.8 services: db: image: postgres:15 container_name: madeira-db restart: unless-stopped environment: POSTGRES_USER: madeira POSTGRES_PASSWORD: change_this_password POSTGRES_DB: madeira volumes: - ./data/db:/var/lib/postgresql/data - ./init:/docker-entrypoint-initdb.d healthcheck: test: [CMD-SHELL, pg_isready -U madeira] interval: 10s timeout: 5s retries: 5 app: image: madeira-app:latest container_name: madeira-app restart: unless-stopped depends_on: db: condition: service_healthy environment: DATABASE_URL: postgres://madeira:change_this_passworddb:5432/madeira PORT: 8080 ports: - 127.0.0.1:8080:8080 web: image: caddy:2 container_name: madeira-web restart: unless-stopped depends_on: - app ports: - 80:80 - 443:443 volumes: - ./Caddyfile:/etc/caddy/Caddyfile:ro - caddy_data:/data - caddy_config:/config volumes: caddy_data: caddy_config:有几个点要特别说明。数据库容器里挂了 ./init 目录放在这个目录下的 .sql 脚本会在容器首次启动时自动执行。我就是用它来建表和创建扩展的这样不用手动进容器执行初始化 SQL。注意只有数据库目录第一次初始化时才会执行 init 脚本如果之后修改脚本不会自动重跑。密码别用明文写在这个文件里长期放着我日常用 .env 文件管理并且用只读权限控制。App 服务的端口我故意绑定到 127.0.0.1不直接暴露公网。外网流量先经过 Caddy 的容器再由 Caddy 反向代理转发到宿主机的 8080这样应用层不直接接触公网少一个攻击面。启动命令很简单docker compose up -d docker compose logs -f第一次启动后用docker compose ps查看服务状态。如果一切正常三四个容器都会显示 healthy 或 running。到这里你的服务已经可以在本地和局域网访问了。如果本地已经能打开再确认公网访问是否正常。4.3 初始化数据库与导入旧笔记数据库初始化我放在 ./init/01_schema.sql 里。除了建表和建索引我还会把全文检索配置也放进去比如创建 zhparser 扩展、配置中文检索语法。这样做的好处是整套环境在一台新服务器上可以一键还原不需要手工敲一长串 SQL。初始化文件的头部长这样-- 01_schema.sql CREATE EXTENSION IF NOT EXISTS pgcrypto; CREATE EXTENSION IF NOT EXISTS zhparser; CREATE TEXT SEARCH CONFIGURATION zh (PARSER zhparser); ALTER TEXT SEARCH CONFIGURATION zh ADD MAPPING FOR n,v,a,i,e,l WITH simple;然后是四张核心表的建表语句。重复执行时最好先DROP TABLE IF EXISTS ... CASCADE;不过生产环境千万别这么干这个脚本只有第一次初始化的时候用。我的建议是初始化之后就把 init 目录里的脚本归档禁止再对生产库执行。旧笔记迁移是另一个大工程。我之前几百个 Markdown 文件要转进新系统写了个一次性 Python 脚本大概思路是遍历目录读取每个 .md 文件解析出标题和正文写入数据库 notes 表并顺手打上原始文件路径作为 source。核心逻辑示例import os import psycopg2 import uuid conn psycopg2.connect(dbnamemadeira usermadeira) cur conn.cursor() for root, _, files in os.walk(./old_notes): for name in files: if not name.endswith(.md): continue path os.path.join(root, name) with open(path, r, encodingutf-8) as f: content f.read() title content.splitlines()[0].strip(# ).strip() body content note_id uuid.uuid4() cur.execute( INSERT INTO notes (id, slug, title, body, status, source) VALUES (%s, %s, %s, %s, archived, %s), (note_id, str(note_id), title, body, path), ) conn.commit() cur.close() conn.close()这段脚本只做说明用途你真的迁移时还需要处理文件编码问题、标签提取、图片链接改写等。一个重要的点迁移之前先备份一次然后小批量试跑别一口气全导。我当年就是直接全量跑结果有一部分文件编码是 GBK读出来乱码好在之后做了编码检测才慢慢修复。4.4 日常使用流程收件箱、标签、周回顾部署完成只是开始真正考验人的是怎么持续用起来。我的流程分为日常、整理、回顾三个阶段。日常阶段任何想法进来就随手记。这个阶段的笔记统一标记为 inbox 状态允许内容乱、允许标题缺、允许只有一句话。关键是先捕获不要纠结格式。整理阶段会定期一般是晚上或周末打开收件箱逐条处理有价值的转成正式笔记补充背景和链接没有价值的直接删除暂时用不上的标记为“待命”状态让它留在系统里但不占注意力。每次整理我都会刻意做标签归类因为标签是后续聚合的唯一入口。回顾阶段我每周做一次。用 SQL 查本周创建和更新的所有笔记SELECT title, status, updated_at FROM notes WHERE updated_at now() - interval 7 days ORDER BY updated_at DESC;我也会查一下热门的标签SELECT t.name, COUNT(nt.note_id) AS note_count FROM tags t JOIN note_tags nt ON nt.tag_id t.id GROUP BY t.name ORDER BY note_count DESC LIMIT 20;把这些结果拼成一份周报既是对自己一周思考的回顾也是系统的健康检查。如果某个标签的内容连续几周没有增长那我可能就不该再往这个方向投入了。5. 常见问题与排查技巧实录5.1 十分钟问题速查表在维护 Madeira 的过程中我整理了一份高频问题速查表遇到对应症状可以直接对照排查。症状可能原因处理方式页面打开很慢服务内存不足或磁盘 IO 瓶颈检查 docker stats 内存占用确认数据卷在本地 SSD中文搜索结果为空没有创建 zhparser 配置或索引失效确认 CREATE TEXT SEARCH CONFIGURATION 已执行检查 GIN 索引时间显示差 8 小时应用层用了本地时间而数据库用 UTC统一用 TIMESTAMPTZ应用只做展示时区转换某条笔记保存失败正文里含有非法字符或 JSON 字段格式错误检查 metadata JSONB 字段是否为合法 JSON数据库连接失败密码不一致或容器没起来docker compose ps 看状态用docker compose logs db看日志备份文件很大没有设置 WAL 归档策略或者历史数据太多定期清理旧备份优化索引重建膨胀表我每次遇到问题都会第一时间看日志而不是猜。容器日志在 docker compose 生态里特别方便一条docker compose logs -f就能把所有容器的输出串起来。排查这类自托管系统最大的忌讳是“凭感觉改配置”一定要找到具体的错误信息再动手。5.2 三个我踩过最深的坑第一个坑是时区。最开始建表时我用的是 timestamp without time zone应用在本地开发时一切正常部署到服务器之后所有时间看起来都是对不上号的。后来才意识到应用层传给数据库的时间没有带时区信息数据库存进去之后不会自动转换。改成 TIMESTAMPTZ 之后所有记录都按 UTC 存储应用展示时再转成本地时间这个问题就彻底消失了。这里的关键认知是时间戳应该是一种无歧义的瞬间记录不应该是“看起来像本地时间”的字符串。第二个坑是中文全文检索索引失效。我给 body 建了 GIN 索引但用to_tsvector(zh, body)查询时发现性能完全没有提升。排查后发现原因创建索引时用了 dbname 默认的 text search configuration而查询时显式指定了 zh导致索引表达式和查询表达式不一致索引根本没有命中。必须确保索引表达式的第一个参数和查询表达式完全一致而且最好在同一个配置下。改成CREATE INDEX ... ON notes USING GIN (to_tsvector(zh, body))之后查询就开始走索引了。第三个坑是 JSONB 字段随手塞东西导致查询变慢。一开始我把来源 URL、阅读进度、情绪标签、地理信息全塞进去结果部分查询要扫全表页面越来越慢。后来我做了约束只放查询时需要过滤的数据其他内容从正文中提取就行。JSONB 是很有用的工具但滥用它会让你付出性能代价关键是要高频查询的字段单独提列低频的才放在 JSONB 里作为附属信息。5.3 让系统更稳的几个小习惯用了快一年我总结出几个让这个系统更稳的习惯。第一个习惯是升级前必备份。不管是升级 PostgreSQL 镜像还是改应用我一定会先跑一次 pg_dump。很多时候你以为“只是改一个小配置”结果数据文件不兼容回滚都没法回。备份成本很低恢复成本极高。第二个习惯是定期重建索引。PostgreSQL 在大量更新删除后索引和表都会膨胀。我大概一个月跑一次REINDEX DATABASE madeira; VACUUM ANALYZE;这两个命令在业务低峰期执行。做了之后查询性能会回到最初的水平数据库文件体积也有明显下降。注意 REINDEX 在重负载期跑可能锁表我都是选在周末凌晨跑。第三个习惯是监控磁盘空间。知识库的数据量增长不算快但日志、备份、Docker 镜像叠加起来可能悄悄把磁盘占满。我写了一个简单脚本检查磁盘剩余量超过阈值就在 cron 里发一个邮件提醒。磁盘满的后果不只是新笔记写不进去还能让整个系统异常这个坑越早防越好。还有一个习惯跟技术关系不大但我觉得值得说定期删东西。知识管理系统的价值不在于存了多少而在于需要用的时候能不能快速找到。我每个月都会翻一遍收件箱和一些低活跃标签把明显无用的内容删除或归档。这样系统里的每一条数据都有意义检索结果也更干净。这算是 Madeira 这个项目教给我的一个道理真正耐放的知识不是什么都往里装而是留下来的都经过筛选。
