Re: Need advice for handling big data in postgres - Mailing list pgsql-general

On 1/15/15 7:42 AM, Vincent Veyron wrote:
> On Wed, 14 Jan 2015 11:42:45 +1100
> Tobias Fielitz <tobias@streethawk.com> wrote:
>>
>> OPTION 1 - PARTITIONING:
>> For each query only a few columns are interesting and I could partition
>> the table (as it was suggested on SO)
>> by *created* and by *code*.
>> There is roughly 10 different codes and I would keep data for the last
>> two months (partitioned by day). So I would end up having 10 * 60 = 600
>> partitions.
>> For every partition I could create indexes that are relevant to that
>> partition (examples: *created*, *created_on_server* or *latitude* and
>> *longitude*).
>>
>> OPTION 2 - MULTIPLE TABLES:
>> I could create the tables myself: one for location log lines, one for
>> comment log lines etc. and store them via python in the correct table
>> (depending on *code*). Each of these tables would only have the columns
>> and indexes needed.
>>
>> OUTCOME:
>> I expect partitioning to be faster because Postgres selects the correct
>> partition for me automatically. I can easily get rid of old data by
>> dropping the corresponding partition. The downside of the partition
>> approach is that all partitions inherit all columns of the master table
>> which is unnecessary (and consumes disc space?).
>>
>
> I gather from the comments in this list that null fields have a very low overhead; see :
>
> http://www.postgresql.org/message-id/87prx92lj9.fsf@oxford.xeocode.com

Roughly speaking storing a NULL costs some extra CPU and that's it. (There's a fixed-size NULL bitmap; if a field is
nullthen it's marked as such in the bitmap and nothing is stored in the tuple). 

> I would worry a lot more about the maintenance problems option 2 will induce : if a code value changes or is
added/deleted,your python script needs updating. SQL queries will also be a lot more complicated (union select on
varioustables) and harder to optimize. Maintaining the coherence between the script and the tables will get harder and
harder.

Actually, because there are different pieces of data needed for different codes, this is a great use for inheritance
insteadof just simple partitioning. That would allow you to put only the common fields in the parent table, and then
eachchild table adds whatever fields make sense. You can then use the parent table for cases where you don't care about
code-specificfields and use the specific code tables when you do (note that you don't want to insert into the parent
tablethough). I have a hard time buying the code maintenance argument since you'll have that problem no matter what. 

That said, dealing with 3 billion rows is a pretty tall order. At a minimum you'd need time partitioning (in addition
toinheritance if you go that route). I'd partition by day, or maybe even hour. 

Something to consider... on a 64-bit machine your tuple header will be 32 bytes and the 6 NOT NULL fixed-width fields
willbe 6*8=48 bytes. So you're already at 70 * 3B = 210GB. That's ignoring all other data, page overhead, fill factor
andindexes. I believe those indexes will be another 16 bytes per row (24B for the 2 field one) so ~300GB, again
ignoringfill factor and non-leaf pages. You'll need a fairly beefy server to make that perform well. Depending on your
insertrate and access patterns I think you could handle this with one server for writing and a separate one for
reading,but you'll have to be careful. 

BTW, inheritance (and by extension partitioning) doesn't affect binary replication. The only trick with logical
replicationis the need to add new partitions to the replication system as you create them. 
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com