Great article! We also tried lots of databases in the past at SWCode, but then ended up using Postgres for almost all our usecases. There really must be a good argument for using something else which can‘t be done with some Postgres extension.
Postgres is eating the database world
21–30 of 147 posts
Re: Postgres is eating the database world
#22Postgres is simply the best. One thing I would like however is the ability to have control over the query planner for specific tasks. There is a dark art to influencing the query planner, but essentially it is unpredictable, and postgres can get it consistently wrong in certain scenarios. If you could just enable a special query mode that gives you absolute control over the QP for that query, it would solve a major p…
The problem is not the query planner per se. There is a much more subtle problem and it is related to how you have created the query in the join structure.
For many queries, the order in which you specify the joins doesn't really matter. But there are a number of classes where the join order dramatically affects how fast the query can actually run and nothing the query planner does will change this.
I came across this problem around 30 years ago. By accident, I discovered what the problem cause was - the order of the joins. The original query was built and took 30 - 40 minutes to run. I deleted particular joins to see what intermediate results were. In reestablishing the joins, the query time went to down to a couple of seconds.
I was able to establish that the order of joins in this particular case was generating a Cartesian product of the original base records. By judicious reordering of the joins, this Cartesian product was avoided.
If you are aware of this kind of problem, you can solve it faster than any query planner ever could.
Re: Postgres is eating the database world
#23Postgres is simply the best. One thing I would like however is the ability to have control over the query planner for specific tasks. There is a dark art to influencing the query planner, but essentially it is unpredictable, and postgres can get it consistently wrong in certain scenarios. If you could just enable a special query mode that gives you absolute control over the QP for that query, it would solve a major p…
The problem is not the query planner per se. There is a much more subtle problem and it is related to how you have created the query in the join structure. For many queries, the order in which you specify the joins doesn't really matter. But there are a number of classes where the join order dramatically affects how fast the query can actually run and nothing the query planner does will change this. I came across thi…
Would love to see a practical example of this.
Another thing to consider is table fragmentation. Fragmentation > bad row count estimation > bad query plan.
Re: Postgres is eating the database world
#24Alright I'm convinced, I'm using postgres for my next project. Anyone have any experiences with it and Entity Framework Core?
I am using it in a hobby code first project. Had no issues with it, but my data model is admittedly not complex and the the data is small in size.
Re: Postgres is eating the database world
#25Earlier quoted context omitted.
I haven’t used Postgres but is this the issue you’re talking about? https://github.com/launchbadge/sqlx/pull/1539
No this is a fundamental concept in postgres. If you do EXPLAIN ANALYZE on a query, you get the query plan, which is influenced by the query, indexes, table structure, etc. But the QP may decide to do a silly thing like a sequential scan where a better path exists, and adding an index to avoid the scan would be cost prohibitive. So if you could just override the QP and say "Use this index and do this type of sort and…
Nobody does actually. Postgres not having deterministic query plans is a big pain and a good reason not to use it. The same query may use different query plans depending on the estimated number of affected rows, very frustrating.
Re: Postgres is eating the database world
#26Is there an extension that can make it compete with TigerBeetle for transaction processing speed?
Re: Postgres is eating the database world
#27Postgres is such a great tool. The feature I'd love to see added that has been kicking around the mailing list for ages now would be incremental view maintenance. Being able to keep moderately complex analysis workloads fresh in realtime would be such a boon.
Materialized views with pg_cron to refresh them? (Or even just cron and your usual interface, if you don't want to install something extra)
Re: Postgres is eating the database world
#28Earlier quoted context omitted.
No this is a fundamental concept in postgres. If you do EXPLAIN ANALYZE on a query, you get the query plan, which is influenced by the query, indexes, table structure, etc. But the QP may decide to do a silly thing like a sequential scan where a better path exists, and adding an index to avoid the scan would be cost prohibitive. So if you could just override the QP and say "Use this index and do this type of sort and…
I wonder if for non-trivial use cases we should just go back to imperative programming.
The way I see it traditional databases are frameworks, and we need to switch to something more like libraries - use the high-level interface when we need it, but be able to dig underneath. Postgres has taken some small steps in this direction with e.g. making the parser available as a separate library; some newer systems (e.g. distributed-first datastores that combine LevelDB with some higher-level layer) go further.
Re: Postgres is eating the database world
#29Having used Postgres for many projects, yet never having used any of the other tools in the ecosystem, I'm surprised by how many tools there are!
How does one go about finding paying customers when developing a new database tool? How does one figure out the size of the market, and pricing structure?
Re: Postgres is eating the database world
#30Alright I'm convinced, I'm using postgres for my next project. Anyone have any experiences with it and Entity Framework Core?
Coming from the MS ecosystem i can definitely vouch for EF core, but I am more of a Dapper person myself.
But I don't use PG because my needs are not so heavy nor have I the data sizes or advanced features that makes PG a more reliable choice over sqllite.