How to Efficiently Store IP Addresses in PostgreSQL and MySQL

PostgreSQL

PostgreSQL uses the INET type to store IPv4 or IPv6 addresses, including subnet information, in the format IP/subnetMask (e.g., 192.168.1.1/24).

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

The INET type validates input, can test subnet containment, and improves data integrity and query performance. Example:

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

MySQL

MySQL can store IP addresses as VARCHAR, but it is not recommended because MySQL provides dedicated types and functions.

IPv4

For IPv4, MySQL recommends using INT UNSIGNED together with INET_ATON() and INET_NTOA() functions.

CREATE TABLE `ip_test` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `ip` int(10) unsigned DEFAULT NULL,
  KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 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 INT UNSIGNED for IPv4:

Only 4 bytes storage.

Supports range queries and index optimization for better performance.

IPv6

For IPv6, MySQL can use VARBINARY(16) with INET6_ATON() and INET6_NTOA() functions.

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;

Summary

Major databases generally discourage storing IP addresses as plain VARCHAR; instead they provide built‑in types and functions that offer higher storage efficiency and faster queries.