Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Looking Ahead to Postgres 19

snowflake.com

Post reply on HN
Print view View original

121–130 of 138 posts

Re: Looking Ahead to Postgres 19

#121

Post by saila »

khurs wrote: post #90
bob1029 wrote: post #31

Earlier quoted context omitted.

SQLite and MSSQL are my two solutions for relational storage problems. If I am going to use a "free" provider, SQLite is impossible to beat. They cover a majority of use cases today. SQLite starts to fall apart with backup, replication and tooling. If I am on the hook for things like system availability and disaster recovery, I don't have a problem spending money to cover my ass. If I am going to pay any amount of mo…

>I am going all the way. The developer experience around MSSQL is untouchable. SSMS and VS with sql projects runs circles around contemporary entity framework crap. Sprinkle in 3rd party tools from vendors like RedGate and you can replace multi-million dollar consulting packages. Try Postgresql. I was previously SQL Server and the move has been great. SSMS doesn't offer much over alternatives, both PGAdmin and others…

> VS is dying

I don't think this is true at all. If anything, it's the opposite, in that MS has greatly improved recent versions of Visual Studio.

> VSCode is the future

Maybe for some types of projects, but I have both VS and VS Code on my work machine and hardly ever use VS Code. There's just no comparison when working on .NET projects where VS is the clear winner.

Re: Looking Ahead to Postgres 19

#123

Post by sgt »

khurs wrote: post #82
sgt wrote: post #72

Earlier quoted context omitted.

Which document store do you prefer? And do you use it via Postgres using a foreign wrapper?

Two most recent companies: The first was using SQL Server alongside CouchDB, an older company. CRUD Data into SQL, documents into Couch. (used separately, document Url in Couch stored in SQL and brought in by front end) The second, startup with funding, was using Azure SQL Server (and complaining of the cost) and using Azure Cosmo (Key Value NoSQL) alongside it. Same relationship, no link between the two, it's the fr…

I wonder if Postgres might be well coupled with MongoDB via fdw.

Re: Looking Ahead to Postgres 19

#124

Post by colechristensen »

bob1029 wrote: post #83
colechristensen wrote: post #64

Earlier quoted context omitted.

Can you expand on what is better with MSSQL?

One of the biggest advantages is that a lot of people in the business are already comfortable with the ecosystem. I know that from the perspective of HN that SSMS, pgAdmin, DBeaver, DB Browser for SQLite, et. al. are mostly isomorphic, but from the perspective of everyone else in the business, these are substantially different things to think about. Whether the popularity of the MS ecosystem is good or not is a separ…

"It's better because Microsoft makes it and people in the MS Tech Bubble prefer MS things" is not a convincing argument.

It is in fact a detractor because inviting a tool from the MS fandom invites people from the MS fandom who want to change out everything for the Microsoft version based only on who made it.

Re: Looking Ahead to Postgres 19

#125

Post by mickeyp »

sgarland wrote: post #102
mickeyp wrote: post #44

The graph database feature looks interesting, but I wonder... SELECT customer_name FROM GRAPH_TABLE (myshop MATCH (c IS customers)-[IS customer_orders]->(o IS orders WHERE o.ordered_when = current_date) COLUMNS (c.name AS customer_name)); That is _awful_ syntax; it is reminiscent of neo4j, which is surely not a tool anyone serious should copy from outright in 2026. And of course the final thing I am left wondering is…

> [RLS]… planner goes haywire and does per-row-matching Er… yes? It’s called Row-level security; how else are you going to validate that a row passes a policy?

The same way other databases do it. With join mechanics and proper query planning. You're confusing functionality with implementation.

Re: Looking Ahead to Postgres 19

#126

Post by CraigJPerry »

SigmundA wrote: post #110
zbentley wrote: post #80

Earlier quoted context omitted.

> lightweight connection; connection bouncers improve the situation, but you still have an unreasonably high memory footprint per concurrent connection. Pg connections are definitely heavy, but usually on resources other than memory in my experience. If you configure reasonable dirty reclamation and recycling, the memory numbers are often overstated due to Linux tools’ deceptive fork accounting and shared buffers. Of…

Each PG connection being a whole process does not scale like MSSQL that uses a thread per connection which has a max of 32k per instance. There are no need for connection poolers in front of MSSQL although it is normal to pool connections in the client application which may hold hundreds open typically in a web server. This also allows MSSQL to more easily share cached query plans between connections since its just s…

>> Each PG connection being a whole process does not scale like MSSQL that uses a thread per connection

