Is your PostgreSQL database slow? Do you see high latency, many slow query entries and are customers already complaining? If you are looking for a performance tuning because you simply want a fast, reliable database, you are on the right website.
PostgreSQL Storage Tuning
One part of performance tuning is storage tuning. A good storage performance is key to good PostgreSQL database performance. But storage is not the only thing: Even things such as the right filesystem and the right RAID level can make a difference and contribute to good performance.
We will help to improve your I/O system to match your requirements.
Do we have to buy more disks?
Our team of PostgreSQL consultants can answer that question – there is no need to buy more hardware unless it is explicitly necessary. PostgreSQL has all the means to figure out, where bottlenecks are. Our skilled eye 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 memory settings really makes sense. Customers have reported staggering performance improvements, which can really improve user experience.
Optimizing table partitioning
If tables grow beyond a certain size, it can make sense to partition your tables. 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 there is no need to do things manually.
In PostgreSQL 10.0 we will have:
In the future more features such as hash partitioning might be available.
The new syntax in PostgreSQL 10.0:
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?
Don’t worry. Our team of experts is here to answer exactly those questions.Contact us
Finding and fixing slow queries
The database is slow, please make it faster.
I think each of us has heard something like that in the past already. PostgreSQL offers all the means necessary to spot slow queries and to figure out exactly what is going on.
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 so that you can your developers and work on the real problems, which are slowing things down.
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.
Always check for missing indexes.
Whenever performance is bad and whenever queries feel somewhat small, contact our support team and we will check your system to figure out if indexing has been done properly.
Rewriting slow queries
If indexing, improving storage, changing memory parameters, and so on are still not enough – maybe rewriting a query can be helpful?
In some cases, small changes to a query can make a large difference (here is an example of such a change: http://www.cybertec-postgresql.com/func-hidden-performance-issues). Rewriting queries needs a lot of expertise, which we can gladly provide to customers around the globe.
Contact us today to receive your personal offer from Cybertec. We offer timely delivery, professional handling, and over 17 years of PostgreSQL experience.Contact us