Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hUG1I-0005Og-A3 for pgsql-performance@arkaria.postgresql.org; Fri, 24 May 2019 19:38:24 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1hUG1H-00046y-02 for pgsql-performance@arkaria.postgresql.org; Fri, 24 May 2019 19:38:23 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hUG1G-00046q-Mw for pgsql-performance@lists.postgresql.org; Fri, 24 May 2019 19:38:22 +0000 Received: from tamriel.snowman.net ([96.255.250.162]) by magus.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1hUG1A-0001k3-BM for pgsql-performance@lists.postgresql.org; Fri, 24 May 2019 19:38:22 +0000 Received: by tamriel.snowman.net (Postfix, from userid 1000) id 35AE75F79F; Fri, 24 May 2019 15:38:15 -0400 (EDT) Date: Fri, 24 May 2019 15:38:15 -0400 From: Stephen Frost To: Lev Kokotov Cc: pgsql-performance@lists.postgresql.org Subject: Re: Use Postgres as a column store by creating one table per column Message-ID: <20190524193815.GE2480@tamriel.snowman.net> References: MIME-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha512; protocol="application/pgp-signature"; boundary="OAsA6xQjj2jRKPG2" Content-Disposition: inline In-Reply-To: User-Agent: Mutt/1.5.24 (2015-08-30) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --OAsA6xQjj2jRKPG2 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline Greetings, * Lev Kokotov (lev.kokotov@gmail.com) wrote: > Is it efficient to use Postgres as a column store by creating one table per > column? Short answer is no, not in a traditional arrangement, anyway. The tuple overhead would be extremely painful. It's possible to improve on that, but it requires sacrificing what the tuple header gives you- visibility information, along with some other things. The question will be if that's acceptable or not. > I'm thinking since Postgres stores tables in continuous blocks of 16MB each > (I think that's the default page size?) I would get efficient reads and > with parallel queries I could benefit from multiple cores. The page size in PG is 8k, not 16MB. Thanks, Stephen --OAsA6xQjj2jRKPG2 Content-Type: application/pgp-signature; name="signature.asc" -----BEGIN PGP SIGNATURE----- Version: GnuPG v1 iQIcBAEBCgAGBQJc6EgmAAoJEO1sijiDR2RVhV0P/3kLAGuiHDIUfWHpWJ6MwU0y Slbl5z5zjEcovUxnzGqnpNTgxemWEaXFpBnoWc9WTlhyeum564P1lwLQPOxzy/+U dD9MdUbFXTD7mzBPzS43oAGCmx3hax1Ncp8hXNkOJpMi+RMuEfyHvAZeZ66Y/VGX 1IVk06bjFk6zrAjlbto4z33Xo9bzH4K0P+PZIfmcqXxSzwokD9fT1fvMgltCEhDM o2lIskawt25nMQbFRNYIq+r0U8jeg77KfeF8K8+0XFQ9ZemM6oKW5IpTQr4hbOdY 7fPWDJKTIJEn+eFWJsndWEb8/fqmZnT3l+tZn8aLOt9w+Xo4vbWZCbW3CbHg5SZ5 FuZKxblE/NJzqayZmZcXvySjL1NEcncQDikWvDioT0Hu3kYbuBYxCYuFL/WGJus3 uI7m8gg2KBIbVQpchbQ0wdg6ogPeo7k+gwl5zSMC0aGwPqkmQRZKIbW8jCVknuIS B2LAsog0ByMDk/rlExUXSptOHPLo1Z406vCpAI/Ye2qaRm4NDo6Ry9GyTuQ5dqmO fPv4s10u2y8i7qgp9MzR5JGa6kxoq9AvA/oQguKhmHZ7W2fgJPMmHEhvrKnqxRCd Ie21Xqnz+7ovBV2niGu2NrX/RQW9MEY8RtBJKnMXItK2YNWR1k8RTRz5rybZyIFj AFfDH3kZNClALQM2UXrJ =3DC/ -----END PGP SIGNATURE----- --OAsA6xQjj2jRKPG2--