They Enabled Postgres Partitioning and their Backend fell apart

День тому

In a wonderful blog, Kyle explores the pains he faced managing a Postgres instance for a startup he works for and how enabling partitioning sigintfically created wait events causing the backend and subsequently NGINX to through 500 errors.
We discuss this in this video/podcast
www.kylehailey.com/post/postg...
0:00 Intro
3:30 Background
6:30 April 1st The partitioning
8:30 May 6th things went south
10:30 April 11 The start of the waits
12:00 Locks
15:30 High volume of locks
17:30 The Problem with Postgres 13
20:00 Fastpath locking
24:00 Moving to Weekly Partitions
31:30 Summary
Discovering Backend Bottlenecks: Unlocking Peak Performance
performance.husseinnasser.com
Fundamentals of Backend Engineering Design patterns udemy course (link redirects to udemy with coupon)
backend.husseinnasser.com
Fundamentals of Networking for Effective Backends udemy course (link redirects to udemy with coupon)
network.husseinnasser.com
Fundamentals of Database Engineering udemy course (link redirects to udemy with coupon)
database.husseinnasser.com
Follow me on Medium
/ membership
Introduction to NGINX (link redirects to udemy with coupon)
nginx.husseinnasser.com
Python on the Backend (link redirects to udemy with coupon)
python.husseinnasser.com
Become a Member on UKposts
/ @hnasr
Buy me a coffee if you liked this
www.buymeacoffee.com/hnasr
Arabic Software Engineering Channel
/ @husseinnasser
🔥 Members Only Content
• Members-only videos
🏭 Backend Engineering Videos in Order
backend.husseinnasser.com
💾 Database Engineering Videos
• Database Engineering
🎙️Listen to the Backend Engineering Podcast
husseinnasser.com/podcast
Gears and tools used on the Channel (affiliates)
🖼️ Slides and Thumbnail Design
Canva
partner.canva.com/c/2766475/6...
Stay Awesome,
Hussein

КОМЕНТАРІ: 48

@hnasr 10 місяців тому

Check out my backend performance course performance.husseinnasser.com

@buddy.abc123 10 місяців тому

As we become more and more senior we realise more and more that every decision in our field is basically a compromise. Nothing is perfect. There is no magic. There is a cost.

@ZeeshanAli-nk3xk 10 місяців тому

deep!

@just_A_doctor 10 місяців тому

We become!?! Senior never said he is senior like that.

@EzequielRegaldo 10 місяців тому

Dude that problem is super jr

@arcanernz 10 місяців тому

@@EzequielRegaldoagreed this bug negates partitions altogether like who thought it was ok to lock all partitions.

@Atlastheyote222 9 місяців тому

Sums up 99% of cloud development tbh

@haythamasalama0 10 місяців тому

Great video ❤

@kushalkamra3803 10 місяців тому

Thank you 🙏. I was wondering why did Postgres team did not anticipate locking issues while scaling in a feature that will be used for scaling.

@engineerscodes 10 місяців тому

Nice video 👌

@Atlastheyote222 9 місяців тому

I was considering doing this for my PVE cluster of development environments to simplify docker swarm deployment. Maybe it isn’t worth the trouble lol

@sujeetagrahari2292 10 місяців тому

You said if the query is to get the data of the spcific day, even it will scan the whole partitions. But aws docs provided in the blog suggest, it will read the many partitions if you want the data from multiple days. "1. You query many days worth of data, which requires the database to read many partitions. 2. The database creates a lock entry for each partition. If partition indexes are part of the optimizer access path, the database creates a lock entry for them, too. 3. When the number of requested locks entries for the same backend process is higher than 16, which is the value of FP_LOCK_SLOTS_PER_BACKEND, the lock manager uses the non-fast path lock method."

@LeoLeo-nx5gi 10 місяців тому

can you please provide the link to AWS blog, thanks !!

@pollathajeeva23 10 місяців тому

Interesting case study I'm just curious that how Distributed SQL which is Postgres compatible mitigate this issue in distributed system like YugaByte and CRDB.

@lakhveerchahal 10 місяців тому

Hi Hussein, at 18:10 what was the query that you ran on pg13? I tried reproducing it, but it is locking only that specific partition. Here's what I did - Select * from orders where order_date = '2020-01-01';

@pallavSemwal 10 місяців тому

could you please do a course on devOps ??

@samirzerrouki3153 10 місяців тому

Hello hussein; Can you please talk about the action took by twitter to prevent scraping (6000post/day). And can we hear your thoughts about this and if there any alternative solutions to achieve the goal. Thank you :))

@diamondkingdiamond6289 10 місяців тому

Also, don’t query times drastically increase the more partitions you have. E.g. if you have 1000 partitions query times will be extremely slow.

@pajeetsingh 10 місяців тому

Source it plox

@noir5820 10 місяців тому

Depends are you querying multiple partitions or not ? The whole point of partitioning is to speed up queries

@jacob_90s 9 місяців тому

It adds a little more over head but lets you scale farther. As with everything, all about pros and cons

@codingjerk 4 місяці тому

Planing will slow down, yes, but 1000 partitions is not a huge number, I used to have 4096 partitions on one huge table and it worked like charm. Plans for some analytical queries were slowed down tho, to like ~100ms overhead, but it's okay for analytical queries anyway.

@mawesome4ever 9 місяців тому

This is interesting! Do you know if this has been fixed in the newer versions of postgres? I'm looking for a DB for my game and i think i'll eventually scale and don't want to run into this same issue

