Training Example: Supabase – Review the Data, Give Your Score & Compare to the Real AI Evaluation

State of Startups 2026: Take the survey.
[H1] Build in a weekendScale to millions
Supabase is the Postgres development platform. Start your project with a Postgres database, Authentication, instant APIs, Edge Functions, Realtime subscriptions, Storage, and Vector embeddings.Start your projectRequest a demo
[IMG: mozilla]
[IMG: github]
[IMG: 1password]
[IMG: pwc]
[IMG: pika]
[IMG: humata]
[IMG: udio]
[IMG: langchain]
[IMG: resend]
[IMG: loops]
[IMG: mobbin]
[IMG: gopuff]
[IMG: chatbase]
[IMG: betashares]
[IMG: submagic]
[IMG: mozilla]
[IMG: github]
[IMG: 1password]
[IMG: pwc]
[IMG: pika]
[IMG: humata]
[IMG: udio]
[IMG: langchain]
[IMG: resend]
[IMG: loops]
[IMG: mobbin]
[IMG: gopuff]
[IMG: chatbase]
[IMG: betashares]
[IMG: submagic]
[IMG: mozilla]
[IMG: github]
[IMG: 1password]
[IMG: pwc]
[IMG: pika]
[IMG: humata]
[IMG: udio]
[IMG: langchain]
[IMG: resend]
[IMG: loops]
[IMG: mobbin]
[IMG: gopuff]
[IMG: chatbase]
[IMG: betashares]
[IMG: submagic]
[IMG: mozilla]
[IMG: github]
[IMG: 1password]
[IMG: pwc]
[IMG: pika]
[IMG: humata]
[IMG: udio]
[IMG: langchain]
[IMG: resend]
[IMG: loops]
[IMG: mobbin]
[IMG: gopuff]
[IMG: chatbase]
[IMG: betashares]
[IMG: submagic]
Trusted by fast-growing companies worldwide
[H2] Postgres Database
Every project is a full Postgres database, the world's most trusted relational database. 100% portable Built-in Auth with RLS Easy to extend
[IMG: Supabase Postgres database]
[IMG: Supabase Postgres database]
[H2] Authentication
Add user sign ups and logins, securing your data with Row Level Security.
[IMG: Supabase Authentication user db rows]
[IMG: Supabase Authentication user db rows]
[IMG: Supabase Authentication user db rows]
[IMG: Supabase Authentication user db rows]
[H2] Edge Functions
Easily write custom code without deploying or scaling servers.
[IMG: Supabase Edge Functions globe]
[IMG: Supabase Edge Functions globe]
$supabasefunctions deploy
[H2] Storage
Store, organize, and serve large files, from videos to images.
[H2] Realtime
Build multiplayer experiences with real-time data synchronization.
[IMG: Supabase Realtime]
[IMG: Supabase Realtime]
[H2] Vector
Integrate your favorite ML-models to store, index and search vector embeddings.OpenAIHugging Face
[IMG: Supabase Vector graph]
[IMG: Supabase Vector graph]
[H2] Data APIs
Instant ready-to-use Restful APIs.
[IMG: Supabase restful DataAPIs]
[IMG: Supabase restful DataAPIs]
[IMG: Supabase restful DataAPIs]
[IMG: Supabase restful DataAPIs]
[IMG: Supabase restful DataAPIs]
[IMG: Supabase restful DataAPIs]
[IMG: Supabase restful DataAPIs]
[IMG: Supabase restful DataAPIs]
Use one or all. Best of breed products. Integrated as a platform.Use Supabase with any frameworkCustomer Stories
[H3] Trusted by the world’s most innovative companies.
See how Supabase empowers companies of all sizes to accelerate their growth and streamline their work.View all storiesView events
[IMG: Quivr]
[IMG: Tinloof]
[IMG: 1Password]
[IMG: Next Door Lending]
[IMG: Maergo]
Maergo's Express Delivery: How Supabase Helped Achieve Scalability, Speed, and Cost Saving
[IMG: Shotgun]
[IMG: Mozilla]
[IMG: Chatbase goes upmarket on Supabase]
Chatbase goes upmarket on Supabase
[IMG: Mobbin]
[IMG: HappyTeams]
[IMG: Scaling securely: one million users in 7 months protected with Supabase Auth]
Scaling securely: one million users in 7 months protected with Supabase Auth
[IMG: Quivr]
[IMG: Tinloof]
[IMG: 1Password]
[IMG: Next Door Lending]
[IMG: Maergo]
Maergo's Express Delivery: How Supabase Helped Achieve Scalability, Speed, and Cost Saving
[IMG: Shotgun]
[IMG: Mozilla]
[IMG: Chatbase goes upmarket on Supabase]
Chatbase goes upmarket on Supabase
[IMG: Mobbin]
[IMG: HappyTeams]
[IMG: Scaling securely: one million users in 7 months protected with Supabase Auth]
Scaling securely: one million users in 7 months protected with Supabase Auth
[IMG: Quivr]
[IMG: Tinloof]
[IMG: 1Password]
[IMG: Next Door Lending]
[IMG: Maergo]
Maergo's Express Delivery: How Supabase Helped Achieve Scalability, Speed, and Cost Saving
[IMG: Shotgun]
[IMG: Mozilla]
[IMG: Chatbase goes upmarket on Supabase]
Chatbase goes upmarket on Supabase
[IMG: Mobbin]
[IMG: HappyTeams]
[IMG: Scaling securely: one million users in 7 months protected with Supabase Auth]
Scaling securely: one million users in 7 months protected with Supabase Auth
[H3] Start building in seconds
Kickstart your next project with templates built by us and our community.View all examplesOfficial GitHub library
[H3] Stay productive and manage your app without leaving the dashboard
Table EditorSQL EditorRLS PoliciesFull CRUDMaterialized ViewsForeign TablesPartitioned TablesEasy as a spreadsheet
[H3] Join the community
Discover what our community has to say about their Supabase experience.Join us on Discord
[IMG: @nerdburn twitter image]
@nerdburnIt’s fun, feels lightweight, and really quick to spin up user auth and a few tables. Almost too easy! Highly recommend.
[IMG: @patrickc twitter image]
@patrickcVery impressed by @supabase's growth. For new startups, they seem to have gone from "promising" to "standard" in remarkably short order.
[IMG: @Aliahsan_sfv twitter image]
@Aliahsan_sfvOkay, I finally tried Supabase today and wow... why did I wait so long? ? Went from 'how do I even start' to having auth + database + real-time updates working in like 20 minutes. Sometimes the hype is actually justified! #Supabase
[IMG: @yatsiv_yuriy twitter image]
@yatsiv_yuriySupabase is the best product experience I've had in years.
Not just tech - taste.
From docs to latency to the URL structure that makes you think "oh, that's obvious"
Feels like every other platform should study how they built it
@supabase I love you
[IMG: @adeelibr twitter image]
@adeelibr@supabase shout out, their MCP is awesome. It's helping me create better row securities and telling me best practises for setting up a supabase app
[IMG: @TyronBache twitter image]
@TyronBacheReally impressed with @supabase's Assistant.
It has helped me troubleshoot and solve complex CORS Configuration issues on Pinger.
[IMG: @MinimEditor twitter image]
@MinimEditorI’ve always used Supabase just as a database.
Yesterday, I helped debug a founder’s vibe-coding project built with React + React Router — no backend server.
The “backend” was entirely Supabase Edge Functions as the API.
First time using Supabase this way.
Impressive.
[IMG: @orlandopedro_ twitter image]
@orlandopedro_Love @supabase custom domains
makes the auth so much better
[IMG: @sdusteric twitter image]
@sdustericLoving #Supabase MCP. Claude Code would not only plan what data we should save but also figure out a migration script by checking what the schema looks like on Supabase via MCP.
[IMG: @SteinlageScott twitter image]
@SteinlageScottI love @supabase's built-in Advisors. The security and performance linters improve everything and boost my confidence in what I'm building!
[IMG: @BowTiedQilin twitter image]
@BowTiedQilinWorking with @supabase has been one of the best dev experiences I've had lately.
Incredibly easy to set up, great documentation, and so many fewer hoops to jump through than the competition.
I definitely plan to use it on any and all future projects.
[IMG: @adm_lawson twitter image]
@adm_lawsonLove supabse edge functions. Cursor+Supabase+MCP+Docker desktop is all I need
[IMG: @gokul_i twitter image]
@gokul_iFirst time running @supabase in local. It just works. Very good DX imo.
[IMG: @dadooos_ twitter image]
@dadooos_Run supabase locally and just wow in silence! I am impressed! This is the kind of tooling I would want for my team.
[IMG: @Rodrigo66799141 twitter image]
@Rodrigo66799141After a week of diving deep into Supabase for my new SaaS project, I'm really impressed with its Auth and RLS features. It makes security much simpler for solo founders. #buildinpublic #SaaS
[IMG: @xthemadgeniusx twitter image]
@xthemadgeniusxLately been using Supabase over AWS/ GCP for products to save on costs and rapid builds(Vibe Code) that do not need all the Infra and the hefty costs that come with AWS/ GCP out the door. Great solution overall.
[IMG: @pontusab twitter image]
@pontusabI love everything about Supabase.
[IMG: @viratt_mankali twitter image]
@viratt_mankaliLove how Supabase makes full stack features this easy. Using it with Next.js and loving the experience!
[IMG: @nerdburn twitter image]
@nerdburnIt’s fun, feels lightweight, and really quick to spin up user auth and a few tables. Almost too easy! Highly recommend.
[IMG: @patrickc twitter image]
@patrickcVery impressed by @supabase's growth. For new startups, they seem to have gone from "promising" to "standard" in remarkably short order.
[IMG: @Aliahsan_sfv twitter image]
@Aliahsan_sfvOkay, I finally tried Supabase today and wow... why did I wait so long? ? Went from 'how do I even start' to having auth + database + real-time updates working in like 20 minutes. Sometimes the hype is actually justified! #Supabase
[IMG: @yatsiv_yuriy twitter image]
@yatsiv_yuriySupabase is the best product experience I've had in years.
Not just tech - taste.
From docs to latency to the URL structure that makes you think "oh, that's obvious"
Feels like every other platform should study how they built it
@supabase I love you
[IMG: @adeelibr twitter image]
@adeelibr@supabase shout out, their MCP is awesome. It's helping me create better row securities and telling me best practises for setting up a supabase app
[IMG: @TyronBache twitter image]
@TyronBacheReally impressed with @supabase's Assistant.
It has helped me troubleshoot and solve complex CORS Configuration issues on Pinger.
[IMG: @MinimEditor twitter image]
@MinimEditorI’ve always used Supabase just as a database.
Yesterday, I helped debug a founder’s vibe-coding project built with React + React Router — no backend server.
The “backend” was entirely Supabase Edge Functions as the API.
First time using Supabase this way.
Impressive.
[IMG: @orlandopedro_ twitter image]
@orlandopedro_Love @supabase custom domains
makes the auth so much better
[IMG: @sdusteric twitter image]
@sdustericLoving #Supabase MCP. Claude Code would not only plan what data we should save but also figure out a migration script by checking what the schema looks like on Supabase via MCP.
[IMG: @SteinlageScott twitter image]
@SteinlageScottI love @supabase's built-in Advisors. The security and performance linters improve everything and boost my confidence in what I'm building!
[IMG: @BowTiedQilin twitter image]
@BowTiedQilinWorking with @supabase has been one of the best dev experiences I've had lately.
Incredibly easy to set up, great documentation, and so many fewer hoops to jump through than the competition.
I definitely plan to use it on any and all future projects.
[IMG: @adm_lawson twitter image]
@adm_lawsonLove supabse edge functions. Cursor+Supabase+MCP+Docker desktop is all I need
[IMG: @gokul_i twitter image]
@gokul_iFirst time running @supabase in local. It just works. Very good DX imo.
[IMG: @dadooos_ twitter image]
@dadooos_Run supabase locally and just wow in silence! I am impressed! This is the kind of tooling I would want for my team.
[IMG: @Rodrigo66799141 twitter image]
@Rodrigo66799141After a week of diving deep into Supabase for my new SaaS project, I'm really impressed with its Auth and RLS features. It makes security much simpler for solo founders. #buildinpublic #SaaS
[IMG: @xthemadgeniusx twitter image]
@xthemadgeniusxLately been using Supabase over AWS/ GCP for products to save on costs and rapid builds(Vibe Code) that do not need all the Infra and the hefty costs that come with AWS/ GCP out the door. Great solution overall.
[IMG: @pontusab twitter image]
@pontusabI love everything about Supabase.
[IMG: @viratt_mankali twitter image]
@viratt_mankaliLove how Supabase makes full stack features this easy. Using it with Next.js and loving the experience!
[IMG: @nerdburn twitter image]
@nerdburnIt’s fun, feels lightweight, and really quick to spin up user auth and a few tables. Almost too easy! Highly recommend.
[IMG: @patrickc twitter image]
@patrickcVery impressed by @supabase's growth. For new startups, they seem to have gone from "promising" to "standard" in remarkably short order.
[IMG: @Aliahsan_sfv twitter image]
@Aliahsan_sfvOkay, I finally tried Supabase today and wow... why did I wait so long? ? Went from 'how do I even start' to having auth + database + real-time updates working in like 20 minutes. Sometimes the hype is actually justified! #Supabase
[IMG: @yatsiv_yuriy twitter image]
@yatsiv_yuriySupabase is the best product experience I've had in years.
Not just tech - taste.
From docs to latency to the URL structure that makes you think "oh, that's obvious"
Feels like every other platform should study how they built it
@supabase I love you
[IMG: @adeelibr twitter image]
@adeelibr@supabase shout out, their MCP is awesome. It's helping me create better row securities and telling me best practises for setting up a supabase app
[IMG: @TyronBache twitter image]
@TyronBacheReally impressed with @supabase's Assistant.
It has helped me troubleshoot and solve complex CORS Configuration issues on Pinger.
[IMG: @MinimEditor twitter image]
@MinimEditorI’ve always used Supabase just as a database.
Yesterday, I helped debug a founder’s vibe-coding project built with React + React Router — no backend server.
The “backend” was entirely Supabase Edge Functions as the API.
First time using Supabase this way.
Impressive.
[IMG: @orlandopedro_ twitter image]
@orlandopedro_Love @supabase custom domains
makes the auth so much better
[IMG: @sdusteric twitter image]
@sdustericLoving #Supabase MCP. Claude Code would not only plan what data we should save but also figure out a migration script by checking what the schema looks like on Supabase via MCP.
[IMG: @SteinlageScott twitter image]
@SteinlageScottI love @supabase's built-in Advisors. The security and performance linters improve everything and boost my confidence in what I'm building!
[IMG: @BowTiedQilin twitter image]
@BowTiedQilinWorking with @supabase has been one of the best dev experiences I've had lately.
Incredibly easy to set up, great documentation, and so many fewer hoops to jump through than the competition.
I definitely plan to use it on any and all future projects.
[IMG: @adm_lawson twitter image]
@adm_lawsonLove supabse edge functions. Cursor+Supabase+MCP+Docker desktop is all I need
[IMG: @gokul_i twitter image]
@gokul_iFirst time running @supabase in local. It just works. Very good DX imo.
[IMG: @dadooos_ twitter image]
@dadooos_Run supabase locally and just wow in silence! I am impressed! This is the kind of tooling I would want for my team.
[IMG: @Rodrigo66799141 twitter image]
@Rodrigo66799141After a week of diving deep into Supabase for my new SaaS project, I'm really impressed with its Auth and RLS features. It makes security much simpler for solo founders. #buildinpublic #SaaS
[IMG: @xthemadgeniusx twitter image]
@xthemadgeniusxLately been using Supabase over AWS/ GCP for products to save o

