# PostgreSQL on ZFS



## alp (Jan 27, 2011)

Hello. I'm going to deploy new database server. Has someone tuned PostgreSQL on ZFS? It's interesting, what is the preferred  value for wal_sync_method? Has someone turned full_page_writes off?


----------



## vermaden (Jan 27, 2011)

I remember that its recommended to use recordsize=4k for pools prepared for PostgreSQL DB.



			
				&quot said:
			
		

> For x86 systems, when the db_block_size and the recordsize are aligned to the system page size of 4k, then it is better to set zfs_immediate_write_sz to a little less than 4096, as 4000.



Also check this one:
http://solarisinternals.com/wiki/index.php/ZFS_for_Databases


----------



## alp (Jan 27, 2011)

vermaden said:
			
		

> I remember that its recommended to use recordsize=4k for pools prepared for PostgreSQL DB.


It seems it should be 8k (default DB page size) for data files. 


> Also check this one:
> http://solarisinternals.com/wiki/index.php/ZFS_for_Databases


Yes, I've read this article , it was useful. However, almost all links for "PostgreSQL ZFS" discuss Solaris installations.  And I'd like to hear some FreeBSD-specific notes. For example, long ago it was recommended to set wal_sync_method to open_sync. Is it still true for modern FreeBSD installations? How does ZFS influences this decision?


----------



## vermaden (Jan 27, 2011)

I do not have DB experience on ZFS, so wait for others to speak


----------



## AndyUKG (Jan 27, 2011)

alp said:
			
		

> Yes, I've read this article , it was useful. However, almost all links for "PostgreSQL ZFS" discuss Solaris installations.  And I'd like to hear some FreeBSD-specific notes. For example, long ago it was recommended to set wal_sync_method to open_sync. Is it still true for modern FreeBSD installations? How does ZFS influences this decision?



I don't know anything specific to FreeBSD or PostgreSQL, but the option you mention seems to relate to DirectIO. ZFS doesn't support DirectIO, but you can achieve some of the same effects/benefits by setting the ARC cache to meta-data only. I've seen this discussed in relation to MySQL but I guess it will apply to other SQL database systems. This will prevent both the database engine and the OS caching the same data...

thanks Andy.


----------

