How to query with formulas in a scalable way using generated columns

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

[@dshukertjr]

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 👇

[@DinoRossYT]

More computing actions scenarios data oriented so we can take advantage of backend all-in-one 🙏

[@brotherly9058]

Thanks very much for this!!!!❤❤

[@dshukertjr]

Glad you liked it!

[@powertipsweb]

Awesome, thank you for the quality content and great service you provide.❤

[@luisandrade1291]

I wish you had shown the execution time of the before and after.

[@dshukertjr]

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!

[@Yusuf-ok5rk]

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.

[@dshukertjr]

Great suggestion! Let me add it in!

[@shmuel-k]

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

[@dshukertjr]

Yup, views are awesome!

[@thibaultbarolat-massole7190]

Great. Does it work with jsonb column?

[@dshukertjr]

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.

[@jaguarconsultoriadigital8147]

Thanks very much.

[@dshukertjr]

Glad to hear you liked it!

[@fawazjoseph878]

Awesome feature 👏🏿🙌🏿 Can you create a generated column using the supabase ui column editor

[@dshukertjr]

It's not quite yet supported, but hopefully in the future!

[@mrrolandlawrence]

persistant computed columns are awesome.

[@akshykhade]

i wasted so much time with triggers and functions for small tasks like this

[@dshukertjr]

I have been there 😂

[@champechilufya1458]

Same here !!!!

[@DinoRossYT]

Same

[@ubitubee]

do one on different type of indexes and approximate string / natural language search

[@dshukertjr]

Awesome suggestion! Will definitely cover the topic in the future!

[@champechilufya1458]

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

[@dshukertjr]

It's relatively new as it was only introduced in Posgres 12, which was released in 2019.

[@ubitubee]

is there a difference, performance-wise, between this method and making functions with triggers?

[@dshukertjr]

Performance-wise probably not. At least nothing significant.

[@brotherly9058]

How can we alter that column formula?

[@DinoRossYT]

So not trigger or functions anymore? 😅

[@dshukertjr]

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"!

[@DinoRossYT]

@@dshukertjr always on point, great example u made here 🙏