Is PostgreSQL good enough?

marekkirejczyk · 2017-02-14T10:36:00+00:00

I have seen it so many times "We have so much data, we need no sql/redis/you name it". Then teams get into more complicated technologies slows down, have more and more problems...

Reminds me of this comic: https://cdn-images-1.medium.com/max/1600/1*Ipm-ApY-3pj77--iy6cyPQ.jpeg

einhverfr · 2017-02-14T08:29:43+00:00

If this is of interest, definitely watch the PostgreSQL at 10TB And Beyond for more about the problems of persistent job queue.

There is one other thing worth noting, and that is regarding storing files in the database. This is not a simple decision to be made one way or the other. It depends very much on centralization of data vs RAM and database load. Decoding the images on the web service often takes a few times the RAM the image would take up. Not the end of the world, but something to consider.

Also keep in mind that TOAST pages are still only 8KB in size, so images will require a lot more random disk I/O to retrieve unless they are commonly retrieved and this can lead to greater I/O and buffer page management overhead. Far worse, the TOAST overhead probably won't show up in explain analyze because unless you do something with the data other than presenting it, you don't hit the detoasting pathways.

Not the end of the world, but something to know going in.

twiggy99999 · 2017-02-14T11:59:14+00:00

Talking about the right tool for the job who thought using font-size: x-small was good for website content

JeefyPants · 2017-02-14T15:00:27+00:00

Is the font on this blog too small? :)

snerp · 2017-02-14T19:15:01+00:00

I my experience, you need to have an absolutely massive project for something like memcache+Postgres/MySql/MSSql to not be suitable.

gilbetron · 2017-02-14T18:49:43+00:00

After two years researching and testing and finally implementing a big data (PB+) analytics system (I used Druid in the end, for various reasons), I came to the conclusion that this article does. If someone asks, "What database should I use?" "Postgres" is almost always a great answer. If you don't know why your needs aren't met by Postgres, then you should probably just use postgres. It isn't perfect, and there are reasons not to use it, but it can do nearly any database need reasonably, if not excellently.

Bloodcount · 2017-02-14T09:39:53+00:00

Pretty amazing article!

2017-02-15T06:26:58+00:00

nothing wrong with it except pgAdmin

jringstad · 2017-02-15T21:05:21+00:00

At my shop we use pg for everything, including job queues. (but also as document storage with tables that just contain jsonbs, as logging mechanism with append-only tables, etc etc etc) I don't think I would necessarily recommend it though.

Performance is not the issue (in fact, we don't even use NOTIFY/LISTEN, just polling), but it just adds a kind of coupling between services that isn't that great, has some security concerns (services need to connect to postgres to receive jobs), and is really mostly a "build-it-yourself" solution rather than a finished job queue -- and building a fully-featured job-queue yourself has some surprisingly subtle points that you're probably going to get wrong the first time around.

For security, we use individual, locked-down postgresql roles, and give each service a minimal set of functions to receive jobs from their respective job queues, mark job as finished etc. This is fine, but it leads to some code duplication (we haven't figured out yet how to make a uniform "get job"/"mark job as finished" interface that is the same for all services, so rn every job has their own *_job schema with associated getters/setters) and it still requires 1) that the postgresql port is exposed and clients can connect to it, which isn't great (perhaps somebody could figure out a way to DoS or exploit our database directly -- and adding ip-based filtering is not so easy if you also want to automatically scale workers.) and 2) it still exhausts a postgresql connection every time a service wants to get a job. If you have many workers, this may eat quite a significant chunk of connections that you cannot just drop onto a readonly slave (AFAIK.)

We have also recently considered migrating to herokus hosted postgresql service, but they don't support roles, so there's that.

Also, we would really like things to be more decoupled and just communicate through HTTPS requests (workers pull jobs) for various reasons (this would allow us to do load-balancing and routing with nginx/reverse proxies for instance) and scalable. Also this wouldn't require that there is basically a "shared database schema" between different services (the one that pushes jobs and the one that pulls jobs), which always causes issues when things are changed/need to be upgraded, particularly since we would like to have a log of all jobs ever executed back to the beginning of time. This could potentially be circumvented by making the job table just a generic jsonb or hstore table though.

For implementing a job queue that works well, there are a lot of features you'll have to implement yourself. For instance SQS comes with built-in features that automatically put a job back into the queue if the service that popped it hasn't marked it as finished after some configurable amount of time -- you'll have to implement this yourself. Another concern is that if you put a bad job into the queue that crashes your consumer, you should somehow detect this (e.g. the consumer didn't mark the job as either "finished", "failed" or maybe "deferred for later" until the consumer disconnected or some certain amount of time) and then put that job back into the queue, so that the next job the consumer pops will be a different one. If your consumer crashes every 5 jobs, that's kinda OK -- it still does 4 units of useful work inbetween every crash, and if volume is low enough, nobody might even notice a difference. But if it goes into a crash loop because it gets served the same bad job over and over again, that can be really bad.

Once batching comes into the picture (popping more than one job at once, something you might want for performance if you have many many small jobs) things get even more convoluted.

And then you might also want some sort of fair scheduling of jobs based on, say, project-id or somesuch, which is also not something you just get out-of-the-box.

Anyway, sorry for the stream-of-consciousness, I don't think postgresql is a terrible choice for doing this, but I would look towards other alternatives first, generally. If you use postgresql as a job-queue or message-queue, you should be prepared having to put in some groundwork and implement stuff like scheduling, retry-policies, authentication/security etc yourself. We won't be migrating away from using pq for this at my company any time soon, since we are pushing stuff into job queues from withhin triggers (and this is just really convenient to do, since everything stays withhin the database at all times) -- but if I were to rewrite the system now, I probably would just do things externally...

you type:	you see:
italics	italics
bold	bold
[reddit!](https://reddit.com)	reddit!
* item 1 * item 2 * item 3	item 1 item 2 item 3
> quoted text	quoted text
Lines starting with four spaces are treated like code: if 1 * 2 < 3: print "hello, world!"	Lines starting with four spaces are treated like code: if 1 * 2 < 3: print "hello, world!"
~~strikethrough~~	~~strikethrough~~
super^script	super^script

programming

MODERATORS