There's no free lunch id think, the PG model is more robust. Unsafe extensions can take down the whole instance in the threaded model, processes contain the blast radius to that connection (also typically easier to debug since this type of issue is thankfully rare, it's also gnarly to get on top of).

Further, on linux (not on windows) a lot of the lines between a thread and a process get blurry (copy on write, shared memory mappings etc). They're both handled very similarly in the kernel, theyre both scheduled using similar machinery.

>> For PG to do plan caching it would need to serialize the plan between processes and that would require some significant work since it was never designed that way.

Is that true? I'm thinking the buffer cache and locks and WAL coordination are just as fast - it's just mmap'd SHM into each process. It's not like every access needs IPC?

Re: Looking Ahead to Postgres 19

#127

Post by foo42 »

I've used postgres on a few projects (via AWS rds) but I've always used it as a pretty vanilla SQL database. Probably the most "fancy" feature I've used being bson columns.

Can anyone recommend good resources for going deeper? (Obviously I could Google, but I appreciate HN recommendations)

Re: Looking Ahead to Postgres 19

#128

Post by pjmlp »

khurs wrote: post #90
bob1029 wrote: post #31

Earlier quoted context omitted.

SQLite and MSSQL are my two solutions for relational storage problems. If I am going to use a "free" provider, SQLite is impossible to beat. They cover a majority of use cases today. SQLite starts to fall apart with backup, replication and tooling. If I am on the hook for things like system availability and disaster recovery, I don't have a problem spending money to cover my ass. If I am going to pay any amount of mo…

>I am going all the way. The developer experience around MSSQL is untouchable. SSMS and VS with sql projects runs circles around contemporary entity framework crap. Sprinkle in 3rd party tools from vendors like RedGate and you can replace multi-million dollar consulting packages. Try Postgresql. I was previously SQL Server and the move has been great. SSMS doesn't offer much over alternatives, both PGAdmin and others…

> SSMS doesn't offer much over alternatives, both PGAdmin and others. VS is dying, VSCode is the future.

Only for UNIX minded folks.

Have you ever debugged stored procedures in PGAdmin, by the way it is still a Electron junk app?

Re: Looking Ahead to Postgres 19

#129

Post by znpy »

kiwicopple wrote: post #101
eatonphil wrote: post #59

Earlier quoted context omitted.

Supabase customers run it (marked alpha). https://supabase.com/blog/orioledb-launch And they continue to work on it. https://supabase.com/blog/orioledb-patent-free

(supabase employee) confirming that we're still working hard on this. aiming to be production-ready this year. benchmarks and compatibility testing are on track

Thank you for chiming in! <3

Re: Looking Ahead to Postgres 19

#130

Post by SigmundA »

CraigJPerry wrote: post #126
SigmundA wrote: post #110

Earlier quoted context omitted.

Each PG connection being a whole process does not scale like MSSQL that uses a thread per connection which has a max of 32k per instance. There are no need for connection poolers in front of MSSQL although it is normal to pool connections in the client application which may hold hundreds open typically in a web server. This also allows MSSQL to more easily share cached query plans between connections since its just s…

>> Each PG connection being a whole process does not scale like MSSQL that uses a thread per connection There's no free lunch id think, the PG model is more robust. Unsafe extensions can take down the whole instance in the threaded model, processes contain the blast radius to that connection (also typically easier to debug since this type of issue is thankfully rare, it's also gnarly to get on top of). Further, on li…

>Unsafe extensions can take down the whole instance in the threaded model, processes contain the blast radius to that connection.

MSSQL has its own task scheduler with the ability to kill etc, its like its own OS internally in that respect but yes there is no memory protection there for unmanaged code.

It doesn't really have unsafe extensions other than it is possible to create extended stored procedures which could do bad things, very frowned upon. If you were concerned about that you would run that database in its own instance (process).

https://learn.microsoft.com/en-us/sql/relational-databases/s...

https://learn.microsoft.com/en-us/sql/t-sql/language-element...

https://learn.microsoft.com/en-us/sql/relational-databases/e...

>I'm thinking the buffer cache and locks and WAL coordination are just as fast - it's just mmap'd SHM into each process.

Plan caches contain pointers to executable code and sometimes jitted code, this cannot be just shared to another process with a different address space and work, this is code not data, to transport between processes it needs to be serialized in a way that is not tied to process memory including things like ASR, certainly doable but since it wasn't designed that way much needs to be changed vs just sharing between threads.

https://en.wikipedia.org/wiki/Position-independent_code

Post reply on HN