postgreSQL多任务同时读写场景下的表设计问题

Question

```bash
postgreSQL同时多任务读取场景下的表设计问题

请教各位大佬，现在有这样一个业务场景，该如何设计数据表？

一、爬虫端：
      爬虫服务端有多个爬虫任务，采集的数据均写入同一个数据库和数据表中。目前数据库已有约五千万行数据。每日还在增加。
      爬虫A 每日添加约30-50 万个新的任务关键词。单次往表里写入的字段约10个。爬虫A每天运行的时间约为5小时。
      爬虫B 、C、D根据任务关键词分别爬取不同的数据源1、数据源2、数据源3。这三个爬虫全天24小时运行。
      这三个数据源各自返回对应的json数据，总共包含约80-90个键值 。
      我目前的做法是从json中把每个键值抽出来作为一个单独的表字段。把三个数据源的数据跟任务关键词数据全部放到同一行中。
     现在一行有94个字段。

二、用户端：
      用户在后台需要经常查询上述采集到的数据，且查询可用的过滤条件大概有 80个。 就是说数据表中的字段大部分都要作为查询条件。
      需要支持最少50个用户同时查询。


遇到的问题：
       查询很慢。
       虽然有几个字段建立了索引，但我猜测是多个任务同时读写导致用户查询数据时响应卡顿。要几分钟才能返回数据，有时甚至卡死。   



我的需求：
1、如何重新设计数据表？希望可以说得详细些。如有图示更好。
2、如果需要买单独的postgresql 数据库（阿里云或腾讯云）需要什么样的配置？

非常感谢

```

Answer 1

一些建议和设计策略：

1. 数据表设计

1.1. 分表策略

数据持续增长，单表超过90字段，考虑查询效率和管理的方便性，建议把数据进行分表处理：

• 任务关键词表：存储爬虫A的任务关键词。
• 数据源表：给每个数据源创建独立的表。比如数据源1表、数据源2表和数据源3表。每个表中存储特定数据。

1.2. 索引优化

• 根据查询需求，给经常作为查询条件的字段创建索引。
• 用组合索引来优化那些经常在一起作为查询条件的字段。
• 用EXPLAIN来分析查询计划，找出导致查询慢的部分。

1.3. 用物化视图

如果用户经常查询相同或相似的数据，考虑用物化视图。物化视图是预先计算好的表，可以大大加快查询速度。

1.4. 数据归档

根据数据的时效性，考虑定期将旧数据迁移到归档表或归档数据库中，以保持主表的轻量化。

2. 数据库配置和优化

2.1. 硬件配置

• CPU: 多核心，这样可以更好地支持并发处理。
• 内存: 越大越好，这样更多的数据可以被缓存，从而提高查询速度。
• 存储: 用SSD，它们比传统的HDD有更快的读写速度。

2.2. 数据库参数优化

• 调整shared_buffers，使其占据总内存的约25%。
• 考虑增加work_mem，从而为每个查询提供更多的内存。
• 调整maintenance_work_mem以优化VACUUM和其他维护操作。

2.3. 定期维护

• 定期运行VACUUM和ANALYZE来优化数据库性能。

3. 其他策略

3.1. 读写分离

用主从复制。主数据库用于写操作，从数据库用于读操作。

3.2. 用缓存

用Redis这样的外部缓存来缓存经常查询的数据。

3.3. 分布式数据库

如果数据增长仍然很快，可以用分布式数据库解决方案如Citus，它是PostgreSQL的一个分布式扩展。

---