Hacker News Reader

Thank you for your comment. I hear you when you say postgres would probably be faster. However, I imagine there would be more work if I chose postgres and the whole development and testing paradigm would change.

First, I would need to figure the right schema and populate the database.

Second, it would need some creative SQL acrobatics that I would probably not be comfortable with.

Third, it would probably be hard to perform quick tests at a small scale that I can perform easily with text files.

Fourth, the solution would probably be hard to port elsewhere where postgres is not available. Most Unix systems have awk available.

Fifth, programming the postgres db in a higher level language would require connector api libs which would be additional effort.

Note that this is not a production work -- it started simply as a hack to see how far I can go without getting into serious rabbitholes and giving up. Surprisingly, with Awk I went really far and never fell into any rabbithole so to speak.

Some of awks stengths are iterative development, pattern matching and integration with other tools. A databases strengths are data consistency, concurrency (multiple readers/writers), transactions, efficiency. So the problem itself is playing to awks strengths and doesn't require the strengths of a database. With a database you'd usually have much more analysis upfront, work out the appropriate data types, work out what is nullable, work out the exceptions to the rules, work out your indexes, etc which isn't great for this type of quick and dirty problem. It also looks like most scripts are culling the dataset on input instead of at processing time which is more efficient.

I don't think it's really displayed here because everything is run through swift, but it can also compose much better with other tools, a database frequently won't have cli tools that integrate well (not sure about postgres). There are 322 source files being globbed for instance, move that to a make file and you can automatically re-run without wasting time on data already processed. If it was in a database you'd have to track source files and manage changes somehow.

> At first glance, that's an awful lot of hardware for a ... decently sized but not awfully large dataset. We're dealing with datasets that size at 32G or 64G of RAM, just a wee bit less.

Note that it doesn't require that much memory, it's just using it to boost performance because it's available, this could have been developed on a dual core 4GB laptop outputting to spinning rust and all that would change is the directory it's output to (and running time), but the same machine may choke on database queries over that much data.

I think the first line itself (High Performance Computing) reveales that this is a special purpose computer (read - real supercomputer). 512 cores and 24TiB of memory further confirm that. However, this is just a small sized supercomputer (unless that's the limit of resources a user can reserve). I don't know the constraints on this particular SGI machine, but many regular apps cannot be run or rendered useless in HPC domain even when using Linux.

@ketanmaheshwari, which supercomputer is this?

Thank you for your comment.

This is an SGI UV300 system similar to this one: https://www.hpcwire.com/2016/05/11/tgac-installs-largest-sgi...

I believe databases like postgres etc. would probably run on this system. I did not choose that route because I wanted to see how far I can go with Awk.

That is a shagload of hardware. I had to work in MSSQL with ~100GB database and we only had, I think, 12 cores in those servers (or maybe it was just 6 + SMT). Avoiding IO is the key, so having enough mem to cache the working set is essential.

Those are massive, massive machines.

lmao I wish, I was running several TB databases on servers with less than 128GB of ram (thankfully we werent processing the entire set) but that amount of ram is eye popping for the dataset, many data warehouse implementations I am familiar with on MSSQL have 10TB datasets with significantly less ram than this :)

On the opposite spectrum, I have have over estimated our hardware requirements with 3x 128GB machines with 32 cores each, and the uncompressed dataset is 200 mb on disk.

One thought ... the author talks a lot about working in an "HPC environment". Most HPC sites don't allow services such as a database to be run.

If you're dealing with such a static single-machine dataset, why not go for SQLite instead of PostGres?

There'd be much less setup overhead

I was thinking the same thing first, but does SQLite parallelize work it does?

How good is SQLite’s parallelism these days?

It’s alright, concurrent reads should not be a problem at least. Most drivers implement some features to make it easier.

Sqlite is single-writer for a single database. I mean. Of course, VHRanger, only your team will need to write to the database, and your team will make sure that only one person on your team will write to it. Eh. I've been there too many times. Oh, but yes, your team will also figure out the fallout if things to wrong. Ah..

Ok. Maybe those are enterprise concerns: Sqlite doesn't scale regarding multiple users reading and writing. Of course it's a read only dataset, but do you know the bouquet of views and derived tables data scientists create around a read-only dataset? Hah. Oh and of course these are not critical, but if they get lost, shit hits the fan because it takes multiple weeks to rebuild them.

I've been in that swamp enough times to just install postgres and stop caring. Takes me 2 more hours now, but avoids weeks of discussions in the future.

Remember that we're comparing this to awk…

There's no overhead.

  apt-get install postgres
  create user
  create database

> rely on automated query optimization and parallelization from there.

I know you said "automated parallelization" but... in newer versions of Postgres, what does it take to trigger some of the automatic query parallelization?

> what does it take to trigger some of the automatic query parallelization

Table scans, index scans (b-tree & bitmap only I believe), joins, and aggregations. There may be some limitations with joins, such as hash joins duplicating hashes across processes or merges requiring separate sorts.

Does this work outside of a shared memory environments? I haven't had to fuss with postgres for several years but last time I used it, this was non-trivial.

Postgres uses separate processes under the hood and thus shared memory to combine results.

Yeah, I work with awk quite a lot and I find weird that they needed so many resources.

Although I agree that for this problem Postgres looks like a better option, I wouldn't discard awk or other Unix tools. It is way faster than people think it is, easy to use and solves pretty well quite a lot of use cases, such as pre-processing records before database ingestion, aggregations, some simple queries on temporary data...

Agreed. If you only have 64 MB of RAM to process your 329 GB, AWK's your tool. As you say, it's faster than many people think.

OP here -- thank you for your comment.

Just wanted to say that the resources were not needed. I simply had access to them and were not being used much at the time so thought why not try them.

Alternatively one can load the data into Google BigQuery and start running queries without indexing the data. It's not free though, however can save a lot of time. BigQuery supports import of JSON and CSV files from Google Cloud Storage and it can even infer the schema.