Lots of people are citing cutting-edge bells and whistles for advanced query plans and data types, but after working at $BIGCO and seeing what exists there (and has existed for years), I get frustrated by the lack of ergonomic and operational essentials in the public-cloud options.
1. Horizontal scaling. PG can do sharding or replication, but that's not the same thing. Manual resharding is something that, once you've experienced it, you don't want to do it ever again, especially when things like Spanner/Cockroach exist. Database-level liquid sharding is such a dramatically superior solution that it makes any system that depends on an enumerated set of master instances seem completely obsolete.
2. Strong commit timestamps, again a la Spanner/Cockroach. Globally-ordered commit times and read snapshots aren't something you'll need in every schema, but when you do, they're magical - suddenly, you can use your OLTP system for strong lease assignment (conditioning other transactions on lease validity), you can construct arbitrary application-level transaction semantics (non-transactional read followed by transactional compare-and-swap is pretty powerful in some scenarios), and all sorts of other things.
3. Interface abstraction. Databases that ship language-support drivers should also ship a fake in-memory implementation for testing purposes that supports the same interfaces and options. There's no reason why I should have to start a subprocess and emulate a network connection to tell whether a query functions properly or not, or whether I've set isolation properties correctly for a pipeline.
4. Declarative schemas, and a well-defined update process (that isn't human-written DDL), are essential at any sort of organizational scale. Every time I see an "ALTER TABLE" statement checked into a repository, I tear my hair out.
5. Queue support, where message publishing/consumption is transactional with other read/write operations. You can always build a queue with a time-keyed table, but it's kind of a pain in the ass to get all the edge cases right. It's much better as a first-class concept supporting automatic sharding, retries, batching, and bounded-out-of-order delivery with a global cursor.
I think the importance of horizontal scaling is overhyped. 99% of PostgreSQL applications are at a size where a single machine can easily handle the workload.
To enable horizontal scaling, you need to make so many tradeoffs that I don't think it's worth it for most applications.
I see people responding to this thread with examples of high-volume workflows that use a small number of machines for the database layer.
I think those examples are missing the point. One of the examples uses a Redis cluster, which is a sign that the DB alone can't support the workflow.
Another example is using a database for certificate issuance. Which I suspect is using one table or multiple that can be shard and it doesn't suffer from lock contention or joints of multiple tables.
I wouldn't be brave enough to count the number of cases where horizontal scaling is needed but I would say it is definitely not zero, especially for read replicas when high availability is needed
I'd be willing to believe 99% of applications, yes - for the same reason that Wordpress can claim to drive some ridiculous percentage of websites - but since the larger use cases tend to come from larger organizations, 99% of developers don't work on systems where one instance suffices. There's a reasonably large number of companies for which that's not true, and there's not very many good "next step" options when you're too big for vertical scaling but too small to fund building your own engine.
From managing databases over the last two decades (starting with Ingres/mSQL), hardware has growing much faster (RAM/CPU/NVMe) than the data needs. I remember the first time we put 2Tb RAM into a machine to handle most analytics in memory.
From my experience TimescaleDB is fast and takes a lot of data in. If you're multi tenant, it's usually easy to shard.
And we do dataware housing on BigQuery, no need to have your own machine and manage the database.
Of course there are people who need unlimited horizontal scaling.
TimescaleDB also offers a good lot of supplementary functions to the PostgeSQL core product to help with time series data analysis... saves a lot of SQL acrobatics!
Generally you don't have your main application database also serve as your data warehouse for lots of reasons.
Generally you offload that to purpose built systems that favor those aforementioned tradeoffs or onto services that run them for you, i.e BigQuery, Snowflake, etc.
Your main application database is unlikely to need sharding unless you really do have a phenomenal amount of customers or you need regional sharding to meet legal requirements about data sovereignty for example.
>Isn't this impossible because some schema changes require data migration?
In the most general case, sure - although there are workarounds for some specific cases (e.g., including previously-known-as names in the declarative schema to allow automatically planning renames). But 99% of the time, you're adding and removing tables and columns in a way that's very well defined. This is one of those areas where the best solution is to legislate away the hard problems - a tool that covers 98% of schema changes automatically (the provably safe ones), and then fails/requires a human to approve the last 2% is still dramatically better than having humans manually write and sequence change plans all the time.
Data migrations will require human effort, but you can sequence the changes and isolate the parts that need different kinds of work. If you're changing a string to an integer, for instance, you can make it clear in your change history that you (1) add the new column, (2) start dual writes, (3) backfill, (4) move readers to the new column, (5) stop the dual write, (6) drop the old column. You can do that with checked-in migration code, but think about what you end up with at the end - with imperative migrations, you have the clutter from all of that history; with declarative schema definitions, you just have the final state, just like how it works for code.
Declarative schemas also usually come with nice ancillary properties - for instance, they can give you automatic rollbacks, because you can pack up the whole schema definition as a static artifact.
>Why not use a dedicated and feature rich queue such as Rabbit MQ or, if you want to get really fancy, Kafka?
Atomicity. It's really, really powerful to be able to write a row and send or receive a message as a single exactly-once transaction. Imagine a queue of events. The writer writes the event data, and sends a message as a notification that it exists; the consumer will keep some sort of aggregate (count, filtered total, etc.). With a separate queueing system, you have to explicitly manage the timing and error cases bridging different atomicity domains, usually reimplementing some sort of idempotency log on your own. If it's all in one place, you just write a transaction that reads the old value, consumes the queue message, and writes a new value.
I'm doing a project perhaps relevant to this. Talk of declarative schemas (or what you imply they offer) is very interesting and I'd like to know more but I can't find anything relevant (just magento and sqlalchemy). Indeed, searching for <<<"Declarative schemas" "sql">>> in google gets this very thread on the first results page.
Any links to clear, actionable and reasonably comprehensive examples of these would be most helpful. Obviously abstract statements of the required semantics are also needed, but I also need to see what actual code would look like.
Magento (the new XML-based version, not the old create/update/revert-script version) gives a lot of these properties. Making it part of the database instead of a third-party tool would be better, though - it lets you cover more features, and with deeper integration you can get transactional updates and history logs.
> Why not use a dedicated and feature rich queue such
> as Rabbit MQ or, if you want to get really fancy, Kafka?
If your queue is in the same database as your data, you can do "ack message/mark job as complete" and "commit result of job to database" in the same transaction. That simplifies a lot of the harder parts of things like materialization pipelines or BI systems.
Wanted to ask you more about the experiences you wrote. Have some similar thoughts as your later points. Do you have a contact method like an email address or something?
Maybe I'm reading it wrong, but that looks like yet another multi-master high-availability system, which is also an important property for a system to have but more or less unrelated tow hat I was talking about.
As far as open-source add-ons for true horizontal scaling, I think [Citus](https://github.com/citusdata/citus) is the most well-known and sophisticated, but I don't have enough experience with it in production to have a particularly strong opinion yet. It might work quite well, but I still fundamentally doubt that it will ever be as good as a system that was designed to support horizontal sharding from the ground up.
> 1. Horizontal scaling. PG can do sharding or replication, but that's not the same thing. Manual resharding is something that, once you've experienced it, you don't want to do it ever again, especially when things like Spanner/Cockroach exist. Database-level liquid sharding is such a dramatically superior solution that it makes any system that depends on an enumerated set of master instances seem completely obsolete.
It feels like the industry has largely failed at this in regards to the traditional RDBMSes. Solutions like TiDB attempt to remedy this in a transparent way, but there are simply too many limitations for it to be a feasible alternative for many projects out there: https://docs.pingcap.com/tidb/stable/mysql-compatibility#uns...
I've only ever seen PostgreSQL/MySQL/MariaDB/Oracle be deployed successfully as single instances in prod that are scaled vertically, because attempts to do otherwise either create issues with performance and resiliency, or make the entire system only have eventual consistency, which is as problematic as it is inevitable when it comes to horizontally scaled and distributed systems.
I don't think that there is a good answer to this, honestly. You will get either horizontal scalability OR data consistency - pick one.
> 3. Interface abstraction. Databases that ship language-support drivers should also ship a fake in-memory implementation for testing purposes that supports the same interfaces and options. There's no reason why I should have to start a subprocess and emulate a network connection to tell whether a query functions properly or not, or whether I've set isolation properties correctly for a pipeline.
That's a good idea, but personally i've seen that almost all abstractions are leaky in one way or another. In the day of containers and highly customizable software that can scale from tens or hundreds of megabytes of memory with a fraction of a CPU core to an entire server with a hundred gigabytes of memory and dozens of CPU cores, it feels somewhat unnecessary to risk these abstractions.
Just make sure that your process for initializing the DB schema and seeding it with data for testing is fully automated and reproducible, and then spin up a disposable database container as a part of your CI process. Either that, or just don't test the actual DB implementation, which i've also seen be done to pretty horrendous results.
Edit: Perhaps my past experience makes me have certain biases - i've also seen manually set up DB instances which eventually leads to there being no good way to test N parallel feature branches, each of which needs a different schema, because that also necessitates N DB instances to be present, half or more of which could in theory break. I don't see any good options apart from containers here, especially when you need extensions which these in-memory implementations wouldn't play nicely with.
It depends on what you're testing. Performance properties and version updates, sure, it's hard to find a substitute for containers. But that's not 99% of testing. If the database has a stable interface (for instance, its dialect of SQL+DDL), it can ship a fake implementation of that interface to use in unit tests or toy examples.
> If the database has a stable interface (for instance, its dialect of SQL+DDL), it can ship a fake implementation of that interface to use in unit tests or toy examples.
Sometimes the interface just has too large of a surface area for it to be simulated properly.
For example, suppose that you have an Oracle stored procedure, which, when given about 20 parameters, goes through a decision tree in a PL/SQL block that spans a few thousands of lines and along the way uses both DBMS_OUTPUT.PUT_LINE, as well as DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS, any number of methods from DBMS_PREDICTIVE_ANALYTICS as well as any number of other packages that are included in the full release: https://docs.oracle.com/en/database/oracle/oracle-database/1... which may also include logic that makes network requests, sends e-mails or even triggers other pieces of code to be executed.
All of that would be almost impossible to fake well enough because of the scope of functionality that's used, especially if methods are not only called, but their output is also checked and the execution depends on this. Should systems be simpler and do less? Probably, but that's not the objective reality in many projects out there.
That said, i agree that it's a good thing to have if you treat your DB as a data store and not much more, which in my opinion is a pretty good and stress free way to go about things overall. But somehow that's also like saying "Just use an ORM to have your system be DB agnostic!" which never actually works out in practice because of implementation details that leak through.
1. Horizontal scaling. PG can do sharding or replication, but that's not the same thing. Manual resharding is something that, once you've experienced it, you don't want to do it ever again, especially when things like Spanner/Cockroach exist. Database-level liquid sharding is such a dramatically superior solution that it makes any system that depends on an enumerated set of master instances seem completely obsolete.
2. Strong commit timestamps, again a la Spanner/Cockroach. Globally-ordered commit times and read snapshots aren't something you'll need in every schema, but when you do, they're magical - suddenly, you can use your OLTP system for strong lease assignment (conditioning other transactions on lease validity), you can construct arbitrary application-level transaction semantics (non-transactional read followed by transactional compare-and-swap is pretty powerful in some scenarios), and all sorts of other things.
3. Interface abstraction. Databases that ship language-support drivers should also ship a fake in-memory implementation for testing purposes that supports the same interfaces and options. There's no reason why I should have to start a subprocess and emulate a network connection to tell whether a query functions properly or not, or whether I've set isolation properties correctly for a pipeline.
4. Declarative schemas, and a well-defined update process (that isn't human-written DDL), are essential at any sort of organizational scale. Every time I see an "ALTER TABLE" statement checked into a repository, I tear my hair out.
5. Queue support, where message publishing/consumption is transactional with other read/write operations. You can always build a queue with a time-keyed table, but it's kind of a pain in the ass to get all the edge cases right. It's much better as a first-class concept supporting automatic sharding, retries, batching, and bounded-out-of-order delivery with a global cursor.