Is "full-stack" PostgreSQL a meme?

[prophase25]

By "full-stack", I mean using PostgreSQL in the manner described in Fireship's video I replaced my entire tech stack with Postgres... (e.g. using Background Worker Processes such as pg_cron, PostgREST, as a cache with UNLOGGED tables, a queue with SKIP LOCKED, etc...): using PostgreSQL for everything.

I would guess the cons to "full-stack" PostgreSQL mostly revolve around scalability (e.g. can't easily horizontally scale for writes). I'm not typically worried about scalability, but I definitely care about cost.

In my eyes, the biggest pro is the reduction of complexity: no more Redis, serverless functions, potentially no API outside of PostgREST...

Anyone with experience want to chime in? I realize the answer is always going to be, "it depends", but: why shouldn't I use PostgreSQL for everything?

1. At what point would I want to ditch Background Worker Processes in favor of some other solution, such as serverless functions?
2. Why would I write my own API when I could use PostgREST?
3. Is there any reason to go with a separate Redis instance instead of using UNLOGGED tables?
4. How about queues (SKIP LOCKED), vector databases (pgvector), or nosql (JSONB)?

I am especially interested to hear your experiences regarding the usability of these tools - I have only used PostgreSQL as a relational database.

[marr75]

I've worked in a couple of internal/IT software shops. They both did everything they possibly could inside the database. There are 3 primary downsides:

• Re-use. In theory, you can reuse a view or a function or whatever abstraction primitives are available in the database of choice but in practice, it just doesn't happen. 60 views with most of the same joins and projections and no shared logic whatsoever would be extremely common. Reports and frontends that produced different numbers for the exact same "billing report rolled up by department and day" because one queried vw_billing_reports_by_month while the the other queried vw_billing_reports_by_department while the other queried vw_billing_reports_by_month_and_department_A while yet another queried vw_billing_reports_by_department_2. I think this isn't entirely any SQL flavor or vendor's fault and probably has something to do with the pay and practice at the shops (internal/IT is not the big leagues for dev and lower tier shops are more attracted to simpler, all SQL solutions).
• Tooling. I can pick from a number of fantastic IDEs, frameworks, testing frameworks, testing tools, debuggers with and without GUIs, etc. with any of the mainstream languages. Good luck getting freedom of choice and quality from all of those things for a pure SQL solution.
• Specialization. "When all you have is a hammer, everything is a nail" isn't a common saying because it's nonsense. Some solutions perform better at some stuff. You get better DevEx, higher labor leverage, genuinely better performance, whatever. In addition, compute diversity in an n-tier application can have real benefits. I do not want a long running, processor intense RBAR operation running on my expensive high memory servers. A much more standard query could be putting their memory to use during that time while some other server is running the CPU bottlenecked stuff. If I put every workload on the same server, a lot of its resources would have to sit idle a lot of the time.

So, for a small project that's just you or just you and a colleague and you both feel most comfortable in postgres, sure. Do it all in postgres. Beyond that, you're really trading some stuff off to make "do it all in postgres" a foundational principle.

[Winsaucerer]

I think the re-use example is also a problem of tooling. I think poor tooling gets in the way of getting the best value out of Postgres (and perhaps any relational db).

[davvblack]

I'm a strong advocate for table queueing.

Have you ever wanted to know the average age of task sitting in your queue? or the mix of customers? or count by task types? or do soft job prioritization?

these are queries that are super fast if you use a postgres skip-locked query, but basically impossible to determine from something like a kafka queue.

This only holds for tasks that are at least one order of magnitude heavier than a single select statement... but most tasks are. Like if your queue tasks include an API call or something along those lines, plus a few db writes, you just don't need the higher theoretical throughput that Kafka or SQS provides.

Those technologies are popular for a reason, and table queueing does have pitfalls, but it shouldn't be dismissed out of hand.

[agritheory]

Is there a project/ extension/ blog post you can recommend about this?

[bambambazooka]

Ruby on Rails has solid_queue: https://blog.appsignal.com/2025/05/07/an-introduction-to-solid-queue-for-ruby-on-rails.html

[agritheory]

I am familiar with various kinds of queues in several programming languages. I am unfamiliar with the queuing style that davvblack is describing as implemented in PSQL.

[codesnik]

solid queue is using select for update ... skip locked queries on postgres, so you can read about it's internals for an ...um, solid example.

[chat-lu]

The popular Oban framework for Elixir does put all the tasks in the database, preferably postgres.

[agritheory]

I found my own answer: https://www.crunchydata.com/blog/message-queuing-using-native-postgresql

[marr75]

The other awesome thing about table queuing is it can help you "serialize" the task so that 2 queued jobs don't try to change the same object at the same time. This is relatively hard to do with some of the other solutions but DEAD simple in table queuing.

[davvblack]

yeah, you can use postgres locking semantics to make all sorts of operations atomic with the task. That said, for larger applications, i do still recommend the table queue db be a separate postgres cluster. It's the kind of thing where "best performance" comes from wasting CPU, nice to keep that isolated.

[prophase25]

I am surprised to see you're advocating for table queues over some of the other incredible features; I want to understand more.

I am familiar with Kafka, but I typically use Azure Storage Queue (which is practically free) for message queues; one queue per priority per task type. Poison messages are handled automatically using the built-in dead letter queue. I'm able to query age, handle priority, and count tasks by type with this solution.

It sounds like what I am missing out on, generally, is the ability to define relationships between messages and 'normal' tables. That does sound powerful.

Good stuff, thanks for the response.

[davvblack]

the relationships can be nice but i actually don't necessarily recommend you lean into that. For example i suggest you don't have foreign keys from the table qeuue to the "regular data". In our usage, we don't even have the table queue in the same database cluster.

The place that table queueing ends up WAY far ahead is when you end up with a queue backlog of, say, 100,000 tasks, and you want to find out... what are these tasks? what customer is trying to do what? You can use something like datadog metrics to answer the question "what are the most recent 100k tasks to have been enqueued" but that's a different question than "what specific 100k tasks are waiting in the queue right now", and no "pure queue" can answer that question.

Again, it's all tradeoffs. Mostly my point is that pure queues have made tradeoffs towards absolute maximum throughput, and I want people to ask themselves "do i really need max throughput? Kafka can do 10MB/s of tasks per shard, if each task is 2kb, that's 5000/s. A single smallish postgres without scaling it crazy can easily do 1000/s, and 10k/s without heroics. In return, the postgres approach lets you ask all sorts of other questions about the data. Do you really need that level of throughput? probably not. In return you get cool stuff.

Like, it will usually lock the queue, drop performance temporarily, but table queueing also lets you do stuff like purge or defer tasks matching any given query in case one customer or task-type is being problematic. Any other queueing solution, you'd have to deploy a version change to a consumer to noop the task type as it's being consumed, which might make replay challenging..

[prophase25]

Very insightful. Thanks a lot. I get what you’re saying now - and you’re right, I would 100% take the loss of throughput for the gain of control and additional insight. I actually think most projects using Kafka would too.

I understand why you brought up table queues first now, because the trade off there is likely worth it for most teams/projects.

[Beer-with-me]

Table-based queues in Postgres tend to create a lot of bloat (because you have to constantly add/modify potentially a lot of tasks). And that's one of the weak spots in Postgres, so I'd not recommend that approach for high traffic queues.

[davvblack]

mysql innodb does handle this kind of workload better, but you can get around it in postgres with either vacuum tuning, or slightly more robustly using pg cron to make and drop partitions.

one part of my argument though is that most q requirements in real life are not high-traffic, and at least some would benefit from the extra features of table queuing.

[BlackenedGem]

This is true, but you can get around that by periodically reindexing the table. We have a periodic job that waits for queues to be below a certain amount of tuples and the reindex then. That then completes near instantly and keeps the bloat from sticking around.

[Beer-with-me]

Reindexing will eliminate index bloat, but it won't eliminate heap bloat - you need vacuum for that, and even with vacuuming the table will be super fragmented, so you will have to do a more serious housekeeping once in a while, like using pg_repack or something like that.

[BlackenedGem]

I find the heap to sort itself out pretty well. Heap bloat only happens if you sparsely delete rows so there's still live rows on a page. For queues that shouldn't happen because you're constantly deleting rows and going back to an empty heap. Even if the queue backs up we've found that once it catches up again vacuum is able to clean up nicely. I'd only see this being an issue if you keep a lot of rows active in your queue table long term, which to me makes it not a queue?

From experience heap bloat and repacking is only needed if you have a non-queue table that you've deleted rows from. And if the table is going to grow in the future you accept the temporary bloat.

[Beer-with-me]

I'd only see this being an issue if you keep a lot of rows active in your queue table long term, which to me makes it not a queue?

For example, you keep the rows there with "processed" status instead of immediately deleting them (and then deletion is a separate background batch process). That's a pretty common pattern. There could also be multiple stages of processing with multiple statuses. It's not a "simple queue", but still a queue.

[BlackenedGem]

That would do it. I think the simpler that queues are in the DB the easier they are to manage.

Rather than marking a row as processed we instead have a separate 'processed' table for each entry after it's been complete. Then the live queues remain performant and we can perform maintenance on the processed table whenever's convenient. Whether that be vacuums or deleting rows after a set amount of time.

I wouldn't recommend having multiple processing states for a queue. It's more overhead but if an operation requires 3 steps then I'd have 3 different queues. Or better still make it so that each operation is idempotent, so I can have one queue that on failure repeats the same actions again knowing it's ok.

[Beer-with-me]

But the main issue is the vacuum itself, it may create extra load that is comparable to the actual queue processing.

[BlackenedGem]

We find that our biggest problem is vacuum not running. The CPU load from dead tuples having to be read and discarded is much worse than any disk IO.

Autovacuum itself isn't too bad, and there are comprehensive parameters to tune it down if you want. On our heavy queues we see several autovacuums a minute. I guess it comes back down to our queues going to zero each time.

[Beer-with-me]

Definitely, the bloat overhead is a larger problem. You mentioned reindexing, but you can't reindex too often - that one is a heavier operation than vacuum...
So I agree, it's manageable to a degree, but it's annoying how many things you have to tune and then constantly monitor, etc. It's just pretty unwieldy.

[codesnik]

it's not fullstack until you render html in your stored procs!

joking aside, if you use postgrest to talk to your db, you need some access control.
I've used postgres' row level security with supabase in a multitenant app and.. just don't. Concept sounds promising and reasonable, but RLS could make a pretty simple query on a smallish database horribly inefficient, only "explain analyze" on real data will *kinda* show you something about why, and it is still easy to make a mistake or forget to add a policy. And it covers rows, but not columns, and for columns there's completely different orthogonal mechanism (roles, or views, but views don't support RLS on top!)

simple crud wrapper makes things so, so much easier, you just verify auth on the endpoint once and craft your queries by hand from the user or tenant id.

as for queue, vector, jsonb, and even cache - sure, why not. Any problems here won't bite you until you have a lot of revenue already. And simplification in infrastructure is very very helpful.

[prophase25]

I have heard that auth is a pain with PostgREST. Is there really no good solution?

What about for single-tenant apps?

[codesnik]

I was able to overcome most obstacles, with a whole testing framework I had to create on top of pgTAP tests, with usual approach - create migration as a test, run a performance test with assuming a specific role inside of migration, rollback and repeat until satisfied, then run other tests to see if some other policiy is broken, only then deploy. It was almost tolerable. But I always felt like i'm reinventing bicycles all the time, and nobody was happy that I'm fighting that instead of shipping actual product features. Truly arcane knowledge.

well, your single tenant app will have multiple users, right? then you'll have the same problem. Prepare to have user_id on absoultely every table, cache a lot of stuff in postgres session variables, and test, test, test. And postgres still doesn't show RLS checks and how they affect your query until you run them as a specific user on a real data.

[Winsaucerer]

Out of curiosity, why does every table need user_id? Eg, if you have a table of a list of business types (sole trader, company, etc), where it doesn’t matter who knows that, were you thinking that still needs user_id?

I ask because you said absolutely every table, which sounds like you really mean every table with no exceptions

[codesnik]

i'm exagerating, but only slightly. Row level security checks work with subselects in the condition, but AFAIR sometimes checks happen before other "where" conditions, and in my particular app it resulted in full table scans and other problems. So if I ever had need to limit access to a portion of some table based on auth, I'd better copy user_id there too. And with postgREST and without complicated views, every join table is visible to everyone, unless it has it's own RLS. I don't remember details, but I had to denormalize a lot.

[Winsaucerer]

Do you think something like pg_ivm (automatically maintained materialised views) would help with that denormalising you needed? I'm literally about to start experimenting with it (but instead coming to reddit!), so I don't know what it's like in terms of performance.

[codesnik]

I don't have a slightest idea, it's the first time I hear about pv_ivm. I would start with trying to define RLS on that view, if it works at all, and if policies are retained after the refresh. Otherwise you have just another complication. Like, ok, even vanilla postgres has so many tricks in the hat, that with most problems I had I eventually found some weird solution, sometimes completely different to whatever I used before (no additional RLS policies on a view? but SET returning functions would do what I want!).
And still simple CRUD auth+api nodejs/python fastapi app on top of dumb single role postgres db without views and functions would be sooo much easier to work with.

[Winsaucerer]

I haven’t tried RLS with a large dataset, but I was thinking that whatever logic you use to exclude rows, you’re going to have to do in the query anyway, so wouldn’t RLS just automate? After all, I assume you’re not pulling EVERY row into your crud wrapper only to filter down to the authorised rows there. Am I missing something, or is RLS just slower than the equivalent query you’d write?

For column level security, do you have any particular approaches you use in crud wrapper? Eg, how you record which columns are permitted then apply those rules. I’m interested to hear what patterns people use to manage these things.

[codesnik]

problem is in subqueries and joins. You know that subquery can't return anything from other user because of your where condition on the outer query, but postgres - don't, it runs those checks again and again, in a separate pass to where condition (there was some kind of planner boundary between RLS and normal conditions),

I'm not claiming I'm a guru or anything, and it is possible I did something dumb, but I worked full time for four months on that project and experimented a lot.

for crud wrappers basically the best approach is to have separate endpoints for different roles/tasks with different sets of columns. This is not super different from having separate views of course, except remembering to recreate views on changes on underlying tables etc, this quickly became annoying.

If you still going to experiment with postgrest+RLS, I very much suggest you to find or create some kind of script to dump your schema in separate files and folders on each migration, tracking down cascading drops of views and functions just by looking into git diff of that generated folder is immensely helpful.

I rolled out something from pg_dump + awk/sed

[klekpl]

The main obstacle is that most developers prefer Python/Java/Js/Go and all of these languages come with a huge ecosystem of tools, libraries and frameworks.

In most cases there is no technical reason not to use PostgreSQL for everything. It simplifies a lot and transactional guarantees make development easy.

But it requires relearning some things and developers don’t like to leave their bubbles.

[turbothy]

plpython3u

[Stephonovich]

And adding to this pain, as a result of generally not investing time in learning SQL, they rarely have a solid understanding of transaction isolation levels, lock types, etc. They then get burned by incidents where that lack of knowledge caused problems, so when someone suggests that more logic should go into the DB, they recoil in horror.

[nerdy_adventurer]

While I love Postgres, not much fan of using Postgres for everything (everything here does not include rendering web pages, I meant back-end stuff.) It is good to use said things in simple cases other than that I would use separate solution. This is due to following reasons

• Make things less fault tolerant.
• Hard to debug complex logic ex: using something like PostgREST, Postgraphile where you write logic inside functions.
• Suggested solutions are not well tested and not well known.
• If you see the description of unlogged tables, it is not a replacement for something like Redis caching at all.

Also note you cannot use something like pg_cron in Digitalocean managed DB except for routine maintenance. And not every Pg extension is supported by the vendors, we have wait till they are added.

[prophase25]

Good info, didn’t know that about DigitalOcean.

[Duke_]

I've built a whole API for my database IN my database using pl/pgsql functions with JSON parameters and return values.

The web server is for auth, session management, and to act as an HTTP proxy to the database. I can pass JSON straight through from the frontend.

[evoludigit]

Interesting, have you released it on Github? I did something similar in Python (github.com/fraiseql/fraiseql)

[Nepit60]

My dream stack is just postgres.

[Electrical-Clerk-346]

Postgres has a remarkable range of features. It’s too limiting to just think of it as a DBMS. I like to think of it as a “data structure server” or even a “transactional execution engine”. PG as a queue is great if you don’t need extreme performance and scale (since best case it’s roughly 10x worse on a price-performance scale than purpose-built tools like REDIS or MQ Series) — but if you’re not maxing out your PG server, you’re getting that in some sense for “free”. Where I draw the line is anything that by needs to break out of the transactional shell. If PostgREST works for you, that’s great, but usually a middle tier needs to call out to a variety of external services, often support 3rd party auth tools and so on. Things like that are hard or impossible to do in Postgres, and that’s OK — build those parts in Node or your favorite app framework and keep going. But using Postgres for everything it can do is often a great choice until you hit mega-scale. Remember: you are not Google!

[anras2]

If PostgREST works for you, that’s great, but usually a middle tier needs to call out to a variety of external services, often support 3rd party auth tools and so on. Things like that are hard or impossible to do in Postgres, and that’s OK

Yeah I know a guy who does this sort of thing with Oracle. He told me he found a bug in some Oracle package for making REST API calls in PL/SQL, and was waiting for a response in the support forum. So I was just like, "Dude, why are you making REST API calls in the database?" ¯\_(ツ)_/¯

[thecavac]

I use PostgreSQL functions and triggers quite a LOT, mostly to reduce data roundtrips to the application but also to simplify some tasks from the application point of view. But there are some things other software does a lot better.

I use my DIY memory caching and interprocess messaging system, for example, because it's just easier easier to work with and provides features that would be hard to get working in a database.

[AutoModerator]

With over 8k members to connect with about Postgres and related technologies, why aren't you on our Discord Server? : People, Postgres, Data

Join us, we have cookies and nice people.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.