Mi INSERT tardaba 25 minutos y no era culpa de los datos: construyendo un Data Warehouse de e-commerce con PostgreSQL

查看原文 / View Original 编辑 / Edit

Mi INSERT tardaba 25 minutos y no era culpa de los datos: construyendo un Data Warehouse de e-commerce con PostgreSQL

Cargar 112.647 filas en una tabla de hechos debería tardar segundos. A mí me tardaba más de 25 minutos, y acababa cancelando la query. Los datos estaban bien, el SQL estaba bien, las dimensiones se poblaban sin problema. El culpable era otro, y descubrirlo fue la parte más instructiva de todo el proyecto. Todo esto surgió construyendo un Data Warehouse en estrella sobre datos reales de e-commerce: no una tabla bonita para hacer un SELECT *, sino un modelo dimensional completo, reproducible desde cero, capaz de responder preguntas de negocio de verdad.

将 112,647 行数据加载到事实表中本应只需几秒钟。但在我这里却耗时超过 25 分钟，最终我不得不取消查询。数据没问题，SQL 也没问题，维度表也填充得很顺利。罪魁祸首另有其人，而发现它正是整个项目中最具启发性的部分。这一切源于我构建的一个基于真实电商数据的星型数据仓库：它不仅仅是一个用来做 SELECT * 的漂亮表格，而是一个完整的、可从零重现的维度模型，能够回答真正的业务问题。

El dataset

数据集

Trabajé con el Brazilian E-Commerce Public Dataset by Olist: pedidos reales de un marketplace brasileño entre septiembre de 2016 y octubre de 2018. Son 9 CSV relacionados entre sí: 99.441 pedidos y 112.650 líneas de venta, 103.886 pagos y 104.719 reseñas, 32.951 productos, 3.095 vendedores, 1.000.163 registros de geolocalización.

我使用了 Olist 提供的巴西电商公共数据集：包含 2016 年 9 月至 2018 年 10 月期间巴西某电商平台的真实订单。它由 9 个相互关联的 CSV 文件组成：99,441 个订单、112,650 行销售明细、103,886 笔支付记录、104,719 条评论、32,951 个产品、3,095 个卖家以及 1,000,163 条地理位置记录。

Y con trampas de datos reales que hay que ver antes de que te muerdan:

  • Un pedido puede tener varios pagos y varias reseñas. Si los unes tal cual a la tabla de hechos, duplicas ventas. Es el error clásico y silencioso: los totales salen inflados y nadie se entera.
  • customer_id no es un cliente. Olist crea uno por cada pedido; la persona real es customer_unique_id. Contar mal aquí te cambia el KPI: hay 99.441 cuentas frente a 96.096 personas.
  • El CSV de productos trae una errata en la cabecera (product_name_lenght, con “lenght”). Si tu esquema la escribe bien y cargas por interfaz gráfica (que empareja por nombre), esas columnas se quedan vacías sin que nadie avise.

这里还有一些必须在被“坑”之前发现的真实数据陷阱：

  • 一个订单可能有多次支付和多条评论。如果直接连接到事实表，销售额就会翻倍。这是一个经典的隐蔽错误：总额虚高，却无人察觉。
  • customer_id 并不是真正的客户。Olist 为每个订单创建一个 ID；真正的个人标识是 customer_unique_id。在这里统计错误会改变 KPI：共有 99,441 个账户，但实际只有 96,096 人。
  • 产品 CSV 的表头有一个拼写错误（product_name_lenght，多了一个 “h”）。如果你的架构写对了名称，而你通过图形界面加载（按名称匹配），这些列就会在没有任何提示的情况下保持为空。

El proceso

处理流程

Monté una arquitectura en capas: CSV → staging → modelo dimensional → vistas → análisis, todo en cuatro scripts ejecutables en orden y idempotentes (el esquema se recrea desde cero, se puede relanzar mil veces). El modelo es un star schema: una tabla de hechos fact_sales al grano de línea de producto dentro de un pedido, y cinco dimensiones (cliente, producto, vendedor, pago y fecha), con claves sustitutas, PK/FK, CHECK y UNIQUE.

我搭建了一个分层架构：CSV → 暂存区 (staging) → 维度模型 → 视图 → 分析。整个过程由四个按顺序执行且幂等的脚本组成（架构从零重建，可以无限次重新运行）。该模型是一个星型架构：一个以订单内产品行作为粒度的事实表 fact_sales，以及五个维度（客户、产品、卖家、支付和日期），并包含代理键、主外键、CHECK 和 UNIQUE 约束。

La clave del ETL está en deduplicar las relaciones 1:N antes de unirlas a la fact. Con ROW_NUMBER() me quedo con el pago principal (el de mayor importe) y la reseña más reciente de cada pedido. Así el grano se respeta y las ventas no se multiplican.

ETL 的关键在于在连接到事实表之前对 1:N 关系进行去重。我使用 ROW_NUMBER() 保留了每个订单的主要支付记录（金额最大的）和最新的一条评论。这样既保证了粒度，又避免了销售额的重复计算。

Y aquí llegó el INSERT eterno

永无止境的 INSERT

