How to use Postgres for everything | Hacker News Reader

How to use Postgres for everything

github.com

289 pointsOlshansky169 comments

Just don't use a single Postgres DB for everything as you scale up to 100+ engineers. You'll inevitably get database-as-the-API.

Now if you have the actual technical leadership [1] to scale your systems by drawing logical and physical boundaries so that each unit has its own Postgres? Yeah Postgres for everything is solid.

[1] Surprisingly rare I've found. Lots of "successful" CTOs who don't do this hard part.

> You'll inevitably get database-as-the-API

I think that's actually the point of "postgres for everything"?

If you don't have any discipline it becomes hell.

Not to mention that a random team writing a migration that locks a key shared table (or otherwise chokes resources) now causes outages for everyone.

Right but in this "100-engineer" scenario you'd have hoped the following would have happened:

- Docs and guidelines on migrations would have been written

- Some level of approval and review is required before execution

These are things that isn't really postgres specific, any company that doesn't have those is going to be a nightmare.

If teams have technical boundaries defined at a higher level in the stack (e.g. APIs) and so they don't share a database, you don't need loads of process and docs and architectural meetings to coordinate. Letting teams delivery independently is a good architectural feature.

This is why everyone adopted "microservices" - it's Conway's law in action, a technical intervention for an organizational problem.

Even with those things, blast radius for mistakes is ..the entire company is down.

e.g. User management deploy has somehow taken down core payment processing.

Not a problem until it's a problem.

And the best thing is that until it’s a problem you can focus on product/market fit and delighting your customers.

Overengineering is a plague amongst SWEs, and almost as dangerous as failing to sell the product in the market.

I think cargo cutting stuff like "Postgres for everything" is also a plague. I agree you don't want to overengineer, but if you're a leader and your policy is basically "engineers can't ever be trusted to do hard things" then you're a bad leader.

Sorry if this comes off as brusque, but I've seen good engineers want to use Elasticache for the right reasons and seen "leaders" tell them no because "caching is one of the hard problems in computer science."

Leadership-by-folksy-saying is sadly a real thing.

I worked in a Postgres for everything business, and the IPO and later value creation for customers and investors (including me) had been stratospheric.

Not dealing with loads of technology choices in the early days is a boon.

Once we hit more than $100m in rev it made sense to allow a bit more optimisation for purpose - but only when we had cash flow to pay for it. Otherwise all these fancy-shmancy choices are just dressed-up tech debt.

> Once we hit more than $100m in rev

Yeah my original comment was about experiences working at places with this kind of monetary success (if not more) and stubbornly not evolving. Low trust eng leadership philosophies will do that though.

"Until it's a problem" is doing a lot of heavy lifting in your sentence. For 99% of projects that "until" will never come.

I agree completely. Far more startups are sunk by the cost of hypothetical technical problems than real product/market ones.

Drop the scare quotes from "successful". These guys shipped products. The migration to multiple databases, syncing user information etc. is a milestone, not a necessity at every step.

Thank you

Unfortunately a lot of places teach software engineers to build over complexity before they need it. I like to remind people that StackOverflow ran on a couple of IIS servers and a msSQL db for a good while before scaling up to a little more than a couple of IIS servers. Hell they didn’t even do the CDN thing until 2017 or something similarity “crazy”. Mean while you have hordes of developers who write interfaces which will only ever be consumed by a single class because they’ve been taught to do so.

As I see it the only way to look at scaling issues is that you’ve made it. Almost no user facing software will ever reach a point where something like Django couldn’t run it perfectly fine. It’s when you do, you build solutions, which sometimes means forking and beating the hell out of Django (Instagram), sometimes going into Java or whatever, spreading out to multiple data bases and so on. Of course once you actually hit the scaling issues you’ll also have enough engineers to actually do it.

nah at the scale I'm talking about, if you've shipped a produc but your engineers are increasingly dissatisfied with the effort it takes to ship (and are becoming gun shy because you haven't decoupled things so their outage blast radius is huge), then you are currently failing as a technical leader. And if those things are true and you stick to "one Postgres monolithic DB for everything" you are cargo culting too hard.

You can't just keep "doing things that don't scale" forever. If you have 100+ engineers [1], you aren't a startup anymore no matter your roots.

[1] and remember, my comment that is the ultimate parent of this conversation is about not continuing to do this at 100+. I said nothing about scaling up to that point.

You could go whole hog with postgrest I suppose

I always wonder if postgrest works well after growing quite a bit. I really should google the largest companies using it.

In my experience, without careful tunning, postgres performance starts dropping significantly around 10 million rows.

We have billions of rows with clustering on one instance, perfect, half a rack almost.

Without any consideration for I/O?

In my experience performance is often directly related to the ratio (total RAM available to Postgresql, shared buffer and buffercache included / total size of tables and indices ), save any weird usage pattern.

Could you tell us a bit more about your experience?

Just check out who the key project maintainers are, and then look at who they're employed by. You'll probably recognize the companies.

If you don't have schema cache reloading enabled (i.e. you're running it in prod), you can linearly scale postgrest instances pretty much infinitely without impact to the db. Each new instance just costs you a handful of connections and a small amount of memory.

Check out supabase, they're all in on postgrest.

You don't have to plan this very early; most companies won't get to 100+ engineers. Let's ship first and worry about this, much much much much later. Overarchitecting stuff makes life hard; coming into companies that have 40 servers running with everything architected for 1000000 engineers and billions of visitors while in reality there aren't even 2 users and there is 1 overworked engineer. Stop doing that and stop people telling to do that.

