3 ms·

I suppose I'm a modern app developer, so here's what I would actually do: Postgres. 1) I'm not going to invent my own system. I am not the first person who wa

by tyre 11y ago

I suppose I'm a modern app developer, so here's what I would actually do: Postgres. 1) I'm not going to invent my own system. I am not the first person who wants to store addresses and boolean values. If I'm looking at this occasionally over many months, I don't want to keep relearning my elegant bit-structure. 2) SQL is great, Postgres is even better with built-in network operators[1] for the more specific queries (e.g. subnets.) 3) Bitfields are all fun and games until you want to change things. I'm already having flashbacks to rails `has_bitfield` columns. Also, fewer people can confidently twiddle bits than can understand boolean SQL columns. 4) The author mentions possible compression methods. I'd rather a tested project like Postgres think about everything to do with storing my data. I understand the criticism, but with cloud storage so cheap it feels like optimization just to show off. I'd rather save time than bits. If there is a satire that involves me using Postgres when I shouldn't, that would be welcome. [1] http://www.postgresql.org/docs/current/static/functions-net.html http://www.postgresql.org/docs/current/static/functions-net....

krick 11y ago
Exactly. Change "json" to "relational DB" and it's not close as ridiculous as author implies. All questions mentioned could be answered with a single SQL: although it will take quite a while. And it is much more extendable than this "huge array" approach. The only thing is that by saying "Postgres" you say pretty much nothing: the real question here is how'd you structure the db. 300M a month is pretty huge, so it's really worth to think if we should just pile everything in 1 table (unique key ip+month, all ports are columns) or if we can denormalize something.
- GFK_of_xmaspast 11y ago
  300M per month doesn't sound like all that much to me?
  - krick 11y ago
    Well, it will be quite a table to do your selects against. Assuming that your hardware for such a project is fairly modest, after a couple of months index won't really fit into the memory, and even selecting a record by the key would take 30 jumps over that table just after 4 months. And most of the tasks in question require running through the whole thing. Not a tragedy, but well worth considering aggregating some data as it comes, especially as it costs pretty much nothing at this point (nmap takes longer anyway). Like counting number of IPs with the open port 22 would be just incrementing some number at the dedicated table by simple rule in the code. Maybe it's worth to invent some more interesting denormalization in order to be able to run more complex queries easily. Maybe I'm wrong: I'd have to try out that to see how fast it actually computes, but I remember having problems with much smaller tables: like these 300M in total, maybe. Although, it was much "wider", with varchars and stuff.
    - greggyb 11y ago
      Not straight Postgres, but this dataset would be ideal for a columnstore index with compression - huge runs of the same value are readily compressed with run-length encoding. Partition the whole thing by the status of the up flag, perhaps additionally by month, and the SELECTs can be pretty well optimized.
- deleted 11y ago
  [deleted]
- agnivade 11y ago
  How about using parent-child table partitioning ? I have used that and pretty much like the feature - https://wiki.postgresql.org/wiki/Month_based_partitioning https://wiki.postgresql.org/wiki/Month_based_partitioning
deleted 11y ago
[deleted]

by tyre 11y ago

krick 11y ago

Exactly. Change "json" to "relational DB" and it's not close as ridiculous as author implies. All questions mentioned could be answered with a single SQL: although it will take quite a while. And it is much more extendable than this "huge array" approach. The only thing is that by saying "Postgres" you say pretty much nothing: the real question here is how'd you structure the db. 300M a month is pretty huge, so it's really worth to think if we should just pile everything in 1 table (unique key ip+month, all ports are columns) or if we can denormalize something.

GFK_of_xmaspast 11y ago
300M per month doesn't sound like all that much to me?
- krick 11y ago
  Well, it will be quite a table to do your selects against. Assuming that your hardware for such a project is fairly modest, after a couple of months index won't really fit into the memory, and even selecting a record by the key would take 30 jumps over that table just after 4 months. And most of the tasks in question require running through the whole thing. Not a tragedy, but well worth considering aggregating some data as it comes, especially as it costs pretty much nothing at this point (nmap takes longer anyway). Like counting number of IPs with the open port 22 would be just incrementing some number at the dedicated table by simple rule in the code. Maybe it's worth to invent some more interesting denormalization in order to be able to run more complex queries easily. Maybe I'm wrong: I'd have to try out that to see how fast it actually computes, but I remember having problems with much smaller tables: like these 300M in total, maybe. Although, it was much "wider", with varchars and stuff.
  - greggyb 11y ago
    Not straight Postgres, but this dataset would be ideal for a columnstore index with compression - huge runs of the same value are readily compressed with run-length encoding. Partition the whole thing by the status of the up flag, perhaps additionally by month, and the SELECTs can be pretty well optimized.
deleted 11y ago
[deleted]
agnivade 11y ago
How about using parent-child table partitioning ? I have used that and pretty much like the feature - https://wiki.postgresql.org/wiki/Month_based_partitioning https://wiki.postgresql.org/wiki/Month_based_partitioning

deleted 11y ago

[deleted]