How to Store IP Addresses Efficiently in PostgreSQL and MySQL

Storing IP addresses is a common requirement, and using the appropriate data types improves integrity and query performance.

PostgreSQL

PostgreSQL offers the INET type, which can store both IPv4 and IPv6 addresses as well as subnet masks (e.g., 192.168.1.1/24). Insertion is straightforward:

INSERT INTO ip_test(id, ip) VALUES (1, '192.168.1.1/24');

The INET type validates input and supports subnet containment checks. For example, the following query returns true because the address 192.168.1.5 lies within the 192.168.1.0/24 network:

SELECT '192.168.1.5'::inet << '192.168.1.0/24'::inet;

MySQL

MySQL does not have a dedicated IP type, but it provides functions to convert IP strings to numeric forms.

IPv4

Use INT UNSIGNED to store IPv4 addresses as 32‑bit integers. Conversion functions INET_ATON() (string to integer) and INET_NTOA() (integer to string) handle the transformation.

CREATE TABLE `ip_test` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `ip` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- Insert
INSERT INTO ip_test VALUES (1, INET_ATON('192.168.1.5'));

-- Query
SELECT id, INET_NTOA(ip) FROM ip_test;

Benefits of this approach include a compact 4‑byte storage size and the ability to use range queries and indexes for faster lookups.

IPv6

For IPv6, store the 128‑bit address in a VARBINARY(16) column. MySQL provides INET6_ATON() and INET6_NTOA() for conversion.

CREATE TABLE `ip_test_v6` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `ip` varbinary(16) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- Insert
INSERT INTO ip_test_v6 (id, ip) VALUES (1, INET6_ATON('FC00:0:130F:0:0:9C0:876A:130B'));

-- Query
SELECT id, INET6_NTOA(ip) FROM ip_test_v6;

Conclusion

Mainstream databases discourage storing IP addresses as plain VARCHAR. Instead, they provide native types ( INET in PostgreSQL) or specialized functions (MySQL’s INET_ATON, INET_NTOA, INET6_ATON, INET6_NTOA) that ensure smaller storage, validation, and more efficient queries.