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 <pgsql-performance-owner+archive@lists.postgresql.org>)
	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 <pgsql-performance-owner+archive@lists.postgresql.org>)
	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 <gneuner2@comcast.net>)
	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 <gneuner2@comcast.net>)
	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 <gneuner2@comcast.net>
Subject: Re: Use Postgres as a column store by creating one table per column
Message-ID: <d871e346-bd77-1081-42b8-af31bc8ed6ed@comcast.net>
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: <pgsql-performance.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-performance@lists.postgresql.org>
List-Owner: <mailto:pgsql-performance-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-performance>
Precedence: bulk

On Tue, 21 May 2019 21:28:07 -0700, Lev Kokotov <lev.kokotov@gmail.com> 
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 <table>
 >FROM <table> WHERE value = <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