Re: Need advice for handling big data in postgres

On 1/15/15 7:42 AM, Vincent Veyron wrote:
> On Wed, 14 Jan 2015 11:42:45 +1100
> Tobias Fielitz <tobias(at)streethawk(dot)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 null then 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 various tables) 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 instead of just simple partitioning. That would allow you to put only the common fields in the parent table, and then each child table adds whatever fields make sense. You can then use the parent table for cases where you don't care about code-specific fields and use the specific code tables when you do (note that you don't want to insert into the parent table though). 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 to inheritance 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 will be 6*8=48 bytes. So you're already at 70 * 3B = 210GB. That's ignoring all other data, page overhead, fill factor and indexes. I believe those indexes will be another 16 bytes per row (24B for the 2 field one) so ~300GB, again ignoring fill factor and non-leaf pages. You'll need a fairly beefy server to make that perform well. Depending on your insert rate 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 replication is 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