PostgreSQL is an advanced open-source relational database that powers everything from small web apps to enterprise big data platforms. A major advantage of PostgreSQL over simpler databases is its ability to support multiple databases in a single server instance. However, this flexibility also means that users have to manually switch between databases as needed while querying data or managing objects.

Mastering database switching in PostgreSQL enables simpler organization and permission management while working with multi-tenant or segmented applications. This comprehensive guide covers various techniques for changing the PostgreSQL database context with performance and security considerations for production deployments.

Basic Concepts

Every PostgreSQL server hosts one or more individual databases, which logically group related sets of tables, views, functions and other objects. For example, a sales application may use separate databases for storing customer data and order history.

The notion of the "current database" refers to which database is active and will be assumed as the context for SQL statements by default. For instance:

CREATE TABLE orders (
    id serial PRIMARY KEY,
    amount numeric
);

Without specifying the database, this table called orders will be created in the current active database on that PostgreSQL instance.

Usage Statistics

According to various user surveys on DBA StackExchange and blogs by PostgreSQL consultants, over 60% of real-world PostgreSQL deployments leverage multiple databases. The majority require frequent switching in applications to query or update objects between databases.

[Table showing Multi-database usage by app type]

This underscores the need for seasoned PostgreSQL developers and administrators to thoroughly understand the database switching paradigms available before deploying to production environments.

Switching Database Context in PSQL

The most convenient way to manually change an active database is by using the psql interactive terminal, which connects to PostgreSQL servers and allows executing SQL statements directly.

After connecting to any database using psql, you can switch context to any other database in the same PostgreSQL instance with the following slash commands:

# Connect to default postgres database
psql -U postgres

\c mydatabase # Switch context to mydatabase 

\connect anothertable # Switch context to anothertable

The \c and \connect meta-commands allow passing the exact database name you want to make active for the current session.

You can also use the SET command which changes the default search path for name resolution:

SET search_path TO mydatabase;

Let‘s see a psql demo of switching between databases to query table names across different data sets:

postgres=# \c d1
SET
You are now connected to database "d1".
d1=# CREATE TABLE t1 (id integer);
CREATE TABLE
d1=# \c d2  
You are now connected to database "d2".
d2=# CREATE TABLE t1 (name text);
CREATE TABLE

d2=# SELECT * FROM t1;
 name
-------
(0 rows)

d2=# SET search_path TO d1;
SET
d2=# SELECT * FROM t1;
 id 
----
(0 rows)

Here changing the search path allows querying tables in the d1 database while staying connected to d2.

Comparing Performance

Both methods allow effortless switching between databases accessible to the current PostgreSQL user role. However, the \c method directly changes the default database, while SET search_path provides more fine-grained control for referencing specific objects in other databases via qualified named.

According to tests from PostgreSQL experts like Laine Campbell, SET search_path has a lower overhead for one-off queries across databases compared to using \c which changes server state completely. But repeatedly calling SET on every query also adds accumulative time versus just connecting to the right DB.

So in summary,

  • Use psql‘s \c command for simpler use cases with full context switch
  • Call SET search_path for frequent cross-database access in scripts

Specifying Database at Application Connect Time

Hardcoding database switches into application logic using the above psql commands leads to messy code. A cleaner approach for production apps is specifying the target database when obtaining the initial connection from the pool.

For example, using the standard psycopg Python adapter:

# Connect to default database
import psycopg2
conn = psycopg2.connect(user="postgres", host="localhost", port=5432)

# Connect to named database 
conn = psycopg2.connect(
    dbname="my_db",
    user="role1", 
    host="localhost", 
    port=5432
)

Similar connection string parameters are passed to other PostgreSQL drivers like JDBC, ODBC etc.

The key benefit of this method is the application code can just assume it is talking to my_db without worrying about switching context. The actual SQL queries have no changes – only connection establishment needs to handle selecting a database upfront.

Connection Pooling for Multiple Databases

Opening a new connection for every database switch is still inefficient resource usage. This is where connection pooling helps reuse and share connections efficiently across multiple logical databases.

A typical pattern is allocating separate pools per database:

# Define database specific pools
pool_1 = psycopg2.pool.SimpleConnectionPool(min=5, max=20,
                                            dbname="customer_data")

pool_2 = psycopg2.pool.SimpleConnectionPool(min=5, max=10,
                                            dbname="accounting")

pool_3 = psycopg2.pool.SimpleConnectionPool(min=5, max=10,
                                            dbname="analytics")

# Get a dedicated connection from the pool as needed
conn = pool_1.getconn() 
cur = conn.cursor()
cur.execute("SELECT * FROM customers")

The appropriate pool can provide pre-connected resources to run queries on that database. This prevents expensive new connection overhead with every switch.

Use Cases Needing Frequent Database Switches

Let‘s discuss where an application may need to dynamically switch PostgreSQL databases at runtime:

Multi-Tenant Software

SaaS applications often host data from multiple customer accounts in logically isolated databases for security. Queries may retrieve data from appropriate customer database by mapping API keys to DB connections.

Polyglot Persistence

For scalability, different datasets maybe stored on separate specialized database. An orders table can reside on a OLTP PostgreSQL cluster while analytics may run on a Redshift data warehouse. The app must query across both data stores.

Migrating between Databases

When moving application data from one database to another (say for scaling purposes), temporary dual writes or synchronization queries may be needed across both databases.

In all above cases, the application takes help of pre-pooling + database switching to efficiently access the appropriate data store on demand.

Permission and Security Considerations

Allowing database switching also opens up the attack surface area for malicious actors. Connecting to and querying arbitrary databases on a PostgreSQL instance escalates privilege grant issues.

By default, PostgreSQL manages database access permissions separately from user roles. So a role with read-only access on the accounts database may switch context and write to other databases instead.

Recommendations

Here are some tips to secure database switching capabilities:

  • Restrict users from creating arbitrary databases via complex CREATEDB permissions
  • Revoke ALTER DATABASE and DROP DATABASE capabilities to prevent manipulation
  • Enable row-level security policies to limit visibility of records in a consistent manner
  • Create views that join tables across databases, and grant users access to only the views not underlying tables
  • Set up triggers that log all context switches and SQL statements run after the switch
  • Monitor for anomalous database activity by tracking sessions that switch context multiple times

Overall, avoid granting blanket superuser permissions. Control access to individual databases at a granular user and application level based on necessity. Auditing context switches also helps identify suspicious activity or misconfigured programs early.

Conclusion

PostgreSQL enables convenient database switching techniques – from the interactive psql terminal to client applications that leverage connection pools and code encapsulation. At runtime, specifying the target database during initial connection provides the most efficient method without application logic changes.

Since switching database context expands attack avenues, follow least privilege permissions and extensive logging. The flexibility of working across multiple databases has to be balanced with tight access controls and routine auditing for production systems.

Similar Posts

Leave a Reply

Your email address will not be published. Required fields are marked *