将Postgresql和Elasticsearch进行数据同步以加速查询

最新推荐文章于 2025-04-18 10:28:00 发布
最新推荐文章于 2025-04-18 10:28:00 发布
阅读量3.1k 收藏 4
点赞数
CC 4.0 BY-SA版权
分类专栏： elasticsearch python 文章标签： python elasticsearch postgresql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/u011819248/article/details/103630441

(本文内容适用于elasticsearch7.x,postgresql11)
受命要求优化一个1500w行的表的查询优化，该表中的数据按天update，所以尽管每天vacuum，但是因为空间并没有实际被释放，查询仍然很慢，这下靓仔怎么办？

一开始想到的方法是进行表分区，按日分区，同时修改数据格式减少update，但是一是附加的代码改动过多，二是实际跑了几个测试数据优化效果只有三分之一。不爽啊，这不是一次合格的优化。

然后想起以前做游戏分发业务的时候，把所有接口数据弄成现成的格式直接从redis进行返回，数据的统计，格式化等等过程在django的signal过程内进行。我现在的项目算是个flask项目，虽然并没使用任何orm，但是这也为我提供了一种思路:把postgresql里的数据用某种方式同步到redis或者类似的玩意儿里。最终经过一系列的调研，选定了elasticsearch。
这段时间里主要靠以下几个文档作为出发点（以下内容适用于elasticsearch2.x）：

https://www.jianshu.com/p/629f698a7c58
https://yq.aliyun.com/articles/56824#modal-login

针对pg11/es7 上述过程已经有些不使用，比如pg-es-fdw已经被归档，而它并不兼容于es7,需要使用其他人fork并迭代的版本:
postgres-elasticsearch-fdw (https://github.com/matthewfranglen/postgres-elasticsearch-fdw)
而它的依赖urllib3在python2(必须大于2.7才能安装)环境里安装的时候如果使用setup.py安装则会因为setuptool版本的问题无法安装，我尝试了这个方法并且提交了issue和pr，最后被拒了,大概是因为我的个例，很蠢，setuptool的迷之配置（https://github.com/urllib3/urllib3/issues/1753），但是我在如此修改安装文件之后成功地安装了相关的包。

正式开始：

0：环境：CentOS Linux release 7.6.1810 (Core)
首先需要安装：
1、PostgreSQL我的版本是11
2、elasticsearch我用的7.4 按照官网的攻略安装就可以
3、安装multicorn:
https://github.com/Kozea/Multicorn
在centos里安装不需要上边链接里花里胡哨的操作，那个是在mac上安装的
4、在pg里新建extension: multicorn，在你想要优化的表所在的schema里创建：
create extension multicorn;
5、安装postgresql插件postgresql-elasticsearch-fdw,上边链接里的pg-es-fw不适用用es7
https://github.com/matthewfranglen/postgres-elasticsearch-fdw

6、在你想要优化的表所在的schema里创建foreign server:

CREATE SERVER multicorn_es FOREIGN DATA WRAPPER multicorn
OPTIONS (
  wrapper 'pg_es_fdw.ElasticsearchFDW'
);

7、如果是新表，或者你打算试试，可以先建个测试表：


                

                
                
        

    

  


        

            

                      
                        最低0.47元/天 解锁文章
                          
                      
            

        


    



                



	

		

			

				
					
2.（后端技术篇java）Postgres库数据同步到ElasticSearch

				
			

			

				

					地图之家
				

				

					10-22
					
					486
					
				

			

		

		

			
				
听老人家说：多看美女会长寿

1.maven导入依赖
<!--elasticsearch依赖包-->
<dependency>
     <groupId>org.elasticsearch</groupId>
     <artifactId>elasticsearch</artifactId>
     <version>7.4.2</version>
</dependency>
<!-- 

			
		

	



                

            
              



	

		

			

				
					
大厂PostgreSQL面试题100道及参考答案（5万字长文)

				
			

			

				

					大模型大数据攻城狮的专栏
				

				

					04-24
					
					1335
					
				

			

		

		

			
				