[H1] Security at Supabase
[H2]
Supabase is trusted by thousands of developers for building and deploying secure applications.
[H3] Multi-factor Authentication#
Supabase allows users to enable Multi-factor authentication (MFA) on their account.
MFA adds an additional layer of security to your user account, by requiring a second factor to verify your user identity.
[H3] SOC 2#
Supabase is SOC 2 Type 2 compliant. This is an important security policy when handling sensitive customer data.Enterprise and Team customers can access our SOC 2 Type 2 report on the dashboard.
[H3] HIPAA#
Supabase is HIPAA compliant. You can store Protected Health Information (PHI) on our hosted platform once you enter into a Business Associate Agreement (BAA) with us and fulfill your HIPAA obligations under our shared responsibility model.Enterprise and Team customers can request to sign our BAA on the dashboard.
[H3] ISO 27001#
Supabase is ISO 27001 certified. ISO 27001 is an internationally recognized standard for information security management systems (ISMS), confirming that we maintain rigorous controls to protect customer data.Enterprise and Team customers can access our ISO 27001 certificate on the dashboard.
[H3] Data Encryption#
All customer data is encrypted at rest with AES-256 and in transit via TLS.Sensitive information like access tokens and keys are encrypted at the application level before they are stored in the database.
[H3] Role-based access control#
Members of organizations in Supabase can be granted access to specific resources.Read more about fine grained access controls including Read-Only and Billing-Only access.
[H3] Backups#
All paid customer databases are backed up every day.Point in Time Recovery allows restoring the database to any point in time. Customers from the Pro Plan have access to this feature as an add-on.
[H3] Payment processing#
Supabase uses Stripe to process payments and does not store personal credit card information for any of our customers.Stripe is a certified PCI Service Provider Level 1, which is the highest level of certification in the payments industry.
[H3] Vulnerability Management#
Supabase works with industry experts to conduct regular penetration tests.In addition to internal security reviews, we use various tools to scan our code for vulnerabilities including GitHub, Vanta, and Snyk.
[H3] DDoS Protection#
Supabase combats Distributed Denial of Service attacks in several ways to mitigate resource abuse and prevent runaway bills.In addition to protection at the CDN level via Cloudflare, we employ fail2ban to prevent brute force logins. Users can customize rate limits for critical API routes and set spend caps to prevent surprise bills.
[H2] Security Newsletter
Sign up for the Supabase Security Newsletter. Receive updates during security incidents.

