Re: [HACKERS] On columnar storage (2)

Hey Alvaro, I was referring to https://wiki.postgresql.org/wiki/ColumnOrientedSTorage . and yes, I'll be at the next fosdem / pgconf.eu for sure. :-) Bert On Thu, Mar 3, 2016 at 3:40 PM, Alvaro Herrera wrote: > Bert wrote: > > > Alvaro, > > You wrote that a wiki page

Re: [HACKERS] On columnar storage (2)

Haribabu Kommi wrote: > The performance report is taken on the patch that is WIP columnar storage > on PostgreSQL database. Only the storage part of the code is finished. > To test the performance, we used custom plan to generate the plans > where it can use the columnar storage. This way we ran

Re: [HACKERS] On columnar storage (2)

Bert wrote: > Alvaro, > You wrote that a wiki page would be opened regarding this. But I still > cannot find such a page (expect for an old page which hasn't changed in the > last year). Is there already something we can look at? Yeah, I haven't done that yet. I will post here as soon as I get

Re: [HACKERS] On columnar storage (2)

On Thu, Mar 3, 2016 at 7:46 PM, Bert wrote: > > Thank you for the performance test. But please not that the patch is 'thrown > away', and will be totally rewritten. I have no idea of the status of the > second / third attempt however. > However, what is interesting is that for

Re: [HACKERS] On columnar storage (2)

Hello Haribabu, Thank you for the performance test. But please not that the patch is 'thrown away', and will be totally rewritten. I have no idea of the status of the second / third attempt however. However, what is interesting is that for some queries this patch is already on par with VCI. Which

Re: [HACKERS] On columnar storage (2)

On Mon, Feb 1, 2016 at 12:11 AM, Alvaro Herrera wrote: > So we discussed some of this stuff during the developer meeting in > Brussels and the main conclusion is that we're going to split this up in > multiple independently useful pieces, and write up the general roadmap

Re: [HACKERS] On columnar storage (2)

So we discussed some of this stuff during the developer meeting in Brussels and the main conclusion is that we're going to split this up in multiple independently useful pieces, and write up the general roadmap in the wiki so that we can discuss in detail on-list. I'm marking this as Returned

Re: [HACKERS] On columnar storage (2)

On Mon, Dec 28, 2015 at 2:15 PM, Alvaro Herrera wrote: >> 1. CS API. >> I agree with you that FDW API seems to be not enough to efficiently support >> work with CS. >> At least we need batch insert. >> But may be it is better to extend FDW API rather than creating

Re: [HACKERS] On columnar storage (2)

On 12/28/15 1:15 PM, Alvaro Herrera wrote: Currently within the executor a tuple is a TupleTableSlot which contains one Datum array, which has all the values coming out of the HeapTuple; but for split storage tuples, we will need to have a TupleTableSlot that has multiple "Datum arrays" (in a

Re: [HACKERS] On columnar storage (2)

Konstantin Knizhnik wrote: > 3. Transpose of data and role of CS. > Let's look once again on Quote example above. Data is received in time > ascending order. But most queries require grouping it by symbol. So at some > stage we have to "transpose" data. To efficiently append data to timeseries

Re: [HACKERS] On columnar storage (2)

Konstantin Knizhnik wrote: Hi, > May be you know, that I have implemented IMCS (in-memory-columnar-store) as > PostgreSQL extension. > It was not so successful, mostly because people prefer to use standard SQL > rather than using some special functions for accessing columnar storage > (CS). Now

Re: [HACKERS] On columnar storage (2)

Hi Alvaro, May be you know, that I have implemented IMCS (in-memory-columnar-store) as PostgreSQL extension. It was not so successful, mostly because people prefer to use standard SQL rather than using some special functions for accessing columnar storage (CS). Now I am thinking about second

Re: [HACKERS] On columnar storage (2)

On Tue, Dec 22, 2015 at 11:43 PM, Alvaro Herrera wrote: > Michael Paquier wrote: >> On Wed, Dec 9, 2015 at 3:10 PM, Jeff Janes wrote: >> > Could we get this rebased past the merge of the parallel execution commits? >> >> +1. Alvaro, Tomas, Simon,

Re: [HACKERS] On columnar storage (2)

On Wed, Dec 9, 2015 at 3:10 PM, Jeff Janes wrote: > Could we get this rebased past the merge of the parallel execution commits? +1. Alvaro, Tomas, Simon, what are the next plans with those patches? -- Michael -- Sent via pgsql-hackers mailing list

Re: [HACKERS] On columnar storage (2)

Michael Paquier wrote: > On Wed, Dec 9, 2015 at 3:10 PM, Jeff Janes wrote: > > Could we get this rebased past the merge of the parallel execution commits? > > +1. Alvaro, Tomas, Simon, what are the next plans with those patches? Yeah, I've been working intermittently on

Re: [HACKERS] On columnar storage (2)

Could we get this rebased past the merge of the parallel execution commits? Thanks, Jeff -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] On columnar storage (2)

On Tue, Sep 1, 2015 at 8:53 AM, Alvaro Herrera wrote: > As discussed in > https://www.postgresql.org/message-id/20150611230316.gm133...@postgresql.org > we've been working on implementing columnar storage for Postgres. > Here's some initial code to show our general idea,

Re: [HACKERS] On columnar storage

Jim Nasby wrote: Related to idea of an 'auto join', I do wish we had the ability to access columns in a referenced FK table from a referring key; something like SELECT customer_id.first_name FROM invoice (which would be translated to SELECT first_name FROM invoice JOIN customer USING(

Re: [HACKERS] On columnar storage

On 6/17/15 2:04 PM, Alvaro Herrera wrote: Jim Nasby wrote: Related to idea of an 'auto join', I do wish we had the ability to access columns in a referenced FK table from a referring key; something like SELECT customer_id.first_name FROM invoice (which would be translated to SELECT first_name

Re: [HACKERS] On columnar storage

On 6/14/15 10:22 AM, Alvaro Herrera wrote: To me, it feels like there are two different features here that would be better separated. First, there's the idea of having a table that gets auto-joined to other tables whenever you access it, so that the user sees one really wide table but really

Re: [HACKERS] On columnar storage

http://vldb.org/pvldb/vol5/p1790_andrewlamb_vldb2012.pdf In sketch: There is the concept of a Write-Optimized-Store (WOS) and Read-optimized-store (ROS), and a TupleMover that moves records from WOS to ROS (some what like vacuum), and from ROS to WOS for updates. It seems to me that heap is

Re: [HACKERS] On columnar storage

On 2015-06-11 20:03:16 -0300, Alvaro Herrera wrote: Rewriter Parsing occurs as currently. During query rewrite, specifically at the bottom of the per-relation loop in fireRIRrules(), we will modify the query tree: each relation RTE containing a colstore will be replaced with a JoinExpr

Re: [HACKERS] On columnar storage

Andres Freund and...@anarazel.de writes: On 2015-06-11 20:03:16 -0300, Alvaro Herrera wrote: Parsing occurs as currently. During query rewrite, specifically at the bottom of the per-relation loop in fireRIRrules(), we will modify the query tree: each relation RTE containing a colstore will be

Re: [HACKERS] On columnar storage

Alvaro Herrera alvhe...@2ndquadrant.com writes: Tom Lane wrote: Alvaro Herrera alvhe...@2ndquadrant.com writes: Won't this cause issues to MergeAppend optimizations? Like what? Well, as I understand, MergeAppend needs to know the sort order of the child node, right? But that's available

Re: [HACKERS] On columnar storage

Hi, On 06/13/15 00:07, Michael Nolan wrote: On Thu, Jun 11, 2015 at 7:03 PM, Alvaro Herrera alvhe...@2ndquadrant.com mailto:alvhe...@2ndquadrant.com wrote: We hope to have a chance to discuss this during the upcoming developer unconference in Ottawa. Here are some preliminary ideas

Re: [HACKERS] On columnar storage

Robert Haas wrote: On Thu, Jun 11, 2015 at 7:03 PM, Alvaro Herrera alvhe...@2ndquadrant.com wrote: I've been trying to figure out a plan to enable native column stores (CS or colstore) for Postgres. Motivations: * avoid the 32 TB limit for tables * avoid the 1600 column limit for

Re: [HACKERS] On columnar storage

On 06/14/15 17:22, Alvaro Herrera wrote: Robert Haas wrote: ,,, Second, there's the idea of a way of storing tuples that is different from PostgreSQL's usual mechanism - i.e. a storage manager API. I understand your concerns about going through the FDW API so maybe that's not the right way to

Re: [HACKERS] On columnar storage

Andres Freund wrote: On 2015-06-11 20:03:16 -0300, Alvaro Herrera wrote: Rewriter Parsing occurs as currently. During query rewrite, specifically at the bottom of the per-relation loop in fireRIRrules(), we will modify the query tree: each relation RTE containing a colstore will be

Re: [HACKERS] On columnar storage

Tom Lane wrote: I wrote: Another model that could be followed is expansion of inheritance-tree references, which happens early in the planner. Actually ... if you intend to allow column storage to work with inherited tables (and if you don't, you'd better have a darn good reason why not),

Re: [HACKERS] On columnar storage

Tom Lane wrote: Alvaro Herrera alvhe...@2ndquadrant.com writes: Tom Lane wrote: Actually ... if you intend to allow column storage to work with inherited tables (and if you don't, you'd better have a darn good reason why not), I think you probably want to do this join insertion *after*

Re: [HACKERS] On columnar storage

I wrote: Another model that could be followed is expansion of inheritance-tree references, which happens early in the planner. Actually ... if you intend to allow column storage to work with inherited tables (and if you don't, you'd better have a darn good reason why not), I think you probably

Re: [HACKERS] On columnar storage

Alvaro Herrera alvhe...@2ndquadrant.com writes: Another choice I considered was subquery_planner: in the spot where expand_inherited_tables() is called, add a new call to expand the RTEs that correspond to tables containing stores. But I had second thoughts because that function says it's

Re: [HACKERS] On columnar storage

Tom Lane wrote: Alvaro Herrera alvhe...@2ndquadrant.com writes: Amit Kapila wrote: Will the column store obey snapshot model similar to current heap tuples, if so will it derive the transaction information from heap tuple? Yes, visibility will be tied to the heap tuple -- a value is

Re: [HACKERS] On columnar storage

On 2015-06-14 17:36, Tomas Vondra wrote: On 06/14/15 17:22, Alvaro Herrera wrote: Robert Haas wrote: ,,, Second, there's the idea of a way of storing tuples that is different from PostgreSQL's usual mechanism - i.e. a storage manager API. I understand your concerns about going through the

Re: [HACKERS] On columnar storage

Alvaro Herrera alvhe...@2ndquadrant.com writes: Tom Lane wrote: Actually ... if you intend to allow column storage to work with inherited tables (and if you don't, you'd better have a darn good reason why not), I think you probably want to do this join insertion *after* inheritance expansion,

Re: [HACKERS] On columnar storage

Tom Lane wrote: Alvaro Herrera alvhe...@2ndquadrant.com writes: Tom Lane wrote: Alvaro Herrera alvhe...@2ndquadrant.com writes: Won't this cause issues to MergeAppend optimizations? Like what? Well, as I understand, MergeAppend needs to know the sort order of the child node,

Re: [HACKERS] On columnar storage

On Sun, Jun 14, 2015 at 10:30 AM, Tomas Vondra tomas.von...@2ndquadrant.com wrote: Are you looking to avoid all hardware-based limits, or would using a 64 bit row pointer be possible? That would give you 2^64 or 1.8 E19 unique rows over whatever granularity/uniqueness you use (per table,

Re: [HACKERS] On columnar storage

Tom Lane wrote: I can't help thinking that this could tie in with the storage level API that I was waving my arms about last year. Or maybe not --- the goals are substantially different --- but I think we ought to reflect on that rather than just doing a narrow hack for column stores used in

Re: [HACKERS] On columnar storage

Alvaro Herrera alvhe...@2ndquadrant.com writes: Tom Lane wrote: I can't help thinking that this could tie in with the storage level API that I was waving my arms about last year. Or maybe not --- the goals are substantially different --- but I think we ought to reflect on that rather than

Re: [HACKERS] On columnar storage

Merlin Moncure wrote: On Thu, Jun 11, 2015 at 6:03 PM, Alvaro Herrera alvhe...@2ndquadrant.com wrote: We hope to have a chance to discuss this during the upcoming developer unconference in Ottawa. Here are some preliminary ideas to shed some light on what we're trying to do. Quick

Re: [HACKERS] On columnar storage

On Thu, Jun 11, 2015 at 7:03 PM, Alvaro Herrera alvhe...@2ndquadrant.com wrote: We hope to have a chance to discuss this during the upcoming developer unconference in Ottawa. Here are some preliminary ideas to shed some light on what we're trying to do. I've been trying to figure out a

Re: [HACKERS] On columnar storage

On Thu, Jun 11, 2015 at 6:03 PM, Alvaro Herrera alvhe...@2ndquadrant.com wrote: We hope to have a chance to discuss this during the upcoming developer unconference in Ottawa. Here are some preliminary ideas to shed some light on what we're trying to do. Quick thought. We already support out

Re: [HACKERS] On columnar storage

Amit Kapila wrote: On Fri, Jun 12, 2015 at 4:33 AM, Alvaro Herrera alvhe...@2ndquadrant.com wrote: There are several parts to this: 1. the CSM API 2. Cataloguing column stores 3. Query processing: rewriter, optimizer, executor I think another important point is about the format

Re: [HACKERS] On columnar storage

Alvaro Herrera alvhe...@2ndquadrant.com writes: Amit Kapila wrote: Will the column store obey snapshot model similar to current heap tuples, if so will it derive the transaction information from heap tuple? Yes, visibility will be tied to the heap tuple -- a value is accessed only when its

[HACKERS] On columnar storage

We hope to have a chance to discuss this during the upcoming developer unconference in Ottawa. Here are some preliminary ideas to shed some light on what we're trying to do. I've been trying to figure out a plan to enable native column stores (CS or colstore) for Postgres. Motivations: *

Re: [HACKERS] On columnar storage

On Fri, Jun 12, 2015 at 4:33 AM, Alvaro Herrera alvhe...@2ndquadrant.com wrote: We hope to have a chance to discuss this during the upcoming developer unconference in Ottawa. Here are some preliminary ideas to shed some light on what we're trying to do. I've been trying to figure out a

Re: [HACKERS] On columnar storage

Josh, * Josh Berkus (j...@agliodbs.com) wrote: On 06/11/2015 04:03 PM, Alvaro Herrera wrote: We hope to have a chance to discuss this during the upcoming developer unconference in Ottawa. Here are some preliminary ideas to shed some light on what we're trying to do. Added to:

Re: [HACKERS] On columnar storage

On 06/11/2015 04:03 PM, Alvaro Herrera wrote: We hope to have a chance to discuss this during the upcoming developer unconference in Ottawa. Here are some preliminary ideas to shed some light on what we're trying to do. Added to: