How Apache Superset connects to PostgreSQL for analytics
Superset connects to PostgreSQL through the psycopg2 driver, which ships bundled with the Superset Docker images, using a postgresql://{username}:{password}@{host}:{port}/{database} connection string on default port 5432. You want dashboards over PostgreSQL data without putting load on the operational database or handing Superset the keys to the metadata store. The fix is to treat these as two separate database connections with different owners, different credentials and different purposes. The analytics source is the database Superset queries when someone builds a chart. The metadata database is where Superset keeps its own state: users, roles, dashboards, saved charts, database connection definitions and, when enabled, alerts and report schedules.
Keeping them apart matters for three reasons. Superset writes to its metadata database during normal use, so pointing an analytics connection at the same cluster or schema lets chart queries compete with application state and makes restores harder to reason about. Credentials also differ: the metadata user needs write access to its own schema, while an analytics connection should only ever read. Finally, the metadata database is the one you back up before every upgrade, as the upgrade guidance requires, and you want that backup to hold Superset state only. Confirm with your DBA whether the analytics source is a replica, a reporting database or the primary, because that choice decides how much query load the dashboards can safely place on it.
The metadata database and analytics source should be different servers or at least different clusters in your plan. Ask the PostgreSQL team which instance they will expose for analytics, whether a hot standby is available for read-only queries, and which connection pooler sits in front of it. Then confirm the supported PostgreSQL major version in your environment against the PostgreSQL versioning policy before you write the connection into Superset, since version support windows drive when the source itself must be upgraded.
What you need before connecting Superset to PostgreSQL
Connecting Superset to PostgreSQL depends on four things lining up: the Superset release and how it is deployed, the PostgreSQL versions on both sides, the network path from Superset workers to the analytics server, and the certificates used for SSL. Work through these cards before any connection string is written, because each one changes what your team has to build and test.
Superset version and deployment method
Pin the Superset release you will run and record how it is installed, whether from a container image, PyPI or the Helm chart. The psycopg2 driver ships in the Superset Docker images, so a container deployment is the simplest path. For PyPI or custom images, confirm the driver is present before testing. Check the upgrade notes for the release you choose, since the driver and dialect behavior can change between major versions.
Metadata database versions
Superset's own metadata database is separate from the analytics source you are connecting. The configuration documentation lists PostgreSQL 10 to 17 as supported for that metadata role, so confirm the version you plan to use falls in that range. Do not point the metadata role at the same server you use for analytics, and do not run the Docker Compose PostgreSQL container in production.
Analytics PostgreSQL version support
The analytics server is the database your users query, so its support window matters as much as Superset's. Confirm whether your server runs a supported major version, since PostgreSQL 14 is supported until November 12, 2026, while 15, 16, 17 and 18 have later end dates. Plan an upgrade before a version reaches end of support, and confirm that your server version is in the range your Superset release is tested against.
Network path from workers to the database
Every Superset process that runs queries, including the web server and any Celery workers used for async queries, alerts and reports, needs a route to the analytics server on port 5432 unless you have changed it. Map which hosts and subnets make that connection, and check firewall rules, security groups and any connection pooler in between. Confirm that worker nodes in every environment can reach the same endpoint, since a connection that works from the web tier can still fail for scheduled reports.
Certificates for SSL connections
Decide how encryption in transit will work before you configure the connection. Confirm whether the server presents a certificate issued by an internal or public authority, obtain the CA bundle that Superset containers must trust, and agree who owns renewal. Test that the certificate chain validates from a worker, not only from a workstation, because the trust store inside a container often differs from the host.
Compatibility with TimescaleDB and YugabyteDB
The Superset database catalog lists TimescaleDB and YugabyteDB under the same PostgreSQL driver, with YugabyteDB on port 5433. Listing a driver does not guarantee that every extension, function or replication feature behaves the same as in PostgreSQL. If either product is your analytics source, ask the vendor which Superset releases they test against and run your own query and dashboard tests before you rely on it.
Configuration sequence for a PostgreSQL analytics source
This sequence takes a PostgreSQL database from a dedicated read-only role to a tested Superset connection and a validated sample dataset. Each step ends with a check you can run before moving on, so a failure is traced to one change rather than to the whole setup.
Confirm the source database and its replica
Decide which PostgreSQL instance Superset will query. A hot standby or a separate reporting database is preferred over the primary, because dashboard queries compete with transactional work for CPU, memory and I/O. PostgreSQL supports hot standby servers that serve read-only queries, so a standby is a valid option if your team operates one. Confirm the PostgreSQL major version against the supported list in the PostgreSQL versioning policy, and confirm that the instance is reachable from the Superset worker network on port 5432 or your custom port. Done when your team has a written host, port, database name and version, and a successful connection from the Superset host using the psql client.
Create a read-only analytics role
A database administrator creates a login role dedicated to Superset analytics. Grant CONNECT on the target database, USAGE on the schemas Superset should expose, and SELECT on the specific tables or views needed, and set default privileges so future tables in those schemas are handled by the same policy. Do not grant write privileges, superuser, or ownership of the tables. Use a strong password stored in your secrets manager, and set the role to allow only the connection limits your team chooses. Done when a psql session logged in as this role can run SELECT against the sample table and fails with a permission error on an INSERT or CREATE TABLE attempt.
Confirm the psycopg2 driver in the image
The Superset database catalog lists psycopg2 as the PostgreSQL driver and notes that it comes bundled with Superset Docker images. If you build your own image or install Superset from PyPI, the driver is not guaranteed to be present, so confirm it. Run a Python import of psycopg2 inside the same container or virtual environment that runs the Superset web and worker processes, not on the host. Done when the import succeeds in each runtime that connects to the database, and your team has recorded the driver version in its image manifest.
Add the database with connection string and SSL options
In Superset, open the database connection settings and add a PostgreSQL database. Use the connection string format postgresql://{username}:{password}@{host}:{port}/{database} and enter the password through the form field or a secret reference rather than pasting it into shared notes. For encrypted connections, set SSL parameters such as sslmode in the connection's extra parameters, and provide any CA certificate your environment requires. Confirm the exact SSL parameter names against the PostgreSQL client documentation for the driver version in your image. Done when the database entry saves without errors and the stored connection shows the read-only role, not an administrator account.
Test the connection from Superset
Use the test connection action in the database settings. A success message confirms that the Superset process can reach the host, that the TLS handshake completes if you enabled it, and that the role can authenticate. If the test fails, check the error text against each cause in turn: DNS or routing to the host, the port, the pg_hba.conf rule that matches the client address and method, the certificate chain, and the role password. Done when the test connection returns success from the Superset web container and the same test passes from the worker container if you use async queries or scheduled reports.
Validate a sample dataset under the read-only role
Add one table or view as a dataset in Superset, open SQL Lab, and run a bounded query such as a SELECT with a LIMIT clause against that dataset. Confirm that the row count and a sample of values match what the same query returns in psql under the same role. Then run an intentional write statement in SQL Lab and confirm PostgreSQL rejects it for permission reasons. Done when the bounded query returns the expected rows and the write attempt fails with a permission error, which shows the role controls, not only Superset, limits what the connection can do.
Record the configuration and access decisions
Write down the chosen host, database, role name, schemas and tables granted, SSL mode, driver version and the location of the stored password. Note who approved the role grants and who can change the Superset database entry. Keep this record with the change ticket so the next upgrade or role review starts from a known state. Done when the record is stored with your other platform documentation and each item matches the live Superset and PostgreSQL configuration you tested.
Choosing the PostgreSQL source: primary, replica or reporting copy
Connecting Superset directly to the primary is the simplest option. Dashboards read the same data the application writes, so there is no replication lag and no extra server to run. The cost is that every chart query, filter change and scheduled report competes with transactions for CPU, memory, I/O and locks on the database that runs your business. Analytical queries that scan large tables can slow checkout, sign-in or order processing, and a runaway query is harder to contain when it hits production. This option suits small datasets, low concurrency and teams that accept the risk in exchange for simplicity. Limit it with a read-only role, a statement timeout for the Superset connection and clear rules on which schemas dashboards may touch.
- Load: all dashboard traffic lands on the production primary, so query shape and concurrency directly affect transactional latency.
- Freshness: data is current to the last commit, which suits operational views that must reflect the latest state.
- Failover: Superset follows whatever the connection string points to, so a primary failover requires updating or redirecting that connection.
- Access control: the same server holds application credentials, so a read-only role must be created and tested rather than assumed.
- Recovery: a heavy report can delay recovery work during an incident, so plan maintenance windows for large queries.
Before you choose this option, ask the database team to show current peak load and the statement timeouts they already enforce.
A hot standby is a server that replays changes from the primary and accepts read-only queries. Pointing Superset at a standby moves dashboard load off the primary while keeping data close to current. PostgreSQL supports both synchronous replication, where a commit waits for standbys, and asynchronous replication, where it does not. Under asynchronous replication, the standby can trail the primary, so dashboards may show slightly older data, and long-running queries on a standby can conflict with replay and be cancelled depending on settings. Confirm those settings with your DBA before you rely on the standby for reporting. The standby also gives you a failover candidate, but only if you have a plan for promotion.
- Load: dashboard queries run on a separate server, which protects the primary from scans and sorts.
- Freshness: lag depends on replication mode, network and replay speed, so measure it and show users how current the data is.
- Query cancellation: settings that govern replay conflicts can end long queries, which affects how slow dashboards behave.
- Failover: PostgreSQL does not detect a primary failure or promote a standby by itself, so automatic failover needs external tools with their own testing.
- Connections: Superset needs a stable endpoint, often a proxy or DNS name, so that a promotion does not require editing every connection.
Check the standby's replication lag, its query cancellation settings and the documented promotion procedure before you name it in the Superset connection.
A separate reporting database receives copies of selected tables through logical replication or another pipeline, such as scheduled exports or change data capture. Logical replication works between different PostgreSQL major versions, so the reporting copy can run a different version from the source, which helps during staged upgrades. This option gives the most isolation: you can add indexes, summary tables and retention rules that would be wrong on the operational system. It also adds the most moving parts. You own the replication setup, the monitoring of its lag and the handling of schema changes, and the reporting copy becomes another system that auditors and incident responders must account for.
- Load: reporting indexes and aggregates live on a system that serves no transactions, so tuning does not touch production.
- Freshness: data is as current as the pipeline allows, so set and publish a target for lag.
- Schema drift: new columns and table changes on the source must be reflected in the subscription or pipeline, or dashboards silently break.
- Failover: the reporting copy is usually not a failover target for the application, so decide whether losing it is acceptable.
- Data scope: copying only needed tables limits exposure of sensitive data to the analytics tier.
Ask who owns the replication setup after go-live and how schema changes will reach the reporting copy before you commit to this design.
How PostgreSQL schemas, roles and Superset objects map
This table shows which PostgreSQL object each Superset concept points to, who owns it, and where access is actually enforced. Read it as a checklist for the connection design: the database role decides what data can be read, while Superset roles only decide what users can see and do inside Superset.
| Superset concept | PostgreSQL object | Who owns or controls it | Where access is enforced |
|---|---|---|---|
| Database connection | One PostgreSQL database, reached with a connection string on port 5432 by default | Platform team creates the connection in Superset with a dedicated login role | Authentication and privileges on the PostgreSQL server, not in Superset |
| Dataset | A table or view in a named schema, such as analytics.orders | Data owner defines which views are exposed, usually as read-only views | Grants on the schema and object for the read-only role |
| Chart and dashboard | Queries built on datasets, executed by the connection's role | Dashboard authors in Superset, within their Superset role | Superset roles limit who can open or edit content, not which rows PostgreSQL returns |
| Metadata database | A separate PostgreSQL or MySQL database holding Superset's own tables | Superset administrators, with its own backup and upgrade routine | Never granted to the analytics read-only role |
| Row-level restriction | Filters or row security defined in PostgreSQL, if the organization uses them | Database administrators confirm the policy works for the analytics login | Confirm in PostgreSQL. Superset row level security is a separate control to verify |
Security controls for the PostgreSQL connection
The Superset connection to PostgreSQL is a standing path from dashboards to your data, so its security depends on several controls working together. The cards below cover the database role, how Superset authenticates to PostgreSQL, whether the server identity is verified, where the credentials live, and how much work a dashboard can push to the database.
Dedicated read-only database role
Create a separate PostgreSQL role for Superset that holds only CONNECT on the target database, USAGE on the schemas it needs, and SELECT on the specific tables or views behind approved datasets. It should have no INSERT, UPDATE, DELETE, CREATE or DDL rights, and it should not own any objects. Check the role with a test query that attempts a write and confirms it is refused. Keep this role separate from the one Superset uses for its metadata database.
SCRAM password authentication
Set password_encryption to scram-sha-256 on the server and require scram-sha-256 in pg_hba.conf for the Superset role. PostgreSQL 18 deprecated MD5 password authentication in favor of SCRAM, so older MD5 entries should be found and removed during the build. Confirm the driver in your Superset image negotiates SCRAM before relying on it in production.
Certificate and other client authentication
PostgreSQL documents certificate, GSSAPI, LDAP and OAuth methods alongside passwords. Certificate authentication removes a stored password for the Superset role, but it depends on certificate issuance, rotation and the certificate being mounted into the Superset container. Choose a method your team can operate, document the rotation owner, and test a failed authentication so you know how the connection fails.
Server certificate verification
Enable TLS on the PostgreSQL server and configure the Superset connection to verify the server certificate, not only encrypt the traffic. Confirm which sslmode value the driver accepts in your Superset version and whether a root CA file must be supplied to the container. An unverified connection can be redirected to a server that presents any certificate, so ask the database team to confirm the CA chain and hostname.
Connection secret handling
The connection string holds the username and password, so it should never be typed into the Superset UI and saved in plain text by default. Store the password in an environment variable or secrets manager and reference it when the connection is created, then restrict who can read or edit database connections in Superset. Also confirm that SECRET_KEY is set to a value you control, because it protects the encrypted connection data stored in the metadata database.
Limiting what dashboards can query
Dashboards run whatever SQL their charts and datasets generate, so limit the damage at the database. Use a dedicated role, grant access to curated views instead of base tables where possible, apply row-level security in PostgreSQL if data must be split by user, and set statement_timeout for the role so a runaway chart cannot hold resources. Confirm with the database owner which timeout and connection limits are acceptable for the reporting workload.
Common PostgreSQL connection and performance failures
This table lists the failures teams most often hit when Superset reads from PostgreSQL, with the likely cause and the first fix to check for each. Read it as a triage list: start with the symptom you see, then work through the cause column before changing the database. The main takeaway is that most failures sit in the image, the connection settings or the role grants, not in PostgreSQL itself.
| Failure | Likely cause | First fix to check |
|---|---|---|
| Driver not found when testing the connection | The psycopg2 driver is missing from a custom Superset image, often because the image was rebuilt from a base that omits it | Confirm psycopg2 is present in the running image, then rebuild from the official image or add the driver to your build and redeploy |
| SSL error or connection refused on a TLS-enforced server | The sslmode in the connection settings does not match what the server accepts, or the CA certificate is not available inside the container | Compare the sslmode and certificate settings against the server's pg_hba.conf and certificate chain, then test with the same settings from a shell in the Superset container |
| Table listed but queries return permission denied | The analytics role has no USAGE on the schema or no SELECT on the table or view | Grant USAGE on the schema and SELECT on the specific tables or views, then check default privileges for tables created later |
| Too many connections under dashboard load | Each chart or worker opens direct connections, and the total exceeds the server's connection limit | Put a pooler such as PgBouncer between Superset and PostgreSQL, then cap the SQLAlchemy pool size per database connection in Superset |
| Dashboards slow on the operational primary | Heavy analytical queries compete with transactional writes and reads on the primary server | Point analytics datasets at a hot standby that serves read-only queries, and set statement timeouts for the analytics role |
| Dataset shows stale or missing recent rows | The connection points at a standby that is replaying changes behind the primary, or at a replica with replication lag | Check replication lag on the standby, confirm which host the connection string targets, and agree on a freshness expectation with data owners |
| Metadata and analytics mixed in one database | Superset's metadata database and the analytics source share a server or database, so dashboard load affects Superset's own state | Keep the metadata database separate, with its own role, and register PostgreSQL only as an analytics source connection |
Why analytics on the operational database slows transactions
Dashboard queries and transactions compete for the same database resources. When Superset points at the operational PostgreSQL cluster that runs your application, every chart load is a read that consumes CPU, memory and I/O on the server that also handles order writes, logins and payments. Analytical queries tend to scan many rows, sort, aggregate and join across large tables, which can evict useful data from shared buffers and push transactional reads to disk. The effect is usually not a single slow query but contention: a dashboard with many charts, refreshed by many users at once, can lengthen lock waits and commit latency for the application. Measure this in your own environment before you decide. Compare transaction latency and I/O wait on the production cluster with and without a representative dashboard load, and check how much headroom the server has at peak business hours.
Connection count is the second pressure point. Each Superset user session, chart query and async worker can open database connections, and PostgreSQL allocates a backend process per connection. Many users opening dashboards at the same moment can exhaust max_connections and starve the application. A connection pooler such as PgBouncer sits between Superset and PostgreSQL, reuses server connections and caps how many reach the database. Confirm which pooling mode your application already uses, because transaction pooling changes which session features are safe, and check how Superset's SQLAlchemy engine settings interact with the pooler before rollout. Pooling limits connection storms, but it does not reduce the cost of the queries themselves, so it complements rather than replaces the controls in the sections above.
The Superset metadata database must never be the analytics source. The metadata database holds dashboards, charts, users, roles, saved queries and alert configuration, and it is the state Superset depends on to run. Pointing analytics at it mixes reporting load with the platform's own writes, risks exposing internal tables to chart authors, and makes an upgrade or restore more fragile. Keep the metadata database separate, as described in the upgrade and architecture guidance, and register your business PostgreSQL databases as distinct connections with read-only roles. Where dashboards must run against live data, plan a hot standby or read replica for analytics, and confirm with your DBA how replication lag will be presented to users.
Questions buyers ask before connecting Superset to PostgreSQL
How much effort does connecting Superset to PostgreSQL take compared with the data model work?
The connection itself is usually the smaller task. Creating a driver-ready connection, testing SSL and creating a read-only role are bounded steps that most teams finish quickly once access is agreed. The larger effort sits in the data model: deciding which schemas Superset should expose, defining datasets and metrics, choosing which tables need row level security, and agreeing on which queries are safe to run against operational tables. Ask any partner to split its estimate into connection work and data model work so you can see where the time actually goes.
Can our team connect Superset to PostgreSQL without a partner?
Often yes, if you have someone comfortable with PostgreSQL roles, SSL settings and Superset administration. The steps are documented and the psycopg2 driver ships with Superset Docker images. A partner adds value where the environment is complex: several databases, a separate metadata database that must stay isolated, replica routing, strict network rules or audit evidence that must be produced. Decide based on the skills you have in-house, and ask whether the scope covers knowledge transfer and written runbooks.
What does the work need from our DBAs and Superset admins?
DBAs own the PostgreSQL side: creating the read-only role, granting schema and table access, confirming the server accepts SSL connections from the Superset network, and reviewing connection limits and statement timeouts. Superset admins own the database connection record, dataset definitions, RBAC mapping and the decision about which SQL Lab features users may reach. Both groups should agree on who approves changes to grants. Ask for the list of approvals and a named contact on each side before the engagement starts.
How do PostgreSQL upgrades affect the Superset connection?
Minor PostgreSQL releases do not change the storage format, so updating usually means replacing executables and restarting the server, which Superset sees as a brief interruption. Major upgrades need a planned method such as pg_upgrade, dump and restore, or logical replication, which can keep downtime to seconds. Check that your Superset version and psycopg2 driver support the PostgreSQL major version you move to, and retest dashboards and SQL Lab queries after the change. Your PostgreSQL version support window, published in the PostgreSQL versioning policy, sets how often this work recurs.
Should Superset query a read replica instead of the primary?
For most operational databases, yes. Dashboard queries can be heavy, and running them on the primary competes with transactions. PostgreSQL supports hot standby servers that serve read-only queries, so a replica can take analytics load. Confirm that the replica lag is acceptable for your dashboards, since asynchronous replication may show slightly older data. Also note that a replica does not protect against accidental writes unless the role is read-only, and that PostgreSQL does not detect primary failure on its own, so a failover plan needs external tooling.
What drives the cost of a fixed-scope engagement?
The written scope sets the price. The main drivers are the number of PostgreSQL clusters and databases to connect, how many schemas and datasets Superset must expose, whether replicas and SSL certificates need new infrastructure, how much row level security and role mapping is required, and whether compliance evidence must be produced. Recurring costs include PostgreSQL and Superset upgrades, dashboard maintenance and any retainer for ongoing support. Ask for the quote to list each deliverable, the environments covered and what the client team must provide. DeployOpen does not publish a price list, so a quote follows from that written scope.
Which Superset metadata database is safer to keep separate from analytics?
Keep the Superset metadata database on its own PostgreSQL instance or cluster, separate from any operational database you query. The metadata database stores users, dashboards and connection settings, and the Superset architecture documentation recommends PostgreSQL or MySQL for it, with versions listed in the configuration documentation. If analytics queries load the same server, they can slow Superset itself and widen the blast radius of a mistake. Back up the metadata database before every Superset upgrade, as the upgrade documentation describes.
How DeployOpen scopes and delivers the PostgreSQL integration
DeployOpen plans, builds, documents and hands over self-hosted Superset connections to PostgreSQL inside your own infrastructure. We start from a written scope that covers which databases Superset may query, which roles it uses and how the analytics source stays separate from the metadata database.
How we scope
- Inventory the PostgreSQL databases, schemas and tables that dashboards need, and name the owner of each.
- Agree the read-only role model, network path and SSL requirements before any connection is configured.
- Confirm the PostgreSQL major version in use and check it against the supported versions list before the build starts.
How we build
- Install the bundled psycopg2 driver in the Superset image and configure the connection string with secrets kept out of the code.
- Create read-only roles on the analytics source and test that Superset cannot write, alter or drop objects.
- Measure heavy dashboard queries on a replica or staging copy and tune them before users reach production.
How we hand over
- Deliver a documented connection configuration, role grants, SSL settings and a list of tested dashboard queries.
- Walk your team through the separation between the metadata database and the analytics source, including backup scope.
- Provide a runbook for rotating database credentials and for changing PostgreSQL versions in your environment.
How we support
- Engagements are fixed-scope, with scope, deliverables and timelines agreed in writing before work starts.
- Ongoing support, upgrades and continuous testing are available as a retainer for the connection and its roles.
- Review Superset and PostgreSQL release notes with your team before any version change reaches production.
