PostgreSQL performance tuning

Our PostgreSQL consultants can advise you on how to optimize your database and make overall improvements in your database performance. If you are looking for PostgreSQL performance tuning because you want a fast, reliable database that simply works, we are here to help.

What CYBERTEC can do for you to boost your database performance:

  • Detect performance problems
  • PostgreSQL storage tuning
  • PostgreSQL configuration optimization
  • Tune postgresql.conf
  • Optimize memory settings
  • Improve partitioning and bulk loading
  • Fix missing indexes
  • Rewrite slow queries
Performance Tuning

PostgreSQL Storage Tuning

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 file system and the right RAID level can make a real difference, too. We will help you 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 root out 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  your memory settings, 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 requires some expertise. DBA’s need to make sure that autovacuum has enough worker processes available, but still uses the I/O system only in moderation in order to avoid interfering 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 has offered native table partitioning. Built in-partitioning has the advantage that you don’t have to use external tools anymore, and that partitioning does not have to be handled manually.

The idea behind partitioning is to split data into smaller chunks. The questions which naturally arise are:

  • When should developers start to partition a table?
  • How should a table be partitioned?
  • What are the benefits?
  • What are the downsides?

Our team of experts is here to answer exactly those questions, and can develop the partitioning strategy that best fits your needs.

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 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 positive 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 performance is bad or queries don’t produce the results that they should, our consulting team is here to help. We will check your system to figure out if your indexing can be improved.

Scaling PostgreSQL to billions of rows

If you have a large PostgreSQL database with billions of rows, you clearly need a clever scaling strategy, since a single PostgreSQL database server cannot store such a huge amount of data. PostgreSQL has the ability to scale out queries to dozens of servers.

CYBERTEC is the right partner for you when it comes to scaling. With our solutions, scaling to many servers is easily possible and we enable the analysis of large amounts of data in the shortest possible time.

Manage the growth of your database – scale PostgreSQL with CYBERTEC

Before scaling out, it is necessary to analyze the real needs of your company and determine where scalability is really needed, and which operations just need a little boost. The PostgreSQL ecosystem allows the user to scale out all important operations, including, but not limited to:

  • Scaling writes to millions of rows / second
  • Scaling reads to millions of transactions / second
  • Scaling analytical workloads to millions or even billions of rows / second

Professional help

Contact us today to receive your personal offer from CYBERTEC. We offer timely delivery, professional handling, and over 20 years of PostgreSQL experience.

CYBERTEC Logo white
CYBERTEC PostgreSQL International GmbH
Römerstraße 19
2752 Wöllersdorf

+43 (0) 2622 93022-0

Get the newest PostgreSQL Info & Tools

    This site is protected by reCAPTCHA and the Google Privacy Policy & Terms of Service apply.

    CYBERTEC PostgreSQL International GmbH
    phone-handsetmagnifiercrosscross-circle linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram