title: PostgreSQL: going to run out of IDs in integer columns
body: <h1>Problem</h1>
  
  <p>We are building a web application in Java on top of PostgreSQL. It's quite large and successful, and it should be able to run for a few more years at least.</p>
  
  <p>Unfortunately, we (well, I) made a grave mistake in the early stages of the design process: <strong>all the database IDs are integers, handed out from one shared sequence</strong>.</p>
  
  <p><a href="https://docs.oracle.com/javase/7/docs/api/java/lang/Integer.html" rel="nofollow">Java's max int is 2^31-1</a>, so approximately 2 billions. The same goes for <a href="http://www.postgresql.org/docs/current/static/datatype-numeric.html" rel="nofollow">PostgreSQL's integer type</a>. The system is currently eating up ~10k IDs every day, and the rate is going up as we gain new users.</p>
  
  <p>One day, the IDs will run out and overflow.</p>
  
  <h1>Question</h1>
  
  <p>We are looking for ways to fix the situation. Let's get the obvious one out of the way immediately: switching to Java's <code>long</code> and Postgres' <code>bigint</code> is a clean solution, but it's a <strong>ton</strong> of work. We need to postpone it as much as possible.</p>
  
  <p>Some ideas we've had so far:</p>
  
  <ul>
  <li>Don't use one sequence for everything, give each table its own sequence.
  
  <ul>
  <li>Pros: this gives us up to N times more time, where N is the number of tables.</li>
  <li>Cons: we like the fact that every row has a unique ID.</li>
  </ul></li>
  <li>Stop using sequence IDs for some of the tables. For example, a table with customer events doesn't really need an ID: <code>customer, timestamp</code> is a perfectly valid primary key.
  
  <ul>
  <li>Pros: some of our biggest ID-hogs could be changed this way.</li>
  <li>Cons: nontrivial amount of work.</li>
  </ul></li>
  <li>Stop wasting IDs on empty records. This happens with some sub-tables, like customer contact information. Having the record always present makes the code simpler, but it means many customers have an empty contact information record stored.
  
  <ul>
  <li>Pros: some of our biggest ID-hogs could be fixed this way.</li>
  <li>Cons: we lose the code simplicity.</li>
  </ul></li>
  <li>Every new table must use <code>long</code> / <code>bigint</code> with a new sequence.
  
  <ul>
  <li>Pros: at least we don't make it worse.</li>
  <li>Cons: the contact surfaces with the rest of the code will be ugly.</li>
  </ul></li>
  </ul>
  
  <p><strong>Under these constraints, what other approaches will delay the ID depletion?</strong></p>