Currently, the sha256 column of the checksum table in Debsources' Postgres DB
has type character varying(64):

  sha256     | character varying(64) | not null

Such a data type is wasteful in terms of disk space. And it shows:

  debsources=> select count(*) from checksums;
    count
  ----------
   41151812

  public | checksums            | table    | debsources | 4890 MB    |

We should switch to a more economic (and efficient) data type for storing
sha256 checksums. Good options seem to be either bytea [1] or fixed-size bit
strings [2]. Suggestions welcome!

A good, concrete way to help with this bug would be providing sample SQL
snippets to create temporary tables with the new data types, and convert /
inject into them the content of the current checksum table. That would allow to
easily benchmark disk usage and query/index efficiency.

Cheers.

[1]: http://www.postgresql.org/docs/9.4/static/datatype-binary.html#AEN5497
[2]: http://www.postgresql.org/docs/9.4/static/datatype-bit.html

Re: Stefano Zacchiroli 2015-06-02 <20150602214710.10925.72165.reportbug@timira.takhisis.invalid>

Definitely bytea.

I've never really seen bit used in practise. It's mostly present
because it's an SQL standard type. For input/output, everything gets
converted to a lengthy series of 0/1s, while bytea's default "hex"
transfer encoding is especially easy to parse back to the hex format
you'd want anyway. (Don't worry about the fact that there's no length
modifier for bytea, it won't change the behaviour in practise.)

Christoph