Member Avatar for sankar2000

Hello

I am building a database (i prefer MongoDB) that i will store over 100 mil ipv4 and ipv6 records for logging purposes. Data sample : 

1.1.1.1 -> 0x1010101
1.2.3.4 -> 0x1020304
34.53.63.25 -> 0x22353f19
255.255.255.255 -> 0xffffffff

0001:0001:0001:0001:0001:0001:0001:0001 -> 0x10001000100010001000100010001
1111:1111:1111:1111:1111:1111:1111:1111 -> 0x11111111111111111111111111111111
2345:0425:2CA1:0000:0000:0567:5673:23b5 -> 0x234504252ca1000000000567567323b5
2345:0425:2CA1::0567:5673:23b5          -> 0x234504252ca1000000000567567323b5
ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff -> 0xffffffffffffffffffffffffffffffff

I will have a lot of queries retrieving data by IP. I don't care about space, queries must be as fast as possible.

I was thinking about storing in binary or have different 4 (for ipv4) and 8 columns (for ipv6) spitted IP parts.

What is the most efficient way in terms of speed to achieve that?

  • 3 Contributors
  • 2 Replies
  • 586 Views
  • 2 Weeks Discussion Span
  • Latest Post Latest Post by Dani

Dani AI

Generated

Store addresses as a fixed-width binary value and index that field. Normalize every IP to a 16-byte (128‑bit) network-order value: use the 16 bytes returned by inet_pton for IPv6; encode IPv4 as 16 bytes with the IPv4 octets in the last 4 bytes (or as an IPv4‑mapped ::ffff: form if you need explicit mapping). A single BinData/Buffer field of fixed length gives the smallest, most cache-friendly index and the fastest equality and range comparisons.

If you prefer separate fields: store IPv4 as a BSON 64‑bit integer (int64 / NumberLong) — don’t use int32 because it can’t hold values > 0x7FFFFFFF — and store IPv6 as two int64 fields (hi/lo). That works, but equality/range queries require a composite index and a bit more application-side work. The key points: always use network (big‑endian) byte order so bytewise comparison equals numeric comparison, and be consistent about the BSON type you store and query.

Example workflow (conceptual):

# normalize to 16-byte network-order buffer
# IPv4: Buffer.alloc(16); put octets into bytes [12..15]
# IPv6: use inet_pton / library to get 16 bytes

db.logs.createIndex({ ip: 1 });             # index the binary field

# inserts: store the 16-byte buffer in `ip`
db.logs.insertOne({ ip: ipBuf, ts: new Date(), ... });

# exact lookup
db.logs.findOne({ ip: ipBuf });

# CIDR/range: compute ipMinBuf, ipMaxBuf in app and
db.logs.find({ ip: { $gte: ipMinBuf, $lte: ipMaxBuf } });

Operational notes: precompute the binary form before inserts and use bulk writes. For 100M+ rows ensure the index (or at least its working set) fits RAM for best latency. Choose sharding carefully: hashed shard keys distribute equality-heavy workloads well but break range sharding; range sharding preserves CIDR/range queries. Consistency of stored type and endianness is the most common source of bugs. This expands on ’s simple-text suggestion (easiest but larger/slower) and builds on ’s pointer to MongoDB resources — for driver-specific conversion code use your language’s inet_pton/inet_ntop or a well‑tested IP library.

Recommended Answers

All 2 Replies

Member Avatar for rproffitt

Continuing from our last discussion, I know what I would do but it hasn't dawned on you why that would be the fastest.
As such, post over in https://www.mongodb.com/community/forums/ to see what they say.

Member Avatar for Dani

I would store them as plain text, but I suppose it does matter what purpose you are storing the data for. Does it need to be searchable?

commented: Yes, needs to be searchable. I was thinking about splitting them in collections by date 2022-12-col1 ... 2023-01-col2 ... you get the idea +0
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.