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.

PostgreSQL performance tuning chart

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:

PostgreSQL Performance Tuning

 

  • 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.

PostgreSQL Performance Tuning table partitioning

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:

  • range-partitioned
  • list-partitioned

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.

Breaking database performance barriers

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.

Professional help

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