[MYSQL/POSTGRES] Opslag van unsigned integers

[deus4]

Ik heb een zeer grote hoeveelheid netflowdata (>500GB) wat in een database opgeslagen zou moeten worden. Op dit moment is deze data zeer efficiënt in binary files opgeslagen. Zo wordt bijvoorbeeld 1 byte gebruikt om de TCP flags te representeren, en 4 bytes voor het opslaan van 1 ip adres.
Nu moet al deze data in een database gezet worden. Probleem is dat deze data zo efficiënt mogelijk in een database geplaatst moet worden. Onder mysql kan dit uitstekend, want deze ondersteunt unsigned integers, maar postgres lijkt dit niet te kunnen.
-Voor postgres is er ip4r wat het mogelijk maakt om ip's in 4 bytes op te slaan, echter lost dit het probleem niet op voor mijn 1 byte unsigned integers.
Postgress heeft een standaard datatype om ipadressen op te slaan. Echter neemt dit 12 bytes per adres is want dus 3x zoveel ruimte in neemt als benodigd. =Ongewenst.
-Op applicatie niveau zou ik alle signed bytes kunnen converteren naar unsigned bytes, echter is dit zeer vervelend als dit per query meerdere malen moet gebeuren. Bovendien maakt dat de data uit de database ook niet leesbaarder.

Kortom zit ik eigenlijk met 2 problemen:
1. Hoe kan ik unsigned values in postgres efficiënt opslaan zonder grotere datatypes te moeten gebruiken? Dit laatste doet de benodigde opslagruimte linear groeien en is zeker ongewenst in dit geval.
2. Gezien mysql wel unsigned integers ondersteunt, nijg ik naar het gebruik van Mysql ipv Postgress. Zijn er redenen waarom ik toch moet overwegen om mijn integers op applicatie niveau te converteren en dus toch postgres te gebruiken? Graag argumentatie goed onderbouwen. "PG evalueert queries netter dan MySQL" is niet zo'n nuttig argument tenzij je kunt specificiëren op welk gebied dit een voordeel levert (opslagruimte/evaluatie snelheid etc).

[ACM]

Je zou die byte-flags als 8 bits kunnen behandelen en ze in een BIT(8) of een char(1) (non-utf8) kunnen stoppen. Verder raad ik je aan naar postgres 8.3 (nu in beta) te kijken, daar hebben ze een paar bytes per record weggesnoeid en dat zal met jouw database aardig wat schelen. En bij MySQL is 5.1 nuttig omdat die o.a. table-partitioning ondersteunt (wat postgres al een tijdje kan).

Mijn ervaring met Postgres is met grote datasets overigens positief, momenteel is mijn grootste overigens "slechts" zo'n 80GB. Postgres is bijvoorbeeld veel beter in staat te bedenken dat ie met 1 sequential scan toe kan, waar mysql nogal makkelijk besluit er per iteratie een losse te doen (en dat kunnen nog wel eens heel veel iteraties zijn). Ook met allerlei andere soorten queries is Postgres sneller doordat ie efficientere uitvoermethoden kan gebruiken of zijn beschikbare methoden efficienter in weet te zetten. Verder kan je in PostgreSQL bij tabellen de meeste wijzigen aan de structuur doen en indexen toevoegen/verwijderen zonder dat de tabel compleet herschreven wordt.
MySQL wint overigens wel vaak in query-performance als je zogenaamde covering indexen hebt (alle velden die je in je select nodig hebt in de index aanwezig). Maar zelfs dan is het niet gegarandeerd een winst voor MySQL als de query net wat complexer is.

Vergeet overigens niet dat als je je data in wilt laden dat je dat het beste met de bulk-load tools kan doen, zowel MySQL (load data) en PostgreSQL (copy) hebben daar commando's voor die je aardig wat tijd besparen.

[sopsop]

deus4 schreef op dinsdag 06 november 2007 @ 17:30:
Postgress heeft een standaard datatype om ipadressen op te slaan. Echter neemt dit 12 bytes per adres is want dus 3x zoveel ruimte in neemt als benodigd. =Ongewenst.

Ik heb even zitten zoeken in de Postgress docs en er blijkt veel discussie geweest te zijn over het ip datatype. Die is nu redelijk ruim opgezet om in de toekomst naast IPv4 ook IPv6 te ondersteunen, in hetzelfde datatype.

Wellicht iets om mee te nemen in je beslissing voor een datatype. Zodra IPv6 van de grond komt zul je wellicht toch moeten switchen naar een ruimer datatype.