In a row-store database engine, rows are stored in units called pages. Each page has a fixed header and contains multiple rows, with each row having a record header followed by its respective columns.
Buffer pool is a shared memory space between all the database processes where the pages are kept, the pages can also receives writes there too.
Index Only scans are very similar to index scans except that they scan only the indexes and do not touch the table data. This is possible only if the query contains the indexed column in both the SELECT and WHERE clauses.
Using SELECT * means that the database optimizer cannot choose index-only scans. For example, let’s say you need the IDs of students who scored above 90, and you have an index on the grades column that includes the student ID as a non-key, this index is perfect for this query.However, since you asked for all fields, the database needs to access the heap data page to get the remaining fields increasing random reads resulting in far more I/Os. In contrast, the database could have only scanned the grades index and returned the IDs if you hadn’t used SELECT *.
# To disable the index on session level run below query.
set enable_indexscan TO OFF;CREATE INDEX id_idx ON fake_data USING BTREE(id);EXPLAIN ANALYZE SELECT id FROM fake_data WHERE
fake_data.id = 1000;> CREATE TABLE grades(id SERIAL NOT NULL, g INT NOT NULL);
> CREATE INDEX grades_index ON grades(g);
> EXPLAIN ANALYZE SELECT COUNT(*) FROM grades WHERE g=30;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=231.09..231.09 rows=1 width=8) (actual time=4.709..4.711 rows=1 loops=1)
-> Index Only Scan using grades_index on grades (cost=0.42..206.75 rows=9733 width=0) (actual time=0.052..3.322 rows=10038 loops=1)
Index Cond: (g = 30)
Heap Fetches: 0
Planning Time: 0.207 ms
Execution Time: 4.774 ms
import org.postgresql.xa.PGXADataSource;
import org.postgresql.ds.common.BaseDataSource; PostgreSQLContainer container = ...; BaseDataSource dataSource = new PGXADataSource(); dataSource.setUrl(container.getJdbcUrl()); dataSource.setUser(container.getUsername()); dataSource.setPassword(container.getPassword()); dataSource.setDatabaseName(container.getDatabaseName());
import org.testcontainers.containers.PostgreSQLContainer;
1. org.testcontainers:testcontainersincludes core packages.2. org.testcontainers:postgresqlincludes PostgreSQL-specific packages. This dependency depends on database which your application is using. You will find the list of supported databases here.3. org.testcontainers:junit-jupiterintegrates JUnit with Testcontainers.
<properties>
<testcontainers.version>1.16.0</testcontainers.version>
</properties>
<dependency>
<groupId>org.testcontainers</groupId>
<artifactId>junit-jupiter</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.testcontainers</groupId>
<artifactId>postgresql</artifactId>
<scope>test</scope>
</dependency>
<dependencyManagement>
<dependencies>
<dependency>
<groupId>org.testcontainers</groupId>
<artifactId>testcontainers-bom</artifactId>
<version>${testcontainers.version}</version>
<type>pom</type>
<scope>import</scope>
</dependency>
</dependencies>
</dependencyManagement>dependencies {
...
testImplementation 'org.testcontainers:testcontainers'
testImplementation 'org.testcontainers:postgresql'
testImplementation 'org.testcontainers:junit-jupiter'
...
}
dependencyManagement {
imports {
mavenBom "org.testcontainers:testcontainers-bom:1.17.6"
}
}DockerImageName POSTGRES_IMAGE = DockerImageName.parse("debezium/example-postgres:1.7") .asCompatibleSubstituteFor("postgres"); PostgreSQLContainer container = new PostgreSQLContainer<>(POSTGRES_IMAGE) .withDatabaseName("postgres") .withUsername("postgres") .withPassword("postgres") .withConnectTimeoutSeconds(300) .withStartupTimeoutSeconds(300) .withStartupAttempts(5) .withCreateContainerCmdModifier(createContainerCmd -> {...}) container.start(); try (Connection connection = DriverManager.getConnection(container.getJdbcUrl(), container.getUsername(), container.getPassword())) { connection.setSchema("inventory"); try (Statement statement = connection.createStatement()) { ... } }
String jdbcUrl = container.getJdbcUrl() + "&user=" + container.getUsername() + "&password=" + container.getPassword(); // Output jdbc:postgresql://localhost:52135/test?loggerLevel=OFF&user=test&password=test
@Testcontainers @ContextConfiguration(classes = {PersistenceConfig.class}) @DataJpaTest @AutoConfigureTestDatabase(replace = AutoConfigureTestDatabase.Replace.NONE) public class StudentRepositoryJpaIT extends StudentRepositoryTest { @Container public static PostgreSQLContainer container = new PostgreSQLContainer("postgres:latest") .withDatabaseName("students_db") .withUsername("sa") .withPassword("sa"); @DynamicPropertySource public static void overrideProperties(DynamicPropertyRegistry registry){ registry.add("spring.datasource.url", container::getJdbcUrl); registry.add("spring.datasource.username", container::getUsername); registry.add("spring.datasource.password", container::getPassword); registry.add("spring.datasource.driver-class-name", container::getDriverClassName); } ... }
Integer hostPort = container.getFirstMappedPort();String host = container.getHost()import org.junit.jupiter.api.Test; import org.testcontainers.containers.PostgreSQLContainer; public class PostgresIntegrationTest { @Container private static final PostgreSQLContainer<?> postgresContainer = new PostgreSQLContainer<>("postgres:latest"); @Test void testDatabaseConnection() { // Get database connection properties from the container instance String jdbcUrl = postgresContainer.getJdbcUrl(); String username = postgresContainer.getUsername(); String password = postgresContainer.getPassword(); // Perform database tests using the connection properties // ... } }
You cannot choose which actual port is used on the host machine. TestContainers chooses one automatically and makes it accessible through getMappedPort(containerPort) method on the container instance. You can also use getFirstMappedPort() if you only have one.
Integer hostPort = container.getMappedPort(8080);
Integer hostPort = container.getMappedPort(PostgreSQLContainer.POSTGRESQL_PORT);
public static PostgreSQLContainer container = new PostgreSQLContainer<>("postgres:12.1") .withCommand("postgres -c max_prepared_transactions=10") .withCommand("postgres -c max_connections=500");
public static PostgreSQLContainer container = new PostgreSQLContainer<>("postgres:12.1") .withCommand("postgres -c max_prepared_transactions=10 -c max_connections=500");
var container = PostgreSQLContainer(postgresDockerImage)
.withDatabaseName("dataBaseName")
.withUsername("username")
.withPassword("password")
.withEnv(mapOf("PGDATA" to "/var/lib/postgresql/data"))
.withTmpFs(mapOf("/var/lib/postgresql/data" to "rw"))@Container private static final PostgreSQLContainer<?> postgresContainer = new PostgreSQLContainer<>(DockerImageName.parse("postgres:latest")) .withDatabaseName("mydb") .withUsername("user") .withPassword("password");