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

PostgreSQL is the worlds’ best database

2ndquadrant.com

Post reply on HN
Print view View original

131–140 of 365 posts

Re: PostgreSQL is the worlds’ best database

#131

Post by mulander »

jka wrote: post #106
unnouinceput wrote: post #72

For most of the projects where the DB really mattered, throughout my 10+ freelancer carrier, it came down to one thing that client really cared about. Performance. Nothing else mattered, not license price, not whistles and bells, not hype. My clients wanted to have data in front of their eyes the same second when they clicked the button. And when you have a table with 100 million rows in it, and an application is not…

While I do love PostgreSQL (and PostGIS which is excellent at transforming, indexing and querying geographic content) - I feel like MSSQL should get a bit of a shout-out in relation to your comment on performance. An experienced database developer with help from "SET STATISTICS IO ON"[1] and query plans[2] can achieve incredible MSSQL query optimization results. PostgreSQL has good query plan output via the EXPLAIN[2…

PostgreSQL has track_io_timing and passing buffers will then include IO timing.

\set track_io_timing=on;

explain (analyze, verbose, buffers) your-query;

Additionally, it will also include things like times for triggers executed from the query.

Re: PostgreSQL is the worlds’ best database

#132

Post by philliphaydon »

oerpli wrote: post #27

I used PostgreSQL as student and during research and had to switch to Microsoft SQL Server as my company uses that since forever. For features I used on both, the average quality of the SQL Server implementation is between middling and pathetic (mostly the latter). I cannot recall a single instance where I thought "that's nice" or anything positive about how SQL Server does something. At best, it has been "this is no…

SQL Server has a ternary. So your example would be written like:

  select iif(a
Unsure what crosstabview is as I've never used it.

I hate sql server tho. Basic things like indexable arrays in postgresql make it amazing.

Re: PostgreSQL is the worlds’ best database

#133

Post by ants_a »

jka wrote: post #106
unnouinceput wrote: post #72

For most of the projects where the DB really mattered, throughout my 10+ freelancer carrier, it came down to one thing that client really cared about. Performance. Nothing else mattered, not license price, not whistles and bells, not hype. My clients wanted to have data in front of their eyes the same second when they clicked the button. And when you have a table with 100 million rows in it, and an application is not…

While I do love PostgreSQL (and PostGIS which is excellent at transforming, indexing and querying geographic content) - I feel like MSSQL should get a bit of a shout-out in relation to your comment on performance. An experienced database developer with help from "SET STATISTICS IO ON"[1] and query plans[2] can achieve incredible MSSQL query optimization results. PostgreSQL has good query plan output via the EXPLAIN[2…

Configuration parameter `track_io_timing = 'on'` will measure I/O time. And running `EXPLAIN (ANALYZE, BUFFERS)` will output per plan node buffer statistics and I/O time spent. On most modern system IO timing has no measurable overhead and should be permanently enabled. Collecting buffer statistics is also relatively cheap and could be enabled for all queries. For example the following configuration will get a log entry with an explain plan and per node I/O stats for every query above a threshold:

    shared_preload_libraries = 'auto_explain'
    auto_explain.log_min_duration = '5s'
    auto_explain.log_analyze = true
    auto_explain.log_buffers = true
    auto_explain.log_timing = false

Re: PostgreSQL is the worlds’ best database

#134

Post by mulander »

ants_a wrote: post #133
jka wrote: post #106

Earlier quoted context omitted.

While I do love PostgreSQL (and PostGIS which is excellent at transforming, indexing and querying geographic content) - I feel like MSSQL should get a bit of a shout-out in relation to your comment on performance. An experienced database developer with help from "SET STATISTICS IO ON"[1] and query plans[2] can achieve incredible MSSQL query optimization results. PostgreSQL has good query plan output via the EXPLAIN[2…

Configuration parameter `track_io_timing = 'on'` will measure I/O time. And running `EXPLAIN (ANALYZE, BUFFERS)` will output per plan node buffer statistics and I/O time spent. On most modern system IO timing has no measurable overhead and should be permanently enabled. Collecting buffer statistics is also relatively cheap and could be enabled for all queries. For example the following configuration will get a log en…

You don't need to log every auto explain. Just enable track_io_timing and pg_stat_statements and you get per query IO performance metrics much cheaper.

Table F.21. pg_stat_statements Columns

https://www.postgresql.org/docs/11/pgstatstatements.html

blk_read_time

double precision

Total time the statement spent reading blocks, in milliseconds (if track_io_timing is enabled, otherwise zero)

blk_write_time

double precision

Total time the statement spent writing blocks, in milliseconds (if track_io_timing is enabled, otherwise zero)

Re: PostgreSQL is the worlds’ best database

#135

Post by oerpli »

philliphaydon wrote: post #132
oerpli wrote: post #27

I used PostgreSQL as student and during research and had to switch to Microsoft SQL Server as my company uses that since forever. For features I used on both, the average quality of the SQL Server implementation is between middling and pathetic (mostly the latter). I cannot recall a single instance where I thought "that's nice" or anything positive about how SQL Server does something. At best, it has been "this is no…

SQL Server has a ternary. So your example would be written like: select iif(a Unsure what crosstabview is as I've never used it. I hate sql server tho. Basic things like indexable arrays in postgresql make it amazing.

This makes it somewhat easier on the eyes but is still 80% too verbose.

Crosstabview converts a table from:

  |a x 0
  |a y 1
  |a z 2
  |b x 1
  |b z 5
to

  |  x y z
  |a 0 1 2
  |b 1   5

Re: PostgreSQL is the worlds’ best database

#136

Post by irrational »

We were on Oracle for 15 years, but the cost was just too high. We decided to move to Postgres. We thought it wouldn’t be as good as Oracle (you get what you pay for, right?), but it ended up being better. Performance is better. Documentation is better (by far). Adherence to SQL standard is better. Many SQL queries are simpler. Null/empty string is sane. Etc.

Now I don’t have experience with MySQL, SQLServer, etc., but between Oracle and Postgres, Postgres is definitely the best database.

Re: PostgreSQL is the worlds’ best database

#137

Post by thequux »

mekster wrote: post #99

When I learned you cannot change the order of columns in PostgreSQL, it sounded like one of the smarter but slightly weird kid in a class room. On the other hand, MySQL, who's not as bright as the smarter kid, is easy to talk to and feels friendlier and is generally the more popular kid. I do appreciate the strictness of PostgreSQL but if I see small weird stuff, I tend to pick the one that is easier to get along wit…

I'll admit to not being an expert in databases, but I can't figure out why the order of columns in a database would be relevant, unless you're doing `select *` and indexing the resulting columns by number rather than name, which I've always found to be fragile to the point of being useless. What am I missing?

Re: PostgreSQL is the worlds’ best database

#138

Post by yjftsjthsd-h »

juped wrote: post #8
Jonnax wrote: post #2

This is advertising of course. But if I had to select an SQL DB postgres is my only choice. Perhaps I don't know enough about databases and their differences. Anyone have some pros and cons of others? Like why would I pick MySQL, Microsoft, Oracle, Maria etc over Postgres? Apart from support that you gotta pay for.

SQLite is also extremely useful, but it fills a different niche. (One of SQLite's slogans is "SQLite does not compete with client/server databases. SQLite competes with fopen().")

> SQLite competes with fopen()

... and wins, which surprised me when I found out.

Re: PostgreSQL is the worlds’ best database

#140

Post by BiteCode_dev »

Joeri wrote: post #84
BiteCode_dev wrote: post #62

Earlier quoted context omitted.

But that also means that: - the code is not reusable outside of a database setting. So not cacheable. - the code is not reusable accross different storage layers. So not portable. - the code may needs updating if the schema change, you can't abstract that - changing the logic means a db migration - testing the code requires a DB - tooling support to check that code si limited to SQL tooling, which is very weak, espec…

I agree, but I also see the benefits from the other side, so I’ll play devil’s advocate: - it is cached, in the database’s memory, where the cache can be invalidated automatically. It is better to cache views than data anyway. - it is portable to every platform postgres runs, which in practice means it will run everywhere. Portability between databases is overrated because it rarely happens in practice. - the access…

> it is cached, in the database’s memory, where the cache can be invalidated automatically. It is better to cache views than data anyway.

The cache may not be for the data in the data base, but for something else (task queue, calculation, user session, pre-rendering, etc). You effectively split your cache into several systems.

> it is portable to every platform postgres runs, which in practice means it will run everywhere. Portability between databases is overrated because it rarely happens in practice.

As I mentioned, for a project it doesn't matter. For a lib it does.

> the access control logic evolves together with the schema, guaranteeing they have an exact correspondence. This is a good thing.

Not always. Your access control logic could be evolving with your model abstraction layer, which frees you to make changes to the underlying implementation without having to change the control logic every time. It's also a good for your unit tests, as they are not linked to something with a side effect.

> integration tests should involve a live database

Integration tests are slow. You can run them as you develop.

> Have you looked at jetbrains datagrip?

It's fantastic. If you are willing to pay the price of it for and force it on your entire team. If you are on an open source project, will your expect that from all your contributors ?

High barrier of entry, with no modularity. Linters, formatters, debuggers, auto-importers, they all depend on that one graphical commercial product that is not integrated with your regular IDE and other tools.

Not to say it's not a good editor if you do write a lot of SQL, as JetBrains products are always worth their price.

Post reply on HN