If you die before that, it was the market, not engineers being slow. Of course. Or sales.

This (don't overarchitecture stuff) is actually an argument in favor of using e.g. Postgres for everything, as adding more and more tools adds complexity and (architectural) overhead.

> Let's ship first and worry about this, much much much much later. Overarchitecting stuff makes life hard; coming into companies that have 40 servers running with everything architected for 1000000 engineers and billions of visitors while in reality there aren't even 2 users and there is 1 overworked engineer.

Even if you try to draw boundaries between different bits of the system, you are unlikely to end up with 40 servers, not even close. The average system wouldn't even have 40 separate use cases for PostgreSQL or even the need for 40 different dependencies.

However, if you do split it up early, you more realistically would have something along the lines of:

  * your main database - the main business domain stuff goes here
  * key-value storage/cache - decoupled from the main instance, because of the decoupling nobody will be tempted to put business domain specific columns here but keep it generic
  * message queue - for when processing some data takes a bunch of resources but during peak load you need to register a bunch of stuff quickly and process it when you get the capacity
  * blob storage - to not make the main database bloat a whole bunch, but to keep any binary stuff in a separate instance, provided you don't need S3 compatibility
  * auth - the actual user data, assuming you use Keycloak or something like it
  * metrics - all of the APM stuff, like from Apache Skywalking or PostgreSQL

Give or take 2-3 services, all of which can run off of a Docker Compose stack locally or with the container management platform of your choice (not even Kubernetes necessarily, but simpler ones like Hashicorp Nomad or even Docker Swarm, using the Compose format). All of which can have their backups be treated similarly, similar approaches to clustering, all of which have similar applicable tools, all of which have similar libraries for integration with any apps and all of which can be granularly inspected in regards to how they perform and scaled as needed.

It's arguably better than a single large instance that ends up with 300 tables eventually, has 100 GB of data in the shared test environment and you wouldn't know where to start in regards to making a working local environment if you join a legacy org that isn't using OCI containers and giving each dev a local environment. The single large deployment will rot faster than multiple ones. How many you actually need? Depends on what you do, it wasn't that long ago that GitLab decided to split their singular DB into multiple parts: https://about.gitlab.com/blog/2022/06/02/splitting-database-... (which also shows that you can get pretty far with a single schema as well, to anyone who wants a counter argument, though they did split in the end)

Realistically, if you're doing a personal or small project, everything can be in the same instance because it'll probably never go that far, but I've also seen both monolithic codebases (that are deployed as singleton apps, e.g. not even horizontal scaling) and what shoving all of the data in a single database leads to (regardless of whether it's PostgreSQL or Oracle), it's never pleasant. I'd at the very least look in the direction of splitting out bits of a larger system based on the use case, not even DDD, just like "here's the API and DB instance that process file uploads, they are somewhat decoupled from our business logic in regards to products and shopping carts and all that".

Now, would I personally always use PostgreSQL? Not necessarily, since there are also benefits to Redis/Valkey, MinIO, RabbitMQ and others, alongside good integrations with lots of frameworks/libraries that just work out of the box, as opposed to you needing to write a bunch of arguably awkward SQL for PostgreSQL. But the idea of using fewer tools like PostgreSQL for different use cases (that they are still good for) seems sound to me as well.

User above was making a point.

And you are lucky to not see an org where everything is a microservice that uses some unusual database, because the poeple responsible wanted to use some fancy new technology. Also seems you were lucky to not see messy development where some data is in a legacy system, some in new (which doesnt quite work), some in "cool" mongoDB that uses math.random to report just 10% of errors and rest is plugged via CSV files coming from ERP edited in Excel...

> And you are lucky to not see an org where everything is a microservice that uses some unusual database

I've been unlucky to see an org where everything is a monolith (not horizontally scalabe due to a plethora of design choices along the way) that uses Oracle, including plenty of stored procedures and DB links along the way.

Honestly, I'm starting to think that you can't win with these things and that there will be projects that suck to work with regardless of the tech stack or regardless of how much you try to make them not suck.