PostgreSQL，通常简称为Postgres，是一个高度可扩展的开源对象关系型数据库系统（ORDBMS）。它以其稳定性、强大的功能和对SQL标准的遵守而闻名。PostgreSQL支持大量的SQL数据类型和各种编程接口，允许开发者构建复杂的数据库应用。作为一个对象关系型数据库，PostgreSQL不仅支持传统的关系数据库特性，如表、索引、视图等，还支持对象导向的特性，如表的继承、函数的多态等。

			
		

	



              


  
              

                


	

		

			

				
					
从PostgreSQL同步数据到Elasticsearch

				
			

			

				

					做GIS梦的人
				

				

					05-08
					
					1341
					
				

			

		

		

			
				
之前用Elasticsearch进行数据查询的方式很少，最近在进行Elasticsearch相关的查询操作。这里记录一下如何使用python语言，将PostgreSQL数据库中的数据同步到Elasticsearch中，方便进行高效的查询。

			
		

	





	

		

			

				
					
postgres数据同步到elasticsearch

				
			

			

				

					recover517的博客
				

				

					07-22
					
					6074
					
				

			

		

		

			
				
公司的题库系统数据量较大，通常用户会对题目题干进行模糊查询，postgres的like查询效率低下，考虑采用elasticsearch优化查询方案。




  参考 https://www.jianshu.com/p/629f698a7c58




环境如下：


mac 
postgres 10.2
python 2.7.13
es 6.3.1


运行步骤，虽然按照简书上的一步一步来，但是...

			
		

	



		

			
		



	

		

			

				
					
Postgresql和Elasticsearch的全文搜索功能对比

					
最新发布

				
			

			

				

					weixin_47372904的博客
				

				

					04-18
					
					816
					
				

			

		

		

			
				
Postgresql和Elasticsearch的全文搜索功能对比

			
		

	





	

		

			

				
					
将数据从PostgreSQL同步到Elasticsearch的经验总结

				
			

			

				

					mimi886的专栏
				

				

					01-12
					
					5677
					
				

			

		

		

			
				
Elasticsearch 是一款基于 Apache
 Lucene 构建的开源全文检索引擎，它能够轻松地进行大规模的横向扩展，以支撑PB级的结构化和非结构化海量数据的处理。而关系型数据库比较擅长对数据的管理，但对 全文检索功能的支持相对不足，所以有时候一些实际项目需要将关系型数据库中的数据同步到Elasticsearch中，以提供更加强大的全文检索功能。另 外，一些基于关系型数据库的历史遗留系

			
		

	





	

		

			

				
					
elastic search与postgresql的数据同步

				
			

			

				

					qfzhaohan的博客
				

				

					11-05
					
					2331
					
				

			

		

		

			
				
方案选择

由于是针对业务数据做全文搜索，数据存在数据库里，所以将这些数据同步到 ES ，怎么做呢，无非两种方式：一是通过 ES 的 API 进行增删改查，二是通过中间件进行数据全量、增量的同步。很明显 API 的方式比较麻烦，那就试试第二种吧。中间件的方式总共搜了三种方案：

方案1：elasticsearch-jdbc
一个 独立的第三方工具。使用很简单，写个 shell 脚本就行了：



这个工具的活跃度也很高，问题是它的版本兼容性：目前最高只兼容Elasticsearch2.3.4
elastic

			
		

	





	

		

			

				
					
FlinkCDC实战：将 MySQL 数据同步至 ES

				
			

			

				

					m0_74823094的博客
				

				

					01-16
					
					619
					
				

			

		

		

			
				
CDC (Change Data Capture) 是 变更数据获取的简称。核心思想是监测并捕获数据库的变动(数据或数据表的插入、更新以及删除等)，将这些变更按发生的顺序完整地记录下来，写入到消息中间件中以供其他服务进行订阅并消费。Flink 社区开发了 flink-cdc-connectors 组件，这个一个可以直接从 MySQL、PostgreSQL等数据库直接读取全量数据和增量变更数据的source 组件。

			
		

	





	

		

			

				
					
