{"id":35841,"date":"2024-11-26T08:56:03","date_gmt":"2024-11-26T07:56:03","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/?p=35841"},"modified":"2024-11-26T08:56:07","modified_gmt":"2024-11-26T07:56:07","slug":"postgresql-maybe-we-should-give-zfs-a-chance-3-testing-with-an-aligned-record-size","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/postgresql-maybe-we-should-give-zfs-a-chance-3-testing-with-an-aligned-record-size\/","title":{"rendered":"PostgreSQL: Maybe we should give ZFS a chance (3) \u2013 testing with an aligned record size"},"content":{"rendered":"\n<p>In the last post about <a href=\"https:\/\/www.dbi-services.com\/blog\/postgresql-maybe-we-should-give-zfs-a-chance-2-testing\/\">PostgreSQL on ZFS<\/a> the pgbench tests showed, that ZFS was slower for the standard tests compared to ext4 on Debian. A comment to that post made me wonder if changing the ZFS record size would change the results, so this blog post is about exactly that. There is plenty of information out there when it comes to ZFS for PostgreSQL and most of them recommend aligning the ZFS record size and the PostgreSQL block size in some way or the other (e.g. please see <a href=\"https:\/\/vadosware.io\/post\/everything-ive-seen-on-optimizing-postgres-on-zfs-on-linux\/#increasing-postgres-blocksize\" target=\"_blank\" rel=\"noreferrer noopener\">here<\/a>). Reading that, there are basically three options: Either change the ZFS record size, change the PostgreSQL block size or change both of them.<\/p>\n\n\n\n<p>As I don&#8217;t like changing the PostgreSQL block size, as everybody (as far as I know) runs with 8kB, the first test runs against a ZFS with an aligned record size to match the PostgreSQL block size:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: bash; highlight: [1,30,31,34,35,36,37,38]; title: ; notranslate\" title=\"\">\n&#x5B;postgres@freebsd ~]$ geom disk list\nGeom name: nda0\nProviders:\n1. Name: nda0\n   Mediasize: 10737418240 (10G)\n   Sectorsize: 512\n   Stripesize: 4096\n   Stripeoffset: 0\n   Mode: r2w2e6\n   descr: Amazon Elastic Block Store\n   ident: vol0a89ebfc6cc9173ff\n   rotationrate: 0\n   fwsectors: 0\n   fwheads: 0\n\nGeom name: nda1\nProviders:\n1. Name: nda1\n   Mediasize: 21474836480 (20G)\n   Sectorsize: 512\n   Stripesize: 4096\n   Stripeoffset: 0\n   Mode: r0w0e0\n   descr: Amazon Elastic Block Store\n   ident: vol0a6dcd2a6a1a1b898\n   rotationrate: 0\n   fwsectors: 0\n   fwheads: 0\n\n&#x5B;postgres@freebsd ~]$ sudo zpool create pgpool \/dev\/nda1\n&#x5B;postgres@freebsd ~]$ sudo zpool list\nNAME     SIZE  ALLOC   FREE  CKPOINT  EXPANDSZ   FRAG    CAP  DEDUP    HEALTH  ALTROOT\npgpool  19.5G   360K  19.5G        -         -     0%     0%  1.00x    ONLINE  -\n&#x5B;postgres@freebsd ~]$ sudo zfs create pgpool\/pgzfs\n&#x5B;postgres@freebsd ~]$ sudo zfs set mountpoint=\/u02\/pgdata\/zfs pgpool\/pgzfs\n&#x5B;postgres@freebsd ~]$ sudo zfs set compression=zstd pgpool\/pgzfs\n&#x5B;postgres@freebsd ~]$ sudo zfs set recordsize=8K pgpool\/pgzfs\n&#x5B;postgres@freebsd ~]$ df -h | grep zfs\ndf: minimum blocksize is 512\npgpool\/pgzfs        19G     96K     19G     0%    \/u02\/pgdata\/zfs\n<\/pre><\/div>\n\n\n<p>For PostgreSQL, exactly the same setup as before (no checksums, and the same three parameter changes):<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: bash; highlight: [1,29,30,31,32,34,35]; title: ; notranslate\" title=\"\">\n$ initdb --pgdata=\/u02\/pgdata\/zfs\/pg\nThe files belonging to this database system will be owned by user &quot;postgres&quot;.\nThis user must also own the server process.\n\nThe database cluster will be initialized with locale &quot;C.UTF-8&quot;.\nThe default database encoding has accordingly been set to &quot;UTF8&quot;.\nThe default text search configuration will be set to &quot;english&quot;.\n\nData page checksums are disabled.\n\ncreating directory \/u02\/pgdata\/zfs\/pg ... ok\ncreating subdirectories ... ok\nselecting dynamic shared memory implementation ... posix\nselecting default &quot;max_connections&quot; ... 100\nselecting default &quot;shared_buffers&quot; ... 128MB\nselecting default time zone ... UTC\ncreating configuration files ... ok\nrunning bootstrap script ... ok\nperforming post-bootstrap initialization ... ok\nsyncing data to disk ... ok\n\ninitdb: warning: enabling &quot;trust&quot; authentication for local connections\ninitdb: hint: You can change this by editing pg_hba.conf or using the option -A, or --auth-local and --auth-host, the next time you run initdb.\n\nSuccess. You can now start the database server using:\n\n    pg_ctl -D \/u02\/pgdata\/zfs\/pg -l logfile start\n\n$ echo &quot;wal_init_zero=&#039;off&#039;&quot; &gt;&gt; \/u02\/pgdata\/zfs\/pg\/postgresql.auto.conf\n$ echo &quot;full_page_writes=&#039;off&#039;&quot; &gt;&gt; \/u02\/pgdata\/zfs\/pg\/postgresql.auto.conf\n$ echo &quot;port=5433&quot; &gt;&gt; \/u02\/pgdata\/zfs\/pg\/postgresql.auto.conf\n$ pg_ctl --pgdata=\/u02\/pgdata\/zfs\/pg\/ --log=\/u02\/pgdata\/zfs\/pg\/log.log start\nwaiting for server to start.... done\n$ export PGPORT=5433\n$ psql\npsql (17.1 dbi services build)\nType &quot;help&quot; for help.\n\npostgres=# \\q\n<\/pre><\/div>\n\n\n<p>We&#8217;ll use the same data set as in the <a href=\"https:\/\/www.dbi-services.com\/blog\/postgresql-maybe-we-should-give-zfs-a-chance-2-testing\/\" target=\"_blank\" rel=\"noreferrer noopener\">last post<\/a>:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: bash; highlight: [1]; title: ; notranslate\" title=\"\">\n$ pgbench -p 5433 -i -s 1000 postgres\n<\/pre><\/div>\n\n\n<p>Same test as before:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: bash; highlight: [1,11,15,26,30,41,45]; title: ; notranslate\" title=\"\">\n$ for i in {1..3}; do pgbench --client=4 --jobs=4 --time=900 --port=5433; done\npgbench (17.1 dbi services build)\nstarting vacuum...end.\ntransaction type: &lt;builtin: TPC-B (sort of)&gt;\nscaling factor: 1000\nquery mode: simple\nnumber of clients: 4\nnumber of threads: 4\nmaximum number of tries: 1\nduration: 900 s\nnumber of transactions actually processed: 778622\nnumber of failed transactions: 0 (0.000%)\nlatency average = 4.624 ms\ninitial connection time = 36.558 ms\ntps = 865.137263 (without initial connection time)\n\npgbench (17.1 dbi services build)\nstarting vacuum...end.\ntransaction type: &lt;builtin: TPC-B (sort of)&gt;\nscaling factor: 1000\nquery mode: simple\nnumber of clients: 4\nnumber of threads: 4\nmaximum number of tries: 1\nduration: 900 s\nnumber of transactions actually processed: 812209\nnumber of failed transactions: 0 (0.000%)\nlatency average = 4.432 ms\ninitial connection time = 11.599 ms\ntps = 902.449933 (without initial connection time)\n\npgbench (17.1 dbi services build)\nstarting vacuum...end.\ntransaction type: &lt;builtin: TPC-B (sort of)&gt;\nscaling factor: 1000\nquery mode: simple\nnumber of clients: 4\nnumber of threads: 4\nmaximum number of tries: 1\nduration: 900 s\nnumber of transactions actually processed: 828141\nnumber of failed transactions: 0 (0.000%)\nlatency average = 4.347 ms\ninitial connection time = 11.673 ms\ntps = 920.148011 (without initial connection time)\n<\/pre><\/div>\n\n\n<p>Comparing that to the <a href=\"https:\/\/www.dbi-services.com\/blog\/postgresql-maybe-we-should-give-zfs-a-chance-2-testing\/\" target=\"_blank\" rel=\"noreferrer noopener\">previous numbers<\/a>  this is almost double the speed.<\/p>\n\n\n\n<p>Same test with the simple-update run:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: bash; highlight: [1,11,15,26,30,41,45]; title: ; notranslate\" title=\"\">\n$ do pgbench --client=4 --jobs=4 --time=900 --port=5433 --builtin=simple-update; done\npgbench (17.1 dbi services build)\nstarting vacuum...end.\ntransaction type: &lt;builtin: simple update&gt;\nscaling factor: 1000\nquery mode: simple\nnumber of clients: 4\nnumber of threads: 4\nmaximum number of tries: 1\nduration: 900 s\nnumber of transactions actually processed: 900677\nnumber of failed transactions: 0 (0.000%)\nlatency average = 3.997 ms\ninitial connection time = 14.278 ms\ntps = 1000.689595 (without initial connection time)\n\npgbench (17.1 dbi services build)\nstarting vacuum...end.\ntransaction type: &lt;builtin: simple update&gt;\nscaling factor: 1000\nquery mode: simple\nnumber of clients: 4\nnumber of threads: 4\nmaximum number of tries: 1\nduration: 900 s\nnumber of transactions actually processed: 928162\nnumber of failed transactions: 0 (0.000%)\nlatency average = 3.879 ms\ninitial connection time = 10.493 ms\ntps = 1031.289907 (without initial connection time)\n\npgbench (17.1 dbi services build)\nstarting vacuum...end.\ntransaction type: &lt;builtin: simple update&gt;\nscaling factor: 1000\nquery mode: simple\nnumber of clients: 4\nnumber of threads: 4\nmaximum number of tries: 1\nduration: 900 s\nnumber of transactions actually processed: 939785\nnumber of failed transactions: 0 (0.000%)\nlatency average = 3.831 ms\ninitial connection time = 16.170 ms\ntps = 1044.205709 (without initial connection time)\n<\/pre><\/div>\n\n\n<p>Same picture here, considerable faster. This means, that aligning the PostgreSQL block size and the ZFS record size indeed matters a lot. <\/p>\n\n\n\n<p>How does that look like if we increase the PostgreSQL block size and also align the ZFS record size? Same test with a 32kB block size in PostgreSQL and a 32kB record size with ZFS:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: bash; highlight: [1,2,5,6,7,8,9,10,14,15]; title: ; notranslate\" title=\"\">\n$ sudo zfs set recordsize=32K pgpool\/pgzfs\n$ zfs get recordsize \/u02\/pgdata\/zfs\nNAME          PROPERTY    VALUE    SOURCE\npgpool\/pgzfs  recordsize  32K      local\n$ initdb -D \/u02\/pgdata\/zfs\/pg\/\n$ echo &quot;wal_init_zero=&#039;off&#039;&quot; &gt;&gt; \/u02\/pgdata\/zfs\/pg\/postgresql.auto.conf\n$ echo &quot;full_page_writes=&#039;off&#039;&quot; &gt;&gt; \/u02\/pgdata\/zfs\/pg\/postgresql.auto.conf\n$ echo &quot;port=5433&quot; &gt;&gt; \/u02\/pgdata\/zfs\/pg\/postgresql.auto.conf\n$ pg_ctl --pgdata=\/u02\/pgdata\/zfs\/pg\/ --log=\/u02\/pgdata\/zfs\/pg\/log.log start\n$ psql -c &quot;show block_size&quot;\n block_size \n------------\n 32768\n$ pgbench -p 5433 -i -s 1000 postgres\n$ psql -c &quot;\\l+&quot;\n                                                                                  List of databases\n   Name    |  Owner   | Encoding | Locale Provider | Collate |  Ctype  | Locale | ICU Rules |   Access privileges   | Size  | Tablespace |                Description                 \n-----------+----------+----------+-----------------+---------+---------+--------+-----------+-----------------------+-------+------------+--------------------------------------------\n postgres  | postgres | UTF8     | libc            | C.UTF-8 | C.UTF-8 |        |           |                       | 14 GB | pg_default | default administrative connection database\n template0 | postgres | UTF8     | libc            | C.UTF-8 | C.UTF-8 |        |           | =c\/postgres          +| 16 MB | pg_default | unmodifiable empty database\n           |          |          |                 |         |         |        |           | postgres=CTc\/postgres |       |            | \n template1 | postgres | UTF8     | libc            | C.UTF-8 | C.UTF-8 |        |           | =c\/postgres          +| 16 MB | pg_default | default template for new databases\n           |          |          |                 |         |         |        |           | postgres=CTc\/postgres |       |            | \n(3 rows)\n<\/pre><\/div>\n\n\n<p>Same standard test again (summaries only):<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: bash; highlight: [1]; title: ; notranslate\" title=\"\">\n$ for i in {1..3}; do pgbench --client=4 --jobs=4 --time=900 --port=5433; done\n\n# run 1\nnumber of transactions actually processed: 964541\ntps = 1071.697122 (without initial connection time)\n\n# run 2\nnumber of transactions actually processed: 1045888\ntps = 1162.073678 (without initial connection time)\n\n# run 3\nnumber of transactions actually processed: 1063922\ntps = 1182.126126 (without initial connection time)\n<\/pre><\/div>\n\n\n<p>Even better than with an aligned 8kB block- and recordsize. Same test with the simple-update run:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: bash; highlight: [1]; title: ; notranslate\" title=\"\">\n$ for i in {1..3}; do pgbench --client=4 --jobs=4 --time=900 --port=5433 --builtin=simple-update; done\n\n# run 1\nnumber of transactions actually processed: 1233855\ntps = 1370.926077 (without initial connection time)\n\n# run 2\nnumber of transactions actually processed: 1242710\ntps = 1380.790526 (without initial connection time)\n\n# run 3\nnumber of transactions actually processed: 1251885\ntps = 1390.963532 (without initial connection time)\n<\/pre><\/div>\n\n\n<p>Same picture, using a 32kB block- and recordsize gives better results.<\/p>\n\n\n\n<p>To summarize: If you want to go for ZFS with PostgreSQL it indeed matters that the PostgreSQL block size and the ZFS record size are aligned. I am still not sure if I would go for a 32kB block size in PostgreSQL, this requires more testing. We haven&#8217;t even looked at other stuff to adapt (shared_buffers, checkpointing, &#8230; ) but I think that ZFS today is a valid option to run PostgreSQL workloads. But, again: Please test for your own, using your workload, before taking any decisions.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>In the last post about PostgreSQL on ZFS the pgbench tests showed, that ZFS was slower for the standard tests compared to ext4 on Debian. A comment to that post made me wonder if changing the ZFS record size would change the results, so this blog post is about exactly that. There is plenty of [&hellip;]<\/p>\n","protected":false},"author":29,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229,198],"tags":[77],"type_dbi":[2749],"class_list":["post-35841","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","category-database-management","tag-postgresql","type-postgresql"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v27.2 (Yoast SEO v27.2) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>PostgreSQL: Maybe we should give ZFS a chance (3) \u2013 testing with an aligned record size - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/postgresql-maybe-we-should-give-zfs-a-chance-3-testing-with-an-aligned-record-size\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"PostgreSQL: Maybe we should give ZFS a chance (3) \u2013 testing with an aligned record size\" \/>\n<meta property=\"og:description\" content=\"In the last post about PostgreSQL on ZFS the pgbench tests showed, that ZFS was slower for the standard tests compared to ext4 on Debian. A comment to that post made me wonder if changing the ZFS record size would change the results, so this blog post is about exactly that. There is plenty of [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/postgresql-maybe-we-should-give-zfs-a-chance-3-testing-with-an-aligned-record-size\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2024-11-26T07:56:03+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2024-11-26T07:56:07+00:00\" \/>\n<meta name=\"author\" content=\"Daniel Westermann\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@westermanndanie\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Daniel Westermann\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-maybe-we-should-give-zfs-a-chance-3-testing-with-an-aligned-record-size\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-maybe-we-should-give-zfs-a-chance-3-testing-with-an-aligned-record-size\/\"},\"author\":{\"name\":\"Daniel Westermann\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"headline\":\"PostgreSQL: Maybe we should give ZFS a chance (3) \u2013 testing with an aligned record size\",\"datePublished\":\"2024-11-26T07:56:03+00:00\",\"dateModified\":\"2024-11-26T07:56:07+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-maybe-we-should-give-zfs-a-chance-3-testing-with-an-aligned-record-size\/\"},\"wordCount\":393,\"commentCount\":0,\"keywords\":[\"PostgreSQL\"],\"articleSection\":[\"Database Administration &amp; Monitoring\",\"Database management\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/postgresql-maybe-we-should-give-zfs-a-chance-3-testing-with-an-aligned-record-size\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-maybe-we-should-give-zfs-a-chance-3-testing-with-an-aligned-record-size\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-maybe-we-should-give-zfs-a-chance-3-testing-with-an-aligned-record-size\/\",\"name\":\"PostgreSQL: Maybe we should give ZFS a chance (3) \u2013 testing with an aligned record size - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2024-11-26T07:56:03+00:00\",\"dateModified\":\"2024-11-26T07:56:07+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-maybe-we-should-give-zfs-a-chance-3-testing-with-an-aligned-record-size\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/postgresql-maybe-we-should-give-zfs-a-chance-3-testing-with-an-aligned-record-size\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-maybe-we-should-give-zfs-a-chance-3-testing-with-an-aligned-record-size\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"PostgreSQL: Maybe we should give ZFS a chance (3) \u2013 testing with an aligned record size\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\",\"name\":\"Daniel Westermann\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"caption\":\"Daniel Westermann\"},\"description\":\"Daniel Westermann is Principal Consultant and Technology Leader Open Infrastructure at dbi services. He has more than 15 years of experience in management, engineering and optimization of databases and infrastructures, especially on Oracle and PostgreSQL. Since the beginning of his career, he has specialized in Oracle Technologies and is Oracle Certified Professional 12c and Oracle Certified Expert RAC\/GridInfra. Over time, Daniel has become increasingly interested in open source technologies, becoming \u201cTechnology Leader Open Infrastructure\u201d and PostgreSQL expert. \u00a0Based on community or EnterpriseDB tools, he develops and installs complex high available solutions with PostgreSQL. He is also a certified PostgreSQL Plus 9.0 Professional and a Postgres Advanced Server 9.4 Professional. He is a regular speaker at PostgreSQL conferences in Switzerland and Europe. Today Daniel is also supporting our customers on AWS services such as AWS RDS, database migrations into the cloud, EC2 and automated infrastructure management with AWS SSM (System Manager). He is a certified AWS Solutions Architect Professional. Prior to dbi services, Daniel was Management System Engineer at LC SYSTEMS-Engineering AG in Basel. Before that, he worked as Oracle Developper &amp;\u00a0Project Manager at Delta Energy Solutions AG in Basel (today Powel AG). Daniel holds a diploma in Business Informatics (DHBW, Germany). His branch-related experience mainly covers the pharma industry, the financial sector, energy, lottery and telecommunications.\",\"sameAs\":[\"https:\/\/x.com\/westermanndanie\"],\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/daniel-westermann\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"PostgreSQL: Maybe we should give ZFS a chance (3) \u2013 testing with an aligned record size - dbi Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/postgresql-maybe-we-should-give-zfs-a-chance-3-testing-with-an-aligned-record-size\/","og_locale":"en_US","og_type":"article","og_title":"PostgreSQL: Maybe we should give ZFS a chance (3) \u2013 testing with an aligned record size","og_description":"In the last post about PostgreSQL on ZFS the pgbench tests showed, that ZFS was slower for the standard tests compared to ext4 on Debian. A comment to that post made me wonder if changing the ZFS record size would change the results, so this blog post is about exactly that. There is plenty of [&hellip;]","og_url":"https:\/\/www.dbi-services.com\/blog\/postgresql-maybe-we-should-give-zfs-a-chance-3-testing-with-an-aligned-record-size\/","og_site_name":"dbi Blog","article_published_time":"2024-11-26T07:56:03+00:00","article_modified_time":"2024-11-26T07:56:07+00:00","author":"Daniel Westermann","twitter_card":"summary_large_image","twitter_creator":"@westermanndanie","twitter_misc":{"Written by":"Daniel Westermann","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-maybe-we-should-give-zfs-a-chance-3-testing-with-an-aligned-record-size\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-maybe-we-should-give-zfs-a-chance-3-testing-with-an-aligned-record-size\/"},"author":{"name":"Daniel Westermann","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"headline":"PostgreSQL: Maybe we should give ZFS a chance (3) \u2013 testing with an aligned record size","datePublished":"2024-11-26T07:56:03+00:00","dateModified":"2024-11-26T07:56:07+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-maybe-we-should-give-zfs-a-chance-3-testing-with-an-aligned-record-size\/"},"wordCount":393,"commentCount":0,"keywords":["PostgreSQL"],"articleSection":["Database Administration &amp; Monitoring","Database management"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/postgresql-maybe-we-should-give-zfs-a-chance-3-testing-with-an-aligned-record-size\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-maybe-we-should-give-zfs-a-chance-3-testing-with-an-aligned-record-size\/","url":"https:\/\/www.dbi-services.com\/blog\/postgresql-maybe-we-should-give-zfs-a-chance-3-testing-with-an-aligned-record-size\/","name":"PostgreSQL: Maybe we should give ZFS a chance (3) \u2013 testing with an aligned record size - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2024-11-26T07:56:03+00:00","dateModified":"2024-11-26T07:56:07+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-maybe-we-should-give-zfs-a-chance-3-testing-with-an-aligned-record-size\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/postgresql-maybe-we-should-give-zfs-a-chance-3-testing-with-an-aligned-record-size\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-maybe-we-should-give-zfs-a-chance-3-testing-with-an-aligned-record-size\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"PostgreSQL: Maybe we should give ZFS a chance (3) \u2013 testing with an aligned record size"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66","name":"Daniel Westermann","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","caption":"Daniel Westermann"},"description":"Daniel Westermann is Principal Consultant and Technology Leader Open Infrastructure at dbi services. He has more than 15 years of experience in management, engineering and optimization of databases and infrastructures, especially on Oracle and PostgreSQL. Since the beginning of his career, he has specialized in Oracle Technologies and is Oracle Certified Professional 12c and Oracle Certified Expert RAC\/GridInfra. Over time, Daniel has become increasingly interested in open source technologies, becoming \u201cTechnology Leader Open Infrastructure\u201d and PostgreSQL expert. \u00a0Based on community or EnterpriseDB tools, he develops and installs complex high available solutions with PostgreSQL. He is also a certified PostgreSQL Plus 9.0 Professional and a Postgres Advanced Server 9.4 Professional. He is a regular speaker at PostgreSQL conferences in Switzerland and Europe. Today Daniel is also supporting our customers on AWS services such as AWS RDS, database migrations into the cloud, EC2 and automated infrastructure management with AWS SSM (System Manager). He is a certified AWS Solutions Architect Professional. Prior to dbi services, Daniel was Management System Engineer at LC SYSTEMS-Engineering AG in Basel. Before that, he worked as Oracle Developper &amp;\u00a0Project Manager at Delta Energy Solutions AG in Basel (today Powel AG). Daniel holds a diploma in Business Informatics (DHBW, Germany). His branch-related experience mainly covers the pharma industry, the financial sector, energy, lottery and telecommunications.","sameAs":["https:\/\/x.com\/westermanndanie"],"url":"https:\/\/www.dbi-services.com\/blog\/author\/daniel-westermann\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/35841","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/29"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=35841"}],"version-history":[{"count":32,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/35841\/revisions"}],"predecessor-version":[{"id":35906,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/35841\/revisions\/35906"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=35841"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=35841"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=35841"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=35841"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}