Hasty Briefsbeta

全部标签

postgresql

共 16 篇

双语

PostgreSQL Autovacuum Internals and Benchmark
2 months ago
- 自动清理对于PostgreSQL性能和数据库生存至关重要；必须正确调整以避免性能下降。
- MVCC维护多个行版本；清理回收对任何事务不再可见的死元组。
- 自动清理启动器按数据库启动工作进程；工作进程根据死元组和插入的阈值独立清理符合条件的表。
- 清理使用可见性映射跳过没有死元组的页面，从而高效运行；缓冲区环限制了对缓存的影响。
- 索引清理需要每个清理周期对每个索引进行全面扫描；有限的工作内存可能导致多次扫描，增加成本。
- 自动清理的成本由脏页（包含死元组的页面）数量驱动，而不是死元组本身的计数。
- 基准测试表明，索引线性放大清理成本；死元组的紧凑分布比分散分布导致更低的成本。
- 并发操作通过页面级锁定处理，清理不会阻塞正常的DML，除了短暂的清理锁。
It was surprisingly hard to break CloudNativePG replication
2 months ago
- 作者尝试创建一个落后于主库的PostgreSQL副本，但发现它一直在追赶，因为当流复制中断时，副本会从S3归档中拉取WAL日志。
- 即便没有流复制，副本的重放LSN也会推进，这得益于CloudNativePG的restore_command——它会从对象存储中获取WAL。
- PostgreSQL的备用恢复状态机在流复制失败时会回退到归档模式，形成一个循环，使副本几乎保持同步。
- CloudNativePG同时配置了primary_conninfo和restore_command，从而启用了这一回退机制；其wal-restore二进制程序会从S3预取段文件。
- 要真正中断复制，必须同时阻止流复制和归档访问；作者使用Chaos Mesh切断了副本与S3端点的连接。
- 故意破坏系统是理解其行为的一种有价值的方式，也能确保监控工具产生有意义的告警。
PostgreSQL's MVCC is bad. So is everyone else's
2 months ago
- PostgreSQL的MVCC将旧行版本存储在表本身中，导致膨胀、写放大以及依赖VACUUM进行清理。
- 替代的MVCC设计（Oracle/InnoDB、SQL Server、MongoDB、基于LSM的数据库）将成本转移到撤销日志、tempdb、缓存或压缩中，并具有各自的故障模式，如快照过旧错误、回滚开销或实例范围的停顿。
- 所有MVCC系统都面临相同的基本矛盾：它们必须选择如何处理版本存储、索引、清理和长时间运行的事务，没有一种设计能消除所有成本——它们只是将成本在写入者、读取者、后台进程或存储组件之间转移。
- PostgreSQL基于堆的MVCC提供零成本的回滚且从不取消读取者，但使得垃圾可见并需操作员驱动，这与基于撤销的引擎形成对比，后者对写入和历史读取施加成本。
- 替换PostgreSQL MVCC（zheap、OrioleDB）的努力正在进行中，但面临重大的工程挑战，因为堆的简单性是系统的基础。
PostgreSQL as a Temporal Database
2 months ago
- PostgreSQL 18 引入了使用 WITHOUT OVERLAPS 的时间主键和唯一键，以防止时间段重叠。
- PostgreSQL 19 增加了原生时间 DML，支持 UPDATE/DELETE ... FOR PORTION OF 用于精确的历史修改。
- 使用 PERIOD 子句的时间外键确保随时间变化的参照完整性，但尚不支持引用操作。
How to Back Up PostgreSQL in Docker (Local, S3, and Plakar)
2 months ago
- 在Docker中进行PostgreSQL备份时，可以使用pg_dumpall配合gzip，将备份存储在本地或S3上，并通过cron调度和清理策略来管理。
- 传统的每日完整SQL转储效率低下，因为即使只是很小的变更，也需要每天传输整个数据库，导致高昂的存储和传输成本。
- Plakar通过跨快照分块存储实现去重，大幅减少传输和存储量（例如，对于一个不断增长的10 GB数据库，每年约需64 GB存储，而传统方式需12.5 TB）。
- 设置Plakar需要创建仓库（本地或S3），配置别名和通行短语，并编写备份与恢复脚本。
- Plakar支持独立式的增量快照；清理策略（例如，7天内每天保留1个快照）可以自动删除旧快照。
- 对于S3，需安装Plakar S3插件，使用AWS凭证添加存储别名，然后使用基于S3仓库相同的备份/恢复脚本。
- Plakar简化了不同存储后端（本地、S3等）之间的切换，命令几乎相同，并且内置的维护功能可处理数据清理。
Why It's So Hard to Add a Column in the Middle of a PostgreSQL Table
2 months ago
- PostgreSQL的ADD COLUMN仅将新列添加到末尾，没有'AFTER'子句，这与MySQL不同。这是由于系统目录中的attnum字段所致，该字段为每个列创建时分配一个唯一标识符且永不更改。attnum承担三个角色：作为列的身份标识、磁盘元组中的物理位置以及查询中的逻辑顺序，这使得重新排序变得复杂。删除的列会留下墓碑记录，其attnum槽位不会回收，导致未使用空间直到表被重写。列顺序因数据类型对齐填充影响存储效率；通过'列俄罗斯方块'优化将较宽的列放在首位可节省约20%的磁盘空间。自2014年起，PostgreSQL开发者已提议将attnum拆分为身份、物理和逻辑位置的独立字段，但由于广泛的代码依赖，实施停滞不前。目前，通过ACCESS EXCLUSIVE锁重建表是重新排序列以获取存储增益的唯一方法。MySQL能够轻松处理列重排序，因为它将逻辑和物理列顺序分离，允许在近期版本中无需表重写即可即时添加列。
Why Huge Pages matter for Postgres
2 months ago
- 大页是较大的内存单元（2MB 或 1GB），相比标准的 4KB 页面，它允许一个页表项覆盖更多的内存，从而减少了 CPU 的 TLB 必须缓存的转换次数。
- Postgres 对页面大小很敏感，因为它的 shared_buffers 缓存是由每个后端的页表映射的；使用 4KB 页面时，大型缓存可能会消耗过多的 RAM 用于页表，并导致 TLB 未命中，从而降低读取速度。
- 使用大页（例如 2MB）可以显著减少每个连接的页表内存使用量——在测试中，从每个连接约 31MB 降至约 0.5MB——并提高 TLB 覆盖率，将热点工作集保持在缓存中，从而提升吞吐量。
- ClickHouse Managed Postgres 通过预先保留内存（RAM 的 25%），配置 huge_pages = 'on' 以防止静默回退，并通过测量而非假设来调整 shared_buffers 的大小以适合保留的内存池，从而确保使用大页。
- 在 EC2 实例上的测试显示，使用 200 个连接时，4KB 页面导致 6.12GB 的页表，而使用 2MB 大页时仅为 111MB；由于减少了转换开销，使用大页时吞吐量提高了 12%。
PgDog (YC P25) Is Hiring a Founding Software Engineer
2 months ago
- PgDog是一家专注于扩展PostgreSQL的初创企业，已获多家公司采用且目前正在招聘。
- 该岗位涉及运用Rust语言、PostgreSQL数据库、基础设施及分布式系统技术，直接为客户构建并交付产品功能。
- 理想的候选人需具备细节导向意识、测试驱动开发习惯、能熟练处理生产环境代码，并能在人工智能辅助与独立思考间取得平衡。
- 职责范围涵盖漏洞修复、功能开发、路线图设计、客户对接以及人才招聘。
- 工作内容包括从零开始使用Rust构建分布式数据库，时间将分配于漏洞修复、新功能开发和基础设施优化三方面。
The four horsemen behind Postgres outages
2 months ago
- PostgreSQL 常见故障是由于真空和事务ID回绕造成的，其中删除的行累积，32位ID会导致数据库关闭，如果不回收的话。
- 连接限制和基于进程的并行性导致扩展问题，需要重启或使用像PgBouncer这样的工具，而基于线程的模型在pgrust中提供了一个更安全的替代方案。
- 糟糕的查询计划可能会严重减慢查询速度，并且对规划器的控制有限，不像那些允许提示的数据库；pgrust旨在构建一个自适应查询规划器。
- PostgreSQL中的JSON缺乏统计和压缩功能，导致查询计划效率低下和存储问题；pgrust计划添加真实的统计和压缩功能来解决这个问题。
Preempt_none Is Dead; Your Postgres Probably Doesn't Care
2 months ago
- AWS基准测试显示，由于移除PREEMPT_NONE抢占模式导致的回归，PostgreSQL在Linux 7.0上的吞吐量降至6.x版本的0.51倍。
- 性能问题源于使用4KB页面和大共享内存（100+ GB）时，自旋锁（buffer_strategy_lock）内的次要页错误，而PREEMPT_LAZY加剧了这一问题。
- 启用大页（huge_pages=on）可通过减少页错误和TLB压力来消除回归，使内核升级对大多数用户安全。
- 自托管服务器应正确配置大页；容器化设置需要主机级大页预留或减少shared_buffers。
- 云数据库用户依赖供应商处理内核和大页配置，无需自行采取行动。
- 内核社区建议使用rseq提供调度器提示，但这并非实用的即时解决方案；PostgreSQL可能在未来的版本中采用它。
- 该基准测试工件强调了正确配置（大页）对于大共享内存工作负载的重要性，而非Linux的根本性问题。
Show HN: PostgreSQL performance and cost across 23 EC2 instance types
2 months ago
- 根据数据集大小和吞吐量需求选择 AWS 实例；从经过基准测试的实例-磁盘组合中挑选满足要求的最便宜选项。
- 该基准测试的工作负载为 90% 读取和 10% 写入，并发数为 32，在 us-east-1 区域使用 PostgreSQL 17 数据库并按需计费。
- 性能指标包括每秒事务数 (TPS)、延迟和每小时成本；像 c8i.large 和 m8g.large 这样的实例能以较低成本提供较高的 TPS，而 t 系列实例更便宜但性能较差。
Tests Are SQL Files Too
3 months ago
- NpgsqlRest 3.12.0 引入了SQL文件作为REST端点，消除了部署和迁移需求。
- NpgsqlRest 3.19.0 通过命名参数、用于实时验证的监视模式以及原生测试运行器增强了此功能。
- 测试运行器使用带有嵌入式HTTP注释的SQL文件进行进程内端点测试，并具有自动回滚以确保隔离性。
- PostgreSQL 的特性，如事务性DDL、可延迟约束和廉价数据库创建，使得数据库测试变得可行。
- 监视模式提供持续验证并在保存时重新运行测试，从而改善开发体验。
- AI TDD（测试驱动开发）通过快速、隔离的测试及为AI代理提供真实反馈循环得以实现。
PostgreSQL and the OOM Killer: Why You Must Use Strict Memory Overcommit
3 months ago
- 严格的过度提交内存策略用于保护PostgreSQL数据库免遭灾难性的OOM终止，将破坏性故障转化为优雅的错误处理。
- PostgreSQL的架构意味着OOM终止可能损坏共享内存，导致连接中断、事务中止和冗长的崩溃恢复过程。
- 一个内核错误（在Linux 6.5版本中引入）导致虚报的内存提交量膨胀，在严格过度提交策略下引发了意外的ENOMEM错误。
- 该错误在Linux 6.8版本中通过修正move_vma()函数中一个导致内存记账错误处理倒置的条件得到修复。
- Ubicloud设置提交限制的经验法则是：物理内存（不包括大页）的80%加上为侧车进程预留的固定2GB缓冲空间。
- 在启用严格过度提交策略前，监控内存使用情况并理解工作负载特性至关重要，以避免频繁的ENOMEM错误。
Commodore 64 Basic for PostgreSQL
3 months ago
- PL/CBMBASIC允许PostgreSQL运行用Commodore 64 BASIC V2编写的函数，使用了编译成C语言的1982年原始解释器。
- 其特点包括代码行号从10开始且必需，函数参数在第0至9行作为BASIC变量注入。
- 包含一个强制C64 BASIC命名规则的验证器，防止变量名包含嵌入式关键字（例如，'TOTAL'包含'TO'）。
- OUT参数的处理方式是在程序执行后从模拟的RAM中读取BASIC变量表。
- 设备8被映射到数据库，允许使用OPEN、INPUT#、PRINT#等命令来执行SQL语句和读取结果。
- 错误通过解释器的ERROR向量捕获，提供PostgreSQL错误的同时附带原始BASIC错误代码。
- 性能基准测试显示，它比PL/Python慢，但比原始硬件快，每次调用有15微秒的重置开销。
- 限制包括仅支持大写字符串、255个字符的字符串长度限制、不支持NULL值，以及仅限超级用户使用的非受信任语言状态。
Looking Ahead to Postgres 19
3 months ago
- Postgres 19 包含了涵盖应用开发、运维、性能和可扩展性的多项功能。
- REPACK CONCURRENTLY 是一个内置命令，用于无锁回收表膨胀，提升生产数据库的使用体验。
- 分区增强功能允许合并和拆分分区，为适应不断变化的数据工作负载提供了灵活性。
- 逻辑复制现在支持序列同步、发布的 EXCEPT 子句，并通过 effective_wal_level 提供更好的配置。
- 自动清理改进包括并行工作线程、优先级评分系统，以及通过新视图和日志增强的监控。
- SQL 属性图查询 (SQL/PGQ) 允许对关系数据进行图样式查询，无需单独的数据库。
- COPY 命令升级包括跳过多行标题、ON_ERROR SET_NULL 选项，以及更易处理数据工作流的 JSON 输出。
- SQL 易用性功能包括 GROUP BY ALL、窗口函数的 IGNORE NULLS，以及使用 RETURNING 改进的 upsert 行为。
- 性能增强涵盖了规划器优化、反连接、COPY 中的 SIMD 使用，以及更快的外键检查。
- 该版本强调广度，使 Postgres 更适用于多种用户角色和工作负载，并鼓励在正式发布前进行测试。
Reading the internals of Postgres: Database cluster, databases, and tables
3 months ago
- PostgreSQL使用“数据库集群”来指代管理多个数据库的单个实例，而非多个服务器。
- 数据库和其他对象通过OID（无符号整数）标识，用户创建对象的OID从16384开始。
- 像pg_class和pg_database这样的系统目录存储元数据，是可查询的常规表。
- 物理结构以数据目录（$PGDATA）为中心，包含按OID命名的每个数据库的子目录和表。
- 表和索引存储在base/{数据库OID}/{关系文件节点}下的文件中，具有空闲空间和可见性映射的分叉。
- VACUUM FULL可以更改表的relfilenode，使其与OID解耦。
- 表空间允许将数据库对象存储在自定义目录中，在$PGDATA/pg_tblspc/中有符号链接。
- 堆表由包含页头、行指针的8KB页组成，元组从页面末尾开始存储。
- TOAST通过将大值存储在单独的外部表中（分块并索引）来处理它们。
- 写入元组涉及更新页头和行指针，并考虑对齐问题。
- 读取元组可使用顺序扫描或索引扫描（例如B树），具体取决于查询。

postgresql

PostgreSQL Autovacuum Internals and Benchmark

It was surprisingly hard to break CloudNativePG replication

PostgreSQL's MVCC is bad. So is everyone else's

PostgreSQL as a Temporal Database

How to Back Up PostgreSQL in Docker (Local, S3, and Plakar)

Why It's So Hard to Add a Column in the Middle of a PostgreSQL Table

Why Huge Pages matter for Postgres

PgDog (YC P25) Is Hiring a Founding Software Engineer

The four horsemen behind Postgres outages

Preempt_none Is Dead; Your Postgres Probably Doesn't Care

Show HN: PostgreSQL performance and cost across 23 EC2 instance types

Tests Are SQL Files Too

PostgreSQL and the OOM Killer: Why You Must Use Strict Memory Overcommit

Commodore 64 Basic for PostgreSQL

Looking Ahead to Postgres 19

Reading the internals of Postgres: Database cluster, databases, and tables