TapData 信创数据源 | 国产信创数据库 PolarDB MySQL 数据同步指南，加速国产化进程，推进自主创新建设

				
			

			

				

					Tapdata 技术博客
				

				

					08-13
					
					1362
					
				

			

		

		

			
				
本专题将以 TapData 正在支持的各国产信创数据源为原点，提供详细的数据库同步、迁移教程，为有需求的用户提供更灵活的工具选择。本文将介绍的 TapData 数据源为——PolarDB。

			
		

	





	

		

			

				
					
PGSync: 数据从PostgreSQL到Elasticsearch/OpenSearch同步工具

				
			

			

				

					gitblog_00389的博客
				

				

					08-09
					
					1274
					
				

			

		

		

			
				
PGSync: 数据从PostgreSQL到Elasticsearch/OpenSearch同步工具
 pgsyncPostgres to Elasticsearch/OpenSearch sync项目地址:https://gitcode.com/gh_mirrors/pgs/pgsync 1. 项目介绍
PGSync 是一个用于将数据从 PostgreSQL 同步到 Elasticsearch ...

			
		

	





	

		

			

				
					
ElasticSearch对接PostgreSQL数据库（postgres-elasticsearch-fdw）

				
			

			

				

					Tkning_Jing
				

				

					06-22
					
					1501
					
				

			

		

		

			
				
通过PostgreSQL（以下简称PG）数据库进行ElasticSearch（以下简称ES）数据库的检索。利用 pg 中的外部表将存储于 es 的数据源获取至pg进行查询。在 pg 端操纵 es 数据库，无需调整使用 es 语法，即使用传统SQL语法，即可得到想要的结果。...

			
		

	





	

		

			

				
					
Go-amazonriver是一个将postgresql的实时数据同步到es或kafka的服务

				
			

			

				

					08-14
				

			

		

		

			
				
amazonriver 利用pg内部的逻辑复制功能,通过在pg创建逻辑复制槽,接收数据库的逻辑变更,通过解析test_decoding特定格式的消息,得到逻辑数据

			
		

	





	

		

			

				
					
pgsync：Postgres到elasticsearch同步

				
			

			

				

					01-30
				

			

		

		

			
				
PG同步
PostgreSQL到Elasticsearch同步
是一个中间件，用于轻松地将数据从同步到 。 它允许您将保留为事实来源，并在公开结构化的非规范化文档。
 对嵌套实体的更改将传播到 。 然后，PGSync的高级查询生成器会根据您的架构动态生成优化SQL查询。 PGSync的咨询模型使您可以快速移动和转换大量数据，同时保持关系的完整性。
 只需用JSON描述您的文档结构或架构， 就会连续捕获数据中的更改并将其加载到而无需编写任何代码。 将您的关系数据转换为结构化的文档格式。
 它使您可以直接从利用的表现力和可伸缩性。 您不必编写复杂的查询和转换管道。 PGSync轻巧，灵活且快速。
 更适合作为辅助的非规范化搜索引擎，以配合更传统的规范化数据存储。 此外，您不应该将主要数据存储在。
 那么，首先如何将数据放入 ？ 和类的工具可以帮助完成此任务，但仍需要进行一些工程和开发。
 和工具可能很复杂，并且需要昂贵的工程工作。
 PGSync的其他好处包括：
 实时分析
可靠的主要数据存储/真理来源
按需缩放
轻松连接多个嵌套表
PGSync架构：
为什么？
 在较高的级别上，您将数据

			
		

	





	

		

			

				
					
postgres-elasticsearch-fdw:从Postgres到Elastic Search外部数据包装器

				
			

			

				

					05-16
				

			

		

		

			
				
