161–170 of 326 posts
Also, getting up and running with an investor demo ASAP with zero technical fuss, because you have a startup idea but you're broke and can't pay your next month's rent unless you either (A) finish this demo and get that investor money next week, or (B) quit working on your idea and take the Google offer. (Yes, I've actually been there.)
Earlier quoted context omitted.
3) Fine tuned control over query plans if I want it. I feel like when most people say this, what they really want is a better query planner. The optimum query plan depends on a lot of dynamically changing factors: system load, free RAM, and of course the data in the tables themselves. Any hints we give the query planner are going to help at certain times and be somewhere between "suboptimial" and "disasterous" at mos…
> The optimum query plan depends on a lot of dynamically changing factors Except … I’m part of a large organization with a very thorough incident post-mortem process and I’ve read a lot of analyses that end up blamed on the dreaded “query plan flip.” This ends up being an unplanned change that has an unexpected perf cost (and no real “rollback”) and causes an outage. The lesson I’ve seen learned over and over is to h…
Hasn't happened often, but the few occasions have of course been at the worst possible time.
I'm missing something as easy to deploy as SQLite, but without its performance problems (like the stop-the-world write lock). Basically, I'd love an embeddable Postgres with a single-file storage.
Subscriptions. Databases like Firebase will automatically push changes to query results down to clients. You can add this to Postgres with tools like Hasura, but it's poll based and not very efficient. It's a super-useful feature for keeping UIs in sync with database state.
Disclosure (Supabase cofounder)
One thing I find really interesting from Redis that I would love in a relational database is the concept of ‘blocking’ queries, which block until they get a result. For example: https://redis.io/commands/BLPOP If I could do something like: Select id, msg From job Where id > 1224 And have that query block until there actually was a job with an id > 1224, it would open up some interesting use cases.
A "niced" query blocks (and its working set may even be "swapped out") as long as: - the read buffer does not contain anything pertinent for it, - there are "too much" pending I/O requests ( submitting an I/O request useful for this query would slow down other less-niced queries)
The "niceness" of a query may be dynamically modified by an authorized user.
Bonus: - any role is associated to a minimal level of niceness (GRANT'able) - the underlying logic interacts with the OS corresponding logic in order to take into account all I/Os (this is especially useful if PG runs on a non-dedicated server)
Earlier quoted context omitted.
CDB was great until we started doing table creation on the minute and hundreds of inserts on those tables. When we tried to drop tables on a schedule, CDB never could catch up and would generally just crash (3 nodes). I really don't like the magic you have to do with CDB for things that your commodity DBs can be expected do.
Can you tell me more about the use case where you'd create a new table every minute?
Earlier quoted context omitted.
Can you tell me more about the use case where you'd create a new table every minute?
Not OP but for analytics use cases it is common to create new tables for each minute, hour, day or whatever granularity you collect data in. This makes it easier to aggregate later, you don't end up with extremely big tables, you can drop a subset of the data without affecting the performance of the table currently being written to etc..
Earlier quoted context omitted.
To be fair, while I kind of agree the system should be able to handle it regardless, 10,000s of tables sounds outside the realm of 99.99% of all use cases.
You might be surprised to learn how common the "store the results of the user's query into a temporary table" pattern is.
The implementation effectively just creates real tables that get cleaned up; they have all the same durability and distributed state despite not being accessible outside of the current session.
Getting something done here turned out to be a big deal in order to get ORM and driver tests to run, which is extremely high value.
A better implementation would just store the data locally and not involve any of the distributed infrastructure. If we did that, then temp tables wouldn't run into the other schema scalability bottlenecks I'm raising above.
Earlier quoted context omitted.
It always rubs me the wrong way - all those paxos/raft approaches (which are great, but...) simply elect the leader to pick writes. In that sense there is no distribution of computation at all. It's still single target that has to cruch through updates. Replication is just for reads. Are we going to have something better anytime soon? Like real distribution, when you add more servers writes distribute as well?
CAP theorem. Choose two, it's a fundamental limitation of scaling a database.
Even better, sometimes you can change which guarantee you need.
We can do better then "pick two"
1. High performance read/write of Scylla/Cassandra with high availability[1]. It has some limitations for OLTP workloads and require careful planning. Postgres without Citus is not really HA and even then companies would often invent custom sharding/cluster management system. 2. Powerful graph query language like Cypher. It might not perform well in real life, but my personal experience left me amazed[2]. There is a…
Postgres comes with the building blocks for both sharding and HA out of the box, and they're extensively discussed in the docs. You don't need proprietary addons other than as pure convenience.