DBA may cry

DBA may cry

Database administrators usually work behind the scenes, over the weekends and in antisocial hours. They strive to look after the database which is at the core of any business with little or no recognition.

The sad truth is that the DBA is one of the most obscure and one of the less appreciated roles within the IT organisations.

presenting pg chameleon at the brighton postgresql meetup

Back from Estonia after the amazing European PGConf 2016 I’m about to give a talk on the MySQL to PostgreSQL replication project I’m developing. But first things first. I want to thank all the organisers of the PGConf Europe. The talks were super interesting the location absolutely stunning and the old and new friends I met, remembered me how amazing is the PostgreSQL community. Unfortunately I missed the social events (did somebody said a DBA can have social life?

Directory format and default compression

After upgrading some clusters to PostgreSQL 9.4.4 I noticed an increase of the database backup. Because the databases are quite large I’m taking the advantage of the parallel export introduced with PostgreSQL 9.3. The parallel dump uses the PostgreSQL’s snapshot export with multiple backends. The functionality requires the dump to be in directory format where a toc file is saved alongside with the compressed exports, one per each table saved by pg_dump.

PostgreSQL 8.0, the 2q memory manager

With the revolutionary PostgreSQL 8.0 were introduced a different memory manager, the two queues. This algorithm uses three list of buffer page pointers called cache directory blocks (CDB). The lists T1,T2 are actually pointing buffered pages. B1 lists pages been recently in the shared buffer. Figure 3.1:PostgreSQL 8.0, CDB lists The list T1 is used as LRU for the pages loaded from disk.

PostgreSQL 7.4, the LRU list

3.1.1 PostgreSQL 7.4, the LRU list In PostgreSQL 7.4 the free space in the shared buffer was managed using a simple last recently used list. When a page is first request from disk the buffer manager puts it into the first buffer in the free LRU list. If the first buffer is used by another page the list is shifted by one unity and the eventual last page in the list is discarded.