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

Mastering PostgreSQL Administration [pdf]

momjian.us

Post reply on HN
Print view View original

41–50 of 56 posts

Re: Mastering PostgreSQL Administration [pdf]

#41

Post by SimonDorfman »

qwertox wrote: post #34

I have a bookmarking system built with MongoDB as the database. It has user and device management, real-time synchronization. I am seriously considering migrating it to PostgreSQL, rewriting the entire backend. One of the greatest things about MongoDB is the existence of Studio 3T, a really good administration software for MongoDB, and I wouldn't want to miss it. I spent a couple of years coding with MySQL and used p…

Azure Data Studio (which I like for Microsoft databases) has an extension to connect to Postgres. Maybe worth trying.

https://docs.microsoft.com/en-us/sql/azure-data-studio/what-...

https://docs.microsoft.com/en-us/sql/azure-data-studio/exten...

Re: Mastering PostgreSQL Administration [pdf]

#42

Post by garethm »

qwertox wrote: post #34

I have a bookmarking system built with MongoDB as the database. It has user and device management, real-time synchronization. I am seriously considering migrating it to PostgreSQL, rewriting the entire backend. One of the greatest things about MongoDB is the existence of Studio 3T, a really good administration software for MongoDB, and I wouldn't want to miss it. I spent a couple of years coding with MySQL and used p…

I use DataGrip with PostgreSQL and am generally happy with it.

Re: Mastering PostgreSQL Administration [pdf]

#43

Post by rpedela »

znpy wrote: post #24

I reconsidered mysql after switching jobs and seeing a multi-master clustered mysql database. Is anything similar available for postgresql?

If your data has an obvious shard key, like customer ID, then I suggest taking a look at Citus. Not exactly multi-master, but a good sharded solution for PG. There is a managed version of Citus on Azure too.

Re: Mastering PostgreSQL Administration [pdf]

#44

Post by 0df8dkdf »

christophilus wrote: post #2

I debated running my own Postgres cluster for a side project, but ended up paying for Digitalocean’s managed offering. I’d like to hear from anyone running their own Postgres. Why did you choose to do that vs pay for a managed instance? How much of a hassle has it been? Is it something you really need significant Postgres experience to do well (to achieve security, backups, high availability, etc)? Biggest gotchas to…

go with managed unless you have very specialised needs.

Re: Mastering PostgreSQL Administration [pdf]

#45

Post by 0df8dkdf »

0df8dkdf wrote: post #44
christophilus wrote: post #2

I debated running my own Postgres cluster for a side project, but ended up paying for Digitalocean’s managed offering. I’d like to hear from anyone running their own Postgres. Why did you choose to do that vs pay for a managed instance? How much of a hassle has it been? Is it something you really need significant Postgres experience to do well (to achieve security, backups, high availability, etc)? Biggest gotchas to…

go with managed unless you have very specialised needs.

by that I mean like extensions or things that require editing of config files.

Re: Mastering PostgreSQL Administration [pdf]

#46

Post by jdnier »

Here's the description for this PDF from the author's website: https://momjian.us/main/presentations/administration.html

  Mastering PostgreSQL Administration

  This talk is designed for PostgreSQL administrators.  It covers all aspects of PostgreSQL administration, including installation, security, file structure, configuration, reporting, backup, daily maintenance, monitoring activity, disk space computations, and disaster recovery.  It shows how to control host connectivity, configure the server, find the query being run by each session, and find the disk space used by each database.

Re: Mastering PostgreSQL Administration [pdf]

#47

Post by Wintereise »

3np wrote: post #16
fabian2k wrote: post #10

Earlier quoted context omitted.

The first consideration should be backups. This can be pretty easy if your database is small and you're okay with losing a few hours or a day of data if something happens. Then it's simply a matter of having a cron job dumping the database and backing those dumps up via some common backup tool. A proper backup with point-in-time recovery is not that hard to set up, but it does take some time to read up on the topic a…

> High availibility is a bit more complicated to set up with Postgres, and I don't have much experience with that part. There is Patroni[0], which has a Docker-based setup template in Spilo[1]. I don't have expertise enough to say anything about these, except that I've been running an HA setup with Spilo for several months without issues so far. Would be interesting to hear what more experienced postgres people have…

Patroni is rather rough around the edges to get going, but works fairly well once you do.

We manage a rather large cluster using it and for the most part, it works.

