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

111–120 of 153 posts

Re: PostgreSQL 17

#111

Post by sgarland »

openrisk wrote: post #83

There was LAMP and then MERN and MEAN etc. and then there was Postgres. Its not quite visible yet, but all this progres by postgres (excuse the pun) on making JSON more deeply integrated with relational principles will surely at some point enable a new paradigm, at least for full stack web frameworks?

We’re already in a new paradigm, one in which web devs are abandoning referential integrity guarantees in favor of not having to think about data modeling.

I’d say they’re then surprised when their DB calls are slow (nothing to do with referential integrity, just the TOAST/DETOAST overhead), but since they also haven’t experienced how fast a DB with local disks and good data locality can be, they have no idea.

Re: PostgreSQL 17

#112

Post by sgarland »

nikita wrote: post #67

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 clo…

MySQL can be faster in certain circumstances (mostly range selects), but only if your schema and queries are designed to exploit InnoDB’s clustering index.

But even then, in some recent tests I did, Postgres was less than 0.1 msec slower. And if the schema and queries were not designed with InnoDB in mind, Postgres had little to no performance regression, whereas MySQL had a 100x slowdown.

I love MySQL for a variety of reasons, but it’s getting harder for me to continue to defend it.

Re: PostgreSQL 17

#113

Post by mind-blight »

brunoqc wrote: post #108
mind-blight wrote: post #103

Earlier quoted context omitted.

I've been using duckdb to import data into postgres (especially CSVs and JSON) and it has been really effective. Duckdb can run SQL across the different data formats and insert or update directly into postgres. I run duckdb with python and Prefect for batch jobs, but you can use whatever language or scheduler you perfer. I can't recommend this setup enough. The only weird things I've run into is a really complex join…

Thanks. My current pattern is to parse the files with rust, copy from stdin into a psql temp table, update the rows that have changed and delete the rows not existing anymore. I'm hoping it's less wasteful than truncating and importing the whole table every time there is one single change.

It probably is, but you can do that workflow with DuckDB too. It's just really flexible since it can all be done in SQL. Check out the DuckDB postgres connection (https://duckdb.org/docs/extensions/postgres.html)

To import from a Salesforce Bulk Export CSV (for example) into a postgres table is a few lines of SQL

``` INSERT INTO pgdb.accounts ( id, created_at, updated_at ) SELECT "Id", "CreatedDate"::TIMESTAMP, "UpdatedDate"::TIMESTAMP FROM read_csv('/path/to/file.csv') WHERE "Id" NOT IN (SELECT id FROM pgdb.accounts) ```

The path can be in a cloud storage provider as well, which is really nice. You can do updates which join from the postgres db or the other CSV files (or a MySQL database) as well. The data transforms (casting to timestamp, uuid, etc.) have been super handy along with all the other SQL niceties that you get

Re: PostgreSQL 17

#115

Post by bigger_cheese »

bradford wrote: post #72
gigatexal wrote: post #24

Earlier quoted context omitted.

What do you love so about PowerBI? I’ve not looked at it very closely. I’ve worked with Tableau and Looker and LookerPro. All of which seemed fine. Is it a home run if the end users aren’t on Windows or using Excel? I’m thinking about the feature where you can use a SQLServer DB as a data source to Excel.

> What do you love so about PowerBI? For a large portion of my career, the dashboarding solutions I've worked with have followed a similiar model: they provide a presentation layer directly on top of a query of some kind (usually, but not always, a SQL query). This seems like a natural next step for organizations that have a lot of data in one spot, but no obvious way of visualizing it. But, after implementing and ma…

My Org has been heavily pushing us to use Power BI, I have found it has a lot of pain points.

I expect how useful it is depends a lot on your use cases. If you want to throw a couple of KPI's on a dashboard it fulfils that requirement pretty well but if you want to do any analytics (beyond basic aggregations like, min, max and median) or slightly complicated trending (like multiple Y axis) Power BI is painfully complicated.

Re: PostgreSQL 17

#118

Post by systems »

bigger_cheese wrote: post #115
bradford wrote: post #72

Earlier quoted context omitted.

> What do you love so about PowerBI? For a large portion of my career, the dashboarding solutions I've worked with have followed a similiar model: they provide a presentation layer directly on top of a query of some kind (usually, but not always, a SQL query). This seems like a natural next step for organizations that have a lot of data in one spot, but no obvious way of visualizing it. But, after implementing and ma…

My Org has been heavily pushing us to use Power BI, I have found it has a lot of pain points. I expect how useful it is depends a lot on your use cases. If you want to throw a couple of KPI's on a dashboard it fulfils that requirement pretty well but if you want to do any analytics (beyond basic aggregations like, min, max and median) or slightly complicated trending (like multiple Y axis) Power BI is painfully compl…

PowerBI might be the only BI solution with a decent functional language

DAX is supper powerful, if you design your datawarehouse as a proper stat scheme, there is nothing you cannot calculate using DAX

Nothing in the market is even close, most BI tool have magic expression language, DAX is a proper language

Re: PostgreSQL 17

#119

Post by cjonas »

0cf8612b2e1e wrote: post #73
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…

A personal rule of mine is to always separate data receipt+storage from parsing. The retrieval is comparatively very expensive and has few possible failure modes. Parsing can always fail in new and exciting ways. Disk space to store the returned data is cheap and can be periodically flushed only when you are certain the content has been properly extracted.

Did you mean "retrieval is comparatively inexpensive"? I think I'm on the same page but this threw me off.

Re: PostgreSQL 17

#120

Post by bigger_cheese »

systems wrote: post #118
bigger_cheese wrote: post #115

Earlier quoted context omitted.

My Org has been heavily pushing us to use Power BI, I have found it has a lot of pain points. I expect how useful it is depends a lot on your use cases. If you want to throw a couple of KPI's on a dashboard it fulfils that requirement pretty well but if you want to do any analytics (beyond basic aggregations like, min, max and median) or slightly complicated trending (like multiple Y axis) Power BI is painfully compl…

PowerBI might be the only BI solution with a decent functional language DAX is supper powerful, if you design your datawarehouse as a proper stat scheme, there is nothing you cannot calculate using DAX Nothing in the market is even close, most BI tool have magic expression language, DAX is a proper language

I'm sure it can work but I've found it hard to slot into our use cases.

Power BI's trending is one thing I have really struggled with the limitations it has.

For one of my use cases (industrial plant data) we often want to trend timeseries data on a common xaxis with uniquely scaled yaxis.

i.e Temperature, Pressure, Flow Rate, Fill height etc all have differently scaled yaxis but common xaxis.

Something like this example https://imgur.com/a/zd5Giom

Power BI limits you to a single primary and a single secondary Y axis only (i.e limit of two scales).

Post reply on HN