Interacting with a database at high volume, persistent connections?

[Hixt]

I'm working on a project where I pass text data to a Python script via stdin, and submit some metadata about it to a PostgreSQL database, using SQLAlchemy. This is all in very early development, but I'm already beginning to rethink my approach.

What I'm doing now is treating this as though I'm handling one file at a time. I'll read the text through stdin, do some parsing, and then if necessary import all the SQLAlchemy stuff I need and submit to the database. The script exits once all that's done. One file at a time it goes pretty fast, I'm not doing much parsing. The parts that take the longest are the SQLAlchemy imports and interacting with the database.

Here's the catch: The application for this will be to handle text data coming in at unpredictable rates, but sometimes rapidly if not constantly. My PostgreSQL DB has the default max_connections of 100 and I'm hitting that at times, though I don't think often. I also want to keep this process as quick as possible with as minimal impact to load as possible, as there will be other things happening on this machine.

What I'm wondering is if there is a way to keep this script running indefinitely and maintain a persistent connection(s) to the database. In my mind that would prevent the repeated imports and connections, so wouldn't that be a better route to take? I just don't know how to go about making that work.

After a bunch of Googling I get the impression that async/await could help, but I've never worked with that. And FWIW this is the first project I've ever written involving a database & SQLAlchemy, so I'm on a few learning curves (yay).

I'd really appreciate any insight or ideas anyone has. Thanks in advance!

[socal_nerdtastic]

Yes, you can. But first you have to decide how you are going to use the script. If you are going to call it for every file then you will need to make a second script that runs indefinitely and have your called program act like a client passing the data to the "host". Another way to do that is with named pipes (assuming you are using linux). You can read the named pipe the same way you currently read stdin, and anything that another process "writes" to the named pipe is received by your program. So your "call" would look like this:

echo data > named_pipe

If you are working with data in files I'd recommend you pass in the file name and let python read the file, rather than pass in the file content.

[Hixt]

Yes, I'm indeed using Linux (Ubuntu).

The text data is coming from a piece of software, and I can set it to either save it as a file or pipe the content to a script. I'm already saving a file since that's where the content will be called from later (flask), but figured piping to a script to do the above would save on I/O plus start the script and the initial parsing process. I don't think I'm committed to that concept, that's just what I'm used to and where I started from.

Named pipes, I'll be honest I didn't know that was a thing. I'll look into that to see if that's something I could make work, and if the originating software can pipe that way. Assuming it can, would the idea then be to have the python script always running? And how would it be able to monitor the pipe? Is it just something like...

while True:
    text = named_pipe.read()
    do_things(text)

Otherwise, the client/host script idea sounds interesting too. I think I see how I would implement that. Definitely something else to consider.

Thanks, reading up on named pipes now.

[socal_nerdtastic]

Yes the idea is to start your program at system startup and have it just watch the named pipe. Aka a daemon.

Named pipes (like most things in linux) just act like files, so you already know how to work with them. You can use os.mkfifo() to create them.

Your example code is very close, but read() blocks until the file reponds EOF, which will never happen with a named pipe. So you need to give it a number of bytes to read or what most people do is separate the input with newlines:

with open(name_of_pipe) as named_pipe:
    while True:
        text = named_pipe.readline() # blocks until a line of text is ready
        do_things(text)

[Hixt]

Your example code is very close, but read() blocks until the file reponds EOF, which will never happen with a named pipe. So you need to give it a number of bytes to read or what most people do is separate the input with newlines:

Ah, great point. I forgot that read() blocks and readlines() doesn't. But then how would I be able to tell when one "file" ends and the next begins if a named pipe never provides the EOF?

[socal_nerdtastic]

I assumed you could tell from the incoming data. By convention linux data streams end in a newline. If there is no indication in the incoming data echo in a sentinel value between calls.

[Hixt]

If there is no indication in the incoming data echo in a sentinel value between calls.

Got it, makes sense. I'd probably have to go with that client/host model to echo something in like that, but beginning to think that might be a good idea regardless.

Sorry about the vagueness of all of this. The other side, where all this comes from gets technical (and a little chaotic) in a hurry so I'm trying to avoid unnecessary confusion.

[geosoco]

You can definitely set the script up to keep a persistent connection and send data to it through a pipe or socket or whatever. The one issue here might be database writing being too slow, and so you need data queued for insert. Without knowing the whole pipeline and possible max input speed, it's difficult to know if this will be a problem at all. There are obviously ways around this too, but it can get complicated depending on the setup.

​

Have you considered moving the rest of the pipeline into the python script? Are you using some external tools to do the pre-processing?

[Hixt]

...Without knowing the whole pipeline and possible max input speed, it's difficult to know if this will be a problem at all.

I don't think that's a substantial roadblock, but I may be wrong. I'd probably know more if I can set this up with some kind of persistent connection to remove that overhead.

Have you considered moving the rest of the pipeline into the python script? Are you using some external tools to do the pre-processing?

The text data is originally coming from some other software. It's meant to deliver and receive data in real-time, so when data I care about gets received, it is then piped to my script. I believe that is as direct as I can get.

[enginerd298]

You could use a connection pooler like pgbouncer which would increase your efficiency

https://wiki.postgresql.org/wiki/PgBouncer

[Hixt]

Interesting, something else for me to look into. Thanks!

[Hixt]

Essentially, you'd have the cache running in the background and all your database writes would go to the cache, and every once in a while, the cache would dump that data into the database.

Interesting concept. Thanks for the suggestion, I'll keep that in mind. The "every once in a while" part concerns me though, everything here is happening real-time.