That said, in general, you could probably do worse than PostgreSQL or even MariaDB (because at least with that one you can still run it in a container locally, much like MySQL, instead of running into Oracle XE or Oracle free version, whatever they called the latest one, limitations where you can't even bring the shared environment schema over to local containers).

> Oracle

> you can't win

Is there anything good about Oracle in 2024? Their business model seems to be make products that require expensive consultants and are difficult to migrate-out.

I still have some optimism in me and think that you can win - if you use the correct technologies.

Every now and then there are multiple blog posts here about "choosing boring technology". For example the blog about 7 databases in 7 weeks linked to this version: https://boringtechnology.club/

I agree totally. But I've seen companies go to 100+ and still insist that the level of monolithic coupling I describe hasn't been outgrown.

database as API works fine if you have properly abstracted things with sprocs and views. It will be also far less brittle than 100 services exposed as GraphQL

Stored procedures just add overhead and make everyone's lives harder. Forget about any ORMs, you're writing raw SQL with all the quirks of PL/pgSQL biting you all the time.

There's advantages too:

- marking some columns as NOT NULL.

- referential integrity means you can't accidentally have dangling pointers to non-existant dat.

- mutually exclusive columns let's the database enforce things like "at least one of A and B needs to be Nonzero, and both cannot be Nonzero at the same time.

- create a type that allows only values matching a specific regex.

Seriously, if you want strong typing across composite data, there isn't a language invented yet that comes even close to a RDBMS.

Of course the majority of Devs don't know any of this because their ORM doesn't expose any of this; it gives them a way to store and query tabular data, and nothing else.

None of this prevents you from doing both inside the DB _and_ the app. That way you cover your bases that (1) your app is sound and has maximum amount of fail-early validations to avoid corrupted data states and (2) even if somebody decides to skip the app and try to be clever in a psql console they'll still not be able introduce corrupted data states and (3) leave the door open for other apps to be able to connect to the same DB and do stuff (or simply to allow for a rewrite in another language).

The things you listed aren’t stored procedures, they are all possible to implement as check constraints. They are great, and they are fully compatible with ORMs. A stored procedure is a bit of code, stored in and executed by the database, usually written in a 1960s-era language (like PL/SQL or PL/pgSQL).

> The things you listed aren’t stored procedures, they are all possible to implement as check constraints. They are great, and they are fully compatible with ORMs.

I didn't claim that they are not compatible with ORMS. I said the majority of developers have no clue just how much of value they can get out of their database using types and constraints because the only interface they have every used to the RDBMS is the ORM, and the ORM doesn't expose any of this.

I've commonly seen developers put in things like `if ((!A && B) || (A && !B)) { /* updateDbWithOneOf(A,B) */ }` in their code rather than use the constraints provided by the RDBMS.

Well for starters they can improve your security posture. In proper dbs like PG the version change is transactional so you don't have to deal with schema being out of sync with code. You don't have to plan for all the possible future scenarios where you will need transaction boundary to cross the service boundaries. You can write stored procedures in pretty much any lang. Query optimisers and execution engines are far more tested and preferment vs some GraphQL gateway.

I'm so glad to see somebody made this comment!!

Postgres for everything is pretty neat in that you can take expertise from one place, and use it somewhere else (or just not have to learn a zillion tools)

The same database for everything is a really good way to have a tangled mess (are people still using the word complected?) where nobody knows which parts are depended on by what.

Database-as-the-API can scale surprisingly far, particularly if you sell a single-tenant shard to each customer and therefore a separate database to each customer. Drawing logical software boundaries before Product even knows what the domain looks like (i.e. which features will sell) is quite risky.

Postgres is inherently multi-tenant. Have separate logic DBs in one physical instance, connect using roles with minimal permissions, expose views (and materialized views!) for querying so you can mutate the underlying tables without requiring applications to change.

Great point - wish the people in charge at my job knew that lol

I absolutely love Postgres, but please allow me to say that you absolutely don't want to expose an API generated from a database to people outside of your team. This limits you a lot in changing the way you store your data.

I wrote about this topic before and haven't changed my opinion much. You don't want to have that tight coupling: https://wundergraph.com/blog/six-year-graphql-recap#generate...

you could use views as a layer in between?

You should use views as the layer in between. They'll let you version your API and make changes without breaking things.

What exactly is the problem with tight coupling? You're going to insert an entire layer that basically translates format A to format B, just so you can later change a column name in the database and not have to change it in the API or something?

Yes. A lot of the work I've done through my career is essentially this, once you boil away the rest of the details.

1. You don’t want or need to expose lots of implementation details. Many of your data structures should be private, and many should be partly private.

2. Your data structures should not dictate the shape of your api, usage patterns should (e.g. the user always needs records a,b,c together or they have a and want c but don’t care about b)

3. It stops you changing any implementation details later and/or means any change is definitely a breaking change for someone.

Normalization is one of those typical issues where you might be fine with having everything normalized when you start off, but then once performance gets bad you end up denormalizing tables that are typically joined.

There's a few issues; one is that if you have the DB do everything, all of your business logic lives there too, instead of just the data. This is still fine if you have a single use case, but what if in addition to your main application, you also need to use it for things like BI, customer service, analytics / predictions, etc? It then quickly becomes better to use it as a datastore and have another layer decide what to do with it.

And in 30 odd years, everything will be different again, but your company's main data store will not have moved as fast.

The extremely obvious problem is that how you store data is an implementation detail and those change when requirements (or the market) evolve. I'll give you an API and will make triple sure it's as fast as a machine can even serve it and you let me worry about how it's all stored.

To additionally answer you with an analogy: when you have a problem with a company, you call the call center, not Jenny from accounting in particular. Jenny might have helped you twice or thrice but she might leave the company next year and now you have no idea how to solve your problem. Have call centers to dispatch your requests wherever it's applicable in the given day and leave Jenny alone.

> * What exactly is the problem with tight coupling?*

As Joel Spolsky put it: ”the cost of software is the cost of its coupling”.

More specifically the cost of making changes when “if I change this thing I have to change that thing”. But if there’s no attention paid to coupling, then it’s not just the two things you gave to change, but “if I change this thing I have to change those 40 things”.

I was recently annoyed to find postgres indexes don't support skipping [1] you also can't have the nul character in a string (\u0000) [2]. Its great, but it has some strange WTF gaps in places.

[1] https://wiki.postgresql.org/wiki/Loose_indexscan

[2] https://stackoverflow.com/questions/28813409/are-null-bytes-...

Yes, skip-index scans require custom sql now.

I am also a bit annoyed by cache-like uses not being first-class. Unlogged tables get you far, temporary tables are nice, but still all this feels like a hurdle, awkward and not what you actually need.

> I am also a bit annoyed by cache-like uses not being first-class.

Since what happened recently with Redis[1] the first thing I thought about was Postgre, but the performance[2] difference is too noticeable, so one have to look for other alternatives, and not very confident due thinking such alternatives may follow the same "Redi's attitude" ( ValKey, DragonflyDB, KeyDB, Kvrocks, MinIO, RabbitMQ, etc etc^2 ).

It would be nice if these cache-like uses within Postgre had a tinny push.

[1] https://news.ycombinator.com/item?id=42239607

[2] https://medium.com/redis-with-raphael-de-lio/can-postgres-re...

    XXXXX achieves a latency of 0.095 ms, which is approximately 85% faster than the 0.679 ms latency observed for Postgres’ unlogged table.
    
    It also handles a much higher request rate, with 892.857,12 requests per second compared to Postgres’ 15.946,02 transactions per second.

What is a reasonable use for a null character in a string? My first instinct is that strings with nulls in them should absolutely be rejected.

Not everything needs to be a C-string (null-terminated array/sequence of characters.) We are advanced enough with our understanding of Things that we can include metadata along with a chunk of bytes to indicate “this is a ‘string’ and it’s q bytes long and can have any value you want in there.”

That said, I’m with you. And if someone wants nulls inside their “strings” then they probably want blobs.

There are two kinds of programmers: Those who think of strings as text, and those who think of strings as a sequence of bytes. The second group doesn’t care about the special case where a byte is all zeroes.

In that second case the string is better represented as "bytea", which has most (but not all) of the features of the "text" type.

I agree with your take, it's just that many programmers want to easily jump from "byte array" to "string in XYZ encoding". I personally prefer byte arrays for unsafe data and to do deserialization in application code.

In other words, considering we are talking about string and unicode...

There are two types of programmers, those that are wrong and those that are very wrong

lol. :)

