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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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 to...
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).
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/
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.
- 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.
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.
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.
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.
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.
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?
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.
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?
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 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://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.
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)
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...
The vast majority of software does not need to be these crazy complex distributed things. Anyways, there's several flavors of Postgres if the need arises.
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.
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.
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.
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).
180 comments
[ 4.6 ms ] story [ 221 ms ] threadI 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.
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.
https://m.youtube.com/playlist?list=PLBrWqg4Ny6vVwwrxjgEtJgd...
Getting up in the morning, seeing an article that references you, bliss!
[0]: https://www.amazingcto.com/postgres-for-everything/
pg fulltext search is very limited and user unfriendly, not a great suggestion here
- 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?
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.
I think that's actually the point of "postgres for everything"?
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.
- 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.
e.g. User management deploy has somehow taken down core payment processing.
Overengineering is a plague amongst SWEs, and almost as dangerous as failing to sell the product in the market.
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.
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.
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.
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.
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.
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.
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.
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:
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 to...
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...
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).
> 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/
- 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.
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.
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.
Now hoping for better results with DGraph, but it seems that graph databases are living a precarious existence.
Subjectively I’d prefer neo4j. I have been following the company since its inception.
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.
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...
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.
And in 30 odd years, everything will be different again, but your company's main data store will not have moved as fast.
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.
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”.
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.
Created a PR to update some of the vectordb links and also fixed the markdown: https://github.com/Olshansk/postgres_for_everything/pull/1
Easily the most stable part of the stack (100% uptime on RDS since 2021/02/01).
I'm wondering, any approach for bitemporal dbs, like xtdb?
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 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...
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.
Check https://apex.oracle.com/ which does that.
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.
We discussed this very thing on supabase
https://github.com/orgs/supabase/discussions/18061#discussio...
When you do find your reading glasses, the links below might be helpful.
https://en.wikipedia.org/wiki/TiDB https://en.wikipedia.org/wiki/YugabyteDB
https://ydb.tech/
https://ydb.tech/
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.
This likely means tokenization striping out special characters. Try ngram search methods. They should work out of box.
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.
[0] https://youtube.com/playlist?list=PLBrWqg4Ny6vVwwrxjgEtJgdre...
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
I found https://www.thenile.dev/pricing which supports which apparently supports unlimited databases.
Postgres major version upgrades are the main reason I don’t self host it, though maybe I should rethink my position on that!
For tuning, postgresqlco.nf[1] is great.
[1] https://postgresqlco.nf/tuning-guide