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

PostgreSQL 17

postgresql.org

Post reply on HN
Print view View original

61–70 of 153 posts

Re: PostgreSQL 17

#61

Post by setr »

kragen wrote: post #23
systems wrote: post #14

Earlier quoted context omitted.

Well, from one VERY expensive vendor, to another considerably less expensive vendor Also, MSSQL have few things going for it, and surprisingly no one seem to be even trying to catch up - Their BI Stacks (PowerBI, SSAS) - Their Database Development (SDK) ( https://learn.microsoft.com/en-us/sql/tools/sql-database-projects/sql-database-projects?view=sql-server-ver16 ) The MSSQL BI stack is unmatched , SSAS is the top st…

does 'bi' mean 'olap'? because the literal expansion 'business intelligence' (or the explanation in the Wikipedia article) is hard to interpret as something that makes sense in contexts like this where you're apparently talking about features of software rather than social practices. the reference to 'bi cubes' makes me think that maybe it means 'olap'?

bi tooling is usually report/analysis builders, intended to be consumed by.. business users. More importantly, they're usually also meant to be used by business users to build their own reports in the first place -- although in practice it often ends up going back to IT to maintain/update anyways. Basically anything that competes with excel pivot tables is a bi tool.

OLAP/cubes usually underpins bi tooling, since the usecase is almost entirely across-the-board aggregations.

Re: PostgreSQL 17

#62

Post by darth_avocado »

ellisv wrote: post #36
kiwicopple wrote: post #4

Another amazing release, congrats to all the contributors. There are simply too many things to call out - just a few highlights: Massive improvements to vacuum operations: "PostgreSQL 17 introduces a new internal memory structure for vacuum that consumes up to 20x less memory." Much needed features for backups: "pg_basebackup, the backup utility included in PostgreSQL, now supports incremental backups and adds the pg…

> I'm a huge fan of FDW's Do you have any recommendations on how to manage credentials for `CREATE USER MAPPING ` within the context of cloud hosted dbs?

If your company doesn't have an internal tool for storing credentials, you can always store them in the cloud provider's secrets management tool. E.g. Secrets Manager or Secure String in Parameter Store on AWS. Your CI/CD pipeline can pull the secrets from there.

Re: PostgreSQL 17

#63

Post by dewey »

clarkbw wrote: post #42
clarkbw wrote: post #39

Some awesome quality-of-life improvements here as well. The random function now takes min, max parameters SELECT random(1, 10) AS random_number;

It's never available on homebrew the same day so we all worked hard to make it available the same day on Neon. If you want to try out the JSON_TABLE and MERGE RETURNING features you can spin up a free instance quickly. https://neon.tech/blog/postgres-17 (note that not all extensions are available yet, that takes some time still)

How is your hosted managed Postgres platform an alternative to Homebrew (Installing PG on your local Mac)? There's also many Homebrew formulas that have PG 17 already like (https://github.com/petere/homebrew-postgresql/commit/2faf438...).

Re: PostgreSQL 17

#64

Post by lpapez »

Amazing release, Postgres is a gift that keeps on giving.

I hope to one day see Incremental View Maintenance extension (IVM) be turned into a first class feature, it's the only thing I need regularly which isn't immediately there!

Re: PostgreSQL 17

#65

Post by koolba »

pphysch wrote: post #48
systems wrote: post #14

Earlier quoted context omitted.

Well, from one VERY expensive vendor, to another considerably less expensive vendor Also, MSSQL have few things going for it, and surprisingly no one seem to be even trying to catch up - Their BI Stacks (PowerBI, SSAS) - Their Database Development (SDK) ( https://learn.microsoft.com/en-us/sql/tools/sql-database-projects/sql-database-projects?view=sql-server-ver16 ) The MSSQL BI stack is unmatched , SSAS is the top st…

> And finally the GEM that is database projects, you can program your DB changes declaratively, there is nothing like this in the market and again, no one is even trying Automatic migration tools have essentially been doing this for a while (e.g. Django migrations). But I agree it would be nice if Postgres had better support built in.

Database migrations to arrive at a target state are an interesting beast. It’s not just the destination that matters, it’s how you get there.

The naive approach of applying whatever DDL you need to turn database schema A into B is not necessarily the one anybody would actually want to run, and in larger deployments it’s most definitely not the one you’d want to run.

Intermediate availability and data migration go well beyond simply adding tables or columns. They’re highly application specific.

The main improvements in this space are making existing operations CONCURRENT (e.g., like index creation) and minimizing overall lock times for operations that cannot (e.g., adding columns with default values).

Re: PostgreSQL 17

#67

Post by nikita »

A number of features stood out to me in this release:

1. Chipping away more at vacuum. Fundamentally Postgres doesn't have undo log and therefore has to have vacuum. It's a trade-off of fast recovery vs well.. having to vacuum. The unfortunate part about vacuum is that it adds load to the system exactly when the system needs all the resources. I hope one day people stop knowing that vacuum exists, we are one step closer, but not there.

2. Performance gets better and not worse. Mark Callaghan blogs about MySQL and Postgres performance changes over time and MySQL keep regressing performance while Postgres keeps improving.

https://x.com/MarkCallaghanDB https://smalldatum.blogspot.com/

3. JSON. Postgres keep improving QOL for the interop with JS and TS.

4. Logical replication is becoming a super robust way of moving data in and out. This is very useful when you move data from one instance to another especially if version numbers don't match. Recently we have been using it to move at the speed of 1Gb/s

5. Optimizer. The better the optimizer the less you think about the optimizer. According to the research community SQL Server has the best optimizer. It's very encouraging that every release PG Optimizer gets better.

Re: PostgreSQL 17

#68

Post by ilkhan4 »

AlfeG wrote: post #53
kjax wrote: post #41

Earlier quoted context omitted.

After scanning the Azure and AWS pricing pages and calculators for comparably speced Azure Manged SQL and Amazon RDS for PostgreSQL instances, AWS's RDS comes out at about 1/3 the price of Azure Managed SQL. This matches my experience with deployments on AWS and Azure. Both are managed db instances, but Azure's is consistently more expensive for comparable offerings on AWS or GCP.

Is there any analog for Azure SQL Elastic Pools for Postgres anywhere? We pay in total something around 600 bucks to manage around 250 databases in MSSQL servers (with failover for prod databases, DTU based model) We pay for log analytics more then we pay for Sql Servers. Those Elastic Pools is a blocker for us on the way to migrate to Postgres from MSSQL...

It's been a while since I've looked at elastic pools in Azure, but maybe Neon (https://neon.tech/) or recently Nile (https://www.thenile.dev/) might work in terms of spinning up a bunch of separate logical DBs with shared compute/storage.

Re: PostgreSQL 17

#69

Post by nikita »

ilkhan4 wrote: post #68
AlfeG wrote: post #53

Earlier quoted context omitted.

Is there any analog for Azure SQL Elastic Pools for Postgres anywhere? We pay in total something around 600 bucks to manage around 250 databases in MSSQL servers (with failover for prod databases, DTU based model) We pay for log analytics more then we pay for Sql Servers. Those Elastic Pools is a blocker for us on the way to migrate to Postgres from MSSQL...

It's been a while since I've looked at elastic pools in Azure, but maybe Neon ( https://neon.tech/ ) or recently Nile ( https://www.thenile.dev/ ) might work in terms of spinning up a bunch of separate logical DBs with shared compute/storage.

(neon ceo). We have lots of examples of this. Here is one with Retool. https://neon.tech/blog/how-retool-uses-retool-and-the-neon-a...

Re: PostgreSQL 17

#70

Post by abyesilyurt »

jackschultz wrote: post #16

Very cool with the JSON_TABLE. The style of putting json response (from API, created from scraping, ect.) into jsonb column and then writing a view on top to parse / flatten is something I've been doing for a few years now. I've found it really great to put the json into a table, somewhere safe, and then do the parsing rather than dealing with possible errors on the scripting language side. I haven't seen this style…

> putting json response (from API, created from scraping, ect.) into jsonb column and then writing a view on top to parse

That’s a very good idea!

Post reply on HN