Postgres scales … when you do this!
Вставка
- Опубліковано 23 лип 2024
- Partitioning Tables can greatly improve the efficiency of your Postgres queries by drastically reducing the size of the result set being processed. In this video, Jon Meyers demonstrates how to refactor a giant multi-tenant table into much smaller, individual partitions.
🐘 Partitioning tables: supabase.com/docs/guides/data...
Table partitioning in Postgres is a powerful database feature that allows large tables to be divided into smaller, more manageable pieces called partitions. Each partition can be accessed and managed independently, improving query performance and simplifying maintenance. Partitions can be created based on various criteria, such as range, list, or hash, enabling efficient organization of data. By filtering queries to search within specific partitions, database operations become faster and more efficient. This technique is particularly useful for handling large datasets, optimizing resource usage, and enhancing overall database performance.
00:00 When you need Table Partitions
02:19 Creating a Partitioned Table by List of values
03:39 Creating Partitions for each unique value
04:42 Querying partitioned tables
06:21 Maintaining partitions as data changes
08:36 Querying across multiple partitions
09:18 Combining Indexes with Partitioned Tables
💻 Videos to watch next:
▶ Make your queries 43,240x faster: • Make your queries 43,2...
▶ The FASTEST possible way to query data: • The FASTEST possible w...
▶ Simple trick to make your queries WAY more efficient: • Simple trick to make y...
👇 Learn more about Supabase 👇
🕸 Website: supabase.com/
🏁 Get started: app.supabase.com/
📄 Docs: supabase.com/docs
🔔 Subscribe for more tutorials and feature updates from Supabase: / @supabase
📱 Connect with Us:
🐙 Github: www.github.com/supabase
💬 Discord: www.discord.supabase.com/
🐦 Twitter: / supabase
▶ Instagram (follow for memes): / supabasecom
ABOUT SUPABASE:
Supabase is the open source Firebase alternative. Supabase provides a full Postgres database for every project with pgvector, backups, realtime, and more. Add and manage email and password, passwordless, OAuth, and mobile logins to your project through a suite of identity providers and APIs.
Build in a weekend, scale to millions.
#Supabase #AppDevelopment #RealtimeApps #DeveloperTools - Наука та технологія
Yes! Please do a video on partitioning a table with no down time. This recent Postgres series of content has been a goldmine of knowledge that’s either hidden in the docs, or locked in somebody’s head somewhere because they got the experience but never shared it.
It’s worth bearing in mind that it’s not feasible for Supabase to document or teach us about all of these features. They are not Supabase features; they are PostgreSQL features, and PostgreSQL has been around for decades. It’s all documented on the PostgreSQL site, with thousands of tutorials all over the Internet.
Excellent! Will add it to the list! 👍
I hope this series never ends.
Thanks! Glad you’re enjoying the series! 🙌
you're an underated content creator have learnt a ton about relational dbs from your vids!
Glad to hear that! What would you like to see next?
Please make a video showing how to use pg_partman, every SaaS app would use partitioning if it’s automated.
Will add it to the list! 👍
table partitioning in postgres is a superpower! handy for datawarehouses too.
Totally! Surprisingly easy to implement for how much this can help the performance of your database!
Very helpful! Thank you
Glad to hear it! 🙌
Absolutely brilliant video! So helpful! Definitely going to do this in my Supabase project and am so keen to check out Partman (video on that please!)
And would love to see how to do it without downtime too!
Awesome! Will add both of those to the list 👍
The MANGOS LOL
The best companies!
Amazing 🔥 I like Supabase 🔥🔥
Same! Glad you enjoyed the video! 🙌
Yes… please do a video on partitions and spend a little time explaining how we would use the api call in the likes of Flutterflow.
Will add it to the list 👍
When would you recommend to use either index or partition?
Another great video by the way 😉
Thanks! Glad you enjoyed it!
I would say reach for an index pretty early, and then look at partitioning when your indexes slow down, assuming you have easily partitionable data 👍
How do you update in live your partitions when you have new data (in your people table for example here)?
You can either create a catch-all partition as “default” or use an extension like pg_partman
and what about the referenced tables? do they also get automatically partitioned or not
They do not get automatically partitioned. Usually the referenced table does not grow at the same rate, but you could also partition that table if it gets too big 👍
@@JonMeyers then why dont u guys add this feature where it can partition the referenced table or automatice partition ?
and yes i saw pgpartman but theres no tutorial or anything on how to use it properly like you do (yes i am a noob programer )
Postgres 1
Supabase 0
Not sure what that means, but every point for Postgres is also a point for Supabase 🤝