From lev.kokotov@gmail.com Wed May 22 04:28:07 2019 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 1hTIrY-00086g-96 for pgsql-performance@arkaria.postgresql.org; Wed, 22 May 2019 04:28: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 1hTIrX-0001Fa-3a for pgsql-performance@arkaria.postgresql.org; Wed, 22 May 2019 04:28: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 1hTIrW-0001FT-RI for pgsql-performance@lists.postgresql.org; Wed, 22 May 2019 04:28:22 +0000 Received: from mail-ot1-x343.google.com ([2607:f8b0:4864:20::343]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hTIrU-0004q8-Q6 for pgsql-performance@lists.postgresql.org; Wed, 22 May 2019 04:28:22 +0000 Received: by mail-ot1-x343.google.com with SMTP id c3so873921otr.3 for ; Tue, 21 May 2019 21:28:20 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=mime-version:from:date:message-id:subject:to; bh=n2ZhQnISciWwMda+5J79BGnDp0GcHhBfaxIofFbknEw=; b=GdOJeaDy/F5UTcmX1QAmYHVGLL0sTo0+XIxyvD06/EJm6wjtSFXhiAeio2CfKgbL0w e9iMvw4qo4jf1hatGtU2QL+xcrBO0ZZkfWc6Ki2oodtRyj4m9KPbWOH01J07JPgTXeSi YbDpgCSt2JbicKRP90M7vavNWs2jKRiby3NLJVUdB8iKF9v6XaZ78kFuXJ5BwzXMElH4 fg2msvpDr8r/nLs5g21NXwaiSRKu9J7KtCj/bRxtg2w5gmO9kCoqBerQ014IdbWPIwCb vT5Uks0419qTBFIj+oGdceXCMmR8IU2YTrGNi39Gd3qRP/XcmIg1OIhnRcOZ5V7XJaKF b5eA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:from:date:message-id:subject:to; bh=n2ZhQnISciWwMda+5J79BGnDp0GcHhBfaxIofFbknEw=; b=MX9ec9JrbeqipC9gRdI4X62AJTsF6kWMGs3sGwej+KnbIMmUUtJmm+2/znTcd4nF26 RDuhswrql5C7Wwh0UY5MYFVaEx7e8iin/Pd4rsbGYYbHe2Tnt90kOLeK5i+n2/PC3fyG z05Uz1vOxguftYwN922PtEGS43995xbSb2Ww6zKrMp4o7oHs3HF424gVan41Eamm+NsQ CGICPjgS9NTIvkvwankKjK5g6UtL1rsVgAvS8/QDvY1fnp973KS8cgF/CVX76Ri3mdLt tKSgb0zvkoY6ktsDrFszP254KwJqlQgZXa4OAYcB9Sd+g7lHFNUJpOn9Xole5NH3CtN5 SP3Q== X-Gm-Message-State: APjAAAWLDkM/gANVDJWzVQ6XPFO43ZSEeJ69b4DHmmQxfejOfsulj5YQ GNJg0qOfKejEU7SBUveGmG0PkACeIfoy929qJdiohe5+tKY= X-Google-Smtp-Source: APXvYqykIkvDMxlicMingp+kupO9isW7yYONf3rSHHCXkrzGQo8jlxvD643j5t4JyF3qZY/2afLx1vsnN2G9oDRjrfA= X-Received: by 2002:a9d:7dd5:: with SMTP id k21mr25618918otn.167.1558499298781; Tue, 21 May 2019 21:28:18 -0700 (PDT) MIME-Version: 1.0 From: Lev Kokotov Date: Tue, 21 May 2019 21:28:07 -0700 Message-ID: Subject: Use Postgres as a column store by creating one table per column To: pgsql-performance@lists.postgresql.org Content-Type: multipart/alternative; boundary="000000000000162fd90589726575" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --000000000000162fd90589726575 Content-Type: text/plain; charset="UTF-8" Is it efficient to use Postgres as a column store by creating one table per column? I would query it with something like `[...] UNION SELECT value AS FROM
WHERE value = UNION [...]` to build a row. 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. Thanks! Best, Lev --000000000000162fd90589726575 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Is it efficient to use Postgres as a column sto= re by creating one table per column?

I would query it with something= like `[...] UNION SELECT value AS <table> FROM <table> WHERE v= alue =3D <value> UNION [...]` to build a row.

I'm thinking= since Postgres stores tables in continuous blocks of 16MB each (I think th= at's the default page size?) I would get efficient reads and with paral= lel queries I could benefit from multiple cores.