Database
[H1]
Tables and DataTables are where you store your data.
Tables are similar to excel spreadsheets. They contain columns and rows.
For example, this table has 3 "columns" (id, name, description) and 4 "rows" of data:
idnamedescription1The Phantom MenaceTwo Jedi escape a hostile blockade to find allies and come across a young boy who may bring balance to the Force.2Attack of the ClonesTen years after the invasion of Naboo, the Galactic Republic is facing a Separatist movement.3Revenge of the SithAs Obi-Wan pursues a new threat, Anakin acts as a double agent between the Jedi Council and Palpatine and is lured into a sinister plan to rule the galaxy.4Star WarsLuke Skywalker joins forces with a Jedi Knight, a cocky pilot, a Wookiee and two droids to save the galaxy from the Empire's world-destroying battle station.
There are a few important differences from a spreadsheet, but it's a good starting point if you're new to Relational databases.
[H2] Creating tables#
When creating a table, it's best practice to add columns at the same time.
[IMG: Tables and columns]
You must define the "data type" of each column when it is created. You can add and remove columns at any time after creating a table.
Supabase provides several options for creating tables. You can use the Dashboard or create them directly using SQL.
We provide a SQL editor within the Dashboard, or you can connect to your database
and run the SQL queries yourself.
DashboardSQL
Go to the Table Editor page in the Dashboard.
Click New Table and create a table with the name todos.
Click Save.
Click New Column and create a column with the name task and type text.
Click Save.
When naming tables, use lowercase and underscores instead of spaces (e.g., table_name, not Table Name).
[H2] Columns#
You must define the "data type" when you create a column.
[H3] Data types#
Every column is a predefined type. Postgres provides many default types, and you can even design your own (or use extensions) if the default types don't fit your needs. You can use any data type that Postgres supports via the SQL editor. We only support a subset of these in the Table Editor in an effort to keep the experience simple for people with less experience with databases.
Show/Hide default data typesNameAliasesDescriptionbigintint8signed eight-byte integerbigserialserial8autoincrementing eight-byte integerbitfixed-length bit stringbit varyingvarbitvariable-length bit stringbooleanboollogical Boolean (true/false)boxrectangular box on a planebyteabinary data (“byte array”)charactercharfixed-length character stringcharacter varyingvarcharvariable-length character stringcidrIPv4 or IPv6 network addresscirclecircle on a planedatecalendar date (year, month, day)double precisionfloat8double precision floating-point number (8 bytes)inetIPv4 or IPv6 host addressintegerint, int4signed four-byte integerinterval [ fields ]time spanjsontextual JSON datajsonbbinary JSON data, decomposedlineinfinite line on a planelsegline segment on a planemacaddrMAC (Media Access Control) addressmacaddr8MAC (Media Access Control) address (EUI-64 format)moneycurrency amountnumericdecimalexact numeric of selectable precisionpathgeometric path on a planepg_lsnPostgres Log Sequence Numberpg_snapshotuser-level transaction ID snapshotpointgeometric point on a planepolygonclosed geometric path on a planerealfloat4single precision floating-point number (4 bytes)smallintint2signed two-byte integersmallserialserial2autoincrementing two-byte integerserialserial4autoincrementing four-byte integertextvariable-length character stringtime [ without time zone ]time of day (no time zone)time with time zonetimetztime of day, including time zonetimestamp [ without time zone ]date and time (no time zone)timestamp with time zonetimestamptzdate and time, including time zonetsquerytext search querytsvectortext search documenttxid_snapshotuser-level transaction ID snapshot (deprecated; see pg_snapshot)uuiduniversally unique identifierxmlXML data
You can "cast" columns from one type to another, however there can be some incompatibilities between types.
For example, if you cast a timestamp to a date, you will lose all the time information that was previously saved.
[H3] Primary keys#
A table can have a "primary key" - a unique identifier for every row of data. A few tips for Primary Keys:
It's recommended to create a Primary Key for every table in your database.
You can use any column as a primary key, as long as it is unique for every row.
It's common to use a uuid type or a numbered identity column as your primary key.
1create table movies (2 id bigint generated always as identity primary key3);
In the example above, we have:
created a column called id
assigned the data type bigint
instructed the database that this should be generated always as identity, which means that Postgres will automatically assign a unique number to this column.
Because it's unique, we can also use it as our primary key.
We could also use generated by default as identity, which would allow us to insert our own unique values.
1create table movies (2 id bigint generated by default as identity primary key3);
[H2] Loading data#
There are several ways to load data in Supabase. You can load data directly into the database or using the APIs.
Use the "Bulk Loading" instructions if you are loading large data sets.
[H3] Basic data loading#
SQLJavaScriptDartSwiftPythonKotlin1insert into movies2 (name, description)3values4 (5 'The Empire Strikes Back',6 'After the Rebels are brutally overpowered by the Empire on the ice planet Hoth, Luke Skywalker begins Jedi training with Yoda.'7 ),8 (9 'Return of the Jedi',10 'After a daring mission to rescue Han Solo from Jabba the Hutt, the Rebels dispatch to Endor to destroy the second Death Star.'11 );
[H3] Bulk data loading#
When inserting large data sets it's best to use Postgres's COPY command.
This loads data directly from a file into a table. There are several file formats available for copying data: text, CSV, binary, JSON, etc.
For example, if you wanted to load a CSV file into your movies table:
1"The Empire Strikes Back", "After the Rebels are brutally overpowered by the Empire on the ice planet Hoth, Luke Skywalker begins Jedi training with Yoda."2"Return of the Jedi", "After a daring mission to rescue Han Solo from Jabba the Hutt, the Rebels dispatch to Endor to destroy the second Death Star."
You would connect to your database directly and load the file with the COPY command:
1psql -h DATABASE_URL -p 5432 -d postgres -U postgres \2 -c "\COPY movies FROM './movies.csv';"
Additionally use the DELIMITER, HEADER and FORMAT options as defined in the Postgres COPY docs.
1psql -h DATABASE_URL -p 5432 -d postgres -U postgres \2 -c "\COPY movies FROM './movies.csv' WITH DELIMITER ',' CSV HEADER"
If you receive an error FATAL: password authentication failed for user "postgres", reset your database password in the Database Settings and try again.
[H2] Joining tables with foreign keys#
Tables can be "joined" together using Foreign Keys.
[IMG: Foreign Keys]
This is where the "Relational" naming comes from, as data typically forms some sort of relationship.
In our "movies" example above, we might want to add a "category" for each movie (for example, "Action", or "Documentary").
Let's create a new table called categories and "link" our movies table.
1create table categories (2 id bigint generated always as identity primary key,3 name text -- category name4);56alter table movies7 add column category_id bigint references categories;
You can also create "many-to-many" relationships by creating a "join" table.
For example if you had the following situations:
You have a list of movies.
A movie can have several actors.
An actor can perform in several movies.
DashboardSQL
[H2] Schemas#
Tables belong to schemas. Schemas are a way of organizing your tables, often for security reasons.
[IMG: Schemas and tables]
If you don't explicitly pass a schema when creating a table, Postgres will assume that you want to create the table in the public schema.
We can create schemas for organizing tables. For example, we might want a private schema which is hidden from our API:
1create schema private;
Now we can create tables inside the private schema:
1create table private.salaries (2 id bigint generated by default as identity primary key,3 salary bigint not null,4 actor_id bigint not null references public.actors5);
[H2] Views#
A View is a convenient shortcut to a query. Creating a view does not involve new tables or data. When run, an underlying query is executed, returning its results to the user.
Say we have the following tables from a database of a university:
students
idnametype1Princess Leiaundergraduate2Yodagraduate3Anakin Skywalkergraduate
courses
idtitlecode1Introduction to PostgresPG1012Authentication TheoriesAUTH2053Fundamentals of SupabaseSUP412
grades
idstudent_idcourse_idresult111B+213A+322A431A-532A633B-
Creating a view consisting of all the three tables will look like this:
1create view transcripts as2 select3 students.name,4 students.type,5 courses.title,6 courses.code,7 grades.result8 from grades9 left join students on grades.student_id = students.id10 left join courses on grades.course_id = courses.id;1112grant all on table transcripts to authenticated;
Once done, we can now access the underlying query with:
1select * from transcripts;
[H3] View security#
By default, views are accessed with their creator's permission ("security definer"). If a privileged role creates a view, others accessing it will use that role's elevated permissions. To enforce row level security policies, define the view with the "security invoker" modifier.
1-- alter a security_definer view to be security_invoker2alter view <view name>3set (security_invoker = true);45-- create a view with the security_invoker modifier6create view <view name> with(security_invoker=true) as (7 select * from <some table>8);
[H3] When to use views#
Views provide several benefits:
Simplicity
Consistency
Logical Organization
Security
[H4] Simplicity#
As a query becomes more complex, it can be a hassle to call it over and over - especially when we run it regularly. In the example above, instead of repeatedly running:
1select2 students.name,3 students.type,4 courses.title,5 courses.code,6 grades.result7from8 grades9 left join students on grades.student_id = students.id10 left join courses on grades.course_id = courses.id;
We can run this instead:
1select * from transcripts;
Additionally, a view behaves like a typical table. We can safely use it in table JOINs or even create new views using existing views.
[H4] Consistency#
Views ensure that the likelihood of mistakes decreases when repeatedly executing a query. In our example above, we may decide that we want to exclude the course Introduction to Postgres. The query would become:
1select2 students.name,3 students.type,4 courses.title,5 courses.code,6 grades.result7from8 grades9 left join students on grades.student_id = students.id10 left join courses on grades.course_id = courses.id11where courses.code != 'PG101';
Without a view, we would need to go into every dependent query to add the new rule. This would increase in the likelihood of errors and inconsistencies, as well as introducing a lot of effort for a developer. With views, we can alter just the underlying query in the view transcripts. The change will be applied to all applications using this view.
[H4] Logical organization#
With views, we can give our query a name. This is extremely useful for teams working with the same database. Instead of guessing what a query is supposed to do, a well-named view can explain it. For example, by looking at the name of the view transcripts, we can infer that the underlying query might involve the students, courses, and grades tables.
[H4] Security#
Views can restrict the amount and type of data presented to a user. Instead of allowing a user direct access to a set of tables, we provide them a view instead. We can prevent them from reading sensitive columns by excluding them from the underlying query.
[H3] Materialized views#
A materialized view is a form of view but it also stores the results to disk. In subsequent reads of a materialized view, the time taken to return its results would be much faster than a conventional view. This is because the data is readily available for a materialized view while the conventional view executes the underlying query each time it is called.
Using our example above, a materialized view can be created like this:
1create materialized view transcripts as2 select3 students.name,4 students.type,5 courses.title,6 courses.code,7 grades.result8 from9 grades10 left join students on grades.student_id = students.id11 left join courses on grades.course_id = courses.id;
Reading from the materialized view is the same as a conventional view:
1select * from transcripts;
[H3] Refreshing materialized views#
Unfortunately, there is a trade-off - data in materialized views are not always up to date. We need to refresh it regularly to prevent the data from becoming too stale. To do so:
1refresh materialized view transcripts;
It's up to you how regularly refresh your materialized views, and it's probably different for each view depending on its use-case.
[H3] Materialized views vs conventional views#
Materialized views are useful when execution times for queries or views are too slow. These could likely occur in views or queries involving multiple tables and billions of rows. When using such a view, however, there should be tolerance towards data being outdated. Some use-cases for materialized views are internal dashboards and analytics.
Creating a materialized view is not a solution to inefficient queries. You should always seek to optimize a slow running query even if you are implementing a materialized view.
[H2] Resources#
Official Docs: Create table
Official Docs: Create view
Postgres Tutorial: Create tables
Postgres Tutorial: Add column
Postgres Tutorial: Views
[H3] Is this helpful?
No Yes
[H3] AI Tools
Copy as MarkdownAsk ChatGPTAsk Claude