@rayanfarhat5006 9 місяців тому

Every DB has it's own problems when scaling, if your game need scaling, then you have enough money to hire a db expert to fix your scaling issues

@pajeetsingh 10 місяців тому

When they are working on database for their startup; Why do they always start with one big table? As part of design and expecting 10 mils row per day; the initial design could have considered partitioning. Is the industry standard is to start with novice design and fix-as-the-bugs/requirements-come-by?

@martinvuyk5326 10 місяців тому

Yep, that's exactly why NoSQL grew in popularity. FrontEnd and BackEnd don't wanna think about db

@mishikookropiridze 8 місяців тому

You won't know that you will have 10 mil rows per day until you have it.

@DoubleM55 2 місяці тому

It would be overkill to always engineer everything to support billions of rows, when probably it won't be needed. As your data starts to grow, you figure out a way to move forward. Works fine in most cases, unless you hit the wall on some weird behavior like shown in this video.

@ifyugwumba8120 10 місяців тому

How do you do it sir just after creating a course on udemy you are back on UKposts. Show me thy ways Am not even done with your udemy course

@GebzNotJebz 10 місяців тому

يعطيك العافية يا هندسة.. محتوي رائع

@rmacpie3475 9 місяців тому

Hi, My IP is dynamic and it's uses intrrnal wan services Any solutions for port forwarding? I use UPnP port forwarding no use, Try all solutions please help

@pajeetsingh 10 місяців тому

As of latest postgres, does it really create a process per SELECT operation? That's crazy thinking.

@DoubleM55 2 місяці тому

No, It never did. It creates a process per *connection*. And then one connection can perform many queries.

@BryanChance 10 місяців тому

Partitioning is too scary for me unless the data is non-critical and some data loss is acceptable. Often the configuration is too complex and brittle that it becomes a bigger problem than what you're to solve. LOL ooh gives me nightmares..

@AkashBanik-sf2dw 9 місяців тому

What happened to your discord server??

@rydmerlin 10 місяців тому

Unlike Oracle it won’t automatically create the partition upon insert. In my last assignment I chose to partition with a 7 day interval and the table had billions of rows over a 10 year period. The table was also subpartitioned. This DBA looks like he had experience with Oracle. What authority could he had used to verify his plan before he took action?

@dinoscheidt 8 місяців тому

Well, this kind of smoke testing is a case where human-machine collaboration (i.e. talking it through with the next version of chat gpt) might be a valid path for the future. Not because the senior DBA doesn’t know what he is doing, and he still needs to know what he is doing, but because an llm is great to potentially filter down a bunch of documentation, content, cases studies and issues one could never have read all and pattern match them. Otherwise no fault - its just life if one can’t afford to do an infinite amount of research and never get to action.

@mhcbon4606 10 місяців тому

i dont have a wide picture on the industry, my gut feeling and small experience tells me that very often in system design people relies on auto partitioning systems to "make it happen". they are confusing quantitative distribution and qualitative distribution. Partitioning the data in space is one thing, Partitioning the data by access and usage is something else. anyways, not my concern anymore.

@bilincinontolojikizdirabi 8 місяців тому

why do they need to use transactions on this huge table? Isn't using data marts or DWHs are more effective? This tablo should not be a transactional table i believe, especially with 22 indexes???

@jp-wi8xr 10 місяців тому

"Disney magic genie" made me realize, you look like Aladdin.

@robarnold8377 9 місяців тому

Great video - this will sound odd but your accent is so Scottish in tone. I promise it’s not just because lock was said 100 times.

@robarnold8377 9 місяців тому

Also I never experienced this issue in oracle. Balancing global vs local partitions was important - but simple oracle partitions definitely felt more reliable.

@robarnold8377 9 місяців тому

All the partitions wtaf! I just got to that part of the video. Hell That’s a bug. That negates the usage of partitions on ANYTHING that gets an update operation Maybe Postgres communicated this out in advance. But that does not seem like a good start of a partition use case

@yassineoujaa2670 9 місяців тому

Recently I asked the AI to generate random arabic names , and the one of the names was Nasser Hussein lol

@jondoe79 10 місяців тому

Only Partition for data to be archived.

@mrngwozdz 7 місяців тому

You can do partitioning by date by adding additional table and rule on it. Here is how to achievie this: CREATE RULE autocall_createpartitionifnotexists AS ON INSERT TO public.data_temp DO INSTEAD (" + "SELECT createpartitionifnotexists((new.value)::date) AS createpartitionifnotexists;" + "INSERT INTO public.data (value)" + "VALUES (NEW.value);" + ") And function createpartitionifnotexists: CREATE OR REPLACE FUNCTION public.createpartitionifnotexists(fordate date) RETURNS void AS ' declare dayStart date := date_trunc(''day'', forDate); declare dayEndExclusive date := dayStart + interval ''1 day''; declare tableName text := ''public.data_'' || to_char(forDate, ''YYYYmmdd''); begin if to_regclass(tableName) is null then execute format(''create table %I partition of public.data for values from (%L) to (%L)'', tableName, dayStart, dayEndExclusive); execute format(''create unique index on %I (id)'', tableName); execute format(''create index on %I USING btree (value)'', tableName); end if; end;' LANGUAGE plpgsql;

@pajeetsingh 10 місяців тому

19:14 S you don't know and we have liberty to disagree with you?

@scriptkiddie6151 10 місяців тому

Whattt?