=
Thanks!

Best,
Lev
--000000000000162fd90589726575-- From pryzby@telsasoft.com Wed May 22 04:43:09 2019 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 1hTJ5z-0000Nf-CY for pgsql-performance@arkaria.postgresql.org; Wed, 22 May 2019 04:43:19 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1hTJ5x-00041a-6D for pgsql-performance@arkaria.postgresql.org; Wed, 22 May 2019 04:43:17 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hTJ5w-00041R-K8 for pgsql-performance@lists.postgresql.org; Wed, 22 May 2019 04:43:16 +0000 Received: from mail-io1-xd44.google.com ([2607:f8b0:4864:20::d44]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hTJ5t-0003EB-BN for pgsql-performance@lists.postgresql.org; Wed, 22 May 2019 04:43:15 +0000 Received: by mail-io1-xd44.google.com with SMTP id u2so795809ioc.4 for ; Tue, 21 May 2019 21:43:12 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=telsasoft-com.20150623.gappssmtp.com; s=20150623; h=date:from:to:cc:subject:message-id:references:mime-version :content-disposition:in-reply-to:user-agent; bh=6VMLcIa+OC9zzeI+ECp+S1tuVRN1t1JvKk61Q1e/LNM=; b=FRG/dk9JEYWSwY+N5uWhm2aBZqRsjXvsaqEsEteslavhZh57dW1yLOIBgK7BxZjPDH ORkH9/fOzSD3+w3OCOl/ykC0WGLKARoVSaOK0OeWnYm+7lRUOIrrx4Ayq+1os6hl2MRN 7Wg2VbwgL3pdplkLBAL6F0utfRPJel1b8tu6WLcVszUf1EILB7oaXxWboMhAAC/ch3bK 0xodsHFaZ+mRuAJehprO59CsCOtUnWzPqgdFPTQ2kvoGpUvNYNN98LLR3/5lVIga1H6k 9J7Y4N13M9wwz3TzUypzX5E77baIu1+XKhRcuAhOPKoLETJJRU4Ud4Jwgwgg2RNaBsGd rWeA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:date:from:to:cc:subject:message-id:references :mime-version:content-disposition:in-reply-to:user-agent; bh=6VMLcIa+OC9zzeI+ECp+S1tuVRN1t1JvKk61Q1e/LNM=; b=orsIUBfREhS6g2rm2lJ4goqwYINN/6Azw+wKvNPy/S/uDpnuXULtsNzPehYaGP1hou hVLsc2iOP67TFtX+CMFQcbXMKYYwVqpUXG8rAlvLLk0TYR0LzAwzYDYjUNeEm3cDzyYp j4uhItiONcQDGYbGSDj3PN12vb15JE4aiRtteXngZlS61FRgajWSvwRMJZVvnALPQzVr KtdF1loF9S5xvi2FSFTMUzwjINpWyOoLEfNJnHKlQQ2Xeohb3Se03M55Hbp0ySeDjIwN XWgMxRjaIDol20KbX+4RS8do3QZIP44XRuBinmzIkCBWVN7yvqAPpMdak2OVfR8OBYYV jbSQ== X-Gm-Message-State: APjAAAWnZxVhfM4LxHfdQ0aaYnNH1fOiW42zxdKZESV1TKtug+PTnYVb 43wsE21E02hyfclKyMuWKXVBbg== X-Google-Smtp-Source: APXvYqx5afQe7sx+i2WyxVf9e2EzKWgJ9f5BIGxpP7/dudslq7yfhkp/uUA2VmWm9mdNEJLCrE37uw== X-Received: by 2002:a5d:8713:: with SMTP id u19mr15523766iom.222.1558500191950; Tue, 21 May 2019 21:43:11 -0700 (PDT) Received: from pryzbyj (charmander.telsasoft.com. [50.244.222.1]) by smtp.gmail.com with ESMTPSA id u195sm175779ita.34.2019.05.21.21.43.10 (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Tue, 21 May 2019 21:43:11 -0700 (PDT) Received: by pryzbyj (Postfix, from userid 1000) id 0E575800AA7; Tue, 21 May 2019 23:43:10 -0500 (CDT) Date: Tue, 21 May 2019 23:43:09 -0500 From: Justin Pryzby 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: <20190522044309.GI4426@telsasoft.com> References: MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii 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 On Tue, May 21, 2019 at 09:28:07PM -0700, Lev Kokotov wrote: > Is it efficient to use Postgres as a column store by creating one table per > column? > > I would query it with something like `[...] UNION SELECT value AS
> FROM
WHERE value = UNION [...]` to build a row. I think you mean JOIN not UNION. It'd be awful (At one point I tried it very briefly). If you were joining 2, 10 column tables, that'd be 19 joins. I imagine the tables would be "serial id unique, float value" or similar, so the execution might not be terrible, as it'd be using an index lookup for each column. But the planner would suffer, badly. Don't even try to read EXPLAIN. Actually, the execution would also be hitting at least 2x files per "column" (one for the index and one for the table data), so that's not great. Also, the overhead of a 2-column table is high, so your DB would be much bigger and have very high overhead. Sorry to reference a 2ndary source, but.. https://stackoverflow.com/questions/13570613/making-sense-of-postgres-row-sizes > 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. Default page size is 8kb Justin From gneuner2@comcast.net Thu May 23 05:08:42 2019 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 1hTfyP-0001qd-QK for pgsql-performance@arkaria.postgresql.org; Thu, 23 May 2019 05:09:02 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1hTfyN-0001ti-L8 for pgsql-performance@arkaria.postgresql.org; Thu, 23 May 2019 05:08:59 +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 1hTfyN-0001lc-13 for pgsql-performance@lists.postgresql.org; Thu, 23 May 2019 05:08:59 +0000 Received: from resqmta-ch2-02v.sys.comcast.net ([2001:558:fe21:29:69:252:207:34]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hTfyE-0007jQ-Te for pgsql-performance@postgresql.org; Thu, 23 May 2019 05:08:57 +0000 Received: from resomta-ch2-17v.sys.comcast.net ([69.252.207.113]) by resqmta-ch2-02v.sys.comcast.net with ESMTP id Tfp2h2DZ4jYLRTfy9h7CIN; Thu, 23 May 2019 05:08:45 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=comcast.net; s=20190202a; t=1558588125; bh=0Ccg3NN7BLu19/rtadRoxlHEmmdkXF30cR+bwL+1Y/w=; h=Received:Received:To:From:Subject:Message-ID:Date:MIME-Version: Content-Type; b=IV9kVtMoSUoM/ac2/b3qqxiikdf7IHSpN5ID9hLkKNc7SyhD+qBd4y8xSGHdoqxTN aoAyKyKedsow9X8/Os2HpcHAOKzkL1jQDYDj12znXoIxOI3R2+DAON7ZPNC/xA6U8K m0PG0EERMxP5eSySBBb1IKkbRk7CewKXNQsD6Gl5bOgxzCMyPLMw07JqPb+u6yOAi3 +tALpNesn1tCCR9Hqf/g/YodslD2ltLglq73uOMsq1g68FioZkRmb3mvybDrvy5BqX c3QI3oRXlj3UmMgv0csVCCWeOIcQHei8Z1Hf2b9yrZ1altJcvn3GltFHwtLYMji2BG uFFp/Vz9uJ7Dg== Received: from [IPv6:2601:19c:4702:1a94:b10e:58cb:879f:8be0] ([IPv6:2601:19c:4702:1a94:b10e:58cb:879f:8be0]) by resomta-ch2-17v.sys.comcast.net with ESMTPSA id Tfy7hCkuN4yS6Tfy9hUbPy; Thu, 23 May 2019 05:08:45 +0000 X-Xfinity-VMeta: sc=0;st=legit To: pgsql-performance@postgresql.org From: George Neuner Subject: Re: Use Postgres as a column store by creating one table per column Message-ID: Date: Thu, 23 May 2019 01:08:42 -0400 User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:60.0) Gecko/20100101 Thunderbird/60.7.0 MIME-Version: 1.0 Content-Type: text/plain; charset=windows-1252; format=flowed Content-Transfer-Encoding: 8bit Content-Language: en-US List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk On Tue, 21 May 2019 21:28:07 -0700, Lev Kokotov wrote: >Is it efficient to use Postgres as a column store by creating one table per >column? > >I would query it with something like `[...] UNION SELECT value AS
>FROM
WHERE value = UNION [...]` to build a row. I think you mean JOIN. You'd need more than that: Postgresql uses MVCC for concurrency, so whenever you update any row in a table, the ordering of the rows within the table changes.  And the JOIN operation inherently is unordered - you need to sort the result deliberately to control ordering. To emulate a column-store, at the very least you need a way to associate values from different "columns" that belong to the same "row" of the virtual table.  IOW, every value in every "column" needs an explicit "row" identifier.  E.g.,    col1 = { rowid, value1 }, col2 = { rowid, value2 }, ... For performance you would need to have indexes on at least the rowid in each of the "column" tables. This is a bare minimum and can only work if the columns of your virtual table and the queries against it are application controlled or statically known.  If you want to do something more flexible that will support ad hoc table modifications, elastically sized values (strings, bytes, arrays, JSON, XML), etc. this example is not suffice and the implementation can get very complicated very quickly Justin Pryzby was not joking when he said the performance could be awful ... at least as compared to a more normal row-oriented structure.  Performance of a query that involves more than a handful of "columns", in general, will be horrible.  It is up to you to decide whether some (maybe little) increase in performance in processing *single* columns will offset likely MASSIVE loss of performance in processing multiple columns. >I'm thinking since Postgres stores tables in continuous blocks of 16MB each >(I think that's the default page size?) Default page size is 8 KB.  You'd have to recompile to change that, and it might break something - a whole lot of code depends on the knowing the size of storage pages. George From tomas.vondra@2ndquadrant.com Fri May 24 18:06:29 2019 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 1hUEac-0001ad-L2 for pgsql-performance@arkaria.postgresql.org; Fri, 24 May 2019 18:06:47 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1hUEab-0003G6-70 for pgsql-performance@arkaria.postgresql.org; Fri, 24 May 2019 18:06:45 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hUEaa-00031U-K2 for pgsql-performance@lists.postgresql.org; Fri, 24 May 2019 18:06:44 +0000 Received: from mail-wm1-x343.google.com ([2a00:1450:4864:20::343]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hUEaX-0005Pu-5c for pgsql-performance@postgresql.org; Fri, 24 May 2019 18:06:43 +0000 Received: by mail-wm1-x343.google.com with SMTP id v19so1748162wmh.0 for ; Fri, 24 May 2019 11:06:40 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=2ndquadrant-com.20150623.gappssmtp.com; s=20150623; h=date:from:to:cc:subject:message-id:references:mime-version :content-disposition:content-transfer-encoding:in-reply-to :user-agent; bh=yKHY8nyzrDhecPN7frkh4uxC16hf9N4S3qSEKSCbdkQ=; b=zAwGZJ23yoHUzh3q2OeWZSXWL9aYQC+nZs23rM7yQpbsFteTagQtA8Tuz7yZNLYRod BqfPuQEpOv84SGP/XfFyFLCgdjTRMe2a8pplsWDAs98pPcjQUycIlYPQEEKaWa7OHe15 Ce4VxIDTuhNmu9A4vq29kG9GpZBbXtGRciEAaUzFL6ucIcAT1JQuXCS5E/3MxWywjeSL wZJLqYOCSyVwXTPwFWRji67XG79ItqDWyEMmunrlBoIOeO5+iBwl6wbxbC1fwVbkOtCr bDauxUYBE5W17PJDPVTGz/dbVUWOM//TG3QjWHfMaRTAoyIPTvTEYwiMA3uWqfQcFSQP XqKQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:date:from:to:cc:subject:message-id:references :mime-version:content-disposition:content-transfer-encoding :in-reply-to:user-agent; bh=yKHY8nyzrDhecPN7frkh4uxC16hf9N4S3qSEKSCbdkQ=; b=gtkcDNJfvYh4E86YolfoZUib5ORtjCugIgVrJAwIyDYfhDC3GRGsTI6tFy1oaWzHAd +8k54yeCwBpJlE3dSwaoNTjxRynYvHA610JkJaLoSfYY6L8inMi31JLgd/CyRSzEw/kg koKWWyHIiZj7qbqsRfJkfqmG6XwDv20P+F9zBrJFg35eFMR/867MWOtpYwpbYur46BVb cL/kead9L5xRXjBSAIig+Y+Lb1yzxeSVDfjAmL0mMMf+u/TX4GE79m4h/h1qYlmy4fSU CEg0z/V/PpFHZfz1dHTsvXrBEnB0bAkbKLO3r8lxl+Zlt8dDsI4+iOEABsdCeSnwBvj/ KthA== X-Gm-Message-State: APjAAAWZUfJztM5j24jjQhOfitb7wP4Ved+0qmnsUSaPzxRAQ2uDmB6x EbduE0n29+rwSQCId1vRTxv71fcCpkA= X-Google-Smtp-Source: APXvYqw/7Eyubx+9zk/9AXKHSYnZbYT15z8rI0hyNHLgOx64PEPzE0IoKnBcfnJnMPvu+xKPll/6vw== X-Received: by 2002:a7b:cb01:: with SMTP id u1mr16600680wmj.153.1558721199308; Fri, 24 May 2019 11:06:39 -0700 (PDT) Received: from localhost (ip-86-49-243-43.net.upcbroadband.cz. [86.49.243.43]) by smtp.gmail.com with ESMTPSA id x6sm4512469wru.36.2019.05.24.11.06.30 (version=TLS1_3 cipher=AEAD-AES256-GCM-SHA384 bits=256/256); Fri, 24 May 2019 11:06:31 -0700 (PDT) Date: Fri, 24 May 2019 20:06:29 +0200 From: Tomas Vondra To: George Neuner Cc: pgsql-performance@postgresql.org Subject: Re: Use Postgres as a column store by creating one table per column Message-ID: <20190524180629.42d3u4m7a3jgh5hq@development> References: MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1; format=flowed Content-Disposition: inline Content-Transfer-Encoding: 8bit In-Reply-To: User-Agent: NeoMutt/20180716-1444-295967 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk On Thu, May 23, 2019 at 01:08:42AM -0400, George Neuner wrote: >On Tue, 21 May 2019 21:28:07 -0700, Lev Kokotov > wrote: > >>Is it efficient to use Postgres as a column store by creating one >table per >>column? >> >>I would query it with something like `[...] UNION SELECT value AS
>>FROM
WHERE value = UNION [...]` to build a row. > >I think you mean JOIN. > >You'd need more than that: Postgresql uses MVCC for concurrency, so >whenever you update any row in a table, the ordering of the rows >within the table changes.  And the JOIN operation inherently is >unordered - you need to sort the result deliberately to control >ordering. > >To emulate a column-store, at the very least you need a way to >associate values from different "columns" that belong to the same >"row" of the virtual table.  IOW, every value in every "column" needs >an explicit "row" identifier.  E.g., > >   col1 = { rowid, value1 }, col2 = { rowid, value2 }, ... > >For performance you would need to have indexes on at least the rowid >in each of the "column" tables. > >This is a bare minimum and can only work if the columns of your >virtual table and the queries against it are application controlled or >statically known.  If you want to do something more flexible that will >support ad hoc table modifications, elastically sized values (strings, >bytes, arrays, JSON, XML), etc. this example is not suffice and the >implementation can get very complicated very quickly > > >Justin Pryzby was not joking when he said the performance could be >awful ... at least as compared to a more normal row-oriented >structure.  Performance of a query that involves more than a handful >of "columns", in general, will be horrible.  It is up to you to decide >whether some (maybe little) increase in performance in processing >*single* columns will offset likely MASSIVE loss of performance in >processing multiple columns. > Maybe take a look at this paper: http://db.csail.mit.edu/projects/cstore/abadi-sigmod08.pdf which essentially compares this approach to a "real" column store. It certainly won't give you performance comparable to column store, it adds quite a bit of overhead (disk space because of row headers, CPU because of extra joins, etc.). And it can't give you the column-store benefits - compression and/or more efficient execution. > >>I'm thinking since Postgres stores tables in continuous blocks of >16MB each >>(I think that's the default page size?) > >Default page size is 8 KB.  You'd have to recompile to change that, >and it might break something - a whole lot of code depends on the >knowing the size of storage pages. > > Right. And the largest page size is 64kB. But 8kB is a pretty good trade-off, in most cases. regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services From sfrost@snowman.net Fri May 24 19:38:15 2019 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-- From mark.kirkwood@catalyst.net.nz Sat May 25 03:55:52 2019 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 1hUNnD-0001CZ-8U for pgsql-performance@arkaria.postgresql.org; Sat, 25 May 2019 03:56: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 1hUNn9-0001Js-3U for pgsql-performance@arkaria.postgresql.org; Sat, 25 May 2019 03:56:19 +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 1hUNn7-0001Bo-Tb for pgsql-performance@lists.postgresql.org; Sat, 25 May 2019 03:56:18 +0000 Received: from cat-porwal-prod-mail1.catalyst.net.nz ([2404:130:4080::4]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hUNmx-0006su-El for pgsql-performance@lists.postgresql.org; Sat, 25 May 2019 03:56:15 +0000 Received: from [IPv6:2406:e001:800:f801:290b:e6af:8a14:538b] (unknown [IPv6:2406:e001:800:f801:290b:e6af:8a14:538b]) (Authenticated sender: mark.kirkwood@catalyst.net.nz) by cat-porwal-prod-mail1.catalyst.net.nz (Postfix) with ESMTPSA id 8282E81068 for ; Sat, 25 May 2019 15:55:55 +1200 (NZST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=catalyst.net.nz; s=default; t=1558756556; h=from:from:reply-to:subject:subject:date:date:message-id:message-id: to:to:cc:mime-version:mime-version:content-type:content-type: content-transfer-encoding:content-transfer-encoding: in-reply-to:in-reply-to:references:references; bh=XQyOeVi2YpqOYMM73hHzR8fbNsDwCAq4POzYayHHXDw=; b=ubdiDjQRGFgFw2Th4fqPZWIqgcIcfP96HvWvTmyZEUlhiODPQTBlJavo0fDzgprVtPrvX3 lqGVCCUIPzx2qBY+yPO+hgit3zpoeynnNWBlq5hH5yyudzW3lNnyuCExoeDTnyGG7mDCOh Z4JrirLfpJc9GlWYwxw+JZh1pAJ7V0ni3C8Bln4lSHxHJR8H0yGcGWobTSXKZFcBxa2gYv Svt3quNWFKCDKKKc8zklS5H+AlW/sZ5GQJbhzkQ+e4LsivZp8tG8BmAZSS63Yk0FTf2K0X YlJlxu9XVALkF0geXpxHsng2G8IXtjJHM3oZWvQnPXtMLzcTQeVJwUli8x5tMA== Subject: Re: Use Postgres as a column store by creating one table per column To: pgsql-performance@lists.postgresql.org References: From: Mark Kirkwood Message-ID: <71cff455-6b4d-f969-5fd8-9b77c9a2c298@catalyst.net.nz> Date: Sat, 25 May 2019 15:55:52 +1200 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:60.0) Gecko/20100101 Thunderbird/60.6.1 MIME-Version: 1.0 In-Reply-To: Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 7bit Content-Language: en-US ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=catalyst.net.nz; s=default; t=1558756557; h=from:from:reply-to:subject:subject:date:date:message-id:message-id: to:to:cc:mime-version:mime-version:content-type:content-type: content-transfer-encoding:content-transfer-encoding: in-reply-to:in-reply-to:references:references; bh=XQyOeVi2YpqOYMM73hHzR8fbNsDwCAq4POzYayHHXDw=; b=oqT6f1G9Ohi9l9KUOkOqnf5I45ONWGLHTpcjDOp9mRqJ0Rc4daJ/bbhpluLpRylRi9vS5J se5S/5hesQQT2eVQoOxiFKBKs5P/3MS7JYE/IufLxcOfj8MFSBy/huksJ2iAuqB7uLxtQ0 z/nNtvNIZJe9oXJORGf0D3MHBWyBQ8PmXumlso5Z0XDH7TQQulGx1fhJEJs975vUSTayTI KtzW6PzS6kmi5G9zFeaP0HtcYFMD7KPzXfGRwz8A/6JUUWGzv1bw855POF2gMySP4psIXp fG+Qo3E56ZsTxNKt9v5fw5LyEVmQVjJNB757GquGsEVOrSZz01NYKt+r0psLrQ== ARC-Seal: i=1; s=default; d=catalyst.net.nz; t=1558756557; a=rsa-sha256; cv=none; b=fReb3b9G0oZggT85YxAVsoQ1JG7qdx32oeZ/i0WOQBvCd/ZB64Um2bPc5o62wdfxiONYOa ds20nRJW2zvr9FcF39fuAsY+lwNLKbgUNgITXw+puXWTvSegEhvwtilpfH44Xsr1L6qXej G7mLjAsmx7hj9klMw5NShz06Odh9A4PEmp4n8Mse67HmlVyabDE8hLVMFCIlPJNYoXw2l6 G+MJxcJkCiEvc9B27SJi4LtH3N4/iy8donXFBOB0YcFxSC1nuntvA4ANu6Anxy74tvZg2Z grgN4YGcE6nhc2Is2lhVJiBKPX5NUkcDp6BAuhFHODxRzZn+EnPtxiG8bqV/Jw== ARC-Authentication-Results: i=1; ORIGINATING; auth=pass smtp.auth=mark.kirkwood@catalyst.net.nz smtp.mailfrom=mark.kirkwood@catalyst.net.nz Authentication-Results: ORIGINATING; auth=pass smtp.auth=mark.kirkwood@catalyst.net.nz smtp.mailfrom=mark.kirkwood@catalyst.net.nz List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk On 22/05/19 4:28 PM, Lev Kokotov wrote: > Is it efficient to use Postgres as a column store by creating one > table per column? > > I would query it with something like `[...] UNION SELECT value AS >
FROM
WHERE value = UNION [...]` to build a row. > > 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. > > Take a look at Zedstore, which is a column store built to plug into v12 storage layer: https://www.postgresql.org/message-id/CALfoeiuF-m5jg51mJUPm5GN8u396o5sA2AF5N97vTRAEDYac7w%40mail.gmail.com