Use generated columns to perform queries with formulas in a scalable way
Vložit
- čas přidán 4. 08. 2024
- Generated columns allow us to calculate a column based on other columns to create a new column for a table. They also allow us to index based on the column, which allows us to scale any query involving the column.
Often, queries involving complex formulas do not scale, but generated columns allow us to do so by allowing us to index the column. This can be used for financial analysis, sorting by profit margins, and many other applications. It also allows us to display latitude and longitude from PostGIS types, which works great in combination with Supabase realtime.
In this video, / dshukertjr explains how to create a generated column, how to use the generated column to run queries with complex mathematical formulas efficiently, and how to use the generated column in combination with Supabase realtime to display PostGIS data in the app.
Chapters
00:00 Intro
00:21 Explaining the scalability issue with formulas in a query
01:16 Using generated columns to solve the scalability issue
04:09 Using Supabase realtime and PostGIS with generated columns
06:30 Outro
💻 Videos to watch next:
▶ • Make your queries 43,2...
▶ • Getting started with P...
▶ • How to store and query...
👇 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 - Věda a technologie
We are working on series of Postgres videos on this channel right now! What other Postgres concepts would you like us to cover? Let us know in the comments blow 👇
More computing actions scenarios data oriented so we can take advantage of backend all-in-one 🙏
I think people should explore more on postgres powers not just storing data, thanks for this
Great. Does it work with jsonb column?
Yup, that is another awesome use case. You could extract certain values from jsonb columns as a generated column, or you could construct jsonb columns using other columns.
Thanks very much for this!!!!❤❤
Glad you liked it!
i wasted so much time with triggers and functions for small tasks like this
I have been there 😂
Same here !!!!
Same
Amazing presenters of Supabase very informative videos!
Would you PLEASE make a video explaining best practices for handling time + timezones in & out of Supabase ?
If you could add some specifics regarding Flutterflow and how it handles the issue - and how the two could come together - apart from helping my particular non coder situation could be the ONE video on the internet that shows best practices and rules to handle the "time" issue once and for all.
PLEASE! 🤞 😅 🤕
hey these new videos of yours are pretty good. keep up the good work. Oh also imo you should add your name + any other person that worked in the video in description.
Great suggestion! Let me add it in!
Awesome, thank you for the quality content and great service you provide.❤
I wish you had shown the execution time of the before and after.
I only had 1000 rows in this particular example, but yes, I should have prepared enough sample data so that I could demonstrate the execution time difference. Trust me though, querying millions of rows with this formula in the query will take a very long time without using a generated column!
@@dshukertjr Using formula took me very long time to run query and very hard to maintain. I wish i knew this 3 years ago
persistant computed columns are awesome.
Thanks very much.
Glad to hear you liked it!
Geberated columns are only able to access data from the current row. So you can't use them for rollup fields or to reference another table. If you need data from another table, you're back to triggers, functions, and views
Yup, views are awesome!
Awesome feature 👏🏿🙌🏿
Can you create a generated column using the supabase ui column editor
It's not quite yet supported, but hopefully in the future!
is there a difference, performance-wise, between this method and making functions with triggers?
Performance-wise probably not. At least nothing significant.
do one on different type of indexes and approximate string / natural language search
Awesome suggestion! Will definitely cover the topic in the future!
Is this new or has it always been there? I think computed columns might have always been a postgres thing, but this allows me to generate unique compound ids, which i can use in conflict upsert type queries. Glad i clicked
It's relatively new as it was only introduced in Posgres 12, which was released in 2019.
How can we alter that column formula?
You can drop the existing column and add a new one with the same name in a transaction!
So not trigger or functions anymore? 😅
If your use case can be done using generated column, you probably should use generated columns, but triggers have more capabilities than generated column such as reaching out to other tables. So the answer is "it depends"!
@@dshukertjr always on point, great example u made here 🙏