PostgreSQL Elastic Search外部数据包装器
这使您可以在Elastic Search中为数据建立索引，然后从PostgreSQL中进行搜索。 您可以写也可以读。
概要
支持的版本
弹性搜寻
依赖安装命令
5
 sudo pip install "elasticsearch>=5,<6>=6,<7>=7,<8"
 PostgreSQL
依赖安装命令
9.4
 sudo apt-get install postgresql-9.4-python-multicorn
 9.5
 sudo apt-get install postgresql-9.5-python-multicorn
 9.6
 sudo apt-get inst

			
		

	





	

		

			

				
					
隧道：PG数据同步工具（Java实现）

				
			

			

				

					02-05
				

			

		

		

			
				
PG数据同步工具（Java实现） 
 隧道是一个将postgresql的实时数据同步到es或kafka的服务
版本支持
Postgresql 9.4或更高版本
Kafka 0.8或更高版本
ElasticSearch 5.x
架构图
原理
tunnel利用pg内部的逻辑复制功能，通过在pg逻辑复制槽，接收数据库的逻辑更改，通过解析test_decoding特定格式的消息，得到逻辑数据
安装使用
打包
$ git clone https://github.com/hellobike/tunnel
$ cd tunnel
$ mvn clean package -Dmaven.test.skip=

			
		

	





	

		

			

				
					
PostgreSQL与es的数据同步方案调研

				
			

			

				

					IT- 研究者
				

				

					04-20
					
					4064
					
				

			

		

		

			
				
PostgreSQL与es的数据同步方案调研
1、logstash
基于时间戳，或者id增量同步，且不能实时同步暂不考虑
2、pg-es-fdw
https://github.com/Mikulas/pg-es-fdw?spm=a2c4e.10696291.0.0.4c7b19a49sHIXL
python> = 2.7 ❤️，任何elasticsearch（基于两年前的情况），postgres> = 9.2
已存档
年久失修且不在维护，故不考虑
3、Go-amazonriver
https:/

			
		

	





	

		

			

				
					
Postgresql与Elasticsearch数据同步提高查询性能

				
			

			

				

					weixin_33858336的博客
				

				

					06-16
					
					2353
					
				

			

		

		

			
				
一般来说，影响数据库最大的性能问题有两个，一个是对数据库的读写操作，一个是数据库中的数据太大导致操作慢，对于前者我们可以适当借助缓存来减少一部分读操作，而针对一些复杂的报表分析和搜索可以交给hadoop和elasticsearch,对于写并发大，读也并发大，我们可以考虑分库分表，主从读写分离或者两者结合等方式来提高并发性和时效性，例如PG大并发写，大数据查看可以用elasticsearch与PG数...

			
		

	





	

		

			

				
					
ElasticSearch与PostgreSQL的整合

				
			

			

				

					AI天才研究院
				

				

					01-25
					
					799
					
				

			

		

		

			
				
1.背景介绍

1. 背景介绍

Elasticsearch 是一个基于 Lucene 库的搜索引擎，由 Elastic 公司开发。它是一个分布式、实时、高性能的搜索引擎，可以用于文本搜索、数据分析、日志分析等。PostgreSQL 是一个高性能、可扩展的关系型数据库管理系统，由 PostgreSQL Global Development Group 开发。

在现代应用中，数据量越来越大，传统的...

			
		

	





	

		

			

				
					
Java代码：将postgresql表的列信息同步到Elasticsearch索引映射

				
			

			

				

					学亮编程手记
				

				

					08-17
					
					311
					
				

			

		

		

			
				
以下是将postgresql表的列信息同步到Elasticsearch，使用Elasticsearch的Java高级客户端（Java High Level REST Client）进行操作。请注意，上述Java代码仅提供了基本的操作示例，您需要根据实际情况进行适当的修改和调整。另外，为了使Java代码能够连接到PostgreSQL和Elasticsearch，您需要在项目的。文件中，并根据实际需要进行适当的版本调整和其他配置。请将上述依赖项添加到您的项目的。文件中添加相应的依赖项。

			
		

	





	

		

			

				
					
