Content-Type: multipart/alternative;
 boundary="------------C9cGgG0ZFLPpUrAcXqf5Z4ba"
Message-ID: <9abb8f15-001e-8aa8-d930-fe5af71f829c@gmail.com>
Date: Tue, 15 Feb 2022 14:13:30 -0700
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:91.0) Gecko/20100101
 Thunderbird/91.6.0
Subject: Re: Storing thousands of csv files in postgresql
Content-Language: en-CA
To: pgsql-sql@lists.postgresql.org
References: 
 <CANbgw4BfmAUsMTsh0z2Y-97jusZObaC+Ez7Mm=papd-WUDd89A@mail.gmail.com>
 <CAJexoSL-RADZAFFtW6NgGNg+SiomNwvcApWpogwuW3EsdGHJXA@mail.gmail.com>
 <CANbgw4D-fkNPwqoh95yJn7jtVsty8NbBMSBME0S5qVHi-43OeA@mail.gmail.com>
 <CAJexoSJwEKFLLuD=LHfHX-+yiaOBD3a8MWG3f969T_FWirj36A@mail.gmail.com>
From: Rob Sargent <robjsargent@gmail.com>
In-Reply-To: 
 <CAJexoSJwEKFLLuD=LHfHX-+yiaOBD3a8MWG3f969T_FWirj36A@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/9abb8f15-001e-8aa8-d930-fe5af71f829c%40gmail.com>
Precedence: bulk

This is a multi-part message in MIME format.
--------------C9cGgG0ZFLPpUrAcXqf5Z4ba
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit


> I don't think you need a "federated" postgres network like Citus at 
> all - I think this solves a different use case. For your design 
> problem, I think that having a bunch of independent Pg servers would 
> be fine - as long as you don't need to run searches across CSV tables 
> stored across different databases (in which case you do need 
> index/search federation of some kind).
>
> Regarding Erik Brandsberg's point about XFS, I think this is a useful 
> alternative approach, if I understand the idea. Instead of storing 
> your CSV files in Postgres, just store them as CSV files on the file 
> system. You can still store the schemas in Pg, but each schema would 
> just point to a file in the file system and you'd manipulate the files 
> in the filesystem using whatever language is appropriate (I find ruby 
> to be excellent for managing CSV files). If you need to index those 
> files to run searches against them, I'd direct your attention to 
> https://prestodb.io/ which is the core technology that runs Amazon 
> Athena. This allows you to search CSV files with various schema (among 
> other data bindings). So you might find that Pg as your schema 
> storage, XFS (or any modern FS) to store large numbers of CSV files, 
> and Presto/Athena to index/search those files, along with some CSV 
> management language (like Ruby or something even higher level) to 
> manage the data.
>
> I think if I were dealing with less than 10k CSV files (and therefore 
> Pg tables), I might use Pg, and if I were dealing with 10k+ files, I'd 
> start looking at file systems + Presto. But that's a WAG.
>
> Steve
>
>
I think the add/remove column requirement alone justifies NOT using 
files.  The CSV approach will temp the system to handle some versioning 
nonsense. Using tables also provides some protection against the 
inevitable garbage data in the CSVs.
--------------C9cGgG0ZFLPpUrAcXqf5Z4ba
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit

<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
    <br>
    <blockquote type="cite"
cite="mid:CAJexoSJwEKFLLuD=LHfHX-+yiaOBD3a8MWG3f969T_FWirj36A@mail.gmail.com">
      <div dir="ltr">
        <div class="gmail_quote">
          <div>I don't think you need a "federated" postgres network
            like Citus at all - I think this solves a different use
            case. For your design problem, I think that having a bunch
            of independent Pg servers would be fine - as long as you
            don't need to run searches across CSV tables stored across
            different databases (in which case you do need index/search
            federation of some kind).</div>
          <div><br>
          </div>
          <div>Regarding Erik Brandsberg's point about XFS, I think this
            is a useful alternative approach, if I understand the idea.
            Instead of storing your CSV files in Postgres, just store
            them as CSV files on the file system. You can still store
            the schemas in Pg, but each schema would just point to a
            file in the file system and you'd manipulate the files in
            the filesystem using whatever language is appropriate (I
            find ruby to be excellent for managing CSV files). If you
            need to index those files to run searches against them, I'd
            direct your attention to <a href="https://prestodb.io/"
              moz-do-not-send="true" class="moz-txt-link-freetext">https://prestodb.io/</a>
            which is the core technology that runs Amazon Athena. This
            allows you to search CSV files with various schema (among
            other data bindings). So you might find that Pg as your
            schema storage, XFS (or any modern FS) to store large
            numbers of CSV files, and Presto/Athena to index/search
            those files, along with some CSV management language (like
            Ruby or something even higher level) to manage the data.</div>
          <div><br>
          </div>
          <div>I think if I were dealing with less than 10k CSV files
            (and therefore Pg tables), I might use Pg, and if I were
            dealing with 10k+ files, I'd start looking at file systems +
            Presto. But that's a WAG.</div>
          <div><br>
          </div>
          <div>Steve</div>
          <div><br>
          </div>
          <div> </div>
        </div>
      </div>
      <br>
    </blockquote>
    I think the add/remove column requirement alone justifies NOT using
    files.  The CSV approach will temp the system to handle some
    versioning nonsense. Using tables also provides some protection
    against the inevitable garbage data in the CSVs. 
  </body>
</html>

--------------C9cGgG0ZFLPpUrAcXqf5Z4ba--