Funny but not entirely true. I had cases when we had to urgently store a firehose of data and figure out the right string encoding later. Just dumping the strings with uncertain encoding in `bytea` columns helped us there.

Plus for some fields it helps with auditability f.ex. when you get raw binary-encoded telemetry from devices in the field, you should store their raw payloads _and_ the parsed data structures that you got from them. Being this paranoid has saved my neck a few times.

The secret is to accept you are not without fault and take measures to be able to correct yourself in the future.

Indeed, one system I dealt with used char instead of blob. The text as stored was riddled with U+FFFE (unicode unknown character).

Yup. It's a huge red flag when a datatype intended to be used for representation of written human language is abused to store something that has no glyph recognisable in any human language.

There's a lot to complain about with nul-terminated strings, but not being able to store arbitrary bytes ain't one of them.

That your JSON deserializer accepted them.

> you also can't have the nul character in a string …

Let me introduce you to blob…

PGQueuer is a lightweight job queue for Python, built entirely on PostgreSQL. It uses SKIP LOCKED for efficient and safe job processing, with a minimalist design that keeps things simple and performant.

If you’re already using Postgres and want a Python-native way to manage background jobs without adding extra infrastructure, PGQueuer might be worth a look: GitHub - https://github.com/janbjorge/pgqueuer

I always wondered about the claim that SKIP LOCKED is all that efficient. Surely there are lots of cases where this is a really suboptimal pattern.

Simple example: if you have a mixture of very short jobs and longer duration jobs, then there might be hundreds or thousands of short jobs executed for each longer job. In such a case the rows in the jobs table for the longer jobs will be skipped over hundreds of times. The more long-running jobs running concurrently, the more wasted work as locked rows get skipped again and again. It wouldn't be a huge issue if load is low, but surely a case where rows get moved to a separate "running" table would be more efficient. I can think of several other scenarios where SKIP LOCKED would lead to lots of wasted work.

Job rows could have an indexed column state so you just query for the rows with state "not-started".

This way you won't need to skip over the long jobs that are in state "processing".

I'm not 100% confident, but this sounds like it would have unexpected effects.

Good point about SKIP LOCKED inefficiencies with mixed-duration jobs. In PGQueuers benchmarks, throughput reached up to 18k jobs/sec, showing it can handle high concurrency well. For mixed workloads, strategies like batching or partitioning by job type can help.

While a separate "running" table reduces skips, it adds complexity. SKIP LOCKED strikes a good balance for simplicity and performance in many use cases.

One known issue is that vacuum will become an issue if the load is persistent for longer periods leading to bloat.

>One known issue is that vacuum will become an issue if the load is persistent for longer periods leading to bloat.

Generally what you need to do there is have some column that can be sorted on that you can use as a high watermark. This is often an id (PK) that you either track in a central service or periodically recalculate. I've worked at places where this was a timestamp as well. Perhaps not as clean as an id but it allowed us to schedule when the item was executed. As a queue feature this is somewhat of an antipattern but did make it clean to implement exponential backoff within the framework itself.

Also https://github.com/TkTech/chancy for another (early) Python option that goes the other way and aims to have bells and whistles included like a dashboard, workflows, mixed-mode workers, etc...

Check out the Similar Projects section in the docs for a whole bunch of Postgres-backed task queues. Haven't heard of pgqueuer before, another one to add!

What are its advantages compared to a more dedicated job queue system?

I think PGQueuers main advantage is simplicity; no extra infrastructure is needed, as it runs entirely on PostgreSQL. This makes it ideal for projects already using Postgres and operational familiarity. While it may lack the advanced features or scalability of dedicated systems like Kafka or RabbitMQ, it’s a great choice for lightweight without the overhead of additional services.

