Anders Brownworth

Technology and Disruption

600+M Rows in PostgreSQL

I've been working on a PostgreSQL project which has a table with 644,742,830 (or 644.7M) rows in it. There is an index on an array column, in this case a column of type character varying(35)[] with a UNIQUE constraint. Each row has exactly two strings in it which happen to be bitcoin addresses. When I SELECT some address from that column:

SELECT * FROM keys WHERE addresses @> '1Axrv5WPGA65wpxCpV87sYdwxhkmy8mvF2';

the database is actually SELECTing from amongst 1,289,485,660 (or 1.2 Billion) keys. Because the database is on an SSD and there is enough RAM in the system to fit the index, I can regularly return results within the 1ms to 2ms range!

On disk, the entire database (including a few other columns and several other tables) is in the 421Gig range. I had been worrying about having to use some other strategy (LevelDB possibly) but have been amazed by the results. I'll keep adding rows until I ether run out of disk space or run into a performance problem. Gotta love good indexes!

Comments (0)

Leave a Comment

Name:
Location: (city / state / country)
Email: (not published / no spam)
Comment:

No HTML is allowed. Cookies must be enabled to post. Your comment will appear on this page after a moderator OKs it. Offensive content will not be published.

Click the umbrella to submit your comment.

To create links in comments:
[link:http://anders.com/] becomes http://anders.com/
[link:http://anders.com/|Anders.com] becomes Anders.com
Notice there is no rel="nofollow" in these hrefs. Links in comments will carry page rank from this site so only link to things worthy of people's attention.