如何优化一百万条数据，使查询速度加快

				
			

			

				

					02-19
				

			

		

		

			
				
<think>好的，用户问的是如何优化一百万条数据来加快查询速度。我需要先理清楚可能的优化方向。首先，一百万条数据不算特别大，但如果没有合适的优化，查询可能会变慢。

用户可能是开发人员或者数据库管理员，他们遇到了查询性能的问题，想要找到解决方法。他们可能对数据库优化有一定了解，但需要更具体的指导。深层需求可能是希望全面了解优化的各个方面，而不仅仅是索引之类的常见方法。

接下来，我应该考虑多个层面的优化策略。首先是数据库设计方面，比如表结构是否合理，有没有正规化或反正规化，字段类型是否合适。然后是索引优化，包括选择合适的索引类型，避免过多索引，使用覆盖索引等。

查询语句本身的优化也很重要，比如避免SELECT *，优化JOIN操作和WHERE子句。分区和分表也是常用的方法，特别是针对大数据量表。硬件和配置方面，增加内存、调整缓存设置也能提升性能。

还有可能需要考虑读写分离、使用缓存机制如Redis，或者引入搜索引擎如Elasticsearch。定期维护，比如重建索引、清理数据，也是不可忽视的。如果这些还不够，可能需要分布式数据库解决方案。

需要注意用户的数据量是一百万，这个级别可能分区或分表已经有效，但分布式可能暂时不需要。还要提醒用户具体方法要根据数据库类型（比如MySQL、PostgreSQL）来调整，因为不同DBMS有不同的优化手段。

最后，应该分点说明，结构清晰，让用户能逐步排查和优化。可能还要提到分析执行计划，使用慢查询日志等工具，帮助定位问题。</think>优化百万级数据查询速度需要从数据库设计、索引策略、查询优化、硬件配置等多方面入手，以下分层次介绍常见优化方案：

---

### **一、数据库设计优化**
1. **合理设计表结构**
   - **正规化与反正规化**  
     根据业务场景平衡数据冗余：高频查询的关联表可适度反规范化（如冗余关键字段），减少 JOIN 操作。
   - **选择合适的数据类型**  
     使用最小适用类型（如 `INT` 代替 `BIGINT`，`VARCHAR(255)` 改为合理长度），避免 `TEXT/BLOB` 存储高频查询字段。

2. **分区表（Partitioning）**  
   按时间、地域等业务维度分区（如按月份划分），缩小单次查询扫描范围。

---

### **二、索引优化（核心手段）**
1. **创建高效索引**
   - **覆盖索引（Covering Index）**  
     索引包含查询所需全部字段（如 `INDEX (a, b)` 对应 `SELECT a, b FROM table`），避免回表。
   - **复合索引顺序**  
     遵循最左前缀原则，高频条件字段放左侧（如 `WHERE a=1 AND b=2` 创建 `INDEX(a, b)`）。
   - **避免过度索引**  
     索引过多会降低写入速度，定期清理无用索引。

2. **索引类型选择**
   - **B-Tree**：默认结构，适合范围查询（`>`, `<`, `BETWEEN`）。
   - **Hash**：仅精确匹配（`=`），不支持排序（如 MySQL MEMORY 引擎）。
   - **全文索引**：针对文本搜索（如 `LIKE '%keyword%'` 优化）。

3. **索引失效场景规避**
   - 避免对索引字段使用函数（如 `WHERE YEAR(date) = 2023` → 改为范围查询）。
   - 注意隐式类型转换（如字符串字段用数字查询）。
   - 减少 `OR` 条件（可拆分为 UNION 查询）。

---

### **三、SQL 查询优化**
1. **精简查询逻辑**
   - 避免 `SELECT *`，只取必要字段。
   - 使用 `LIMIT` 分页，避免全表扫描（分页深度大时考虑游标分页）。
   - 用 `EXISTS` 替代 `IN` 子查询（尤其关联大表时）。