Being stuck with MariaDB/MySQL in some projects, I recently compared it to PostgreSQL and found many of these extended capabilities existing there also, including JSON, temporal tables (w/ SYSTEM VERSIONING), columnar and vector storages etc.

LISTEN/NOTIFY type functionality was sort of missing but otherwise it was surprising how it is keeping up also, while little of that is probably being used by many legacy apps.

Can you please add this shameless plug?

https://github.com/jankovicsandras/plpgsql_bm25

Opensource BM25 search in PL/pgSQL (for example where you can't use Rust extensions), and hybrid search with pgvector and Reciprocal Rank Fusion.

Try opening a pull request maybe

Cool to see you here!

We discussed this very thing on supabase

https://github.com/orgs/supabase/discussions/18061#discussio...

"It's inspired by this article from the Amazing CTO [0]"

Getting up in the morning, seeing an article that references you, bliss!

[0]: https://www.amazingcto.com/postgres-for-everything/

> Use Postgres for Fulltext Search instead of Elastic

pg fulltext search is very limited and user unfriendly, not a great suggestion here

I can see a lot of benefits of having 1 API to access a lot of functionality.

For example instead of integrating with a message queue I can just do an INSERT this is great. It lowers the friction.

Vector search is a no brainer too. Why would I have 2 databases when 1 can do it all.

Using Postgres to generate HTML is questionable though. I haven't tried it but I can't image its a viable way to create user interfaces.

> Using Postgres to generate HTML is questionable though. I haven't tried it but I can't image its a viable way to create user interfaces.

Check https://apex.oracle.com/ which does that.

While we are it - are there any good resources on how to best self host a Postgres database? Any tips and tricks, best practices, docker / no docker etc? I’m looking to self host a database server for my multiple pet projects, but I would love to get backups, optimizations and other stuff done well.

I find this YouTube channel[0] has a great number of videos on how to setup Postgres on a regular Linux machine, explaining how configure it and make it work for high availability. It’s easier than you’d think.

[0] https://youtube.com/playlist?list=PLBrWqg4Ny6vVwwrxjgEtJgdre...

afaik:

On promise: Use containers but the data folder should be mounted volume

On cloud/k8s: Just use a managed DB, setting up a DB in k8s is hard because the filesystem

Not the answer you were looking for, but I had been shopping recently for slightly overlapping reasons (I want to ship projects, but they are all smaller MVPs that might run for a while and I didn't want to pay for 1 database service for each one on Render).

I found https://www.thenile.dev/pricing which supports which apparently supports unlimited databases.

When I self host I lean towards SQLite simply because it’s simpler. In-place upgrades, trivial backups via Litestream, etc.

Postgres major version upgrades are the main reason I don’t self host it, though maybe I should rethink my position on that!

For backups, pg_dump is good and simple for starting out.

For tuning, postgresqlco.nf[1] is great.

[1] https://postgresqlco.nf/tuning-guide

Having just spent the better part of two weeks integrating Apache Age for Graph data, just to realize the project is stale and a mess, don’t take this list on face value.

Now hoping for better results with DGraph, but it seems that graph databases are living a precarious existence.

Haaaa same here for apache age. Can you elaborate a little please

The original sponsor of the project just withdrew all resources, the state of the existing codebase is far from mature, the client I tried (python) was really shaky and the lidt goes on. As for the precarious life of graph database companies. DGraph also went though being sold recently, and OrientDB that I also liked, as acquired by SAP, only to be abandoned. Neo4j has stood its time, but the licensing doesn’t fit our needs.

What did you decide on in the end ?

I wonder what's the catch with Dgraph? Why not chose it above Neo4j? I'm asking because the graph db projects I've been involved in has all used Neo4j and it would be nice to know of a good alternative.

I want it to be as free as possible, neo4j only let’s you run a single database in non-Enterprise mode. We are building a consumer product, where the database is embedded and not centralized in a cloud, so my focus is perhaps different from most. Both neo4j and dgraph comes with additional non-compete clauses, but DGraph’s work for our use case.

Subjectively I’d prefer neo4j. I have been following the company since its inception.

Came here to say this. Last time I checked, Apache Age was wildly inferior to Neo4j. So technically, it does exist and has right to be on the list, but I wouldn't recommend it for serious workloads.

If I may ask, what type of use cases are there for which a graph database is well suited?

I could imagine there to be a few (that I can't think of and haven't seen). I have seen graph databases used where they didn't make sense though.

Ours is speculative too. In our case, we a building knowledge graphs for individuals where the schema depends on the person and their needs. We also hope that the relationships between data is where the value lies, rather than in the nodes which consist of ingested data. Arguably, the structure can easily be captured with a NoSQL DB or even a relational database. The precarious life of Graph databases is likely due to this - once a schema is established, why would you choose a proprietary datastore?

This is a lovely list, thank you. But what's really missing is multi master and high availability. I'm glad to see that partitioning via sharding is covered.

IMHO the true limitations of RDBMS are not about usage, but scaling: Multi master across simple zones, High availability, Partitioning.

(IMHO it comes from ACID compliance, so I don't know if it's even solveable natively)

> IMHO it comes from ACID compliance.

That was the whole marketing spiel of Cassandra DB in 2012+ and the source of the CAP theorem

You can get HA via leader election through Patroni.

https://github.com/patroni/patroni

I like this.

I strongly believe that picking some tech stack you know when you are starting out is always the right decision, until it's not. Only then do you pick a different solution.

Better to move fast leveraging what you know, until you need something else.

Thanks for the gist mention!: https://gist.github.com/cpursley/c8fb81fe8a7e5df038158bdfe0f...

I've been running my project as a business for almost four years now and I think my Postgres DB is the only thing that I haven't changed.

Easily the most stable part of the stack (100% uptime on RDS since 2021/02/01).

How do you handle minor upgrades with 100% availability?

It has built-in replicas for upgrades and general availability (for a price)

Anyone experienced with postgres full text search?

I want to get something simple setup but couldn't get it to work.

I want to match substrings like "/r/chatgpt" (sub reddits) in url links, but couldn't get it to match.

Tried a few types of queries like phrase, plain, default, simple, english. All have some weird issues, either not matching special characters, or not matching substrings (partial match). Also I'm somewhat limited on the syntax side by what can be done with drizzle ORM.

> I want to match substrings like "/r/chatgpt" (sub reddits) in url links, but couldn't get it to match.

This likely means tokenization striping out special characters. Try ngram search methods. They should work out of box.

I fully agree. Postgres has solved many of the problems that many are re-solving with GenAI related databases.

With txtai (https://github.com/neuml/txtai), I've went all in with Postgres + pgvector. Projects can start small with a SQLite backend then switch the persistence to Postgres. With this, you get all the years of battle-tested production experience from Postgres built-in for free.

I love Postgres but wouldn't want to use it for everyting.

Taking graph databases, for example, Postgres (via Apache AGE) stores graph data in relational tables with O(log(n)) index lookups and O(k ⋅ log(n)) traversal*

Whereas a true graph database like Neo4J stores graph data in adjacency lists, which means O(1) index lookups and O() traversal.

That's a massive difference in traversal complexity for most graphs.

*k is the degree of the node (number of edges connected to a node).

Typo: Neo4j: O(k) traversal

What about binary blobs? I hate having to store them outside of the database and that you can't just do a dbdump/import to clone a project.

Have you had a look at the bytea[1] type? It allows you to store binary objects up to 1GB and it's dumped transparently just like a string.

[1] https://www.postgresql.org/docs/current/datatype-binary.html

Anyone has advice for backup up a postgres database that is run for personal use? I currently have one that is used by miniflux. I wrote a script for backing it up but it is gonna fail me some day.

``` #!/usr/bin/env sh

set -e

PGUSER=miniflux PGPASSWORD=... pg_dump -F t -h 127.0.0.1 miniflux | gzip > /backups/miniflux_db_temp.tar.gz mv /backups/miniflux_db{_temp,}.tar.gz ```

That works but for a more robust solution you could look at https://pgbackrest.org

I do something similar but pipe a dump of the whole db with pg_dump into restic. Only works well for small to medium sized DBs.

Wonder if someone can write a SQLite for everything article too - would be far more difficult though if considering HA.

The markdown is a bit broken. But I agree with the message.

Created a PR to update some of the vectordb links and also fixed the markdown: https://github.com/Olshansk/postgres_for_everything/pull/1

If anyone has seen a steams implementation in Postgres please let me know. Not just pub/sub, but full implementation of streams with consumer groups would be very interesting to me.

Second best, how would you implement this in Postgres? I’m tempted to give it a go but I haven’t a fully-baked plan yet.

Have a look at diogob/postgres-websockets or supabase/realtime.

While I appreciate postgres flexibility this feels like a mistake. A bit like a person insisting Rust is best and using other languages are unnecessary.

You could, but it is going to force technical compromises. Even tools that can do everything aren't the best at everything.

Postgres administration is such a PITA. I'd rather use sqlite for everything.

sqlite is neat but most of us need a real database with enterprise features

Glad to see pg_render on the list! Here's a demo site rendered entirely using just Postgres(t): https://pgrender.org

Frustratingly, all the demos are blank canvases for me. I use PiHole at home but I checked the query logs and I see no blocking anywhere. Enabled all JS via uMatrix and uBlock Origin but still no dice.

macOS, Safari and Firefox.

Great repo! Thanks!

I'm wondering, any approach for bitemporal dbs, like xtdb?

You definitely don’t need a special database for bitemporal data. Just a datetime and as of data time column, your value column and whatever metadata you want (or a jsonb col for metadata if you want more flexibility at the cost of some speed of filtering by metadata)

How do you find it when you scale it up to every table, every query?

I’m not 100% sure what you mean. Systems I have used that do this don’t generally store each time series in a different table. Normally there’s just one big table for intraday time series and one for daily, with columns being like ts, as_of, series_id, value, metadata or something like that.

It scales just fine depending of course on the usual stuff - load pattern etc. If you want really high scalability you should be using something like clickhouse anyway.

Edit to add: the rationale behind having separate intraday and daily time series tables in those systems is the type of the value and as of timestamps is different (in one its a date, in one its a datetime), and storing dates as datetimes is a rich source of bugs.

I meant 'scale' mostly in the sense of 'complexity' (sorry!). If you only have a small number of tables you need/want this versioning for then the DIY approach is workable, but if you want to apply this across an entire schema then things can get complicated fast.

I have only seen bitemporality being useful in two contexts. 1 is a timeseries store which as I say usually 2 tables is all you need. Secondly is an EAV (entity/attribute/value store) which is just one table. So again entirely manageable. I’ve seen it work just fine with 10s of thousands of logical timeseries and billions of ticks or entities up to the millions without much of an issue. You definitely don’t need a special database and if anything the special database would probably scale worse than normal databases of the kind I’ve mentioned.

An EAV table is usually a symptom of a wider set of issues with schema management, and in contrast, most people I've spoken to who have implemented their own EAV tables on top of a regular SQL database have ended up regretting it because the approach is too hard to scale and maintain. In your experience, was the EAV model limited to a subset of the overall schema?

I agree a special database shouldn't be necessary at all, and instead, convenient syntax for immutable DML and temporal support should be built into Postgres already. But short of a miracle it will probably take a new ('special') database in order for Postgres to evolve in response. Therefore, in the meantime, we believe there's a gap in the market for organisations who value the 'safety' (foolproof complexity reduction) that native bitemporality in a database can offer above the raw query performance offered by existing update-in-place databases: https://xtdb.com/blog/but-bitemporality-always-introduces-co...

s/postgres/sqlite/g

One of the problems I have with this is that I wonder if this doesn't majorly impact blue/green upgrades and (actual cluster, not schema) migrations.

This idea that Postgres should be used for everything really need to die in a professional context.

I was appointed in a company of 10 dev that did just that. All backend code was PostgreSQL functions, event queue was using Postgres, security was done with rls, frontend was using posgtraphile using graphql to expose these functions, triggers were being used to validate information on insert/update.

It was a mess. Postgres is a wonderful database, use it as a database. But don't do anything else with it.

Before some people come and say "things were not done the right way, people didn't know what they were doing". The dev were all fan of Postgres contributing to the projects around, there was a big review culture so people were really trying to the best.

The queue system was locking all the time between concurrent requests => so queue system with postgres works for a pet project

All the requests were 3 or 4 times longer due to fact that you have to check the rls on each row. We have also all pour API migrated now and each time the sql duration decrease by that factor ( and it is the exact same sql request ). And the db was locking all the time because of that as it feels likes rls breaks the deadlock detection Postgres algorithm

SQL is super verbose a language, you spend your time repeating the same line of code , it makes basic function about 100 lines long when they are 4-5 lines in nodes js

It is impossible to log things inside these functions to have to make sure things will work and if it doesn't you have no way to know where the code did go through

You can't make external API call, so you have to use a queue system to make any basic things there

There are not real lib , so everything need to be reimplemeted

It is absolutely not performant to code inside the db, you can't do a map so you O(n2) code all the time

API were needed for the external world , so there was actually another service in front of the database for some case and a lot of logic were reimplemeted inside it

There was a downtime at each deployment as we had to remove all the rls and recreate them ( despite the fact that all code was in insert if not update clauses) it worked at the beginning but at some point in time it stopped working and there was no way to find why, so drop all rls and recreate them

It is impossible to hire dev that wants to work on that stack and be business oriented , you would attract only purely tech people that care only about doing there own technical stuff

We are almost out of it now after 1 year of migration work and I don't see anything positive about this Postgres do everything culture compared to a regular node js + Postgres as a database + sqs stack

So to conclude, as a pet project it can be great to use Postgres like that, in a professional context you are going to kill the company with this technical choice

I agree. Postgres as the only piece of your data-layer? Yes. Postgres as your application and business-logic layer? No, thanks.

Has anyone here run Firebird in production? It seems simpler (from an ops / sysadmin point of view).

Great repo. I love Postgres too. Use it for everything until there's an actual bottleneck

Postgres, ClickHouse and NATS for everything

You use NATS as opposed to Kafka, I presume?

Also, what's Clickhouse for? Logs, observability data?

NATS is great because it has pub/sub, streaming (like Kafka), KVS (like Redis). It is possible to do some of these things in Postgres but really, why?

We use ClickHouse for time series data. Postgres was ok up to low billions of points. Despite trying to use timescale for this purpose it did not fit our use case.

Very valuable, thank you. As time series data (and especially observability data) tends to very quickly explode in volume, I believe planning for tens of billions, if not trillions, of records is worth planning for from the start and it is not over-engineering.

If you don't mind one final question: can you ACK a message in NATS without it being bound to offset that makes it impossible to _not_ ACK a message without ruining the ACKs of the previous messages?

To clarify: I often found myself in situations when I was fetching batches of stuff from Kafka, say, 50 at a time, and then hand them off to 50 parallel agents to process. However, f.ex. messages 17, 31 and 47 failed processing and I could not not ACK them as that would not allow us to ACK those that succeeded before. So I ended up pushing them to another Kafka queue / topic that specifically deals with retries. That's IMO a hack, as most apps out there surely don't need the monstrous speed that Kafka can provide. I am OK with something (not much) slower where I have the freedom to ACK or not-ACK any particular event/message regardless of its position.

Does NATS allow for it?

Perhaps I misunderstand, but if you have 50 parallel agents, why not just have each pull messages, process them and ACK when complete? The part I don't understand is the pre-fetch. Note that NATS is much more flexible than Kafka however, so more likely to fit more uses cases (even just for streaming).

For the first question, I'd definitely recommend using ClickHouse for 10B - 1T points.

I mean the following (let me simplify it). You pull stuff from Kafka in batches of 4 and immediately send each message to a parallel worker, first parsing them in-thread and sequentially and these are the results of the parsing:

1. ok

2. error

3. ok

4. ok

I cannot not-ACK message#2 because that means message#1 is not ACK-ed as well.

Does NATS solve this? F.ex. can I get a reference to each message in my parallel workers for them to also say "I am not ACK-ing this because I failed processing it, let the next batch include it again"?

Yes. This will work fine. Each message is ACK-ed.

Thanks. I'm asking because in Kafka if you ACK a message at offset 15 then all messages from 1 to 14 are ACK-ed as well. You can't just say "ACK all from 1 to 15 except 9".

But if NATS supports that use case then great, I'll migrate to it for that reason alone.

Yes, one of the many differences (advantages) of NATS JetStream over Kafka: with NATS you can ack explicitly each message individually, and even better if you set your stream to be in 'work-queue' mode it will also automatically (and atomically) delete the ack'd message from the stream (i.e. like a 'proper' queue) another difference with Kafka where you can't delete individual messages in the middle of a stream (only trim the tail end).

You can also 'negative ack' messages, specify a back-off period before the message is re-delivered (because NATS automatically re-delivers un-acked (or nacked) messages) when you can't temporarily process it, or 'term' a message (don't try to re-deliver it, e.g. because the payload is bad), or even 'ask for more time before needing to ack the message (if you are temporarily too slow at processing the message).

Ohhh, this is an awesomely informative and concrete message! Extremely useful, thank you!

I like everything about this: the ability to NACK individual messages, the specifying of a backoff period, _and_ to just discard a message f.ex. if you really cannot do anything about it. Super nice. I am grateful.

Is there a local-first postgres solution?

What do you mean local first? Postgres is an executable that you can run on your machine if you wish

I mean storing data in your https://localfirstweb.dev . Sqlite allows that so you can work fully in our client (browser/mobile) without internet potentially and then later sync (rqlite, pouchdb, etc for instance). If I get to do 'everything' with postgres, I need that too, so I was wondering if it exists.

https://electric-sql.com/ does this, running a stripped-down version of postgres on the client.

https://www.powersync.com/ is a similar approach, but using sqlite on the client.

https://zero.rocicorp.dev/ also deserves a mention, but I believe it’s more of a read cache with writes going directly to the server. I’m not sure if it supports SQL client-side or has its own ORM.

Postgres cult strikes back.

TL;DR from many comments: just don’t do it! I’m glad this is becoming mainstream, and people are realizing that Postgres for everything doesn’t work. Don’t get me wrong—I’m a huge Postgres proponent and have spent 10 years helping customers implement it. However, I’m a strong believer in using Postgres for what it’s designed for in first-place.

Postgres was designed as a row-based OLTP database, with over 30 years of effort dedicated to making it robust for that use case.I know there are many extensions attempting to make Postgres support other use cases, such as analytics, queues, and more. Keep in mind that these extensions are relatively recent and aim to retrofit new capabilities onto a database primarily designed for transactional workloads. It’s like adding an F1 car engine to a Toyota Camry — will that work?

Extensions also have many issues—they are not fully Postgres-compatible. In Citus, for example, we added support for the COPY command four years into the company, and chasing SQL coverage was a daily challenge for 10 years. Being unable to use the full capabilities of Postgres and having to work around numerous unsupported features defeats the purpose of being a Postgres extension.

On the other hand, you have purpose-built alternatives like ClickHouse and Snowflake for analytics, Redis for caching, and Kafka for queues. These technologies have benefited from decades of development, laser-focused on supporting specific use cases. As a result, they are robust and highly efficient for their intended purposes.

I often hear that these Postgres extensions are expanding the boundaries of what Postgres can do. While I partly agree, I also question the extent to which these boundaries are truly being expanded. In this era of AI, where data is growing exponentially, handling scale is critical for any technology. These boundaries will likely be broken very quickly.

Take queues as an example: you have a purpose-built technology like Kafka or a Postgres extension that supports queues. For an early-stage startup, adopting a less optimized Postgres-based solution may (not a guarantee) save a few weeks of initial CapEx costs compared to using an optimized solution like Kafka. However, 6 to 12 months later, you may find yourself back to square one when the Postgres-based queue fails to scale. At that point, migrating to a purpose-built technology becomes an arduous task—your system has grown, and now it may take months of effort and a larger team to make the switch.

Ultimately, this approach can cost more time and money than starting with a purpose-built solution from the beginning, which might have only required a few extra weeks of CapEx. I’ve seen this firsthand at Citus, where customers like Cloudflare and Heap eventually migrated to purpose-built databases like ClickHouse and SingleStore respectively. While these migrations happened a few years later, times have changed — data grows faster now, and the need for a purpose-built database arises much sooner. It’s also worth noting that Citus was an incredible piece of technology that required years of development before it could start making a real impact.

TL;DR: Please think carefully before choosing the right technology as you scale. Cramming everything into Postgres might not be the best approach for scaling your business.

You might be right that Postgres isn't for every use, but I think we should enhance its database abilities with extensions. Not everything is set in stone, and every product has its own worth. The world isn't just black or white. Postgres' capabilities in transaction processing (TP) has improved over 30 years, Much of its code logic is designed based on previous data volumes and data models, and it needs to keep pace with the times. So, adding analytical, AI, and distributed computing features through extension is really useful.

Broken markdown.

- the entire repository is 1 file

- the file is titled "read me"

- but you didn't read it (it's not proofread)

why do you want me to read something you did not read?

Reply on news.ycombinator.com