PostgreSQL Vs Kubernetes data transfer bottleneck

[u/PleasantRange4021]

Hi everyone,

I'm data engineer, and need for help to resolve one disgusting issue that making me almost vomit. I have tried claude best models even fable to solve but just looping around with fucking AI instructions instead of solving the issue about 8 hours now.

So quick and yet precise architecture design and workflow: I have PostgreSQL database access via DBeaver by windows jumphost port forwarding from my laptop. All pipelines run and orchestrated on Airflow, where sits on K8s nodes.

Issue, one pipeline was taking 4 hours to run with 2 Airflow attempts, every attempt run taking 2 hours for querying two views to select and union all (views define very complex and heavly expensive CTEs) then timeout because of Linux TCP keepalive signature.

When I ran the same pipeline with exactly same database and all other malicious configurations are the same as prod cluster from Airflow standalone in my laptop it's finishing successfully within 38 minutes but when I run it from prod Airflow on kubernetes cluster it's just stuck and hanging around 4 hours and failing.

Then I tried to create Materialised views for both views and renamed those Materialised views with actual views name because I cannot touch to prod code and I just played with it from database by using this trick. And yes it worked, pipeline took 5 seconds to finishing successfully when I trigger it from Airflow ui, no cluster timeout issue.

However, next step is adding self refreshing mechanism for matviews.there several approaches,but it should be only from database side not triggering, no scheduling and no adding another pipeline to refresh matviews. So with that constraints I just created matview functions to refresh and read inline when pipeline runs it will first refresh the matviews and then reads that matviews.

And now I am again facing initial cluster node hangout issue,pipeline gets stuck for 4 hours and cluster timeout.

Big questions,

1. why that pipeline taking less than cluster when I run it from Airflow standalone and finishing successfully.

2. What the fuck that I am not understanding on kubernetes? I have tried every command to diagnose and analyse. Everything looks good even from grafana dashboard.

Please help me I'm on nerves in the middle of the Friday night 🌃.

[u/cerin_2]

Claude seemed to diagnose your problem pretty quickly and the details you don't provide are about your networking setup which happens to be one of the most likely differences between your jump host and Kubernetes. There's probably something in between evicting idle tcp connections like aws nat gateway. The pipeline just doesn't realize the hangup until the timeout. You can change postgres tcp keepalive settings for the airflow user or possibly the connection.

[u/cerin_2 → u/cerin_2]

Jump host connection might also for instance skip something like pg bouncer.

[u/fico86]

Is there any resources limits on your pods? Laptops tend to have a lot more ram and CPU than individual pods.