Sql
Optimise PostgreSQL for fast testing
In today’s fast-paced software development landscape, the ability to iterate quickly is paramount. Slow testing cycles can become a significant bottleneck, hindering agility and delaying releases. For many applications, PostgreSQL serves as the robust and reliable database backend. However, the default configuration of PostgreSQL may not be optimized for the rapid, iterative nature of testing environments. This article will delve into practical strategies to optimise PostgreSQL for fast testing, enabling developers to run tests more frequently and efficiently, ultimately accelerating the development lifecycle. We will explore techniques covering everything from configuration tweaks to data management strategies, ensuring your PostgreSQL database isn’t the reason your testing grinds to a halt. The goal is to create a lean, mean, testing machine!
Understanding the Bottlenecks in PostgreSQL Testing
Before diving into specific optimization techniques, it’s crucial to understand where potential bottlenecks lie within your PostgreSQL testing environment. One common issue is the overhead associated with creating and destroying test databases. Each test run often requires a clean slate, leading to repetitive database creation and schema setup. This process can be time-consuming, especially for large databases with complex schemas. Another bottleneck arises from the data loading process. Populating test databases with realistic data is essential for effective testing, but importing large datasets can significantly slow down the process. Insufficient hardware resources, such as limited RAM or slow storage, can also contribute to performance issues. Finally, poorly written or unoptimized SQL queries within your tests can exacerbate performance problems, leading to longer test execution times. Identifying these bottlenecks is the first step towards implementing effective optimization strategies.
Another significant factor is the configuration of PostgreSQL itself. The default settings are often geared towards production environments, emphasizing data integrity and reliability over speed. While these are critical for production, they can be overly cautious for testing. For instance, fsync settings, which control how frequently data is written to disk, can have a significant impact on write performance. Similarly, the amount of memory allocated to PostgreSQL can affect query performance, particularly for complex queries involving large datasets. Understanding these configuration parameters and adjusting them appropriately can yield substantial improvements in testing speed. It’s also important to consider the version of PostgreSQL you are using. Newer versions often include performance improvements and bug fixes that can positively impact testing speed.
Finally, consider the overall testing strategy. Are you running integration tests that rely on a fully populated database, or are you focusing on unit tests that can be isolated and run against a smaller, more lightweight database? The type of tests you are running will influence the most effective optimization techniques. For example, if you are primarily running unit tests, you may be able to leverage in-memory databases or mock data to avoid the overhead of a full PostgreSQL instance altogether. By carefully analyzing your testing workflow and identifying the specific bottlenecks, you can tailor your optimization efforts to achieve the greatest impact.
Configuration Tweaks for Speed
Optimizing PostgreSQL’s configuration for testing involves adjusting parameters that prioritize speed over strict data durability, which is often acceptable in a testing environment. One key area is the fsync setting. In production, fsync is typically set to on to ensure data is written to disk immediately, preventing data loss in case of a system crash. However, for testing, setting fsync to off can significantly improve write performance. Setting fsync to off can dramatically speed up test execution, as the database doesn’t wait for writes to be physically flushed to disk. This is safe for temporary test databases that are recreated frequently. Of course, you should never do this in a production environment. This setting tells PostgreSQL to write data to the operating system cache instead of directly to disk, which is much faster. This can lead to potential data loss if the server crashes, but this is usually an acceptable risk in a testing context.
Another important parameter is synchronous_commit. Similar to fsync, synchronous_commit controls when a transaction is considered committed. Setting it to off allows PostgreSQL to return control to the client before the transaction is fully written to disk. This can improve write performance, but also increases the risk of data loss in case of a crash. For testing, setting synchronous_commit to off can be a worthwhile trade-off. Furthermore, adjusting the shared_buffers parameter can also improve performance. shared_buffers determines the amount of memory PostgreSQL uses for caching data. Increasing this value can improve query performance, especially for frequently accessed data. However, increasing shared_buffers too much can lead to memory contention, so it’s important to find a balance. A good starting point is to set shared_buffers to 25% of the system’s RAM.
Finally, consider tuning the wal_level parameter. wal_level controls the amount of information written to the Write-Ahead Log (WAL). Setting it to minimal reduces the amount of WAL data, which can improve write performance. However, it also disables certain features, such as point-in-time recovery. For testing, where data recovery is less critical, setting wal_level to minimal can be a reasonable choice. Remember to restart the PostgreSQL server after making any changes to these configuration parameters for the changes to take effect. Always document the changes you make, so you can revert them easily if needed.
Data Management Strategies for Faster Testing
Effective data management is crucial for optimizing PostgreSQL testing speed. One common strategy is to use database cloning. Instead of creating a new database from scratch for each test run, you can create a template database with the schema and basic data already loaded. Then, for each test run, you can quickly clone this template database, which is much faster than creating a new database from scratch. PostgreSQL provides built-in support for database cloning using the CREATE DATABASE … TEMPLATE command. This allows you to quickly create a copy of a template database, which can then be modified for each test run. This approach can significantly reduce the time it takes to set up test databases.
Another useful technique is to use data masking or anonymization. Instead of using real production data in your tests, you can use anonymized or masked data. This protects sensitive information and also allows you to create smaller, more manageable datasets for testing. There are various tools and techniques available for data masking, including SQL scripts and dedicated data masking software. By using anonymized data, you can reduce the size of your test databases and improve performance. Consider using tools like Faker ([https://fakerjs.dev/](https://fakerjs.dev/)) to generate realistic but fake data. This allows you to populate your test databases with data that resembles real-world data without exposing sensitive information.
Furthermore, consider using lightweight data fixtures. Data fixtures are pre-defined sets of data that are used to populate test databases. Instead of loading large datasets from files, you can use data fixtures to quickly insert the necessary data for each test. Data fixtures can be defined in various formats, such as SQL scripts or YAML files. By using lightweight data fixtures, you can reduce the overhead of data loading and improve test performance. For example, you might have a fixture that creates a few users with different roles, or a fixture that creates a set of products with different attributes. These fixtures can be easily loaded into your test database before each test run, providing a consistent and predictable testing environment. Here’s a quick guide to optimizing your database schema design from pganalyze: PostgreSQL Schema Design
Optimizing Test Code and Queries
Even with a well-configured PostgreSQL database and efficient data management strategies, poorly written test code and inefficient SQL queries can still hinder testing speed. One common mistake is to use overly complex or unoptimized SQL queries within your tests. Make sure to profile your queries and identify any performance bottlenecks. Use tools like EXPLAIN to analyze query execution plans and identify areas for improvement. Optimizing your queries can significantly reduce test execution time. Indexing is crucial. Ensure that your tables are properly indexed to speed up query execution. Pay attention to the queries that are frequently used in your tests and make sure that the relevant columns are indexed. However, avoid over-indexing, as too many indexes can slow down write operations.
Another important aspect is to minimize database interactions within your tests. Each database interaction adds overhead, so it’s important to reduce the number of interactions as much as possible. Use batch operations to perform multiple updates or inserts in a single database call. Avoid querying the database unnecessarily. Cache frequently accessed data in memory to reduce the number of database queries. Furthermore, consider using transactions to group multiple database operations into a single atomic unit. This can improve performance and ensure data consistency. Remember to commit or rollback transactions appropriately to avoid leaving the database in an inconsistent state. Here are some key things to remember:
- Profile your SQL queries using EXPLAIN.
- Ensure proper indexing on frequently queried columns.
- Minimize database interactions within your tests.
Finally, consider using mocking or stubbing to isolate your tests. Instead of relying on a real database for all your tests, you can use mock objects or stubs to simulate the behavior of the database. This can significantly speed up your tests and make them more deterministic. Mocking is particularly useful for unit tests, where you want to test individual components in isolation. By mocking the database, you can avoid the overhead of a real database and focus on testing the logic of your code. You can also use mocking to simulate different database scenarios, such as errors or timeouts, which can be difficult to reproduce with a real database. Tools like Mockito ([https://site.mockito.org/](https://site.mockito.org/)) or similar libraries can help you create mock objects and stubs for your tests. A well written test suite will drastically reduce the amount of debugging needed when something goes wrong. Testing early and often is paramount.
FAQ
- Q: Is it safe to disable fsync in a testing environment?
- A: Yes, it's generally safe to disable fsync in a testing environment because data loss is typically not a concern. However, ensure that you only disable it for temporary test databases.
- Q: How can I create a template database for cloning?
- A: You can create a template database using the CREATE DATABASE ... TEMPLATE command in PostgreSQL. First, create a database with the desired schema and data, then use this command to create a template from it.
- Q: What are some tools for data masking?
- A: There are several tools for data masking, including SQL scripts, dedicated data masking software, and libraries like Faker for generating realistic but fake data.
- Q: How important is indexing for testing?
- A: Indexing is crucial for testing as it speeds up query execution, especially for frequently accessed data. However, avoid over-indexing, as too many indexes can slow down write operations.
- Disable fsync and synchronous_commit in testing environments.
- Use database cloning to quickly create test databases.
By implementing these strategies, you can significantly reduce the time it takes to run your tests, allowing you to iterate more quickly and deliver higher-quality software. Remember to continuously monitor your testing performance and adjust your optimization efforts as needed. The key is to find the right balance between speed and data integrity for your specific testing environment. Don’t be afraid to experiment and try different approaches to see what works best for you. With a little effort, you can transform your PostgreSQL database from a testing bottleneck into a high-performance asset.
Ultimately, optimizing PostgreSQL for fast testing is an ongoing process. Take the time to analyze your current setup, identify areas for improvement, and implement the strategies outlined above. You’ll likely find that even small changes can have a significant impact on your testing speed. So, start experimenting, measure your results, and fine-tune your configuration. Ready to accelerate your testing? Dive in and start optimizing today! Maybe you’ll find your perfect testing setup. For more in-depth performance tuning tips, consider exploring the official PostgreSQL documentation or consulting with a database expert.
Question & Answer :
I am switching to PostgreSQL from SQLite for a typical Rails application.
The problem is that running specs became slow with PG.
On SQLite it took ~34 seconds, on PG it’s ~76 seconds which is more than 2x slower.
So now I want to apply some techniques to bring the performance of the specs on par with SQLite with no code modifications (ideally just by setting the connection options, which is probably not possible).
Couple of obvious things from top of my head are:
- RAM Disk (good setup with RSpec on OSX would be good to see)
- Unlogged tables (can it be applied on the whole database so I don’t have change all the scripts?)
As you may have understood I don’t care about reliability and the rest (the DB is just a throwaway thingy here).
I need to get the most out of the PG and make it as fast as it can possibly be.
Best answer would ideally describe the tricks for doing just that, setup and the drawbacks of those tricks.
UPDATE: fsync = off + full_page_writes = off only decreased time to 65 seconds (-16 secs). Good start, but far from the target of 34.
UPDATE 2: I tried to use RAM disk but the performance gain was within an error margin. So doesn’t seem to be worth it.
UPDATE 3:* I found the biggest bottleneck and now my specs run as fast as the SQLite ones.
The issue was the database cleanup that did the truncation. Apparently SQLite is way too fast there.
To “fix” it I open a transaction before each test and roll it back at the end.
Some numbers for ~700 tests.
- Truncation: SQLite - 34s, PG - 76s.
- Transaction: SQLite - 17s, PG - 18s.
2x speed increase for SQLite. 4x speed increase for PG.
First, always use the latest version of PostgreSQL. Performance improvements are always coming, so you’re probably wasting your time if you’re tuning an old version. For example, PostgreSQL 9.2 significantly improves the speed of TRUNCATE and of course adds index-only scans. Even minor releases should always be followed; see the version policy.
Don’ts
Do NOT put a tablespace on a RAMdisk or other non-durable storage.
If you lose a tablespace the whole database may be damaged and hard to use without significant work. There’s very little advantage to this compared to just using UNLOGGED tables and having lots of RAM for cache anyway.
If you truly want a ramdisk based system, initdb a whole new cluster on the ramdisk by initdbing a new PostgreSQL instance on the ramdisk, so you have a completely disposable PostgreSQL instance.
PostgreSQL server configuration
When testing, you can configure your server for non-durable but faster operation.
This is one of the only acceptable uses for the fsync=off setting in PostgreSQL. This setting pretty much tells PostgreSQL not to bother with ordered writes or any of that other nasty data-integrity-protection and crash-safety stuff, giving it permission to totally trash your data if you lose power or have an OS crash.
Needless to say, you should never enable fsync=off in production unless you’re using Pg as a temporary database for data you can re-generate from elsewhere. If and only if you’re doing to turn fsync off can also turn full_page_writes off, as it no longer does any good then. Beware that fsync=off and full_page_writes apply at the cluster level, so they affect all databases in your PostgreSQL instance.
For production use you can possibly use synchronous_commit=off and set a commit_delay, as you’ll get many of the same benefits as fsync=off without the giant data corruption risk. You do have a small window of loss of recent data if you enable async commit - but that’s it.
If you have the option of slightly altering the DDL, you can also use UNLOGGED tables in Pg 9.1+ to completely avoid WAL logging and gain a real speed boost at the cost of the tables getting erased if the server crashes. There is no configuration option to make all tables unlogged, it must be set during CREATE TABLE. In addition to being good for testing this is handy if you have tables full of generated or unimportant data in a database that otherwise contains stuff you need to be safe.
Check your logs and see if you’re getting warnings about too many checkpoints. If you are, you should increase your checkpoint_segments. You may also want to tune your checkpoint_completion_target to smooth writes out.
Tune shared_buffers to fit your workload. This is OS-dependent, depends on what else is going on with your machine, and requires some trial and error. The defaults are extremely conservative. You may need to increase the OS’s maximum shared memory limit if you increase shared_buffers on PostgreSQL 9.2 and below; 9.3 and above changed how they use shared memory to avoid that.
If you’re using a just a couple of connections that do lots of work, increase work_mem to give them more RAM to play with for sorts etc. Beware that too high a work_mem setting can cause out-of-memory problems because it’s per-sort not per-connection so one query can have many nested sorts. You only really have to increase work_mem if you can see sorts spilling to disk in EXPLAIN or logged with the log_temp_files setting (recommended), but a higher value may also let Pg pick smarter plans.
As said by another poster here it’s wise to put the xlog and the main tables/indexes on separate HDDs if possible. Separate partitions is pretty pointless, you really want separate drives. This separation has much less benefit if you’re running with fsync=off and almost none if you’re using UNLOGGED tables.
Finally, tune your queries. Make sure that your random_page_cost and seq_page_cost reflect your system’s performance, ensure your effective_cache_size is correct, etc. Use EXPLAIN (BUFFERS, ANALYZE) to examine individual query plans, and turn the auto_explain module on to report all slow queries. You can often improve query performance dramatically just by creating an appropriate index or tweaking the cost parameters.
AFAIK there’s no way to set an entire database or cluster as UNLOGGED. It’d be interesting to be able to do so. Consider asking on the PostgreSQL mailing list.
Host OS tuning
There’s some tuning you can do at the operating system level, too. The main thing you might want to do is convince the operating system not to flush writes to disk aggressively, since you really don’t care when/if they make it to disk.
In Linux you can control this with the virtual memory subsystem’s dirty_* settings, like dirty_writeback_centisecs.
The only issue with tuning writeback settings to be too slack is that a flush by some other program may cause all PostgreSQL’s accumulated buffers to be flushed too, causing big stalls while everything blocks on writes. You may be able to alleviate this by running PostgreSQL on a different file system, but some flushes may be device-level or whole-host-level not filesystem-level, so you can’t rely on that.
This tuning really requires playing around with the settings to see what works best for your workload.
On newer kernels, you may wish to ensure that vm.zone_reclaim_mode is set to zero, as it can cause severe performance issues with NUMA systems (most systems these days) due to interactions with how PostgreSQL manages shared_buffers.
Query and workload tuning
These are things that DO require code changes; they may not suit you. Some are things you might be able to apply.
If you’re not batching work into larger transactions, start. Lots of small transactions are expensive, so you should batch stuff whenever it’s possible and practical to do so. If you’re using async commit this is less important, but still highly recommended.
Whenever possible use temporary tables. They don’t generate WAL traffic, so they’re lots faster for inserts and updates. Sometimes it’s worth slurping a bunch of data into a temp table, manipulating it however you need to, then doing an INSERT INTO ... SELECT ... to copy it to the final table. Note that temporary tables are per-session; if your session ends or you lose your connection then the temp table goes away, and no other connection can see the contents of a session’s temp table(s).
If you’re using PostgreSQL 9.1 or newer you can use UNLOGGED tables for data you can afford to lose, like session state. These are visible across different sessions and preserved between connections. They get truncated if the server shuts down uncleanly so they can’t be used for anything you can’t re-create, but they’re great for caches, materialized views, state tables, etc.
In general, don’t DELETE FROM blah;. Use TRUNCATE TABLE blah; instead; it’s a lot quicker when you’re dumping all rows in a table. Truncate many tables in one TRUNCATE call if you can. There’s a caveat if you’re doing lots of TRUNCATES of small tables over and over again, though; see: Postgresql Truncation speed
If you don’t have indexes on foreign keys, DELETEs involving the primary keys referenced by those foreign keys will be horribly slow. Make sure to create such indexes if you ever expect to DELETE from the referenced table(s). Indexes are not required for TRUNCATE.
Don’t create indexes you don’t need. Each index has a maintenance cost. Try to use a minimal set of indexes and let bitmap index scans combine them rather than maintaining too many huge, expensive multi-column indexes. Where indexes are required, try to populate the table first, then create indexes at the end.
Hardware
Having enough RAM to hold the entire database is a huge win if you can manage it.
If you don’t have enough RAM, the faster storage you can get the better. Even a cheap SSD makes a massive difference over spinning rust. Don’t trust cheap SSDs for production though, they’re often not crashsafe and might eat your data.
Learning
Greg Smith’s book, PostgreSQL 9.0 High Performance remains relevant despite referring to a somewhat older version. It should be a useful reference.
Join the PostgreSQL general mailing list and follow it.
