Supabase Security Advisor & Performance Advisor

We're dropping some handy tools in Supabase Studio this week to help with security and performance:

1. Security Advisor: for detecting insecure database configuration
2. Performance Advisor: for suggesting database optimizations
3. Index Advisor: for suggesting indexes on slow-running queries

Learn more about GA Week

We announced General Availability this week, reaching a point where we feel confident our organization can support all types of customers and help them become successful, regardless of their demands. It's a big milestone after four years of building.

As we've grown up as a company, so too have our customers. Many of you have been with us since the start and have seen your projects grow from 0 to literally millions of users, scaling from the free plan up to the largest size servers we offer.

Helping you help yourself

Along with this growth, we've learned many lessons about the types of issues developers encounter using Postgres, especially as they start to get traction. We've built tooling, documentation, and support processes around common issues related to security, performance, resource usage, and slow queries.

As we've helped hundreds of thousands of customers through issues like these, a trend emerged: developers want their problems resolved quickly, but they also want to know what happened and why. This is the typical profile of a Supabase developer - thoughtful, curious, and hungry to learn more about the inner workings of Postgres.

This week, we're adding features into Supabase Studio to address common issues as you scale up. These are powered by tools that we have open sourced this week: index_advisor and splinter (Supabase Postgres linter").

Security Advisor

This week we're adding a Security Advisor to Supabase Studio. This is a new interface for exploring security issues with your database because, well, sometimes even Postgres veterans get it wrong. The Security Advisor runs a set of queries on your database to identify configuration issues.

The Security Advisor is helpful in pointing out security issues that you might have forgotten or not yet be aware of: some lints are general purpose for Postgres projects, while others are specific to Supabase.

As with all of our tooling, it's designed to both help and to teach. The suggestions are well-documented with a rationale, descriptions, examples, and remediation steps. Did you know, for example, that views don't respect RLS policies unless you've set security_invoker=on? Now you do!

Performance Advisor

While database tuning is a speciality on its own, many projects have simple optimizations to improve performance. We're releasing a new Performance Advisor in Supabase Studio to surface the low-hanging fruit.

The Performance Advisor checks for misconfigurations, like tables with unindexed foreign key columns, inefficient RLS policies, or columns with duplicate indexes. As a project grows, issues like this can sneak in and slow your projects down (and fill up your disks).

If you're looking for ways to speed up your database, this is the place to start.

Bonus: Index Advisor

Speaking of performance, we have another treat for you. Last week, we announced PostgreSQL index advisor on Hacker News. This is a Postgres extension that can determine if a given query should have an index. It's already proving useful:

Awesome, the Index Advisor sped my slowest query 4x!
noob-4-life on Hacker News

The Supabase Index Advisor is now available inside Supabase Studio. We've integrated the Index Advisor into our existing Query Performance tool so that you can find your slowest queries and check recommendations. As its name suggests, this analyzes your queries and make recommendations to add or remove table indexes.

What is an index?

Not sure what an index is? Imagine having to look up a person's name in a phonebook where the entries are not alphabetical. This is what your database tables look like by default. Finding a number from a randomly sorted list of records would take a long time. When you add an index, the database stores the sorted values, allowing it to quickly locate a row without having to search through every record sequentially.

This is just the beginning of our plan to make automated data analysis tooling available to all developers. Even if you're experienced with databases, this will be a huge help with the optimization work you have already planned to do. If you're new to databases, the Index Advisor will help you level-up, surfacing issues and showing you how to fix them.

Let's have a look at some queries:

This query doesn't need an index

This query would benefit from an index

What's next

We plan to expand the set of suggestions available in Studio to cover more areas of potential improvement for security and performance. Some of the ideas we have in mind for the future include:

• checking for liberally-permissioned columns that contain personally identifiable information (PII)
• identifying bloated tables/indexes
• advanced Postgres configuration
• suggestions for tightening up Supabase Auth as you move into production

Contributions welcome

Community feedback plays a key role in helping us determine where to invest time in developing future lints. We encourage contributions by suggesting new lints or enhancements.

If you have ideas for new lints or wish to report problems you can open an issue on our GitHub repository splinter or index_advisor.

More on GA week

• Supabase Swift
• Supabase on the AWS Marketplace
• Supabase Open Source Hackathon 2024
• Supabase Bootstrap
• Branching now Publicly Available