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

351–360 of 365 posts

Re: PostgreSQL is the worlds’ best database

#351

Post by zzzcpan »

cryptonector wrote: post #350
jwr wrote: post #25

PostgreSQL is great if you: a) are OK with using SQL (this is not obvious) b) do not need a distributed database I've spent a lot of time on looking at database solutions recently, reading through Jepsen reports and thinking about software architectures. The problem with PostgreSQL is that it is essentially a single-point-of-failure solution (yes, I do know about various replication scenarios and solutions, in fact I…

IMO it should be possible to build a CRDT-style, eventually-consistent, distributed DB using PG. Normalize to the max, and then normalize more, have instances publish replication for log schemas, have a layer of instances that publish merged logs, and have instances subscribe to and apply (CRDT-style) those merged logs.

You can use CRDTs on top of pretty much any database and while they could simplify things, any database won't be optimized for specific constraints of CRDTs, so you won't be able to achieve certain levels of performance, availability, reliability and operational overhead possible with a well designed CRDT based system (I'm talking an order of magnitude difference here).

Re: PostgreSQL is the worlds’ best database

#352

Post by dpc_pw »

stickfigure wrote: post #303
dpc_pw wrote: post #257

Earlier quoted context omitted.

It might be possible write a small proxy in Rust, that would run next to a PG instance, accepted connections using `async` and then forwarded queries using some limited size connection pool.

These are active connections running transactions - unfortunately there's no substitute for just having another connection.

If there is a lot of connections actually running transactions, then I'd expect the db will become overwhelmed due to number of transaction, and per-connection overhead is not going to be a problem.

How I understood the problem is thousands of connections, of which most do a query from time to time only.

Judging by the other comments, it seems solutions like that are already available.

Re: PostgreSQL is the worlds’ best database

#353

Post by od0 »

yjftsjthsd-h wrote: post #146
ggm wrote: post #51

PostgreSQL and ZFS is a marriage made in heaven. Block aligns to disk behaviour, snapshots make great low latency db dumps trivial. Never regretted making the combo

And compression! I've gotten compression ratios above 3x on real production databases, which is the difference between "we need more disks by next month" and "this hardware will last the foreseeable future". Not to mention that it improves performance when lz4 can decompress faster than the disks can read.

I'm getting almost 4x on a 10TB production database! A little surprising the first time you see all the z_wr_iss processes flying :)

Re: PostgreSQL is the worlds’ best database

#354

Post by mfreed »

willvarfar wrote: post #336
mfreed wrote: post #329

Earlier quoted context omitted.

Hi @willvarfar: Just to avoid any confusion, TimescaleDB's compression is available in our community edition, which is fully free to use. Once you specify some compression configs, it's automated and transparent. https://www.timescale.com/products/features https://docs.timescale.com/latest/using-timescaledb/compress... (The "community" edition is available under our Timescale License. It's all source available and fr…

The cool thing about timescaledb is that timescale not Postgres controls the partitions, so who says they all have to be done the same way? In my use cases I have got recent partitions that are upsert heavy, so row-based works best. But as the partitions age, column-based would be better. What every DB seems to force me to do is use the same underlying format for all partitions. What I’d like is a storage engine that…

TimescaleDB mostly does exactly that: Recent data/partitions are maintained in row-based form, and after they reach some age, they are converted into more columnar compressed form.

Re: PostgreSQL is the worlds’ best database

#355

Post by stickfigure »

dpc_pw wrote: post #352
stickfigure wrote: post #303

Earlier quoted context omitted.

These are active connections running transactions - unfortunately there's no substitute for just having another connection.

If there is a lot of connections actually running transactions, then I'd expect the db will become overwhelmed due to number of transaction, and per-connection overhead is not going to be a problem. How I understood the problem is thousands of connections, of which most do a query from time to time only. Judging by the other comments, it seems solutions like that are already available.

Most of my transactions are relatively long-running (minutes) and mostly idle, so the db can handle the transaction load.

There honestly doesn't seem to be a good solution to this problem. I have reorganized my app a bit to try to keep the transactions shorter (mostly checkpointing) but it's using architecture to solve a fundamentally technical problem. I wouldn't have this problem with MySQL.

Switching from processes to threads (or some other abstraction) per connection isn't likely to show up in Postgres anytime soon, so I guess I'm willing to live with this... but I'm not going to pretend that Postgres is without some serious downsides.

Re: PostgreSQL is the worlds’ best database

#356

Post by anarazel »

stickfigure wrote: post #245

Postgres is my go-to RDBMS, but I do have one serious complaint: Connections are too expensive. This is a side-effect of the old-school one-process-per-connection architecture that Postgres uses. MySQL (ick) easily handles thousands of connections on small servers; with Postgres you will need a LOT of RAM to sustain the same, RAM that would be better served as cache. I've found (at least, for my current app) that the…

> with Postgres you will need a LOT of RAM to sustain the same

The per-connection memory overhead is not actually that high. If you configure huge_pages, it's on the order of ~1.5MB-2MB, even with a large shared_buffers setting.

Unfortunately many process monitoring tools (including top & ps) don't represent shared memory usage well. Most of the time each process is attributed not just the process local memory, but all the shared memory it touched. Even though it's obviously only used once across all processes.

In case of top/ps it's a bit easier to see when using huge pages, because they don't include huge pages in RSS (which is not necessarily great, but ...).

Edit: expand below

On halfway recent versions of linux /proc/$pid/smaps_rollup makes this a bit easier. It shows shared memory separately, both when not using huge pages, and when doing so. The helpful bit is that it has a number of 'Pss*' fields, which is basically the 'proportional' version of RSS. It divides repeatedly mapped memory by the number of processes attached to it.

Here's an example of smaps_rollup without using huge pages:

  cat /proc/1684346/smaps_rollup 
  56444bf26000-7fff2d936000 ---p 00000000 00:00 0                          [rollup]
  Rss:             1854392 kB
  Pss:              235614 kB
  Pss_Anon:           1420 kB
  Pss_File:            274 kB
  Pss_Shmem:        233919 kB
  Shared_Clean:      10700 kB
  Shared_Dirty:    1837560 kB
  Private_Clean:         0 kB
  Private_Dirty:      6132 kB
  Referenced:      1853428 kB
  Anonymous:          2664 kB
  LazyFree:              0 kB
  AnonHugePages:         0 kB
  ShmemPmdMapped:        0 kB
  FilePmdMapped:        0 kB
  Shared_Hugetlb:        0 kB
  Private_Hugetlb:       0 kB
  Swap:                  0 kB
  SwapPss:               0 kB
  Locked:                0 kB
You can see that RSS claims 1.8GB of memory. But the proportional amount of anonymous memory is only 1.4MB - even though "Anonymous" shows 2.6MB. That's largely due to that memory not being modified between postmaster and backends (and thus shared). Nearly all of the rest is shared memory that was touched by the process, Shared_Clean + Shared_Dirty.

With huge pages it's a bit easier:

  cat /proc/1684560/smaps_rollup 
  55e67544d000-7ffecebc9000 ---p 00000000 00:00 0                          [rollup]
  Rss:               13312 kB
  Pss:                1671 kB
  Pss_Anon:           1397 kB
  Pss_File:            274 kB
  Pss_Shmem:             0 kB
  Shared_Clean:      10656 kB
  Shared_Dirty:       1292 kB
  Private_Clean:         0 kB
  Private_Dirty:      1364 kB
  Referenced:        12312 kB
  Anonymous:          2656 kB
  LazyFree:              0 kB
  AnonHugePages:         0 kB
  ShmemPmdMapped:        0 kB
  FilePmdMapped:        0 kB
  Shared_Hugetlb:  1310720 kB
  Private_Hugetlb: 2379776 kB
  Swap:                  0 kB
  SwapPss:               0 kB
  Locked:                0 kB

Re: PostgreSQL is the worlds’ best database

#357

Post by topspin »

ptman wrote: post #338
topspin wrote: post #237

Earlier quoted context omitted.

Dbeaver is the answer. It is good enough that I no longer miss pgadmin 3. I've inadvertently become the pgadmin 3 "LTS" maintainer. A release of pgadmin 3 that was altered to support 10.x was previously provided by BigSQL. I forked it on GitHub to add TCP keepalive on client connections. At some point after that BigSQL removed the original repo. Apparently I was the only one that had forked it prior to removal, so no…

so do you consider your pgadmin fork maintained or not? where is it?

No. I'm making no effort to maintain it. If a pull request appeared I'd probably accept it. https://github.com/allentc/pgadmin3-lts

Amusingly it appears the previous maintainer, BigSQL, has since forked my repo.

Re: PostgreSQL is the worlds’ best database

#359

Post by Beefin »

jacques_chester wrote: post #240
Beefin wrote: post #223

Their JSON data type is atrocious, does anybody actually use it or is it a checkbox item?

You can join on arbitrary json fields, with indices. It is an extremely useful swiss army knife. I've used jsonb for performing ETL in-database from REST APIs. I know Concourse from 6.0 uses it for storing resource versions. They had an inputs selection algorithm that became stupidly faster because they can perform joins over JSON objects provided by 3rd-party extensions. Previously it was a nested loop in memory. ht…

Isn't it just a string? can you do nested queries? secondary indexes? really anything beyond just querying the key-value pairs?

Re: PostgreSQL is the worlds’ best database

#360

Post by insulanian »

apta wrote: post #344
insulanian wrote: post #268

Earlier quoted context omitted.

DataGrip is a bit intimidating at first, but much more powerful.

What features does it have over DBeaver?

First thing that comes to mind is code completion, which is unmatched in any other tool in the space.
Post reply on HN