Getting functional PITR that you can restore rapidly is honestly our biggest gripe with PG. WAL-G/Barman certainly do work, but when your DB is measured in terabytes, even bootstrapping a replica is very slow.

Re: Mastering PostgreSQL Administration [pdf]

#48

Post by krnaveen14 »

riku_iki wrote: post #28
krnaveen14 wrote: post #17

Earlier quoted context omitted.

Our product offers near real time (incremental sync every 3 mins) backup and restore solution for on-premise SQLServer data. We have used PostgreSQL to store the backup data in cloud and offered add-on services on top of PostgreSQL data (such as reports, analytics, etc). Every customer data is stored as a separate database. Initially we have used RDS as PostgreSQL instance when the product is in pilot phase. RDS cost…

> we have reduced our cost by $1751.48 / month It doesn't look like a huge win given how much complexity you added, while RDS manages it for you.

I guess $1751 / month isn't a big deal in developed countries. But in India this is a lot. Also if I include the RDS Read Replica in total RDS cost, it comes down to $2627 / month (~ INR 1.93 Lakhs / month). Here this is equivalent to 5 Junior Developer Salary / month.

Based on our current customer base of backup and restore solution with addons, AWS spend is about 12% - 16% of the total product revenue. Our company has about 5000+ Active Customer base where the core product offering is different. Backup and Restore solution is itself an add-on. If we would have priced this considerably larger due to larger RDS spend, then it won't be surprising even if we get just only 10% of the current addon customers (700+).

Plus I would say this isn't a much complexity, everything is automated using Terraform and Ansible - pg installation, streaming replication setup, ZFS RAID0 setup, etc... Not a single command is executed in our EC2 Instances manually. The only benefit we get from RDS is the failover capability with minimal downtime. But for that, x4-x5 increased RDS cost isn't worth for us.

We still use RDS for OLTP and service databases, but not affordable for the backup offering.

Re: Mastering PostgreSQL Administration [pdf]

#49

Post by Wintereise »

krnaveen14 wrote: post #17
christophilus wrote: post #2

I debated running my own Postgres cluster for a side project, but ended up paying for Digitalocean’s managed offering. I’d like to hear from anyone running their own Postgres. Why did you choose to do that vs pay for a managed instance? How much of a hassle has it been? Is it something you really need significant Postgres experience to do well (to achieve security, backups, high availability, etc)? Biggest gotchas to…

Our product offers near real time (incremental sync every 3 mins) backup and restore solution for on-premise SQLServer data. We have used PostgreSQL to store the backup data in cloud and offered add-on services on top of PostgreSQL data (such as reports, analytics, etc). Every customer data is stored as a separate database. Initially we have used RDS as PostgreSQL instance when the product is in pilot phase. RDS cost…

Aren't you getting killed on inter-AZ bandwidth costs with streaming replication?

This was our experience when we tried it.

Re: Mastering PostgreSQL Administration [pdf]

#50

Post by krnaveen14 »

mthomassen wrote: post #32
krnaveen14 wrote: post #17

Earlier quoted context omitted.

Our product offers near real time (incremental sync every 3 mins) backup and restore solution for on-premise SQLServer data. We have used PostgreSQL to store the backup data in cloud and offered add-on services on top of PostgreSQL data (such as reports, analytics, etc). Every customer data is stored as a separate database. Initially we have used RDS as PostgreSQL instance when the product is in pilot phase. RDS cost…

I assume you're using ZFS filesystem for the transparant compression, what's your opninion/experience on using ZFS on cloud storage? I mean; the EBS disks are already redundantly stored by AWS and the COW mechanism could lead to a lot of write amplification; negatively impacting the network attached storage? (I don't use EBS in my day job, but Azure's disk offering don't really offer adequate perforamance when used w…

There is a small write amplification due to pg page size being 8KB and ZFS recordsize being 128KB, but considering the Bulk write nature there is not much impact. Also max IO size of EBS is 256KB which helps us to optimally utilise available IOPS even if there is write amplification. Reducing the ZFS recordsize significantly reduces compression ratio so we kept as it is. If it's an OLTP application, reducing the recordsize will improve latency but for these bulk operations, it's the most suitable.

I haven't used Azure, but based on my raw benchmark and real time usage, I would say type of FileSystem doesn't affect performance of EBS Volumes. Our peak IO usage is 1200 IOPS and 20 MB/s. I would say similar RDS configuration would have x4 - x10 write amplification due to data being not compressed.

Post reply on HN