Re: query performance after database rename

Hi Markus,

Can you give an example ? what is the postgres version your are using? what
exactly you are doing ?

PostgreSQL do not perform any flush operation after renaming. Below is the
debug log of postgresql

< 2018-11-21 11:12:19.481 UTC > DEBUG: postmaster child[14261]: starting
with (
< 2018-11-21 11:12:19.481 UTC > DEBUG: postgres
< 2018-11-21 11:12:19.481 UTC > DEBUG: )
< 2018-11-21 11:12:19.481 UTC > DEBUG: InitPostgres
< 2018-11-21 11:12:19.482 UTC > DEBUG: my backend ID is 3
< 2018-11-21 11:12:19.482 UTC > DEBUG: StartTransaction
< 2018-11-21 11:12:19.482 UTC > DEBUG: name: unnamed; blockState:
DEFAULT; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children:
< 2018-11-21 11:12:19.483 UTC > DEBUG: CommitTransaction
< 2018-11-21 11:12:19.483 UTC > DEBUG: name: unnamed; blockState:
STARTED; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children:
< 2018-11-21 11:12:19.483 UTC > DEBUG: StartTransactionCommand
< 2018-11-21 11:12:19.483 UTC > DEBUG: StartTransaction
< 2018-11-21 11:12:19.483 UTC > DEBUG: name: unnamed; blockState:
DEFAULT; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children:
< 2018-11-21 11:12:19.484 UTC > DEBUG: CommitTransactionCommand
< 2018-11-21 11:12:19.484 UTC > DEBUG: CommitTransaction
< 2018-11-21 11:12:19.484 UTC > DEBUG: name: unnamed; blockState:
STARTED; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children:
< 2018-11-21 11:12:19.484 UTC > LOG: duration: 0.367 ms statement: SELECT
pg_backend_pid()
< 2018-11-21 11:12:19.484 UTC > DEBUG: StartTransactionCommand
< 2018-11-21 11:12:19.484 UTC > DEBUG: StartTransaction
< 2018-11-21 11:12:19.484 UTC > DEBUG: name: unnamed; blockState:
DEFAULT; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children:
< 2018-11-21 11:12:19.484 UTC > DEBUG: ProcessUtility
< 2018-11-21 11:12:19.484 UTC > DEBUG: CommitTransactionCommand
< 2018-11-21 11:12:19.484 UTC > DEBUG: CommitTransaction
< 2018-11-21 11:12:19.484 UTC > DEBUG: name: unnamed; blockState:
STARTED; state: INPROGR, xid/subid/cid: 151784/1/0 (used), nestlvl: 1,
children:
*< 2018-11-21 11:12:19.485 UTC > LOG: duration: 1.007 ms statement: alter
database test rename to test_old*
< 2018-11-21 11:12:19.485 UTC > DEBUG: shmem_exit(0): 1 before_shmem_exit
callbacks to make
< 2018-11-21 11:12:19.485 UTC > DEBUG: shmem_exit(0): 6 on_shmem_exit
callbacks to make
< 2018-11-21 11:12:19.485 UTC > DEBUG: proc_exit(0): 3 callbacks to make
< 2018-11-21 11:12:19.485 UTC > DEBUG: exit(0)
< 2018-11-21 11:12:19.485 UTC > DEBUG: shmem_exit(-1): 0 before_shmem_exit
callbacks to make
< 2018-11-21 11:12:19.485 UTC > DEBUG: shmem_exit(-1): 0 on_shmem_exit
callbacks to make
< 2018-11-21 11:12:19.485 UTC > DEBUG: proc_exit(-1): 0 callbacks to make
< 2018-11-21 11:12:19.486 UTC > DEBUG: reaping dead processes
< 2018-11-21 11:12:19.486 UTC > DEBUG: server process (PID 14261) exited
with exit code 0

On Wed, Nov 21, 2018 at 2:50 PM Zwettler Markus (OIZ) <
Markus(dot)Zwettler(at)zuerich(dot)ch> wrote:

> Hi,
>
> We have a postgresql cluster with two databases.
> We rename (switch) the databases daily (alter database x rename to y;)
> The query performance slows down after each renaming.
> Question: Why? Does postgresql flush the db-cache?
>
> Thanks,
> Markus
>
>
>