Las dimensiones cargaban perfectas. La fact se quedaba colgada. Descarté bloqueos (la sesión estaba active, no esperando), descarté fechas corruptas (el rango era correcto). ¿Entonces? El problema estaba en que todo el ETL corre dentro de una única transacción. Cuando cargo la fact, las dimensiones se acaban de rellenar en esa misma transacción sin confirmar: PostgreSQL no tiene estadísticas actualizadas de ellas y cree que están vacías. Con esa estimación errónea, el planificador elige un Nested Loop con Seq Scan — recorrer la dimensión entera por cada una de las 112.650 filas. Miles de millones de comparaciones.

维度表加载得很完美，但事实表却卡住了。我排除了锁的问题（会话处于活跃状态，而非等待），也排除了日期损坏的问题（范围是正确的）。那么问题出在哪？问题在于整个 ETL 运行在同一个事务中。当我加载事实表时，维度表刚刚在同一个未提交的事务中填充完毕：PostgreSQL 没有它们的最新统计信息，认为它们是空的。由于这种错误的估计，查询规划器选择了带有顺序扫描（Seq Scan）的嵌套循环（Nested Loop）——即为 112,650 行中的每一行遍历整个维度表。这导致了数十亿次的比较。

La solución cabe en cinco líneas, justo antes de cargar la fact:

ANALYZE ecommerce_dw.dim_customer;
ANALYZE ecommerce_dw.dim_product;
ANALYZE ecommerce_dw.dim_seller;
ANALYZE ecommerce_dw.dim_payment;
ANALYZE ecommerce_dw.dim_date;

ANALYZE actualiza las estadísticas (y, a diferencia de VACUUM, sí funciona dentro de una transacción). El planificador pasa a Hash Join y la carga baja a segundos. Lección: el optimizador no es magia, es un modelo estadístico. Si le mientes sobre el tamaño de tus tablas, te devuelve un plan absurdo. Y no te avisa: simplemente tarda para siempre.

解决方案只需五行代码，就在加载事实表之前： （代码见上文） ANALYZE 会更新统计信息（与 VACUUM 不同，它可以在事务内部运行）。规划器随后切换到哈希连接（Hash Join），加载时间缩短至几秒钟。教训：优化器不是魔法，它是一个统计模型。如果你对表的大小撒谎，它就会给你一个荒谬的执行计划。而且它不会提醒你：它只会一直运行下去。

Los resultados

结果

Con el DW cargado (112.647 líneas, 98.665 pedidos, 95.419 clientes reales, 15,8M R$ de facturación), escribí 12 consultas de negocio con CTEs, funciones ventana y funciones propias. Tres hallazgos que me sorprendieron:

数据仓库加载完成后（112,647 行，98,665 个订单，95,419 个真实客户，1580 万雷亚尔的营业额），我编写了 12 个业务查询，使用了 CTE、窗口函数和自定义函数。有三个发现让我感到惊讶：

  1. La logística no influye en la satisfacción: la determina. Cruzando retraso de entrega con valoración media:

    • Sin retraso: 4,21
    • Retraso leve (1-3 días): 3,23
    • Retraso medio (4-7 días): 2,09
    • Retraso grave (>7 días): 1,70 No es una correlación suave. Es un desplome.

  2. Nadie repite. Solo el 3,05% de los clientes vuelve a comprar, y aportan el 5,71% de los ingresos. El marketplace es excelente captando y pésimo reteniendo. Ese es el mayor agujero de negocio del dataset.

  3. Los “power sellers” mandan. El cuartil superior de vendedores (NTILE(4)) genera el 86,58% de la facturación. Más concentrado incluso que un Pareto 80/20.

  4. 物流不只是影响满意度，而是决定了满意度。 将交付延迟与平均评分交叉分析：

    • 无延迟：4.21
    • 轻微延迟（1-3天）：3.23
    • 中度延迟（4-7天）：2.09
    • 严重延迟（>7天）：1.70 这不是平滑的相关性，而是断崖式下跌。

  5. 没人复购。 只有 3.05% 的客户会再次购买，且仅贡献了 5.71% 的收入。该平台在获客方面表现出色，但在留存方面却非常糟糕。这是该数据集中最大的业务漏洞。

  6. “超级卖家”主导市场。 顶层四分位数的卖家（NTILE(4)）贡献了 86.58% 的营业额。这比 80/20 的帕累托法则还要集中。

Lo que aprendí

我的收获

Lo que más me marcó no fue el SQL avanzado, sino que las decisiones invisibles son las que arruinan un análisis: un JOIN mal planteado que duplica ventas, contar clientes por la columna equivocada, una errata en una cabecera. Nada de eso lanza un error. Simplemente te da un número falso con toda la confianza del mundo. Lo siguiente que tiene sentido: cargas incrementales en lugar de recrear el DW entero, y una dimensión de cliente con historial (SCD tipo 2) para seguir la evolución del comportamiento de compra.

对我触动最大的不是高级 SQL，而是那些隐形的决策往往会毁掉分析：一个错误的 JOIN 导致销售额翻倍、用错误的列统计客户、表头的一个拼写错误。这些都不会报错，它们只是会极其自信地给你一个虚假的数据。接下来的合理步骤是：采用增量加载而不是重建整个数据仓库，并建立一个带有历史记录的客户维度（SCD 类型 2），以跟踪购买行为的演变。

📁 Repo completo en GitHub: https://github.com/dnarram/olist-ecommerce-datawarehouse Proyecto desarrollado durante el Máster en Data Science de Evolve.

📁 GitHub 完整仓库：https://github.com/dnarram/olist-ecommerce-datawarehouse 该项目是在 Evolve 数据科学硕士课程期间开发的。

← 返回新闻列表 / Back to news list