4

This is the first time (I think) I have the chance to use the hstore data type, but I would like to hear from more experienced folks if what I have in mind is actually a good idea.

Now, there's this web app in which we import payroll data in the form of XML files, which look roughly like this simplified version:

<Company>
    <Employee>
        <LastName>Smith</LastName>
        <FirstName>John</LastName>
        <HourlyWage>9999.99</HourlyWage>
        <!-- several other hundreds of tags -->
    </Employee>
    <Employee>
        <!-- ... -->
    </Employee> 
</Company>

Each employee carries extremely detailed information, and when I say "several other hundreds of tags" it's because they will usually range between 800 and upwards of 1400. And this is for each month. Apart from a set of core tags, each employee can have a different combination of them, hence the very fluctuating but very realistic figures I gave above.

Now, part of this data is imported with a long, slow and very complex process, and I've observed that, with increasing frequency, we are finding ourselves saying "gosh, if only we had always imported that particular tag!".

While the import process is highly configurable, making it run for just a small set of data is slow, impractical and downright painful. Adding whatever customization is needed to import the hypothetical new tag from now on is much easier, but for building up historic data as if we always imported it, it's messy and error prone.

As an added bonus, the task always falls on those two guys, and I'm one of them, and I would love to make our life a tad simpler.

This is why I'm thinking of writing a quick tool that, during the night, will crack those XML files open and, for each month and each employee, create a record with an hstore column containing all of the employee's tags for that month.

Being an absolute beginner at hstore, this looks to me like a very good use case for it, especially if we consider that:

  • since the tags can differ for each employee, this is essentially schema-less data.

  • storing the tags as an EAV with one row per tag would mean approximately 240k rows per month for a 200-employees company (well into the 2.8M per year). Nothing to be concerned about, sure, but there's not only one customer. And one of them has over 7,000 employees (which would be 100M records per year).

  • this data will only ever need to be read, never changed. Plus, it won't even be read that often anyway.

  • I don't really care or know what any given tag means. I just want to store it for future use, telling me which one is needed is the job of the domain experts. Again, schema-less.

The table I would design would look a bit like this:

- id bigserial
- user_id
- file_timestamp (it's embedded on the name of the file)
- employee_id_1 varchar
- employee_id_2 varchar
- month date
- file_id (id of the XML file, it gets logged in a table before being imported)
- tags hstore

(of employee_id 1 and 2, the former looks like the US SSN, the latter is assigned from our payroll app).

I would also create a unique index on (user_id, employee_id_1, employee_id_2, month, file_id). Not 100% sure about the order of the columns, but I think it would accommodate most SELECTs where one wants to progressively narrow the data down.

Also, I wouldn't want to duplicate the table for each customer, and I don't want or need it to be user-visible. I would create a dedicated schema and stick it in there, which would be a bit easier to manage in case I want to partition it at some point in the future. It's going to be a huge table (not so much for the number of rows, but for the space required for each of them), but keeping into its own schema also means it will be easier to exclude from most backups. Plus, we keep the original XML files anyway, so it wouldn't even be hard to rebuild if things go awry.

With such a design, it looks like generating historical data with a bunch of ad-hoc one-off queries would be child's play.

But since I'm not an expert, I was wondering if:

  1. this is actually a good use case for hstore
  2. there are obvious flaws in my design
  3. there are things I should pay attention to in order to avoid painting myself into a corner when hstore enters the scene
  4. since the tags are in a sizable number but still not that many per row, would it be worth it creating an index on the hstore keys?
  • 1
    hstore is great, for sure - I have used it (in 9.1 which didn't support JSON - which is what I was working with). But Postgres 9.4 supports XML (and JSON).. Why not just process the XML as XML? – Joishi Bodio Dec 4 '15 at 22:56
  • @JoishiBodio because just thinking about the kind of XML I'm dealing with makes me want to cry. I wish it was like the one I made up for my example. It's chock-full of weird non-printable characters that need to be stripped away. Plus, I seem to recall that XML can't be indexed as efficiently as hstore. I'll give it some more thought, though. – s.m. Dec 4 '15 at 23:02
  • hmm... Well I'd suggest at least looking through the postgres documentation about XML and see what functionality it offers. If you don't think that'd be good enough, then by all means use HSTORE for it (it really is a pretty useful key -> value tool). Just a warning, though - if memory serves, every value you get from HSTORE will be given to you as a string and you'll have to cast if it's not supposed to be a string. – Joishi Bodio Dec 4 '15 at 23:07
  • 2
    If you can't use the original XML and can't normalize sensibly I suggest transforming to json and storing as jsonb. – Craig Ringer Dec 5 '15 at 5:00
  • 1
    XML can't be indexed as efficiently as hstore .. Why do you care about that? Are there even going to be queries looking inside the value? If that's not the case, given that you mostly just store the data and never change and hardly read, I would run a quick test and pick the data type resulting in the smallest size on disk. Since you also don't care about data integrity inside the value, maybe even just text - unless there are going to be queries looking at tags inside the value. – Erwin Brandstetter Dec 5 '15 at 5:35
1

At the scope at which you're working. I think JSONB is ideal. It handles deeply nested structures and structures with array-keys. It's also standardized and in the spec for sql2016.

In addition, as I answered here, there is an extension that should help you with space consumption called ZSON,

ZSON is a PostgreSQL extension for transparent JSONB compression. Compression is based on a shared dictionary of strings most frequently used in specific JSONB documents (not only keys, but also values, array elements, etc).

In some cases ZSON can save half of your disk space and give you about 10% more TPS. Memory is saved as well. See docs/benchmark.md. Everything depends on your data and workload, though. Don't believe any benchmarks, re-check everything on your data, configuration, hardware, workload and PostgreSQL version.

You may want to look into ZSON.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.