Our PostgreSQL consultants can advise you on how to optimize your database and make adjustments to improve the overall database performance. If you are looking for a PostgreSQL performance tuning because you simply want a fast, reliable database, we are here to help.
What CYBERTEC can do for you to boost your database performance:
- Detecting performance problems
- PostgreSQL storage tuning
- PostgreSQL configuration optimization
- Tuning postgresql.conf
- Optimizing memory settings
- Improving partitioning and bulk loading
- Fixing missing indexes
- Rewriting slow queries
PostgreSQL Storage Tuning
A good storage performance is the key to good PostgreSQL database performance. But storage is not the only parameter that contributes to good performance: the right filesystem and the right RAID level can make a real difference, too. We will help to improve your I/O system to match your requirements.
Do we have to buy more disks?
There is no need to buy more hardware unless it is explicitly necessary. PostgreSQL has all the means to figure out, where bottlenecks are and if there are any reasons for adding hardware. Our experts can distinguish slowness caused by I/O and recommend solutions, which really make a difference.
Tuning PostgreSQL memory parameters
Are you wondering, which values to put into postgresql.conf? Don’t worry, we can tune postgresql.conf for you. If you are looking to improve memory setting, the following values will be relevant:
- shared_buffers: The PostgreSQL I/O cache
- work_mem: Parameter to tune sorting, grouping, etc.
- maintenance_work_mem: Speed up administrative tasks such as VACUUM, etc.
- temp_buffers: Speed up temporary tables
- effective_cache_size: Tell PostgreSQL about the amount of memory in your server.
- wal_buffers: More speed for the transaction log
Tuning VACUUM and autovacuum
The purpose of VACUUM is to remove dead rows from your tables and indexes. A good VACUUM policy protects your system from memory waste and improves performance significantly. If you are interested in how to tune autovacuum, check out this blogpost >>
Finding the right settings for autovacuum is requires some expertise. DBAs need to make sure that autovacuum has enough worker processes available, but still uses the I/O system only in moderation, in order to not interfere with running queries. Our PostgreSQL consultants are happy to help with configuring VACUUM and finding the optimal setup for your system.
Optimizing table partitioning
If tables grow beyond a certain size, it can make sense to partition them. Since the introduction of version 10.0, PostgreSQL offers native table partitioning. Built in-partitioning has the advantage that you don’t have to use external tools anymore and that partitioning has not to be handled manually.
The idea behind partitioning is to split data into smaller chunks. The questions naturally arising are:
- When should developers start to partition a table?
- How should a table be partitioned?
- What are the benefits?
- What is the downside?
Our team of experts is here to answer exactly those questions and can develop the partitioning strategy that fits your needs best.
Finding and fixing slow queries
No more struggling with slow queries
PostgreSQL offers all the means necessary to spot slow queries and to figure out, what exactly the problem is. Check out this blogpost and learn 3 ways to detect slow queries >>
If you are not sure what is really slow on your system, don’t worry – we are here to help you. We provide a tool called pgwatch2, which is there to help you and to spot problems. In addition to that, we can help to properly understand the data collected by PostgreSQL.
In some cases, small changes to a query can make a large difference and therefore rewriting a query can be really helpful and have a positiv impact on performance. Check out this blogpost for an example of such a change >>
Deploying missing indexes
Did you know that missing indexes cause at least half of all performance problems? A single missing index on a large table can seriously harm performance and reduce throughput in a counterproductive fashion. Check out this blog on indexing foreign keys >>
Always check for missing indexes.
Whether the performance is bad or queries feel somewhat small, our consulting team is here to help will check your system to figure out if indexing can be improved.
Contact us today to receive your personal offer from CYBERTEC. We offer timely delivery, professional handling, and over 20 years of PostgreSQL experience.Contact us >>