Hacker News Reader

Comment by koeng | Hacker News Reader

I use recursive queries in SQL to search persistent suffix arrays (mainly when there is a LOT OF DATA). You can do string search hundreds of gigabytes of data within ~0.004s with SQLite on cheap SSD and pretty much no RAM - my goal is to be able to be able to search and align on all genetic information we have so far (about ~9 trillion base pairs in total)

If anyone knows how to increase INSERT speeds on Postgres, I would appreciate. I can get about 100K per second with COPY or putting the inserts in a transaction. Have tried some more mirrored NVME drives and more RAM with ZFS, as well as dropping some unimportant indexes, but I'm still a little limited. Any thoughts?

View on HN

Assuming you're doing bulk inserts, and can restart the insert on any failure, one tactic is to disable all consistency and durability storage features during the insert. This heavily reduces the IO operations needed and the need to wait for them to complete. That's assuming that iops are the resource that you are constrained on.

Steps:

- use ext2 or even tmpfs as the filesystem, this disables journaling and COW features of ZFS; mount options async,nobarrier

- set tables to be UNLOGGED to disable Postgres WAL

This got things fast enough for my last use-case. But next ideas, as at some point you then become CPU/memory bound

- you could try sharding by partitioning the table

- another trick is to use SQLite as a backing store for inserts (it is quite fast if you turn off all logging) and then query with Postgres via a FDW https://github.com/pgspider/sqlite_fdw

How fast did it go for your last use-case? Disabling all the consistency and durability storage features is dreadful, to say the least, but I'd definitely be open to trying it if it means I can get it done quicker.

The algorithm I'm working with uses single-cores but is linear-time and in my experience is at least 10x-100x faster than the IO. Memory is actually the biggest problem with the method, which is why I need to use a database to back it to disk.

It's relatively safe to do so for a one-time bulk load, especially if you have a way to verify consistency what you've loaded for integrity reasons (foreign keys, for example, might be a single query and a join). If it's not a one-time load, then you are right, it's dreadful.

If, for example, you get a 3-5x speedup without checks (not unheard of), you can do it twice, verify the copies are the same, and get a 2x speedup.

Is there a reason you don't wish to batch inserts into transaction(s)? Otherwise, you're asking the database to commit (fsync to disk) between each insert statement.

Other ideas:

- Have multiple processes inserting in parallel (this interacts with the commit_delay and commit_siblings settings in postgresql.conf)

- Use unlogged tables (but table is truncated on db startup)

- Put fsync=off and synchronous_commit=off in postgresql.conf (but db may be corrupted in a crash)

- switching to BRIN indexes might be an option, but depends on data

I do batch the inserts into a large transaction, and usually optimize for the transaction to commit in approximately ~10s. Not sure if it is optimal, but I like to monitor it like that.

BRIN indexes actually work for some of the data types I'm working with! Thank you for pointing those out!

COPY is the fastest method to insert data in Postgres. As another poster mentioned, setting table to UNLOGGED will help also.

Finally, you can drop ALL indexes and recreate them after import (been hit and miss in my testing but on occasion has helped).

How has dropping and recreating indexes been hit or miss? I've been thinking about doing exactly that

In some cases it did not appear to give me an improvement in data load time - I didn't do a rigorous test though which is why I said it might be 'hit and miss'.

Why ZFS? I thought copy-on-write filesystems were discouraged for database workloads. I know it is possible but are you getting other benefits from ZFS?

For OLTP, COW is horrible. For OLAP it's usually fine. I've had good results with Postgres and ZFS with LZ4 compression.

I have a single physical server for running all my virtual machines. ZFS is nice for everything else, and it just so happens that my database virtual machine takes up most of the SSD space on that computer. I guess I should have a dedicated machine if I really need to improve insert speeds though.

Ah ok, pragmatic. Makes sense.

Have you done things like disable constraints and indexing during loading?

I've disabled all constraints, but I haven't removed indexing during loading. I'll look into doing that, with adding the indexing later

I don’t often work with Postgres itself but I do work with a derivative (Redshift) at $dayjob.

Indexes don’t exist. Constraints are present in the syntax and the planner relies on them but they are never enforced. Our ETL layer deals with checking constraints at load time. Load performance is good. I’ve never run a rows/minute metric because it’s never been a problem.

Coming back to this just to provide a datapoint. I just loaded 10m rows into a table in less than one minute using Redshift. The data is encoded (compressed) at insert time but is not sorted until a vacuum runs.

Came across this upcoming webinar on the topic: https://www.timescale.com/webinar/5-ways-to-improve-yourpost...

I'm new to postgres and having trouble visualizing the recursive query you're describing. Would you be willing to share an example query (or something that illustrates the point on fake data)?

The query that I have working is an abomination (it works though, https://github.com/Koeng101/poly/blob/polyStore_v0.1/store/c...)

The basic idea is that I am using SQL to back a suffix array (https://en.wikipedia.org/wiki/Suffix_array). The suffix array has all suffixes of a string (for "bats", it would have ["bat", "at", "t"] sorted in alphabetical order ["at", "bat", "t"] except storing the index of the suffix [1, 0, 2].

To search for a string, you go halfway in the suffix array and check if the suffix is lexicographically equal to, larger than, or smaller than your input. If it is not equal to, halve in the right direction. If the string is there, eventually you'll hit it.

With inserts you’ll see great results from goosing the transaction array multiplexer. Just make sure you’re aware of piping the commit hash to the properly isolate the down range effects on the memcpy and you’ll avoid corruption issues. This has worked for me anyways.

This comment is entirely random gibberish, not sure why you're posting that. I initially suspected a GPT-3 bot or something like that, but the comment history didn't support that.

GPT doesn’t make the kind of blatant grammatical mistakes the commenter did.

To be fair, I typed this quickly on a phone.

Reply on news.ycombinator.com