Yes, it is possible to work with multiple databases at the same time but you're looking in the wrong place. psycopg2 is just a library that simplifies accessing and manipulating data coming out of PostgreSQL but it doesn't go far beyond what you can do with psql. What you're looking to do you can solve on the database level by using Foreign Data Wrappers.
This does become more complicated in your schema definition but brings remote tables from host some.other.server
database remote_db
to appear as though they live on localhost
in database local_db
. A simple example on how to hook up the wrappers:
CREATE EXTENSION postgres_fdw;
CREATE SERVER some_remote_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'some.remote.server', port '5432', dbname 'remote_db');
CREATE USER MAPPING FOR local_user
SERVER some_remote_server
OPTIONS (user 'remote_user', password 'remote_user_password');
CREATE FOREIGN TABLE local_table_name (id int, value int)
SERVER some_remote_server
OPTIONS ( schema_name 'remote_schema_name', table_name 'remote_table_name');
Now locally you can just run
SELECT * from local_table_name
and the query will get executed against the remote host. Needless to say, this requires connectivity between the two servers.
Similarly, if you really have to, you can create a "remote" server against the localhost and point to a different local database for cross database queries. Feels dirty but it's possible.
As @a_horse_with_no_name mentioned though, this is not very efficient. If you find yourself doing this too frequently, you're not getting the most optimal performance and you'd better have very good reasons for keeping your databases separate at that point.
Due to padding for memory alignment, adding a small column to an index can often take up no extra room. The index might even be smaller, if it is fresher and so more densely packed. And even if not, the cost estimate for looking up one row in an index is very weakly dependent on index size, so the cost is likely to be a tie between the two indexes. When there is a tie between indexes they are broken arbitrarily, and it seems like it is the one created most recently that is usually chosen.
Best Answer
You can't run multiple DDL statements at the same time because each statement will request an exclusive lock. So if you add a primary or foreign key, the table is locked.
You can however create multiple indexes at the same time using the
concurrently
option.Without the
concurrently
this will also block write access to the table while the index is created (read access is still possible in any case).