2. **JOIN 优化**
   - 小表驱动大表（如 `LEFT JOIN` 小表在左侧）。
   - 确保关联字段有索引。

3. **预计算与缓存**
   - 对复杂统计使用物化视图（Materialized View）或定时汇总表。
   - 缓存高频查询结果（如 Redis）。

---

### **四、分库分表与读写分离**
1. **垂直拆分**  
   按业务模块拆分表（如订单表与日志表分离）。

2. **水平分片（Sharding）**  
   单表过大时按哈希、范围等策略分片（如用户 ID 取模分 10 张表）。

3. **读写分离**  
   主库处理写操作，从库处理读查询（需解决数据同步延迟问题）。

---

### **五、数据库配置调优**
1. **内存分配优化**
   - 调整 `innodb_buffer_pool_size`（MySQL）或 `shared_buffers`（PostgreSQL），通常设为物理内存的 70%-80%。

2. **日志与写入优化**
   - 非强一致性场景可关闭 `innodb_flush_log_at_trx_commit=2`（MySQL）。
   - 批量写入使用 `INSERT ... VALUES (),(),()` 减少事务提交次数。

---

### **六、硬件与架构升级**
1. **SSD 替代机械硬盘**  
   显著提升 I/O 性能。

2. **引入缓存层**  
   使用 Redis 缓存热点数据（如近期订单、用户信息）。

3. **使用列式数据库**  
   分析型场景可切换至 ClickHouse、Redshift 等列式存储数据库。

---

### **七、监控与分析工具**
1. **执行计划分析**  
   使用 `EXPLAIN`（MySQL/PostgreSQL）或 `EXPLAIN ANALYZE` 查看查询路径，重点观察 `type`（扫描类型）、`rows`（预估行数）、`Extra`（是否使用临时表/文件排序）。

2. **慢查询日志**  
   开启慢查询日志定位耗时操作（如 MySQL 设置 `long_query_time=2s`）。

---

### **示例：优化分页查询**
**问题 SQL**  
```sql
SELECT * FROM orders ORDER BY id LIMIT 1000000, 10; -- 越往后越慢
```

**优化方案**  
使用游标分页（基于索引列）：  
```sql
SELECT * FROM orders WHERE id > 1000000 ORDER BY id LIMIT 10;
```

---

### **总结**
- **100 万级数据**：通常通过索引 + 分区即可显著优化。
- **千万级以上**：需考虑分库分表或分布式数据库（如 TiDB、Cassandra）。
- **实时分析场景**：可结合 Elasticsearch 或 Apache Doris 加速检索。

建议根据实际场景逐步验证优化效果，优先解决执行时间最长的查询（遵循 80/20 法则）。 

			
		

	



              




          




        



    





    



      

      

        
      

      





	

		
评论 6

	

	
	




  

    

      添加红包
      
    

    

      

        
        

          
          
        

        
请填写红包祝福语或标题

      

      

        
        

          
          个
        

        
红包个数最小为10个

      

      

        
        

          
          元
        

        
红包金额最低5元

      

      

        
        

          当前余额3.43元
          前往充值 >
        

      

      

        

          需支付：10.00元
        

        
        
      

    

  





  

    
    
  

  

    
    
  








  

    

      

        

          

            
            
成就一亿技术人!

          

          

        

        

          

          

            领取后你会自动成为博主和红包主的粉丝
            规则
          

        

      

      

        

          

            
              
            
            

              
hope_wisdom
 发出的红包
            

          

        

        

          

          

          

        

      

    

    

  



      
      

      
实付元

      
使用余额支付

      

        

          

            
            点击重新获取
          

        

        
扫码支付

      

      

        
          
            
          
          
        
      

      

        
        钱包余额
          0
          

            
            

              

                
抵扣说明：

                
 1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
 2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

              

            

          

      

      余额充值