如何在Web中使用PostgreSQL获取连续列表

Question

我正在使用HTTP构建一个API，该API通过分页从PostgreSQL中获取许多行数据。在普通情况下，我通常通过简单的OFFSET/LIMIT子句来实现分页。但是，在这种情况下有一些特殊要求：

• 有很多行数据，我相信用户无法到达末尾（想象Twitter时间轴）。
• 页面不必是随机可访问的，而只能是顺序访问的。
• API将返回一个URL，其中包含一个游标令牌，该令牌指向连续块的页面。
• 游标令牌不必永久存在，只需存在一段时间即可。
• 其排序经常波动（如Reddit排名），但连续的光标应保持其一致的排序。

如何实现此任务？ 我已准备好更改整个数据库架构！

- minhee

---

只是为了确保你的意思。你是在说很多行还是非常宽的行，还是两者都有？ - Kuberchaun

这必须仅使用游标完成吗？因为还有其他不需要管理游标的方法可以完成它。 - PirateApp

Answer 1

假设仅有结果的排序波动而非行内数据，Fredrik的回答是正确的。然而，我建议加入以下内容：

• store the id list in a postgresql table using the array type rather than in memory. Doing it in memory, unless you carefully use something like redis with auto expiry and memory limits, is setting yourself up for a DOS memory consumption attack. I imagine it would look something like this:

  create table foo_paging_cursor (
    cursor_token ..., -- probably a uuid is best or timestamp (see below)
    result_ids integer[], -- or text[] if you have non-integer ids
    expiry_time TIMESTAMP
  );
  

• You need to decide if the cursor_token and result_ids can be shared between users to reduce your storage needs and the time needed to run the initial query per user. If they can be shared, chose a cache window, say 1 or 5 minute(s), and then upon a new request create the cache_token for that time period and then check to see if the results ids have already been calculated for that token. If not, add a new row for that token. You should probably add a lock around the check/insert code to handle concurrent requests for a new token.

• Have a scheduled background job that purges old tokens/results and make sure your client code can handle any errors related to expired/invalid tokens.

不要考虑使用真实的数据库游标来处理此问题。

将结果ID保存在Redis列表中是另一种处理方式（请参见LRANGE命令），但如果您选择这种方式，请注意到期时间和内存使用情况。您的Redis键将是cursor_token，而ids将是列表成员。

- Tavis Rudd

---

最好使用临时表。速度更快，磁盘负载更少。不必担心DOS攻击，临时表只能使用有限的RAM（请阅读手册中关于temp_buffers的内容），当RAM不足时会写入磁盘。 - Erwin Brandstetter

临时表是会话本地的，会在会话终止时被删除。因此，在使用数据库连接池或 HTTP API 端点分布在多个节点并使用不同连接的情况下，这种方法将无法使用。当应用服务器重新启动并需要重新连接到数据库时，也会出现问题。尽管如此，通过将表放入内存支持（通过 tmpfs）的表空间中，您可以获得相同的好处。请参见 http://magazine.redhat.com/2007/12/12/tip-from-an-rhce-memory-storage-on-postgresql/。 - Tavis Rudd

谢谢您的建议。我决定使用memcached，并将逗号分隔的ID存储到带有过期时间的键（即游标令牌）中。谢谢！ - minhee

Answer 2

我对PostgreSQL一无所知，但我是一个相当不错的SQL Server开发人员，所以我想尝试一下 :)

您预计每个会话中用户最多会浏览多少行/页？例如，如果您希望用户在每个会话中最多翻阅10页[每页包含50行]，则可以将该最大值设置为缓存10 * 50行（或仅为行的ID，这取决于您拥有多少内存/同时用户）。

这肯定会有助于加速您的Web服务，而且实现起来非常容易。所以：

• 当用户从第1页请求数据时。运行查询（包括排序、连接检查等），将所有ID存储到数组中（但最多500个ID）。返回与数组中位置0-9处的ID相对应的数据行。
• 当用户请求第2-10页时。返回与数组中位置（page-1）* 50 - （page）* 50-1处的ID相对应的数据行。

你还可以增加数字，500个整数的数组只占用2K内存，但这也取决于你希望初始查询/响应有多快。

我在一个实时网站上使用了类似的技术，当用户继续翻到第10页时，我就转向查询。我想另一种解决方案是继续扩展/填充数组。(再次运行查询，但排除已包含的ID)。

无论如何，希望这能帮到你！

- Fredrik Johansson