Metabase is scalable, battle-hardened software used by tens of thousands of companies to deliver high quality, self-service analytics. It supports high availability via horizontal scaling, and it’s efficient out of the box: a single core machine with 4 gigs of RAM can scale Metabase to hundreds of users.
This article provides high-level guidance and best practices on how to keep Metabase running smoothly in production as the numbers of users and data sources increase.
- Factors that impact Metabase performance and availability.
- Vertical scaling. Running a single instance of Metabase with more cores and memory.
- Horizontal scaling. Running multiple instances of Metabase. Horizontal scaling can improve availability/reliability of your Metabase application.
- Data warehouse tuning. Way out of scope for this article, but we’ll discuss some general strategies for data warehousekeeping.
- Metabase application best practices. Tactics for improving the performance of questions and dashboards.
- Hosted Metabase. Let Metabase handle application operation for you.
Each data system is different, so we can only discuss scaling strategies at a high level, but you should be able to translate these strategies to your particular environment and usage.
Factors that impact Metabase performance and availability
Metabase scales well (both vertically and horizontally), but Metabase is only one component of your data warehouse, and the overall performance of your system will depend on the composition of your system and your usage patterns.
Major factors that impact your experience using Metabase include:
- The number of databases connected to Metabase.
- The number of tables in each database.
- The efficiency of your data warehouse.
- The number of questions in your dashboards.
For example, it won’t matter how many instances of Metabase you run if a question needs to run a query that your database(s) takes 30 minutes to run. That’s just going to take 30 minutes. The solution in that case is either to re-evaluate your need for that data (do you really need all that info every time?), or to find ways to improve the performance of your database, such as reorganizing, indexing, or caching your data.
The number of databases and tables can also affect client performance, but only in large-scale situations where you’re managing hundreds of databases and/or thousands of tables, as the metadata itself can be a lot to query. To help keep performance smooth even at this scale, you can manage when Metabase syncs its metadata with your connected databases.
Now let’s make sure your Metabase application is well-tuned to scale.
Vertical scaling is the brute force approach. Give Metabase more cores and memory, and it will have more resources available to do its work. If you are experiencing performance issues related to the application itself (i.e., unrelated to the breadth and magnitude of your databases), running Metabase on a more powerful machine can improve performance.
That said, Metabase is already efficient out of the box. For example, for a starter Metabase instance on AWS, we recommend running Metabase using Elastic Beanstalk on a
t3.small instance, and scaling up from there. That’s a dual core machine with 2 gigabytes of RAM. Machines with 4-8 gigs of RAM should handle hundreds of users, and you can bump the number of cores and gigabytes of memory if needed.
While adding more cores and memory can be effective, you’re generally better off using horizontal scaling to support more users. The reason is because there are database connectivity limits built into each Metabase instance to prevent the instance from overwhelming your data warehouse with requests. You can increase the number of connections available for your instance, but we still recommend multiple instances.
Horizontal scaling involves running multiple instances of Metabase, in combination with a load balancer, to direct traffic to the instances. Metabase is set up for horizontal scaling out of the box, so you don’t need any special configuration to run multiple instances of Metabase.
The primary use cases for horizontal scaling is to improve reliability (a.k.a. “high availability”), but horizontal scaling can also improve multi-user performance. When the load is balanced, a high-traffic, CPU-bound Metabase instance will perform better (faster) when some of its traffic is directed to other instances, as the CPU load will be distributed across multiple machines.
Metabase ships with a local H2 database to store your application data (all of your questions, dashboards, logs, and other Metabase data), but when running in production you should upgrade to a relational database (like PostgreSQL) running on a separate server. In fact, when scaling horizontally, you must use a relational database that runs on a separate server to store your application data. That way, all instances of Metabase can share a common database. We recommend an external database on a separate server for all production instances, even if you only ever run one instance of Metabase, so an external database is not an added cost for horizontal scaling.
Metabase uses the external application database to store user session data, so people don’t have to worry about losing saved work if one or all Metabase instances go down, and administrators don’t have to deal with configuring sticky sessions to ensure people are connected to the right Metabase instance. The load balancer will route people to an available instance so they can keep right on working.
Taking advantage of time-based horizontal scaling
Some customers adjust the number of Metabase instances based on the time of day. For example, some companies will spin up multiple instances of Metabase in the morning to handle a burst of traffic when people log in and run their morning dashboards, then spin the extra Metabase instances down in the afternoon (or at night, or on the weekends) to save money on cloud spend.
If you’re running Metabase on AWS using our Elastic Beanstalk deployment, you can configure both resource-based scaling triggers and time-based scaling. For other environments, like Kubernetes or Google Cloud Platform, you’ll need to refer to each system’s respective documentation to set up similar autoscaling rules.
Straightforward load balancing
Load balancers direct traffic to multiple Metabase instances to ensure that each request gets the fastest response. If one instance of Metabase goes down temporarily, the load balancer will route requests to another available instance.
Setting up a load balancer with Metabase is simple. Metabase’s API exposes a health check endpoint,
/api/health, that load balancers can call to determine whether a Metabase instance is up and responding to requests. If the instance is healthy, the endpoint will return an HTTP status code
200 OK. Otherwise, the load balancer will know to route the request to another instance.
See our guide to running Metabase on AWS Elastic Beanstalk to see an example of setting up a load balancer to use the
Data warehouse tuning
Architecting a data warehouse is beyond the scope of this article, but you should know that your queries in Metabase will only be as fast as your databases can return data. If you have questions that ask for a lot of data that takes your database a long time to retrieve, those query times will impact your experience, regardless of how fast Metabase is.
Here are three ways you can improve data warehouse performance:
- Structure your data in a way that anticipates the questions people will ask. Identify your usage patterns and store your data in a way that make its easy to return results for questions common in your organization. Compose ETLs to create new tables that bring together frequently queried data from multiple sources.
- Tune your databases. Read up on the documentation for your databases to learn how to improve their performance via indexing, caching, and other optimizations.
- Filter your data. Encourage people to filter data when asking questions. They should also take advantage of Metabase’s data exploration tools (including record previews) so they only query data relevant to the question they’re trying to answer.
- Database or data warehouse? Folks often get started with Metabase using a transactional database like MySQL or Postgres. While these databases scale quite well, they often aren’t optimized for the type of analytical queries that Metabase will use. Operations like
maxcan slow down once you reach a certain scale. As analytics adoption grows, you may find the need to explore dedicated data warehouses like Amazon Redshift, Google BigQuery, or Snowflake.
Metabase application best practices
Here are some strategies to get the most out of your Metabase application:
- Only ask for the data you need.
- Use a managed relational database to store your Metabase application data.
- Cache your queries.
- Look for bottlenecks.
- Increase the maximum number of connections to the Metabase application database.
- Increase the maximum number of connection to each database.
- Sync with your databases only when you need to.
- Upgrade to the latest version of Metabase.
- Keep your browser up to date.
Only ask for the data you need
If people are running a lot of queries that return a lot of records, it won’t matter that Metabase is fast: the users will get their data only as fast as your data warehouse can return the requested records.
And sometimes people go overboard with dashboards, loading them up with 50 questions or more. When a dashboard with 50 questions loads, it sends 50 simultaneous requests asking for data. And depending on the size of that database, it can be quite some time before those records return.
But that’s not the whole story. Metabase doesn’t slow down simply because you put more questions in your dashboard. If your questions don’t pull a lot of data, or your data warehouse can return results in under a second, 50 questions will load quickly.
In general, however, encourage your users to keep their dashboards focused. Dashboards are meant to tell a story about your data, and you can tell a good story with just a handful of questions (or even a single question). Take advantage of Metabase’s data exploration tools to learn about your data (such as the ability to preview records in tables) so you can dial in on only the records you need to answer your questions.
So make sure each question is necessary to complete the dashboard, and be especially mindful when querying data across time or space, as you can filter out a lot of unnecessary data by restricting your question to a shorter timespan or a smaller area.
Use a managed relational database to store your Metabase application data
The application database stores all of your questions, dashboards, collections, permissions, and other data related to the Metabase application. You can use an relational database (like PostgreSQL or MySQL) to manage your application database, but we recommend a managed solution like AWS RDS. RDS will automate backups and make it easy for you to adjust storage and compute as you scale, giving you one less thing to worry about. Managed database solutions are especially useful for Enterprise customers who take advantage of Metabase’s auditing functionality, as enabling auditing will increase the amount of data Metabase stores in the application database.
Cache your queries
You can configure caching on questions to store their results.
Metabase will show users the timestamp for the results, and users can manually refresh the results of the question if they want to rerun the query. Caching is suitable for results that do not update frequently.
Look for bottlenecks
Metabase’s Enterprise Edition offers auditing tools for you to monitor the usage and performance of your application.
You can, for example, see how many questions are being asked, by whom, and how long the questions took to run, which can help identify any bottlenecks that need attention.
Increase the maximum number of connections to the Metabase application database
The default number of connections to the Metabase application database is 15 (MB_APPLICATION_DB_MAX_CONNECTION_POOL_SIZE). If your usage regularly consumes all 15 connections, you can improve performance by increasing the maximum number of connections. Alternatively, you can increase the number of connections via horizontal scaling (e.g., if you add an additional Metabase instance, you effectively add an additional 15 connections to the application database).
You can check the number of connections by viewing the logs, and checking for lines with
... App DB connections: 12/15. In that example, Metabase is using 12 out of the 15 available application database connections.
Increase the maximum number of connections to each database
Similarly, the default maximum number of connections for a single Metabase instance to each database is 15. That’s 15 for each database, so if you’ve connected Metabase to two databases, you’ll have a maximum of 30 connections.
You can increase the maximum number of connections to each database by changing the MB_JDBC_DATA_WAREHOUSE_MAX_CONNECTION_POOL_SIZE environment variable. As above with the application database connections, you can also increase the number of connections via horizontal scaling. Each additional Metabase instance would increase the maximum number of connections by 15 (or by whatever maximum you’ve set). To learn more, see our documentation on environment variables.
Sync with your databases only when you need to
By default, Metabase performs a lightweight sync every hour. The sync does not copy any of your data. Metabase merely checks to make sure the list of tables, columns, and rows it maintains in its application database is up to date with the tables, columns, and rows in your databases.
For large databases, you might consider limiting the number of times Metabase performs the sync, and restricting those synchronizations to off-peak hours, especially if you aren’t frequently adding new tables to your database.
Upgrade to the latest version of Metabase
If you haven’t already, we recommend you upgrade to the latest Metabase version to get the most recent performance improvements.
Serve Metabase via HTTPS over HTTP/2
Serving your Metabase instance via HTTPS over HTTP/2 can increase performance, since browsers on HTTP/1.1 can limit connections to ~6 concurrent connections per domain, whereas HTTP/2 is multiplexed on a single connection. More available connections won’t fix a slow database, or an overloaded Metabase instance that has run out of threads, but you’ll at least know that your browser isn’t throttling your connections.
Keep your browser up to date
There are many ways to set up Metabase; here are some of our favorites:
AWS Elastic Beanstalk
Check out our guide to setting up Metabase on Elastic Beanstalk. We use Elastic Beanstalk to host our internal Metabase application.
Docker & Kubernetes
Other cloud providers
If you don’t want to deal with the care and feeding of a Metabase application, Metabase
will soon now offers a hosted solution. You still have to ensure your data sources are performant, but you no longer have to manage running the Metabase application.
You can check out our Operations Guide in our documentation for more information on maintaining a Metabase instance.
If you still have questions, chances are someone’s already had the same question. Check out the Metabase discussion forum and search for your issue. If you can’t find a solution, submit a question of your own.